V4.24

I'm experimenting with a transaction table that will grow over the years. I've created a test with c350k records to check out speed. Essentially the table will record the results of calls (including the fact that the person called was not available).

I don't know if its just Windows or network buffering but I'm getting some interesting results.

I seeded the table with a few records where I know the call number (either 9 or 11)  and tried running the following

select * from perfstats where _fkcalls = 9 order by _date desc, _time desc

When _fkcalls is 9 the DBSys reported time is c.07secs but if its 11 then its c.8secs it 10x longer. The only germane difference is that the 9's are all fairly near each other in the table in primary index order whilst the 11's are scattered throughout - I deliberately made sure some were are either "end" of the table.

I can sort of accept that but then I tried opening the table, setting the index to decending date/time order and setting a filter - result felt instant for both 9's and 11's

Finally I (for some reason) ran the SQL with the table still open in DBSys with the index set to the decending date/time and for both 9's and 11's the DBSys time is zero. Then I closed the table and ran the sql and back to the previous times.

The sql was all done asking for and receiving a live result set, and without unpreping the query (Canned took between 3 - 4 secs for both)

Any thoughts / comments / observations as to what will happen with ElevateDB?

Roy Lambert

Roy,

<< When _fkcalls is 9 the DBSys reported time is c.07secs but if its 11 then
its c.8secs it 10x longer. The only germane difference is that the 9's are
all fairly near each other in the table in primary index order whilst the
11's are scattered throughout - I deliberately made sure some were are
either "end" of the table. >>

Did you run some query plans on these tests ?  If so, could you post them ?

<< Any thoughts / comments / observations as to what will happen with
ElevateDB? >>

It will be faster....... ?   Actually, until I see the query plan(s), I
can't really say what is going on or what is responsible.  It could just
very well be an issue with the speed of the Windows file system due to the
organization of the table files on disk, and once the table files have been
cached by Windows, the speed improves.  This actually happens quite often.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Being thick I forgot - just tried again ran queries in the sequence as shown by the plans below so if there was any improvement due to caching I'd expect it to benefit the second one.

I'm surprised since I thought DBISAM would use the compound index (CREATE INDEX IF NOT EXISTS "MostRecentFirst" ON "PerfStats" ("_Date" DESC,"_Time" DESC)) ;I have for ordering but no. I tried creating two new descending indices for _Date and _Time but still the same sort of result just a bit faster (plans 3 & 4 below).


================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

select * from perfstats where _fkcalls = 9 order by _date desc, _time desc

Tables Involved
---------------

perfstats (perfstats) table opened shared, has 354590 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

_Date DESC
_Time DESC

WHERE Clause Execution
----------------------

The expression:

_fkcalls = 9

is OPTIMIZED, covers 42 rows or index keys, costs 529 bytes, and will be
applied to the perfstats table (perfstats) before any joins

================================================================================
>>>>> 42 rows affected in 0.078 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

select * from perfstats where _fkcalls = 11 order by _date desc, _time desc

Tables Involved
---------------

perfstats (perfstats) table opened shared, has 354590 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

_Date DESC
_Time DESC

WHERE Clause Execution
----------------------

The expression:

_fkcalls = 11

is OPTIMIZED, covers 47 rows or index keys, costs 592 bytes, and will be
applied to the perfstats table (perfstats) before any joins

================================================================================
>>>>> 47 rows affected in 0.344 seconds
================================================================================


================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

select * from perfstats where _fkcalls = 9 order by _date desc, _time desc

Tables Involved
---------------

perfstats (perfstats) table opened shared, has 354590 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

_Date DESC
_Time DESC

WHERE Clause Execution
----------------------

The expression:

_fkcalls = 9

is OPTIMIZED, covers 42 rows or index keys, costs 529 bytes, and will be
applied to the perfstats table (perfstats) before any joins

================================================================================
>>>>> 42 rows affected in 0.046 seconds
================================================================================

================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

select * from perfstats where _fkcalls = 11 order by _date desc, _time desc

Tables Involved
---------------

perfstats (perfstats) table opened shared, has 354590 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

_Date DESC
_Time DESC

WHERE Clause Execution
----------------------

The expression:

_fkcalls = 11

is OPTIMIZED, covers 47 rows or index keys, costs 592 bytes, and will be
applied to the perfstats table (perfstats) before any joins

================================================================================
>>>>> 47 rows affected in 0.234 seconds
================================================================================



Roy Lambert

Tim

Any thoughts?

Roy Lambert

Roy,

<< Any thoughts? >>

Sorry about that - I missed your response in all of my machine woes this
week.  As far as the timings are concerned, differences below one second can
be caused by just about anything on the machine from longer seek times in
the files due to fragmentation to blips in the file system caching.   If the
records being retrieved are scattered throughout the table, then I would
guess that the first description would fit (seek times).  As for the
descending index issue, I'm not sure what is going on short of you not
setting RequestLive=True.  I tried the same thing here:

================================================================================
SQL statement (Executed with 4.24 Build 1)
================================================================================

SELECT * FROM customer
ORDER BY City DESC, State DESC

Tables Involved
---------------

customer (customer) table opened shared, has 55 rows

Result Set Generation
---------------------

Result set will be live

Result set will consist of one or more rows

Result set will be ordered by the secondary index Test for the table
customer

================================================================================
>>>>> 55 rows affected in 0 seconds
================================================================================

and it worked fine.  The index 'Test' was defined as:

CREATE INDEX Test ON Customer (City DESC, State DESC)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


PC's are great when they go wrong aren't they

Seek times (41Mbn dat, 122Mb idx) could have an impact, but fragmentation is improbable since it was a brand new file, and the one I did the testing on was a copy on a USB drive intended to deliberately slow things down.

I've gone for a TDBISAMTable and a filter since it was consistently fast for either case (faster than sql as well)

Roy Lambert