Length query execution time

[phxbypdx]

Hey all -

I have a number of queries that take a long time to execute (30-60 seconds). I'm hoping y'all can help me with one of them, and I can then apply that knowledge to the rest. I'm a native .NET programmer, so my SQL work generally is visual, not hard coded like this.

I know this is a pretty beefy query, but can anyone help me optimize this query for greater speed?

INSERT INTO lcltblPnL_DtlLabor ( Time_Main_Key, Time_Tech_ID, WF_Shop_ID, Time_Bonus, Time_Vacation, Time_Sick, Time_OtherAdd, Time_OtherDed, LABDolC, LARDolC, LAMDolC, LAFDolC, LASDolC, LADDolC, LAEDolC, LAGDolC, LABHrsPaid, LARHrsPaid, LAMHrsPaid, LAFHrsPaid, LASHrsPaid, LADHrsPaid, LAEHrsPaid, LAGHrsPaid )
SELECT lkpqryLaborCost.Time_Main_Key, lkpqryLaborCost.Time_Tech_ID, lkpqryLaborCost.WF_Shop_ID, lkpqryLaborCost.Time_Bonus, lkpqryLaborCost.Time_Vacation, lkpqryLaborCost.Time_Sick, lkpqryLaborCost.Time_OtherAdd, lkpqryLaborCost.Time_OtherDed, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LABRate])*[SumOfTime_LAB_P]) AS LABDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LARRate])*([SumOfTime_LAR_P])) AS LARDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAMRate])*[SumOfTime_LAM_P]) AS LAMDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAFRate])*[SumOfTime_LAF_P]) AS LAFDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LASRate])*[SumOfTime_LAS_P]) AS LASDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LADRate])*[SumOfTime_LAD_P]) AS LADDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAERate])*[SumOfTime_LAE_P]) AS LAEDolC, Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAGRate])*[SumOfTime_LAG_P]) AS LAGDolC, Sum(lkpqryLaborCost.SumOfTime_LAB_P) AS LABHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAR_P) AS LARHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAM_P) AS LAMHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAF_P) AS LAFHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAS_P) AS LASHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAD_P) AS LADHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAE_P) AS LAEHrsPaid, Sum(lkpqryLaborCost.SumOfTime_LAG_P) AS LAGHrsPaid
FROM (lkpqryLaborCostRate INNER JOIN lkpqryLaborCost ON lkpqryLaborCostRate.Time_Main_Key = lkpqryLaborCost.Time_Main_Key) INNER JOIN lcltblPnL_List ON lkpqryLaborCost.WF_Shop_ID = lcltblPnL_List.WF_Shop_Key
GROUP BY lkpqryLaborCost.Time_Main_Key, lkpqryLaborCost.Time_Tech_ID, lkpqryLaborCost.WF_Shop_ID, lkpqryLaborCost.Time_Bonus, lkpqryLaborCost.Time_Vacation, lkpqryLaborCost.Time_Sick, lkpqryLaborCost.Time_OtherAdd, lkpqryLaborCost.Time_OtherDed;

Thanks,

Scott

 

[namliam]

Lets make it (more) readable first

INSERT INTO lcltblPnL_DtlLabor ( Time_Main_Key, Time_Tech_ID, WF_Shop_ID, Time_Bonus, Time_Vacation, Time_Sick, Time_OtherAdd, Time_OtherDed, LABDolC, LARDolC, LAMDolC, LAFDolC, LASDolC, LADDolC, LAEDolC, LAGDolC, LABHrsPaid, LARHrsPaid, LAMHrsPaid, LAFHrsPaid, LASHrsPaid, LADHrsPaid, LAEHrsPaid, LAGHrsPaid )
SELECT lkpqryLaborCost.Time_Main_Key
     , lkpqryLaborCost.Time_Tech_ID
     , lkpqryLaborCost.WF_Shop_ID
     , lkpqryLaborCost.Time_Bonus
     , lkpqryLaborCost.Time_Vacation
     , lkpqryLaborCost.Time_Sick
     , lkpqryLaborCost.Time_OtherAdd
     , lkpqryLaborCost.Time_OtherDed
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LABRate])*[SumOfTime_LAB_P]) AS LABDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LARRate])*[SumOfTime_LAR_P]) AS LARDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAMRate])*[SumOfTime_LAM_P]) AS LAMDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAFRate])*[SumOfTime_LAF_P]) AS LAFDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LASRate])*[SumOfTime_LAS_P]) AS LASDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LADRate])*[SumOfTime_LAD_P]) AS LADDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAERate])*[SumOfTime_LAE_P]) AS LAEDolC
     , Sum(IIf([TotalRate]<>0,[TotalRate],[Time_LAGRate])*[SumOfTime_LAG_P]) AS LAGDolC
     , Sum(lkpqryLaborCost.SumOfTime_LAB_P) AS LABHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAR_P) AS LARHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAM_P) AS LAMHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAF_P) AS LAFHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAS_P) AS LASHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAD_P) AS LADHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAE_P) AS LAEHrsPaid
     , Sum(lkpqryLaborCost.SumOfTime_LAG_P) AS LAGHrsPaid
