Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 21 to 27 of 27 total |
Speed accessing data |
Mon, Oct 13 2008 11:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
Without your tables to try it on I can't guarantee anything but this might be what you want SELECT DISTINCT D.SalesDate, D.LocationNo, D.ProductCode, D.SalesQty, D.SalesValue, D1.SellingCode, D1.ECRDept, D1.FreeText4 FROM ProductDay D JOIN ProductDetail D1 ON (D1.ProductCode = D.ProductCode) AND (D1.LocationNo = D.LocationNo) WHERE (D1.ECRDept = 3) AND D.SalesDate BETWEEN('2008-08-01' AND '2008-08-07') ORDER BY D.SalesDate, D.LocationNo, D.ProductCode, D.SalesQty, D.SalesValue, D1.SellingCode, D1.ECRDept, D1.FreeText4 Roy Lambert [Team Elevate] |
Mon, Oct 13 2008 2:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
You may want to try using the JOINOPTIMIZECOSTS keyword at the end of the query. Also, if you want to send me the data that you're using (or post it somewhere private so I can download it if it is bigger than 16MB zipped up), then I can take a closer look at what is going on. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 14 2008 5:02 AM | Permanent Link |
Malcolm | Hi Tim,
They zip up to 100mb & 22mb. do you have a FTP site or somewhere I can put them? in the mean time I will try what you suggest. Regards Malcolm |
Wed, Oct 15 2008 10:40 AM | Permanent Link |
Malcolm | Hi Tim/Roy,
Many thanks for your input I firstly tired Roys SQL which brought the times down to under 6 minutes, then I added Tim' s JOINOPTIMIZECOSTS and woooow where did the turo boost come from, down to under 2 minutes. The more you find out the more you need to know i.e. when should and shouldn't you use JOINOPTIMIZECOSTS? How does it achive these results? what are the overheads? Thanks guys very much appreciated Malcolm |
Wed, Oct 15 2008 11:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
Using JOIN rather than the old style syntax can often improve things (I think it makes it easier for Tim's optimiser to figure stuff out) and as for JOINOPTIMIZECOSTS well as it says on your plan (and which I ignored) Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join So it was probably using the big table to do the join rather than the little table. I never had this do me any good which is probably why I ignored it. Roy Lambert [Team Elevate] |
Wed, Oct 15 2008 2:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolmn,
<< I firstly tired Roys SQL which brought the times down to under 6 minutes, then I added Tim' s JOINOPTIMIZECOSTS and woooow where did the turo boost come from, down to under 2 minutes. The more you find out the more you need to know i.e.when should and shouldn't you use JOINOPTIMIZECOSTS? How does it achive these results? what are the overheads? >> The JOINOPTIMIZECOSTS clause works by telling DBISAM to include actual I/O costs when determining the order in which the tables should be joined. DBISAM then uses this information to figure out a more optimal join order. The reason that we don't have it turned on by default, however, is that the optimizer can only do a quick probe to determine the estimated join I/O costs, and sometimes the probe can return results that aren't entirely accurate over the entire set of joined rows. So, it has to be something that you must try with each query to see if it helps or not. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Oct 17 2008 4:31 AM | Permanent Link |
Malcolm | Hi Guys,
All I can say is Thank you, you have been very helpful and it is very much appreciated. Malcolm |
« Previous Page | Page 3 of 3 | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |