--- In [email protected], Svein Erling Tysvær <svein.erling.tysvaer@...> wrote: > > Returning only 500 records, it is probably better not to use the index for > ANAME. You might change your query to: > > SELECT > ID, ANAME > FROM MYTABLE > WHERE ANAME LIKE '%TEST%' > AND DEL+0 = 0 > ORDER BY ANAME||'' > > I don't know how much it will improve,
Great tip :) !!! The DB is on the client side, so i ask him to test Your sugestion: Old version: Prepare time / Total execution time 0.096s / 29.742s 0.172s / 23.428s 0.175s / 24.891s 0.190s / 30.911s 0.183s / 25.198s 0.181s / 37.458s New version (with ||''): Prepare time / Total execution time 0.038s / 2.686s 0.031s / 2.116s 0.031s / 1.800s 0.033s / 1.760s 0.032s / 1.837s P.S. Instead of 150s now there is 30s in original version - I guess during previous tests server was under heavy load. > I was surprised about you saying that it was 100 times > quicker without the ORDER BY. Are you sure that you measured > how much longer it took to return the entire result set, > and not just the first few rows? I would expect I'm not sure how to test that, that statistics from FR: Starting transaction... Preparing query: SELECT ID, ANAME FROM MYTABLE WHERE ANAME LIKE '%TEST%' AND (1=0 OR DEL=0) ORDER BY ANAME Prepare time: 0.181s Field #01: MYTABLE.ID Alias:ID Type:INTEGER Field #02: MYTABLE.ANAME Alias:ANAME Type:STRING(250) PLAN (MYTABLE ORDER ANAME_IDX) Executing... Done. 510103 fetches, 375 marks, 68902 reads, 346 writes. 0 inserts, 0 updates, 0 deletes, 97174 index, 0 seq. Delta memory: -45104 bytes. Total execution time: 37.458s Script execution finished. Plan for the new version of query is: PLAN SORT ((MYTABLE NATURAL)) <><><> So, what all this means ? Using large index for sorting small resultset is ineffective ? > HTH, > Set Thanks a lot for Your Help ! b
