Optimised search of memo data.

[Alan Bourke]

Back in the olden days I believe there was a tool called PhdBase which
enabled quick or quick-ish searching of text data in memo fields. This
product appears to not be available any more. Can anyone recommend an
alternative?
Thx

--
Alan Bourke
alanpbourke (at) fastmail (dot) fm




--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

I found FoxWeb's solution so I'll have a look at that.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

I remember using that product for a search of work order text. That
product is known today as TrackIT but was yanked out of FPW in alpha
testing because the installer and application couldn't fit on a single
floppy disk when encrypted.

Crud that was 25 years ago. I bet if you found that fll it would still
work.

Post by Alan Bourke
I found FoxWeb's solution so I'll have a look at that.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYKyEakg0wYUYiOs81XEeTSG3OXtoRAshLHG-***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

Alan,
I posted a few messages about it some time ago (few years!) and I did manager to track down the author Jim Korenthal
Who did say he would be amenable to releasing the product as open source but I never heard anything more from him unfortunately.

I used it with VFP9 without any problems single user but it fell over when used in a multi-user environment.

Bear in mind there isn't much of its functionality you can't do (if not everything) using M$ SQL Express if you tie in the text search options.
I would certainly go this way for onward support, unless of course you track down Jim!

Dave

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Alan Bourke
Sent: 01 August 2018 11:31
To: ***@leafe.com
Subject: Optimised search of memo data.

Back in the olden days I believe there was a tool called PhdBase which enabled quick or quick-ish searching of text data in memo fields. This product appears to not be available any more. Can anyone recommend an alternative?
Thx

--
Alan Bourke
alanpbourke (at) fastmail (dot) fm




--- StripMime Report -- processed MIME parts --- multipart/alternative
text/plain (text body -- kept)
text/html
---

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@MM1P123MB0953.GBRP123.PROD.OUTLOOK.COM
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

Post by Dave Crozier
Bear in mind there isn't much of its functionality you can't do (if not
everything) using M$ SQL Express if you tie in the text search options.
I would certainly go this way for onward support, unless of course you track down Jim!

Thanks.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Stephen Russell]

SQL server select from a varchar or text data type is easy and fast.

I have 9 years of EDI inbound orders and outbound invoices that I can pound
through in no time at all. When I limit the search to the past few weeks it
is under a second to find a PO#

select * from EDIReceive
where edistring like '%OILWAR004%'
and dtmAdded >='06-01-2018'

The string could be 4000-16000 characters long depending on how many POs
are in a single envelope.

Post by Dave Crozier
Alan,
I posted a few messages about it some time ago (few years!) and I did
manager to track down the author Jim Korenthal
Who did say he would be amenable to releasing the product as open source
but I never heard anything more from him unfortunately.
I used it with VFP9 without any problems single user but it fell over when
used in a multi-user environment.
Bear in mind there isn't much of its functionality you can't do (if not
everything) using M$ SQL Express if you tie in the text search options.
I would certainly go this way for onward support, unless of course you track down Jim!
Dave
-----Original Message-----
Sent: 01 August 2018 11:31
Subject: Optimised search of memo data.
Back in the olden days I believe there was a tool called PhdBase which
enabled quick or quick-ish searching of text data in memo fields. This
product appears to not be available any more. Can anyone recommend an
alternative?
Thx
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
--- StripMime Report -- processed MIME parts --- multipart/alternative
text/plain (text body -- kept)
text/html
---

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYLkhWxw+tsaG8a-i+m8=mMho7M=VenN+=-***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

I was considering a simple MSSQL table with an nvharchar(max) field to hold the VFP memo contents, and full-text indexing on it. Then it is just a question of querying that from VFP, and refreshing the records in it from VFP on some sort of timed basis.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

It would seem that Jim has passed away according to my searches. I know he had movd to New York prior to my last communication and my searches gave up this:


Dave Crozier
Software Development Manager
Flexipol Packaging Ltd.

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Alan Bourke
Sent: 01 August 2018 16:05
To: ***@leafe.com
Subject: Re: Optimised search of memo data.

I was considering a simple MSSQL table with an nvharchar(max) field to hold the VFP memo contents, and full-text indexing on it. Then it is just a question of querying that from VFP, and refreshing the records in it from VFP on some sort of timed basis.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@MM1P123MB0953.GBRP123.PROD.OUTLOOK.COM
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

Oops... never sent a link:

http://www.tributes.com/obituary/show/James-Korenthal-91427622

His age would seem to be correct but it may well be someone else, in which case apologies.

Dave Crozier
Software Development Manager
Flexipol Packaging Ltd.

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Alan Bourke
Sent: 01 August 2018 16:05
To: ***@leafe.com
Subject: Re: Optimised search of memo data.

