RE: Quickest Search

Back to top

Quickest Search

Author: Sytze de Boer

Posted: 2014-09-02 22:33:27 Link

I have a large stock file (say 10,000 records)

I only have a vague idea what I'm searching for

So, I may have a variable like

srch4="upper(name+code+stkmemo+unitmeas2+altsupply+descopt2+descopt3+descopt4+descopt5+bar2code+bar3code+alt2code)"

Is there a better/quicker way to CONSTRUCT the following

Select Code, desc, price, cost, qoh from winstoks Into Table temp order By

Code where At(lcSearch,&srch4)>0

--

Kind regards,

Sytze de Boer

Kiss Software

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: ProFox@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/CAG1nNy8QCyGHzBoFAg6taRyfzDS5TymXOPrAXOpMnP5BchbDAA@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.

New Search

Back to top

RE: Quickest Search

Author: Lee Fakes

Posted: 2014-09-02 23:25:06 Link

> Date: Wed, 3 Sep 2014 15:33:27 +1200

> Subject: Quickest Search

> From: sytze.kiss@gmail.com

> To: profoxtech@leafe.com

>

> I have a large stock file (say 10,000 records)

> I only have a vague idea what I'm searching for

>

> So, I may have a variable like

>

> srch4="upper(name+code+stkmemo+unitmeas2+altsupply+descopt2+descopt3+descopt4+descopt5+bar2code+bar3code+alt2code)"

>

> Is there a better/quicker way to CONSTRUCT the following

>

> Select Code, desc, price, cost, qoh from winstoks Into Table temp order By

> Code where At(lcSearch,&srch4)>0

>

I had a similar problem a while back and solved it by using seperate where clauses.

I found I was able to optimise the statements by placing the indexed fields first and it prevented cross field matches.

Try something like

Select Code, desc, price, cost, qoh ;

from winstoks ;

where ;

(lcSearch $ name) OR ;

(lcSearch $ code) OR ;

(lcSearch $ stkmemo) OR ;

(lcSearch $ unitmeas2) OR ;

(lcSearch $ altsupply) OR ;

(lcSearch $ descopt2) OR ;

(lcSearch $ descopt3) OR ;

(lcSearch $ descopt4) OR ;

(lcSearch $ descopt5) OR ;

(lcSearch $ bar2code) OR ;

(lcSearch $ bar3code) OR ;

(lcSearch $ alt2code) ;

order By Code ;

Into Table temp

Regards

Lee

> --

> Kind regards,

> Sytze de Boer

> Kiss Software

>

> --- StripMime Report -- processed MIME parts ---

> multipart/alternative

> text/plain (text body -- kept)

> text/html

> ---

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@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/BLU175-W334246C919BA589072562885C40@phx.gbl

** 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.

New Search

Back to top

Re: Quickest Search

Author: Man-wai Chang

Posted: 2014-09-03 08:31:34 Link

It's called a full-text search.

In Clipper (the xBase compiler) world, there was a commercial add-on

library for just this purpose. :)

On Wed, Sep 3, 2014 at 11:33 AM, Sytze de Boer <sytze.kiss@gmail.com> wrote:

> I have a large stock file (say 10,000 records)

> I only have a vague idea what I'm searching for

> So, I may have a variable like

--

.~. Might, Courage, Vision. SINCERITY!

/ v \ 64-bit Ubuntu 9.10 (Linux kernel 2.6.39.3)

/( _ )\ http://sites.google.com/site/changmw

^ ^ May the Force and farces be with you!

_______________________________________________

Post Messages to: ProFox@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/CAGv=MJAGEmtRgvu3HfUMFTAU_grY_f59K-Y=cqOSNKEiYEA3Ug@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.

New Search

Back to top

Re: Quickest Search

Author: Michael Glassman

Posted: 2014-09-03 09:54:09 Link