FROM (     lkpqryLaborCostRate 
INNER JOIN lkpqryLaborCost        ON lkpqryLaborCostRate.Time_Main_Key = lkpqryLaborCost.Time_Main_Key) 
INNER JOIN lcltblPnL_List         ON lkpqryLaborCost.WF_Shop_ID        = lcltblPnL_List.WF_Shop_Key
GROUP BY 
       lkpqryLaborCost.Time_Main_Key
     , lkpqryLaborCost.Time_Tech_ID
     , lkpqryLaborCost.WF_Shop_ID
     , lkpqryLaborCost.Time_Bonus
     , lkpqryLaborCost.Time_Vacation
     , lkpqryLaborCost.Time_Sick
     , lkpqryLaborCost.Time_OtherAdd
     , lkpqryLaborCost.Time_OtherDed;

It aint that big, just got 3 tables used. Problem though is that 2 of the tables are queries again (If I read your naming convention right).
This query looks just fine, barring problems in the other queries, I guess this is what it is...

P.s. Welcome to AWF

 

[phxbypdx]

Thanks for cleaning that up for me. I didn't know how it would render until after submission.

The lkpqryLaborCost takes upwards of 30 seconds to run (on its own) and returns 567,000 rows. It seems this query doesn't concern itself with Table I/O. I would be more concerned with limiting Table I/O and working with indexed I/O instead. The LaborCostRate query calls the LaborCost query again, which takes another 30 seconds.

I guess i just needed to dive into this a bit. I think the problem is that I'm pulling information across the network (full IO), filtering it, then saving it back across the network. The information is saved to a table, but every time this report is run, that table (and a dozen more like it, called from other queries) are truncated. And because of the join, I'm saying "Select these 567000 records, filter it for the one we want, and insert to table", then "Select these 567000 records again, filter it for a different one we want, and insert to table"... With 1117 table inserts, we're returning 5.67 million rows. That seems silly.

 

[namliam]

Thanks for cleaning that up for me. I didn't know how it would render until after submission.

It is not about rendering though, it is about general SQL readablity... I am sure you dont have the SQL in code like that barring the Access query designer.

I guess i just needed to dive into this a bit. I think the problem is that I'm pulling information across the network (full IO), filtering it, then saving it back across the network.

This sounds like your query-ing some kind of bigger database (Oracle/SQL Server/ DB2/ like big ass system thing)

Have you considered using a Pass Through query to have your query executed on the source system instead? Making it a local query on the server rather than a complex access the network kind of thing?

 

[phxbypdx]

No, I just have a front-end that's on every workstation, and a back-end that sits on a server.

I have not considered a pass through query. Currently the application links 40 or 50 tables from our server-side data file, then processes everything locally. From my understanding of pass through queries (mostly context, some background, and a google search), I would ask the remote server to process the query on it's end & save the table on itself (remote).

The benefit to this is that I have a much more powerful machine crunching all those numbers, and the end result is a measely 1100 rows that I can return once across the network. Sound right?

 

[namliam]

The benefit to this is that I have a much more powerful machine crunching all those numbers, and the end result is a measely 1100 rows that I can return once across the network. Sound right?

That is exactly right !

However

No, I just have a front-end that's on every workstation, and a back-end that sits on a server.

You do need a server sitting there, if you have a backend access file... then a pass through is not going to work. You do need some (other) big brother type database sitting there MySQL/SQL Server/Oracle/Etc. Access backend will not work.

 

[phxbypdx]

That is exactly right !

However

You do need a server sitting there, if you have a backend access file... then a pass through is not going to work. You do need some (other) big brother type database sitting there MySQL/SQL Server/Oracle/Etc. Access backend will not work.

The plan is to eventually redevelop it as a web app (either .NET or PHP) with a MySQL or SQL Server back-end. In the meantime, I'm stuck with access front-end and access back-end. I think I'm just stuck with this. Queries that query queries of queries isn't exactly how I would have designed it.

Thanks for your help.

Scott