I was considering a simple MSSQL table with an nvharchar(max) field to hold the VFP memo contents, and full-text indexing on it. Then it is just a question of querying that from VFP, and refreshing the records in it from VFP on some sort of timed basis.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@MM1P123MB0953.GBRP123.PROD.OUTLOOK.COM
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Ted Roche]

Does the quantity of text or the number of records or searches justify all
the additional code and processes?

VFP is wickedly fast at text manipulation and searching. searching for
"Rabbit" in the full text (available from Project Gutenberg) returns 101
records out of 101 in 0.03 seconds on an old and small machine.

CREATE DATABASE textsearch
CREATE TABLE textsearch (iKey int autoinc, mText M, tUpdated T DEFAULT
DATETIME())
FOR I=1 TO 101
INSERT INTO textsearch (mText) VALUES (textfile)
NEXT
SELECT * FROM textsearch WHERE "Rabbit" $ mText
* returned 101 records in 0.03 seconds from a 15 Mb fpt

FOR I=1 TO 1000
INSERT INTO textsearch (mText) VALUES (textfile)
NEXT
SELECT * FROM textsearch WHERE "Rabbit" $ mText
* returned 1101 records in 0.33 seconds from a 150 Mb FPT
* quit VFP and restart for cache clearing: 0.63 seconds

Post by Alan Bourke
I was considering a simple MSSQL table with an nvharchar(max) field to

hold the VFP memo contents, and full-text indexing on it. Then it is just a
question of querying that from VFP, and refreshing the records in it from
VFP on some sort of timed basis.

Post by Alan Bourke
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4ticRqhdpYa6D6PubQXqRwQpq3ZyWy60DvxB0JjF3+***@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

Ted,
PHDbase went much further than straight text searches as you could match words within say 3 words of your target as well as many other options such as totally fuzzy matching. I used it successfully in a job recruitment piece of software to analyse CV's from prospective job seekers and it was blazingly fast even on Pentium II's which were new technology at the time and when I first contacted Jim. Funnily enough this thread lead me to search for Jim again and I discovered my post in FoxWikki where I remember I got Jim's email address....

http://fox.wikis.com/wc.dll?Wiki~JimKorenthal

As per Steven Black my email was/is being returned...

Dave


Dave Crozier
Software Development Manager
Flexipol Packaging Ltd.

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Ted Roche
Sent: 01 August 2018 16:36
To: ***@leafe.com
Subject: Re: Optimised search of memo data.

Does the quantity of text or the number of records or searches justify all the additional code and processes?

VFP is wickedly fast at text manipulation and searching. searching for "Rabbit" in the full text (available from Project Gutenberg) returns 101 records out of 101 in 0.03 seconds on an old and small machine.

CREATE DATABASE textsearch
CREATE TABLE textsearch (iKey int autoinc, mText M, tUpdated T DEFAULT
DATETIME())
FOR I=1 TO 101
INSERT INTO textsearch (mText) VALUES (textfile) NEXT SELECT * FROM textsearch WHERE "Rabbit" $ mText
* returned 101 records in 0.03 seconds from a 15 Mb fpt

FOR I=1 TO 1000
INSERT INTO textsearch (mText) VALUES (textfile) NEXT SELECT * FROM textsearch WHERE "Rabbit" $ mText
* returned 1101 records in 0.33 seconds from a 150 Mb FPT
* quit VFP and restart for cache clearing: 0.63 seconds

Post by Alan Bourke
I was considering a simple MSSQL table with an nvharchar(max) field to

hold the VFP memo contents, and full-text indexing on it. Then it is just a question of querying that from VFP, and refreshing the records in it from VFP on some sort of timed basis.

Post by Alan Bourke
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@MM1P123MB0953.GBRP123.PROD.OUTLOOK.COM
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

Post by Ted Roche
Does the quantity of text or the number of records or searches justify all
the additional code and processes?

Ted

Probably up to 100k records, each with a memo field ranging from a few words to about 4k of text, and in a multiuser context. It has to integrate into existing search functionality where users would be used to near-instant results.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Dave Crozier]

Alan,
My further searchings reveal that the Foxweb Full Text search facility may be of use to you:

http://www.foxweb.com/fwFullText/

No idea if it is still supported though.

Dave Crozier
Software Development Manager
Flexipol Packaging Ltd.

-----Original Message-----
From: ProFox <profox-***@leafe.com> On Behalf Of Alan Bourke
Sent: 01 August 2018 16:05
To: ***@leafe.com
Subject: Re: Optimised search of memo data.

I was considering a simple MSSQL table with an nvharchar(max) field to hold the VFP memo contents, and full-text indexing on it. Then it is just a question of querying that from VFP, and refreshing the records in it from VFP on some sort of timed basis.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@MM1P123MB0953.GBRP123.PROD.OUTLOOK.COM
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

[Alan Bourke]

Post by Dave Crozier
Alan,
My further searchings reveal that the Foxweb Full Text search facility

Yes it seems to do the job thanks.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.