Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 10 total |
Speed Improvement - Please Explain |
Thu, Mar 8 2007 1:15 AM | Permanent 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 AM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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? > << 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. Have a great weekend mate. I'm off on Holidays for a week! 8-) Cheers Adam. |
Fri, Mar 9 2007 5:29 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I rather like the idea as well.
Roy Lambert |
Fri, Mar 9 2007 10:28 AM | Permanent 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? > > >><< 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. > > 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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? >> 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. >> Sure, because you're essentially making the database single-user in terms of updates for a moment. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 9 2007 6:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |