The Query that broke the DB's back...

[LiamOC]

Hi,

I have a query, well it comprises of a query and two underlying sub qieries if you like and it's takes the form that uses it forever to load. Unfortunately, I'm doing this database for someone else and they HAVE to have this form but I need to get the query to run about 10 times faster!

Can someone tell me what's making this query so bluddy slow. I've a suspicion the DLookup is partly responsible but is there any way to iprove this mess or am I in serious trouble?

Below are the three queries. The top one is the top level final query that gets me the results I need.

Should I write it in VB maybe? What can I do. PLEASE HELP!!!!!

LV-QuickSelect inc Totals(updateable)

SELECT [LV-MAIN].Segment, [LV-MAIN].[Pole No], [LV-MAIN].[Quick Select], DLookUp("SumOfTotal","LV-QuickSelect inc Totals(nonupdateable)","[Segment]='" & [Segment] & "'And [Pole No]='" & [Pole No] & "'&quot AS TotalCost
FROM [LV-MAIN]
ORDER BY getSortValue([Segment]), Val([LV-MAIN].[Pole No]);

LV-QuickSelect inc Totals(nonupdateable)

SELECT [LV-MAIN].Segment, [LV-MAIN].[Pole No], [LV-MAIN].[Quick Select], Sum([LV-QuickSelect inc Totals(Work Totals)].Total) AS SumOfTotal
FROM [LV-MAIN] LEFT JOIN [LV-QuickSelect inc Totals(Work Totals)] ON ([LV-MAIN].[Pole No] = [LV-QuickSelect inc Totals(Work Totals)].[Pole No]) AND ([LV-MAIN].Segment = [LV-QuickSelect inc Totals(Work Totals)].Segment)
GROUP BY [LV-MAIN].Segment, [LV-MAIN].[Pole No], [LV-MAIN].[Quick Select];

LV-QuickSelect inc Totals(Work Totals)

SELECT [LV-WORK_COMPLETED].Segment, [LV-WORK_COMPLETED].[Pole No], [LV-WORK_COMPLETED].Quantity, [LV-CODING].Price, [Price]*[Quantity] AS Total
FROM [LV-WORK_COMPLETED] LEFT JOIN [LV-CODING] ON [LV-WORK_COMPLETED].Code = [LV-CODING].Code
ORDER BY [LV-WORK_COMPLETED].Segment;

If anyone can help me I would be so so gratefull. Thanks,

Liam

 

[SoloDroid]

Hello Liam,
I can relate. Have you indexed the tables well? This is the first thing to check. Domain aggregate functions, such as DLookup, are murder on a query. The messy work-around is to use more subqueries and include them with joins -- it is dirty but you will find an amazing speed increase. IIf statements can also be death-on-a-stick because they actually execute both the true *AND* false parts, regardless of the outcome! Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°