slow query ..Help

[PHINOJOSA]

good night family

I have a csdb file of more than 3000 records that I query with Selcase, but the query on the cell phone is very slow, is there any way to make the query faster?

Atte. Paul Hinojosa

[justinlakier]

Hello,

You can use StartsWith to optimize a search of your database by making sure it only searches through relevant cases. The "where" clause only filters the cases that SelCase has already found, your "StartsWith" needs to be after the dictionary name in the SelCase function, as the dictionary access parameters page on SelCase and StartsWith mentions.

Hope this helps,
Justin

[PHINOJOSA]

Hi Justinlakier.

Thanks for your suggestion, I have tried but without success, this is my original code. Could you tell me where to make the changes I put in front as indicated but it gives me an error.

Code: Select all

PROC CPI_19
if ! inAdvance() and length(strip($))=0 then 
    if selcase("Digite el alimento que busca:", T2_DICT,"") include(ALIMENTO2, CARACTERISTICAS2, CONDICION2, FUENTE) where pos(tolower(strip(CPI_19)),tolower(ALIMENTO2)) then 
        if  ALIMENTO2 <> 'NO EXISTE' then 
            CPI_19 = ALIMENTO2; 
            CPI_20A = CONDICION2; 
            CPI_21 = PESO_G; 
            CPI_28 = PCT_PC; 
        endif; 
    endif; 
endif;

Thank you very much for your time.

Atte.
PHM

[justinlakier]

Hello,

Startswith() works to filter on the dictionary keys. It looks like this is not possible since you are filtering by whether a non-key item (ALIMENTO2) contains a particular substring (CPI_19). In this case, you cannot improve this filtering with Startswith().

If you are experienced with SQL, you can query the SQLite file (using the .csdb) and then load each applicable case to get the values you want in the include block of the Selcase, so the Selcase only has the user select from included options your query has filtered. See here for more information on SQL in CSPro.

Hope this helps,
Jusin