Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Speed Improvement - Please Explain
Thu, Mar 8 2007 1:15 AMPermanent Link

"Adam H."
Hi Tim

I've been playing around with a SQL query I have (two tables joined together
with an left outer join clause).

The data resides on a file server across the network, and I'm accessing the
tables using DBSYS in local mode. (No server application).

When I run the query - it takes roughly 30 seconds to complete.

However - I've noticed if I do a start transaction, and run the query - the
query executes in just over 2 seconds!!!

(So it would appear as though running a SQL query from within a transaction
to retrieve records (this isn't updating any records, solely retrieving)
offers a huge performance boost. (I always thought that transactions should
only be used when updating / inserting / modifying data, and not just for
retrieval).

Now of course, curiosity's got the best of me. I'm wondering:

a) Should I be wrapping any large result/view only SQL within transactions?

b) Can you explain why it's flying through so fast? That's a huge
difference!

Best Regards

Adam.

Thu, Mar 8 2007 10:40 AMPermanent Link

Dave Harrison
Adam H. wrote:
> Hi Tim
>
> I've been playing around with a SQL query I have (two tables joined together
> with an left outer join clause).
>
> The data resides on a file server across the network, and I'm accessing the
> tables using DBSYS in local mode. (No server application).
>
> When I run the query - it takes roughly 30 seconds to complete.
>
> However - I've noticed if I do a start transaction, and run the query - the
> query executes in just over 2 seconds!!!
>
> (So it would appear as though running a SQL query from within a transaction
> to retrieve records (this isn't updating any records, solely retrieving)
> offers a huge performance boost. (I always thought that transactions should
> only be used when updating / inserting / modifying data, and not just for
> retrieval).
>
> Now of course, curiosity's got the best of me. I'm wondering:
>
> a) Should I be wrapping any large result/view only SQL within transactions?
>
> b) Can you explain why it's flying through so fast? That's a huge
> difference!
>
> Best Regards
>
> Adam.
>
>

Adam,
    Your transaction may have run faster because the data was already
cached from the prior query. To get a fair assessment, reboot your
computer and re-run your transaction test and let us know the results.

Dave
Thu, Mar 8 2007 5:20 PMPermanent Link

"Adam H."
Hi Dave,

Thanks for your reply...

>     Your transaction may have run faster because the data was already
> cached from the prior query. To get a fair assessment, reboot your
> computer and re-run your transaction test and let us know the results.

I've rebooted the computer, and

1) Run the query with transactions and then

2) Run the query without transactions

3) Run the query with transactions again

4) Ran the query without transactions again

The results were different, but still showed an improvement with the
transactions.

The first time I ran it (with transactions) was around 10 seconds.

The second time I ran it (straight after - without transactions) was around
20 seconds. (So still an improvement - twice as fast).

The third time I ran it (with transactions) was around 2 seconds. (Obviously
some caching going on)

The fourth time I ran it (without transactions again) was around 20 seconds
again.

So - there appears to be some caching going on (with the third query), but
still - with the very first query their is a significant speed improvement
by using a transaction. (Roughly twice as fast)

The query involved uses left outer joins (not just on one table), but is
fully optimized with indexes.

Cheers

Adam.

Thu, Mar 8 2007 5:36 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< So it would appear as though running a SQL query from within a
transaction to retrieve records (this isn't updating any records, solely
retrieving)  offers a huge performance boost. (I always thought that
transactions should
only be used when updating / inserting / modifying data, and not just for
retrieval). >>

Yes, but at the expense of concurrency.  The reason that it is so fast is
because DBISAM doesn't have to check for changes by other users during the
course of executing the query.

<< a) Should I be wrapping any large result/view only SQL within
transactions? >>

No, I wouldn't recommend it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Mar 8 2007 8:21 PMPermanent Link

"Adam H."
Hi Tim,

Thanks for your reply.

> Yes, but at the expense of concurrency.  The reason that it is so fast is
> because DBISAM doesn't have to check for changes by other users during the
> course of executing the query.

Ahh - I see. As a thought - is it possible to have some sort of property or
command on a query (for future thoughts) that allows it to ignore changes
while executing the query. (Cache data first or something) to get this speed
improvement for those times where we really don't care about data that's
changed after we initiate the query, or is that more difficult than it
sounds?  Smile

> << a) Should I be wrapping any large result/view only SQL within
> transactions? >>
>
> No, I wouldn't recommend it.

But.. but.. but - It's such a speed improvement.  Wink

Have a great weekend mate. I'm off on Holidays for a week!  8-)

Cheers

Adam.

Fri, Mar 9 2007 5:29 AMPermanent Link

"Jose Eduardo Helminsky"
Adam and Tim

A property on TDBISamQuery will be so good.
There are a lot of times that we know there are no changes in the database.

Since it improves (a lot) the performance, here is my vote.

Eduardo

Fri, Mar 9 2007 6:14 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

I rather like the idea as well.

Roy Lambert
Fri, Mar 9 2007 10:28 AMPermanent Link

Dave Harrison
Adam H. wrote:
> Hi Tim,
>
> Thanks for your reply.
>
>
>>Yes, but at the expense of concurrency.  The reason that it is so fast is
>>because DBISAM doesn't have to check for changes by other users during the
>>course of executing the query.
>
>
> Ahh - I see. As a thought - is it possible to have some sort of property or
> command on a query (for future thoughts) that allows it to ignore changes
> while executing the query. (Cache data first or something) to get this speed
> improvement for those times where we really don't care about data that's
> changed after we initiate the query, or is that more difficult than it
> sounds?  Smile
>
>
>><< a) Should I be wrapping any large result/view only SQL within
>>transactions? >>
>>
>>No, I wouldn't recommend it.
>
>
> But.. but.. but - It's such a speed improvement.  Wink
>
> Have a great weekend mate. I'm off on Holidays for a week!  8-)
>
> Cheers
>
> Adam.
>
>

Adam,
    If you're not updating the values for the query/table, you can also
set the table to ReadOnly and that should also give you a performance
boost too.

From Help File: DBISAM is also able to do extensive read buffering on
any table that is marked read-only at the operating system level, so if
your application is only requiring read-only access then it would
provide a big performance boost to mark the tables as read-only at the
operating system level.  Finally, if security permissions for any of the
physical files that make up the table prevent DBISAM from opening the
table with write access, then DBISAM will also automatically detect this
condition and set the ReadOnly property to True.

Dave
Fri, Mar 9 2007 6:44 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Ahh - I see. As a thought - is it possible to have some sort of property
or command on a query (for future thoughts) that allows it to ignore changes
while executing the query. (Cache data first or something) to get this speed
improvement for those times where we really don't care about data that's
changed after we initiate the query, or is that more difficult than it
sounds?  Smile>>

No, sorry.  Remember, DBISAM has to keep multi-paged data in synch, such as
is the case with indexes and BLOBs.  It isn't just a simple matter of saying
that I don't want to see the most recent records.  For example, what does
DBISAM do if it needs to read a new index page in from disk, and that index
page is completely different in terms of its relationship to the rest of the
index due to reorganization caused by other users' inserts or updates ?

<< But.. but.. but - It's such a speed improvement.  Wink>>

Sure, because you're essentially making the database single-user in terms of
updates for a moment. Smiley

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Mar 9 2007 6:45 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eduardo,

<< A property on TDBISamQuery will be so good.
There are a lot of times that we know there are no changes in the database.

Since it improves (a lot) the performance, here is my vote. >>

Knowing there will be no changes and preventing changes are two separate
things.  The transaction is essentially doing what such a property would
do - prevent any others from writing to the database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image