I solved a similar problem by realizing that items in stock and other such tables rarely get updated. So I added a field called "Searchable" that contains the concatenation of all of the possibly searchable fields. Yes, your file will be larger (though 10,000 record files aren't really very large to start with), and you'll take a one-time hit when you populate this field for all 10K records, but those drawbacks are negligible compared to the time you'll save by only having to search one field in the future. Just don't forget to alter your insert and update routines to include populating the new field.

Mike

Michael H. Glassman

IS Manager

Pioneer Drama Service, Inc.

www.PioneerDrama.com

--------------------------------------------------------------------------------

From: Sytze de Boer

Sent: Tuesday, September 02, 2014 9:33 PM

To: profoxtech@leafe.com

Subject: Quickest Search

I have a large stock file (say 10,000 records)

I only have a vague idea what I'm searching for

So, I may have a variable like

srch4="upper(name+code+stkmemo+unitmeas2+altsupply+descopt2+descopt3+descopt4+descopt5+bar2code+bar3code+alt2code)"

Is there a better/quicker way to CONSTRUCT the following

Select Code, desc, price, cost, qoh from winstoks Into Table temp order By

Code where At(lcSearch,&srch4)>0

--

Kind regards,

Sytze de Boer

Kiss Software

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: ProFox@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/B4C296C1E9194A118F633B4ACCB18EEB@Studio17

** 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.

New Search

Back to top

Re: Quickest Search

Author: Alan Bourke

Posted: 2014-09-03 10:44:15 Link

On Wed, 3 Sep 2014, at 03:54 PM, Michael Glassman wrote:

> Just don't forget to alter your insert and update

> routines to include populating the new field.

Or you could use a field or table update trigger.

There used to be a full-text search product for VFP called PhdBase but I

don't know if it's being updated.

--

Alan Bourke

alanpbourke (at) fastmail (dot) fm

_______________________________________________

Post Messages to: ProFox@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/1409759055.1309051.163201937.024B3A00@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.

New Search

Back to top

RE: Quickest Search

Author: John Weller

Posted: 2014-09-03 11:40:13 Link

I used it years ago but when I needed it for a later application I couldn't

find it. A quick google shows that Dave Crozier used it with VFP9.

John Weller

01380 723235

07976 393631

>

> There used to be a full-text search product for VFP called PhdBase but I

don't

> know if it's being updated.

_______________________________________________

Post Messages to: ProFox@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/002f01cfc795$bcebfab0$36c3f010$@johnweller.co.uk

** 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.

New Search

Back to top

Re: Quickest Search

Author: Stephen Russell

Posted: 2014-09-03 11:46:47 Link

i used it with FPWin in 94. It has been long gone for a long time.

On Wed, Sep 3, 2014 at 11:40 AM, John Weller <john@johnweller.co.uk> wrote:

> I used it years ago but when I needed it for a later application I couldn't

> find it. A quick google shows that Dave Crozier used it with VFP9.

>

> John Weller

> 01380 723235

> 07976 393631

>

> >

> > There used to be a full-text search product for VFP called PhdBase but I

> don't

> > know if it's being updated.

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@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/CAJidMY+awhdRiO-9UVn-oYLbs=v0tqWLOZDspM9mtGNLm8YjBg@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.

New Search

Back to top

Re: Quickest Search

Author: AndyHC

Posted: 2014-09-03 16:30:13 Link

On 03/09/2014 15:54, Michael Glassman wrote:

> I solved a similar problem by realizing that items in stock and other such tables rarely get updated. So I added a field called "Searchable" that contains the concatenation of all of the possibly searchable fields. Yes, your file will be larger (though 10,000 record files aren't really very large to start with), and you'll take a one-time hit when you populate this field for all 10K records, but those drawbacks are negligible compared to the time you'll save by only having to search one field in the future. Just don't forget to alter your insert and update routines to include populating the new field.

>

> Mike

>

> Michael H. Glassman

> IS Manager

> Pioneer Drama Service, Inc.

> www.PioneerDrama.com

>

> --------------------------------------------------------------------------------

>

> From: Sytze de Boer

> Sent: Tuesday, September 02, 2014 9:33 PM

> To: profoxtech@leafe.com

> Subject: Quickest Search

>

> I have a large stock file (say 10,000 records)

> I only have a vague idea what I'm searching for

>

> So, I may have a variable like

>

> srch4="upper(name+code+stkmemo+unitmeas2+altsupply+descopt2+descopt3+descopt4+descopt5+bar2code+bar3code+alt2code)"

>

> Is there a better/quicker way to CONSTRUCT the following

>

> Select Code, desc, price, cost, qoh from winstoks Into Table temp order By

> Code where At(lcSearch,&srch4)>0

>

#bad# database design - sorry!

_______________________________________________

Post Messages to: ProFox@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/54078865.8020700@hawthorncottage.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.

New Search

Back to top

Re: Quickest Search

Author: Gene Wirchenko

Posted: 2014-09-03 22:25:59 Link

[attributions likely mucked]

At 14:30 2014-09-03, AndyHC <jarndice@gmail.com> wrote:

>On 03/09/2014 15:54, Michael Glassman wrote:

>>From: Sytze de Boer

[snip]

>>I have a large stock file (say 10,000 records)

>>I only have a vague idea what I'm searching for

>>

>>So, I may have a variable like

>>

>>srch4="upper(name+code+stkmemo+unitmeas2+altsupply+descopt2+descopt3+descopt4+descopt5+bar2code+bar3code+alt2code)"

"name" is a reserved word. You should use a different name.

(BTW, according to the help, "naptime" is a reserved word. A

search shows it occurs only in the reserved word list. What gives?)

You could get a false positive when the search value spans more

than one column. Given

column1: "con"

column2: "trived"

searching on column1+column2 for "cont" will give a hit.

>>Is there a better/quicker way to CONSTRUCT the following

>>

>>Select Code, desc, price, cost, qoh from winstoks Into Table temp order By

>>Code where At(lcSearch,&srch4)>0

>#bad# database design - sorry!

Oh? What would you do given a requirement to be able to search

several of a table's columns? Or did you mean something else?

Sincerely,

Gene Wirchenko

_______________________________________________

Post Messages to: ProFox@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/

** 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.

New Search

Back to top

Re: Quickest Search

Author: Alan Bourke

Posted: 2014-09-04 03:00:17 Link

On Thu, 4 Sep 2014, at 04:25 AM, Gene Wirchenko wrote:

> (BTW, according to the help, "naptime" is a reserved word. A

> search shows it occurs only in the reserved word list. What gives?)

Ha, so it is! That's weird.

--

Alan Bourke

alanpbourke (at) fastmail (dot) fm

_______________________________________________

Post Messages to: ProFox@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/1409817617.1569779.163493021.111CA524@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.

New Search