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.
