>>>Hello,
>>>
>>>I am using Visual FoxPro 6.0. I am making a search-form. On that search form there are a couple of textboxes, a search-button and a grid. If I fill in the textboxes and press the search-button a SELECT-command is being executed and the result of the search will be showed in the grid. This is all working fine, but there is only one problem. The problem is that it is taking to long to show the results.
>>>
>>>The SELECT-command is using a couple of tables at the same time. The main table, 23000 records, has some fields with only numbers in them. Those numbers are also stored in sub-tables along with the descriptions of the numbers. Those descriptions has to be showed in the grid. The problem is that it is taking to long to show the results. Is there something I am doing wrong? Or is there a faster way to show the results. The SELECT-command I am using is located at the end of this message. Thank you in advance for your time.
>>>
>>>SELECT Bedrijf.DossierNr, ;
>>> UPPER(Bedrijf.BedrNaam), ;
>>> UPPER(Straat.Om), ;
>>> ALLTRIM(Bedrijf.Nummer), ;
>>> StatBedr.Om_NL, ;
>>> VestType.Om_NL ;
>>>FROM Bedrijf, ;
>>> Straat, ;
>>> StatBedr, ;
>>> VestType ;
>>>WHERE Bedrijf.Straat = Straat.Straat ;
>>> AND Bedrijf.StatBedr = StatBedr.StatBedr ;
>>> AND Bedrijf.VestType = VestType.VestType ;
>>> AND &mSeekDosNr ;
>>> AND &mSeekBedrNm ;
>>> AND &mSeekHandNm ;
>>> AND (&mSeekBranch1 ;
>>> OR &mSeekBranch2 ;
>>> OR &mSeekBranch3 ;
>>> OR &mSeekBranch4 ;
>>> OR &mSeekBranch5) ;
>>> AND &mSeekStraat ;
>>> AND &mSeekStatus ;
>>> AND &mSeekDatIns1 ;
>>> AND &mSeekDatIns2 ;
>>>ORDER BY 2 ;
>>>INTO CURSOR Result
>>
>>Firstly, using UPPER,ALLTRIM you slow down the query. Secondly, you have too many filter conditions. Basically, you should build common &cWherestring and it will include all AND/ORs only in case when all serach textboxes will be filled. Surely, you should provide Rushmore optimization having TAG for each join/filter expression, or at least for the most commnoly-used ones. Also, it's helpful to have tag on DELETED(),at least in master table. The last straw, which might be tried after all other options, is to move joints from SELECT to Grid.Column.Controlsources
>
>Now I have changed my SELECT-command to check if it going faster if I only use
>one table and one textbox, but I still have to wait as long as the first time before I get my results. I realy don't get it.
>
>SELECT Bedrijf.DossierNr, ;
> UPPER(Bedrijf.BedrNaam) ;
> FROM Bedrijf ;
> WHERE UPPER(Bedrijf.BedrNaam);
> =ALLTRIM(THISFORM.txtBedrNaam.VALUE) ;
> ORDER BY 2 ;
> INTO CURSOR Result

Afier, you changed WHERE clause including UPPER,ALLTRIM there, and if you get the same time, it may indicate only that you didn't use Rushmore in both cases. So first thing you can do is to use SYS(3054) to check Rushmore. If result is negative, then you should provide Rushmore optimization for you query. It means that all join and filter condition expression should be literally the same as some table tag expresssion. For example, in your second case, table 'bedrijf' should be indexed on 'UPPER(bedrnaam)'. Also, you still have to use DELETED() tag.