Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 15 of 15 total |
Sql speed issue |
Mon, Jun 1 2009 8:58 AM | Permanent Link |
malcolm | Hi Tim,
I have tried your suggestions. Changing the date for >= and <= to between does not seem to make any appreciable difference (but will take your advice on this). Closing down the ZoneAlarm AntiVirus does. With the AV closed using dbsys the data is consistently extracted in approximately 7.5 seconds give or take a few points of a second, whether using <= or between, regardless of the number of times the sql is run. This has reduced the overall time for my program by about 50% i.e. from 30 minutes to about 12. Do you think this is reasonable with the amount of data involved? I think I will review the AntiVirus situation when it is up for renewal. Many thanks Malcolm |
Mon, Jun 1 2009 9:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | malcolm
>This has reduced the overall time for my program by about 50% i.e. from 30 minutes to about 12. Do you think this is reasonable with the >amount of data involved? I think you've made a good start. From your posts the SQL you originally posted about is taking c7.5secs what's the rest of the 12mins used up by? Roy Lambert [Team Elevate] |
Tue, Jun 2 2009 7:31 AM | Permanent Link |
malcolm | Hi Roy,
the 12 mins is the total time for my VB6 program, the SQL time of 7.5secs is for dbsys extracting the data for one location. The VB6 program loops round extracting the data for each location and there are 54 of them. I have just sent another plan to Tim to have a look at to see if he can optimise further. many thanks Malcolm |
Thu, Jun 4 2009 9:01 AM | Permanent Link |
"John Hay" | Malcolm
> SELECT D.LocationNo, sum(D.SalesValue)as TotVal, D1.ECRDept > From ProductDay D JOIN ProductDetail D1 on ( D1.ProductCode = D.ProductCode) and (D1.LocationNo = D.LocationNo) > WHERE D.SalesDate >= '2009-05-16' AND D.SalesDate <= '2009-05-22' And D.LocationNo = 1 > group by d1.ECRDept > JOINOPTIMIZECOSTS Is there an index on locationo in both tables? If there is I wonder why the query is only partially optimized? John |
Thu, Jun 4 2009 2:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< Is there an index on locationo in both tables? If there is I wonder why the query is only partially optimized? >> I thought the same thing, so I had him give me an execution plan without the JOINOPTIMIZECOSTS clause, and the partial-optimization is being forced by the optimizer due to the JOINOPTIMIZECOSTS. Basically DBISAM is saying "it's quicker to execute each join by just using an index for the first condition, and then just looking at the rows satisfied by the first condition for the second condition". -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |