SQL query time 1

[herbstgy]

Greetings, Everyone...

as some of you may remember, there was this c0000005 error in this form.

the c0000005 error has been successfully solved with your help by putting the grid source in a temporary cursor.
now my problem is this:
the SQL command which fills the grid's cursor looks like this:

SELECT emslog.*, tmstcode.uzenet, tmaccode.osztaly, tmaccode.rgb, tmaccode.szkod, ;
 gepek.cim_varos, gepek.cim_utca, gepek.ceg ;
 FROM (.emsname) emslog ;
 LEFT JOIN gepek ON emslog.gepszam=gepek.gepszam ;
 LEFT JOIN tmstcode ON emslog.uzkod=tmstcode.uzkod AND gepek.tipus=tmstcode.kodtip2 ;
 LEFT JOIN tmaccode ON tmstcode.actcode=tmaccode.uzkod ;
 WHERE &filt ;
 ORDER BY 2,3 ;
 INTO CURSOR emstemp READWRITE

yeah, I put it together from 4 tables, but that's not the problem...

the main table (emslog) looks like this:

[pre]
Structure for table: N:\NAPLO\WATCHMAN\EMS2101.DBF
Number of data records: 515071
Date of last update: 2021.01.17
Code Page: 1250
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 GEPSZAM Numeric 8 Asc Machine No
2 DATUM Date 8 Asc Machine No
3 IDO Character 12 No
4 UZKOD Numeric 5 No
** Total ** 34
[/pre]

this table by the 2nd half of the month grows to ~515000 records and by the end of the month ~800000 records.

the trouble is the following:
I do two kinds of queries for the users, one by the ATM ID (gepszam N(8) field) or one for specific EMS code (uzkod N(5) field), and I give the filter parameter to the query in the "WHERE &filt" clause.
When I do the query for the "gepszam" field, the query runs in an acceptable ~0.5sec time, which also allows refreshing through a timer for a near-real-time log watching.
But when I do the query for the "uzkod" field (both are numeric fields) the same query would run for some 20 seconds, which prevents the automatic refreshing.

I don't know, what causes the difference between the query times. the table is the same, only the filter field's position (1st vs 4th) differs. can this cause this huge difference in the query time?

 

[Chris Miller]

Indexes will be the primary accelerator for any queries. I assume you have indexed such a table growing that large. I wonder if you actually include datetime in your query to limit the result to one page only.

The only issue you can have with large tables and multiple conditions is setting too low memory by SYS(3050). In a table with half a million records rushmore needs half a million bits per clause that's index optimized. And this memory usage accumulates with caching, so don't make this too low.

I'm talking about each single clause of a where clause no matter whether you combine it with other conditions with AND or OR, no matter if it's a WHERE or JOIN condition. VFP only takes as much RAM as you tell it to take, every further need will be swapped to TEMP and that can cost more time than it saves.

Chriss

 

[herbstgy]

Dear Chris,

yes, there are index on "gepszam" and "datum" fields.
but I've read somewhere in the help that SQL SELECT ignores indexes.
I can try to index on "uzkod" too to see if it accelerates the query.

 

[herbstgy]

I can try to index on "uzkod" too to see if it accelerates the query.

hey, it works! thanks!

(actually, what I've read in the help was that SQL ignores SET ORDER, not indexes completely.)

 

[Chris Miller]

SQL ignores SET ORDER and SET FILTER because Rushmore optimization determines indexes it uses itself. Plus usage of cached data. It all boils down to determining the record numbers to fetch and then only read those from the table.

Rushmore takes into account multiple indexes, if it helps, and has its bitmap boolean logic to determine records to fetch. And VFP also uses indexes to optimize an ORDER BY and then the TOP clause based on that sorting, ie SELECT Top 20 * FROM TABLE ORDER BY logdatetime DESC, if you have such a field, would be the ideal monitoring query (I know you have further clauses and joins, but you have a chronological order).

There is one of these SYS() functions to have a minimal "SHOW PLAN", I don't remember whether there's a mode showing index optimizations for ORDER BY, but I know Rushmore doesn't tell you everything it does through this output.

Chriss