Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 15 of 15 total
Thread Filtering Very slow using DBISAM 4.26 Build 2
Tue, May 6 2008 9:30 AMPermanent Link

Francois Steyn
Hello Roy,

Attached is the debttran plan and structure created by DBSYS.

Can you please explain what I am missing in my tests.  I have done multiple tests, so if
buffering was changing the results, it would change all the results.

>>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.
OK, but.... the filter is giving me the same results every time and the query too...
Meaning if the query would benefit from the filter (buffer) the filter should have the
same benefit, right?

Is your data situated on a fileserver on the network or local disk?

Ive just executed the sql first using dbsys, then opened the debttran table and applied
the filter... Still the same results... Query is 1.3 secs and Filter takes 27secs....

Can you please explain why I would get far better results using DBISAM3?

Kind Regards
Francois



Attachments: debttran-plan.txt
Tue, May 6 2008 10:34 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Francois

>Can you please explain what I am missing in my tests.

Not yet, and possibly never Smiley

>I have done multiple tests, so if
>buffering was changing the results, it would change all the results.

Possibly. What are the results, just for filtering, if you run it say 3 times in DBSys?

>>>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.

No, there I think you have to accept what Tim is saying - its down to the table having to reconcile back to the index - a query doesn't.

>>It does if the filter is executed and then the query is run without a reboot.
>OK, but.... the filter is giving me the same results every time and the query too...
>Meaning if the query would benefit from the filter (buffer) the filter should have the
>same benefit, right?

I do wish that was the case Smileybut in the land of M$ its not necessarily true.

>Is your data situated on a fileserver on the network or local disk?

Those were local, and against an integer indexed field. I'd missed the fact that you were selecting from a non-indexed field, over a network. All that means is that you could have multiple levels of buffering.

>Ive just executed the sql first using dbsys, then opened the debttran table and applied
>the filter... Still the same results... Query is 1.3 secs and Filter takes 27secs....

This is something that does puzzle me. I've never been able to get really consistent results usually between 10% and 50% different on different runs.

>Can you please explain why I would get far better results using DBISAM3?

Nope. Unfortunately I don't have V3 installed any more, but as a guess (TIM) it could be the way floats are compared.

One other thing I would like you to try is add an index to the amount field and see what results you get then.

Roy Lambert [Team Elevate]
Wed, May 7 2008 12:34 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Francois,

<< Attached is the debttran plan and structure created by DBSYS. >>

Okay, first of all the query plan says that you're executing it with 4.24,
not 4.26.  However, that won't make much difference.

Secondly, the query is un-optimized, and is requiring a brute-force scan of
the table.  The reason that 4.x is slower than 3.x is because 4.x has a
property called TableMaxReadLockCount in the TDBISAMEngine component that
allows for the engine to release a read lock during brute-force table scans
in order to improve concurrency.  3.x doesn't do this, and so requires a lot
less read locks for a table scan, but at the price of reduced concurrency
(no writing can take place during the table scan):

http://www.elevatesoft.com/manual?action=mancompprop&id=dbisam4&product=d&version=7&comp=TDBISAMEngine&prop=TableMaxReadLockCount

To get the 3.x performance in 4.x, just set this property to 1.

However, I would suggest that you add an index instead to optimize the WHERE
clause so that a table scan is not required, especially if this query or
filter is particularly time-sensitive and is executed frequently.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Jun 6 2008 6:06 AMPermanent Link

Ries van der Velden
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

>http://www.elevatesoft.com/manual?
>action=mancompprop&id=dbisam4&product=d&version=7&comp=TDBISAMEngine&prop=TableMaxReadLockCount

>To get the 3.x performance in 4.x, just set this property to 1.

Hello Tim,

I've read this post with great interest, because we've got customers complaining about performance since we've switched from 4.25 build 7 to 4.26
build 1.

I did some testing with the property TableMaxReadLockCount, setting it to 1 and setting it to 50.000. But the strange thing is, that the overal
performance [reading, writing, filtering, queries] of the application is much better when I set the value to 50.000. We've got a client server solution
with 50 concurrent users.

I know that this description is very general, but do you have any idea why we've got this behaviour? Looking at the manual regarding this
property, I would have suspected the opposite behaviour.

Thanks in advance.
Fri, Jun 6 2008 4:58 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Ries,

<< I've read this post with great interest, because we've got customers
complaining about performance since we've switched from 4.25 build 7 to 4.26
build 1.  >>

You may be experiencing this issue instead:

http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.26&type=f&incident=2612

If the filters that are slow are using compound expressions, then
download/install/compile with 4.26 B2 and see if that helps.

--
Tim Young
Elevate Software
www.elevatesoft.com

Previous PagePage 2 of 2
Jump to Page:  1 2
Image