Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 15 total
Thread Filtering Very slow using DBISAM 4.26 Build 2
Mon, May 5 2008 10:53 AMPermanent Link

Francois Steyn
Hello,

We are using DBISAM 4.26 Build 2 and Delphi 7.  We have the following scenario:

Using a transaction table with 54000 records we apply the following filter: amount between
2000 and 2100
Resultset after filter: 715 records
Amount is not indexed, executed using dbsys over network (1gigabit) using normal windows
file shares(dedicated server)
SQL Statement: select * from debttran where amount between 2000 and 2100

DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds
DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked)
DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked)

DBISAM 3.27: Table with Filter: Takes 9 Seconds
DBISAM 3.27: SQL: takes 0.328 Seconds (with want live checked)
DBISAM 3.27: SQL: takes 0.485 Seconds (without want live checked)

Any ideas??

If you need the data, we can provide some test data.

Thanks!
Francois Steyn
Mon, May 5 2008 11:34 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Francois,

> DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds
> DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked)
> DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked)

Did you execute the test actions in the listed order?
What happens if you change the order - do you notice any significant
change in times?

--
Fernando Dias
[Team Elevate]
Mon, May 5 2008 11:42 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Francois,

<< DBISAM 4.26 Build 2: Table with Filter: Takes 27 Seconds
DBISAM 4.26 Build 2: SQL: takes 1.266 Seconds (with want live checked)
DBISAM 4.26 Build 2: SQL: takes 3.625 Seconds (without want live checked)

DBISAM 3.27: Table with Filter: Takes 9 Seconds
DBISAM 3.27: SQL: takes 0.328 Seconds (with want live checked)
DBISAM 3.27: SQL: takes 0.485 Seconds (without want live checked)

Any ideas?? >>

What happens if you add an ORDER BY to the SELECT statement that is
equivalent to the current index order for the table ?   The results should
be the same as with the table.  IOW, the majority of the time isn't being
spent in the filtering, it's being spent in reconciling the filtered set to
the active index order for the purposes of re-positioning the current row.
This has been discussed many times here on the newsgroups.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, May 6 2008 1:56 AMPermanent Link

Francois Steyn
Hello,

Fernando Dias, i did execute the steps as they are listed, but it doesnt matter which
order since those are averages.  I ran all the tests multiple times with no difference.
The queries were executed, then unprepared and re-executed.

Tim Young, the index of the table is the primary key, which is an auto number.  If im not
mistaken, when you omit the order by, it will be ordered by the order of entry, which will
be equivalent to the auto number.  If the majority of the time isnt spent filtering, but
ordering, i still dont see why filtering is soo much slower than the query.  I reckon both
test cases share the same properties.  Also, i dont see why DBISAM3 is 3-4 times faster
than DBISAM4.  I thought this was suppose to be the other way around.  How can I speed up
my test results??

Thanks for all the support!

Kind Regards
Francois
Tue, May 6 2008 5:24 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Francois,

> Fernando Dias, i did execute the steps as they are listed, but it doesnt
> matter which
> order since those are averages.  I ran all the tests multiple times with
> no difference.
> The queries were executed, then unprepared and re-executed.

Are you running the SQL tests mantaining the filtered table open in dbsys?
Because if you are, I think it can explain all - data is being buffered so
the subsequent operations will run much faster than the first one. That's
why I asked you to change the execution irder: execute first an SQL query,
leave it open and then apply the filter to the table - I suspect you will
obtain very different results, caused by the same : buffering.

--
Fernando Dias
[Team Elevate]

Tue, May 6 2008 6:14 AMPermanent Link

Francois Steyn
Hello,

I was running all the tests keeping DBSYS and tables open.

I ran all the test again.  I closed DBSYS after every test and still got the same results.

How would I switch on buffering in DBSYS?

Kind Regards
Francois
Tue, May 6 2008 6:42 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Francois,

> I was running all the tests keeping DBSYS and tables open.
> I ran all the test again.  I closed DBSYS after every test and still got
> the same results.

I find this really strange...
Maybe Tim can bring some light to this because now I'm really confused.
I was really expecting that changing the order should change the results.

> How would I switch on buffering in DBSYS?
It's always on.

--
Fernando Dias
[Team Elevate]

Tue, May 6 2008 8:00 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Francois


The buffering Fernando is talking about is Windows buffering. When you open a table and run a query or set a filter Windows may buffer large chunks of the table essentially meaning that subsequent operations do not require disk access. Even closing the table or flushing the buffers down to disk may not (I believe) clear these buffers. In the event of a flush the OS would have to check to make sure they were current at least.

The only way I know to "even" out the effect is reboot the PC after each test. Running each test several times and only taking the last result is also a good way, but not guaranteed unless you have a magic wand that makes Windows do just what you want.

Roy Lambert [Team Elevate]
Tue, May 6 2008 8:25 AMPermanent Link

Francois Steyn
Hello Roy,

All this still does not explain why a table with filter is slower than a query with a
where clause, both working on order of entry or a secondary index.

Is DBISAM3 suppose to be faster than DBISAM4?  Am I missing something with my tests?


Kind Regards
Francois Steyn
Tue, May 6 2008 8:54 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Francois

>All this still does not explain why a table with filter is slower than a query with a
>where clause, both working on order of entry or a secondary index.

It does if the filter is executed and then the query is run without a reboot.

Table size - 216218 records

filter: _BoxNo between 100 and 250 - 57k results c1.6 secs
sql - select * from mandn where _boxno between 100 and 250
canned c130 secs
live c.2 secs

rerun filter after sql and c.0 secs

These are about the sort of results I'd expect, especially since I have another app hitting the disk

>Is DBISAM3 suppose to be faster than DBISAM4?

No

>Am I missing something with my tests?

Yes.

Can you post the table structure and query plans?

Roy Lambert [Team Elevate]

Page 1 of 2Next Page
Jump to Page:  1 2
Image