On Wed, Feb 9, 2011 at 1:05 PM, Paul McNett <[email protected]> wrote: > On 2/9/11 10:59 AM, Sytze de Boer wrote: >> I have a table called WINHST >> This table has 10 indexes >> The 7th index is on a field called SERNO. Expression is upper(serno) >> >> trcask = the search expression >> Select * From winhst Where serno=trcask Order By invnum Into Table (myfile) > > Doesn't match the index expression, so it can't use the index. > > >> Question 1 >> In order to speed the search, should I do the following? >> sele winhst >> set order to 7 >> Select * From winhst Where serno=trcask Order By invnum Into Table (myfile) > > No. > >> Question 2 >> Does the "order by" clause slow down the select issue ? > > Probably a miniscule amount. > > What you really need to do is match the index expression in your where clause: > > {{{ > select * > from winhst > where trcask = upper(serno) > order by invnum > into table (myfile) > }}} > > Also, if you don't need all the fields from winhst, then don't select them. > Also, > you'll want an index on invnum. If the index expression is on upper(invnum), > then > your order by needs to match: > > {{{ > order by upper(invnum) > }}} > > It's been almost a decade since I've dealt with this, so please excuse any bad > information here. -------------------------------
VFP may be different but try to AVOID functions in SQL whenever possible. Utrcask = upper(trcasl) select * from winhst where serno = Utrcask order by invnum Your Orderby will cause a a secondary action and depending on the volume returned in your select it may be a burden or it may not. Either way you are probably getting a tablescan on the selected records. If the outcome is HUGE in the select _Tally why not index that new table you created on index# only and use it? -- Stephen Russell Sr. Production Systems Programmer CIMSgts 901.246-0159 cell _______________________________________________ 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.
