access query optimization?

pllnu
10-26-2004, 10:03 AM
Any thoughts on optimizing the following query? Thanks

TRANSFORM First(rpt.Data) AS Data
SELECT rpt.Date, rpt.ReportOrder, rpt.ResultName, rpt.ResultType, rpt.TotalLabel, rpt.ReportRange
FROM qryRpt_Lambdas AS rpt
WHERE [Date]=#10/9/2004#
GROUP BY rpt.Date, rpt.ReportOrder, rpt.ResultName, rpt.ResultType, rpt.TotalLabel, rpt.ReportRange
ORDER BY rpt.ReportOrder, rpt.ResultName
PIVOT rpt.Hour;

Shurik12
10-26-2004, 10:17 AM
So what do you want to optimise in fact?

pllnu
10-26-2004, 10:25 AM
Thanks for replying, i'm trying to optimize the transform in terms of time to execute.

Shurik12
10-26-2004, 10:34 AM
Well in general since you're working on Access you should expect that the execution time might be quite considerable (surely if you work with big tables and the database is on the network)
Also you're using qryRpt_Lambdas in the sql which is in its turn based on some tables, this fact is surely slows this Transform query down.
As an alternative you could try to do to things:

-first write data to a sort of temp table ( e.g. make qryRpt_Lambdas a make table query) and then use that table in the Transform query

-you could also try to write a sub-query within this Transform query (no temp table is needed in this case)


Shurik.

pllnu
10-26-2004, 10:54 AM
again, thanks for replying. how do i make the qry_rptlambdas into a make table? my sql skills are limited.

Shurik12
10-26-2004, 12:33 PM
Hi,

Just open your query in Design View, and choose Make-Table query form the Query menu.

nikrolluqui
11-02-2004, 01:12 PM
again, thanks for replying. how do i make the qry_rptlambdas into a make table? my sql skills are limited.


if you would like to get the best of your queries then dont put queries in side a query with a of lot inner and outer joins...take time to connect them one by one until the last query...also do not start a relationship with an outer join, always put them at the last part :D

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum