Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 10 of 19 total |
Very Slow Querys, not an SQL problem |
Sat, Nov 28 2009 10:25 AM | Permanent Link |
Peter Lee | I have an app that has many forms, the data for each is extracted by (usually) a single
query component which is created and destroyed on entry to and exit from the form. On some forms the query performance is very slow. I have picked on one form which is particularly slow and put some timing points in to see where the time is going. In addition I have put all the form init code in a single procedure in order to make things easier to see The longest query is taking 5 seconds which is a long time considering the size of the table and results set. The state of the data is checked every 20 seconds which also servers to prevent a session timeout. If I add a new session component connected to the same server and change the init proc to use the new SessionName then the queries are much quicker 800mS instead of 5000mS. I have tried to ensure that both session components are set the same way including PrivateDir. Does anyone have any good ideas (please)? Peter |
Sat, Nov 28 2009 11:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
First thing to do is produce execution plans for the queries. Those will, usually, identify the cause of the slow performance (eg an extra index or two needed). It also means that you're not just looking at time (Windows & DBISAM caching can make a massive difference in timings). The other general cause of slowdown is linked controls, lookup tables, or events. The puzzling thing about what you report is that just using a different session component speeds things up so it could be one of its properties. When you created the new session did you drop a blank one on the form? If so try a cut'n'paste instead to make sure the properties are the same. What you'll have to do is ctrl-C on the existing session, then change its session name then ctrl-V to paste the copy otherwise you get an error. Roy Lambert |
Sat, Nov 28 2009 3:23 PM | Permanent Link |
Peter Lee | Hi Roy
Thanks for the guidance, my tests with two different sessions weren't entirely fair as one included the offending line (below) and the other didn't. The queries are far from optimized at the moment but it doesn't appear to be the source of the problem. Your comments did however prompt me to look in a different place. I went through the init stages of the app reming out routines that ran queries, eventually one produced the desired effect. The init routine runs only once and is finished long before the one I used for my measurements is run. The offending routine (attached) is the only one in the app that uses blobs, they hold images. When the routine runs during init it extracts blobs from the table and writes them to disk. I presumed that the delay would be in write to disk however removing these lines produced no change. In the end the offending line is the one that checks to see that the blob contains data. There are two lines with this code, however only the second produces a result, there aren't many records with an "ImageIndex" of less than 10. if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then I can't see why this line should produce a permanent effect on the accessing of data. Without the routine the queries in the entire app are a lot faster. My later tests are using localhost rather than a proper network, the times are 5000mS with the line and 300mS without the line. Any ideas? Thanks Peter Roy Lambert wrote: Peter First thing to do is produce execution plans for the queries. Those will, usually, identify the cause of the slow performance (eg an extra index or two needed). It also means that you're not just looking at time (Windows & DBISAM caching can make a massive difference in timings). The other general cause of slowdown is linked controls, lookup tables, or events. The puzzling thing about what you report is that just using a different session component speeds things up so it could be one of its properties. When you created the new session did you drop a blank one on the form? If so try a cut'n'paste instead to make sure the properties are the same. What you'll have to do is ctrl-C on the existing session, then change its session name then ctrl-V to paste the copy otherwise you get an error. Roy Lambert Attachments: offending.pas |
Sun, Nov 29 2009 9:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
I bet you're fun at parties Table := TDBISamQuery.Create(nil); <VBCG> Now seriously. If there are a lot of them, and they are a decent size I suppose they could be screwing free ram but that's about all I can come up with. However, your post is that its the test that's the problem not the actual extraction and writing to disk I'm baffled. I think a full answer will have to wait for Tim since I have no idea why. A couple of observations which have nothing to do with the problem (I had a great suggestion then realised you weren't on ElevateDB : 0..9: begin if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then ; TBlobField(Table.FieldByName('Image')).SaveToFile(Folder + '\' + Table.FieldByName('FileName').AsString); end; The if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then ; achieves nothing. The end of line ; effectively makes the next line stand alone so it will always be executed. finally if Table <> nil then begin Table.Free; Table := nil; end; end; could be better written as finally FreeAndNil(Table); end; Roy Lambert [Team Elevate] |
Sun, Nov 29 2009 11:08 AM | Permanent Link |
Peter Lee | Hi Roy
Hmmm, there are a lot of "Tables" where the routines are simple. I hadn't noticed the colon, it shouldn't be there. Thanks for the "Freeand Nil" much more elegant than my alternative. I will go back and have another look at the problem (anything not to have to watch the tennis) and create a nice simple project that demonstrates the problem. Perhaps Tim can come up with a gem. Cheers Peter |
Sun, Nov 29 2009 1:28 PM | Permanent Link |
Peter Lee | Roy
Thanks for pointing out the ; problem. It meant, of course, that the problem wasn't on the line I though it was. I have done a quick demo to prove the problem and have tried it on another machine with similar results. The project and tables are attached. Push the "Fill Combo" a couple of times and note the times. Then push the "Save BLOB's". Then push the "Fill Combo" a couple of times and see the time differences. Thanks Peter Attachments: Elevate.zip |
Sun, Nov 29 2009 1:32 PM | Permanent Link |
Peter Lee | Roy
Thanks for pointing out the semi colon error. It meant, of course, that the problem wasn't on the line I though it was. I have done a quick demo to prove the problem and have tried it on another machine with similar results. The project and tables are attached. Push the "Fill Combo" a couple of times and note the times. Then push the "Save BLOB's". Then push the "Fill Combo" a couple of times and see the time differences. Thanks Peter Peter Attachments: Elevate.zip |
Sun, Nov 29 2009 11:13 PM | Permanent Link |
Raul | Peter,
Please don't post attachments in regular newsgroups - there is a special binaries one. I recompiled your test app (D2007, DBISAM 4.29 b2) and ran it and here are results: Fillcombo : 734 627 656 Save BLOBs: 1828 Fill combo 703 672 687 Save BLOB 1593 Fill combo 687 672 Running outside IDE took approx 100ms off the FillCombo (so most runs were in the 640ms range) Raul Peter Lee wrote: Roy Thanks for pointing out the semi colon error. It meant, of course, that the problem wasn't on the line I though it was. I have done a quick demo to prove the problem and have tried it on another machine with similar results. The project and tables are attached. Push the "Fill Combo" a couple of times and note the times. Then push the "Save BLOB's". Then push the "Fill Combo" a couple of times and see the time differences. Thanks Peter Peter |
Mon, Nov 30 2009 2:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
First, as Raul says, please don't post large attachments in the normal ngs, especially when you do it twice. Secondly, unlike Raul I do get the same sort of time differences that you see (c5000ms after saving blobs c600ms before). I'm using D6, DBISAM V25 and Vista. This has made me aware that you haven't given us your environment information. Can you let us have the Delphi, DBISAM and OS information please. Roy Lambert [Team Elevate] |
Mon, Nov 30 2009 8:50 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Peter,
I'm seeing figures of: Fill Combo: 594 563 579 563 Save BLOBs: 719 719 703 719 And I do not see any difference in the timings, either before or after the BLOBs are saved. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |