Page 1 of 1
slow query ..Help
Posted: April 16th, 2024, 10:52 pm
by 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
Re: slow query ..Help
Posted: April 17th, 2024, 10:48 am
by 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
Re: slow query ..Help
Posted: June 5th, 2024, 4:34 pm
by 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
Re: slow query ..Help
Posted: June 6th, 2024, 12:41 pm
by 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