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]

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

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

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

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]

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

Hi Guys,

All I can say is Thank you, you have been very helpful and it is very much appreciated.

Malcolm