>I am trying to use a grid to display my results from a select. My WHERE is complex so I understand that it rules out using parameterized views. Currently, I am changing the grid recordsource to "", removing the cursor previously linked to the grid, selecting the records into to a new cursor (reusing the old cursor name), and then changing my recordsource back to the cursor name. The SELECT is executed very fast but when I attempt to repopulate the grid, the less records selected, the slower my results. ( Ex. 650 records < sec., 200 records > sec., 36 records several sec.s)
Without your structure and SQL only a few well known tips here (I'm afraid you've already done these) :
- If you have index key use it in SQL expressions. ie : If you have a tag with a key like cCategory + cSubCat and want to deal with just cCategory , [ cCategory + cSubCat = cSearchCat ] is faster than [ cCategory = cSearchCat ] for the latter doesn't use index.
- Close indexes before issuing SQL.
- Create a dummy tag with an index key deleted().
- Check Rushmore optimization level with sys(3054) and rearrange query if necessary.
Some strange advices follow (they work in particular situations) :
- Use more than one SQL with simple expressions. (Select a set into a cursor and do another SQL on that reduced set).
- Create a routine that would do your own optimization for that particular application
( This sometimes leads to no SQL just use of the original table ).
- Instead of SQL, create a cursor and use something like :
if seek(cSearchexp,cAlias,cTag)
set order to tag cTag
scan while eval(cKeyexp) = cSearchexp
if cExtraExpression
.....
if cSkipexpr
loop
endif
if cCutExpr
exit
endif
else
.......
endif
.....
scatter memvar
insert into mycursor from memvar
endif.
Cetin