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

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]

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

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

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