Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 4 of 4 total |
understanding performance |
Sat, Jan 31 2009 4:46 PM | Permanent Link |
Kevin Killion | A contract developer built a system for us, and I'm trying to understand some performance
issues. One table contains a count of sales by customers. In this snippet, we simply clear all the counts to zero: s := 'update wf_sales '; s := s + 'set SalesCount = 0'; with aQuery do begin close; databasename := 'dbMyEvents'; sql.clear; sql.add(s); execsql; close; end; I timed that "execsql" statement and was surprised by how long it took: To go through 15,382 entries took 4 minutes 34 seconds! I watched the disk light, and there was little disk activity. I have no idea even where to start to look for clues about why this is taking so long. Any tips or suggestions? Thanks to anyone! |
Sun, Feb 1 2009 3:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Kevin
Firstly is this single user or multi user, f/s or c/s. If f/s then check out network performance. If you have DBSys you can try running the query in it with generate plan checked (if you don't have it you can download it from Tim's website). That will tell you the "real" time taken. Otherwise if you have the source and can recompile you can alter the routine to with aQuery do begin close; GeneratePlan := True; <<<<<<<<<<<<<<<<< databasename := 'dbMyEvents'; sql.clear; sql.add(s); execsql; Plan.SaveToFile(filename); <<<<<<<<<<<<<<<<<< close; end; In either case post the plan here and that will, possibly, help, but I doubt it with a simple query such as yours. Since it looks as though a query is being reused one thought is - Is the query hooked up to any visual components that are being refreshed as the query progresses? If so this will slow it down dramatically. Also if FlushBuffers is set to True this will have a considerable impact. Finally if there are any custom functions built in which are fired when SalesCount is altered? Roy Lambert [Team Elevate] |
Mon, Feb 2 2009 3:56 AM | Permanent Link |
"Eduardo [HPro]" | Kevin
try to enclosure the update within a transaction. I think you will se more performance on it. Eduardo |
Sat, Feb 7 2009 2:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Kevin,
<< I timed that "execsql" statement and was surprised by how long it took: To go through 15,382 entries took 4 minutes 34 seconds! I watched the disk light, and there was little disk activity. >> How did you time the statement ? You can get the actual execution time for the statement like this: s := 'update wf_sales '; s := s + 'set SalesCount = 0'; with aQuery do begin close; databasename := 'dbMyEvents'; sql.clear; sql.add(s); execsql; ShowMessage('Execution time is '+FloatToStr(ExecutionTime)+' secs'); close; end; Also, as already asked, it is important to know the architecture of the application - local/single-user, multi-user, file-sharing, multi-user C/S, etc.. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |