Thank you very much for your assistance I have now discovered a new problem. When I do the select statement, 1st time is great Now I repeat the search, and it simply chokes my pc
I use task manager to quit Vfp, and come back in I do a complex Locate from the command line. All is well I repeat the same command It freezes my pc I have tried this a dozen time and each time I have to use task manager to close the app I have disabled the AV program. No difference This is precisely what 1 client is complaining about It is 4 am Going back to bed S On Mon, Jan 24, 2011 at 7:55 PM, Grigore Dolghin <[email protected]> wrote: > For some unknown reason there are two missing end-of-line marks in my > previous message :( I'll try posting the message again: > > ============== > #1. UPPER(), STR(), ALLTRIM() are a killer in SELECTs - they must be > eliminated. > #2. If I correctly understood the table structure - the trcask value is a > part of various fields and you're looking for it using AT() in a > concatenation of those fields. That means, let's say, if first WHERE clause > returns > 0, you know the trcask value is there but you don't know in which > field you found it. You don't need to know where anyway because you're going > to show the entire record anyway. > > I would try improving this by removing all the functions, splitting the > concatenations, indexing on actual expressions and using LIKE on each > expression: > > Index on Upper(stockcode) tag stockcode > Index on Upper(desc) tag desc > Index on Upper(story) tag story > Index on client tag client > Index on Upper(ordref) tag ordref > Index on Upper(project) tag project > Index on Upper(chknum) tag chknum > Index on ttlamount tag ttlamount && note the missing alltrim(str()) - LIKE > doesn't need that > Index on invnum tag invnum Index on upper(serno) tag serno Index on > upper(courdets) tag courdets && this field is not described in fields list; > if it's numeric there's no need for UPPER() > > And the actual select would be > > trcAsk = "%" + trcAsk + "%" && to make the value work with LIKE - it would > find the partial strings > > Select * from winhst ; Where ; > StockCode Like trcask Or ; > Desc Like trcask Or; > Story Like trcask Or ; > Client Like trcask Or ; > .... you get the idea. > > ============== > > > >> -----Original Message----- >> From: [email protected] [mailto:profoxtech- >> [email protected]] On Behalf Of Sytze de Boer >> Sent: Monday, January 24, 2011 5:13 AM >> To: [email protected] >> Subject: VFP-9 SQL speed >> >> I would appreciate some comments regarding extraction speed In my system >> is a history file where some clients now have > million records >> >> Amongst other fields, here are the ones they need to search on and display >> >> client C/10, indexed >> stockcode C/15 indexed >> desc C/100 >> story M >> ordref C/10 >> project C/15 >> chknum C/15 >> ttlamount N/10,2 >> invnum N/10 >> serno C/10, indexed >> >> This is the current routine wich is too slow (trcask=the matter they > searched >> for) >> >> Select * From winhst ; >> where At(trcask,stockcode+Upper(Desc)+Upper(story)+client)>0 ; >> or At(trcask,Upper(ordref)+Upper(Project)+Upper(chknum)) > 0 ; >> or At(trcask,Alltrim(Str(ttlamount,10,2)))>0 Or >> At(trcask,Alltrim(Str(invnum)))>0 ; >> or At(trcask,Upper(serno)+Upper(courdets))>0 ; >> into cursor tmp >> >> I'm sure there's a better way ? >> >> -- >> Regards >> Sytze de Boer >> Kiss Systems >> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** 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.
