Query is slow when parameter is used 1

[patrussell]

I have a query that works great when I use a hard filter value for testing but when I use a parameter to allow the end user to customize the query I get a timeout. This happens even when using the hard filter value for the parameter value.
I have run the query in SMSS and from reporting services with the same result.

Any ideas on why using a parameter would kill the query?

Thanks,

Pat Russell

 

[gmmastros]

There are various reasons. Can you show the query and indicate where you hard coded the value for testing?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[patrussell]

Here is the query using the paramater:

SELECT     TOP (100) PERCENT AMS_PCE_PDO_MDL_SETUP.C_PCE_ID AS [Coil ID], MAX(AMS_PCE_PROD.Q_PROD_OFF_LOC) AS [Discharge Time], 
                      MAX(AMS_PCE_PDI.C_ALLOY_CODE) AS [Alloy Code], MAX(AMS_PCE_PDI.F_STRIP_WIDTH) AS Width, MAX(AMS_PCE_PDI.F_EXIT_THICK_TARG) 
                      AS [Final Thickness], MAX(AMS_PCE_PDI.C_GRADE) AS Grade, MAX(AMS_PCE_PDO_MDL_SETUP.C_MDL_CALC_TYP) AS [Calc Type], 
                      AMS_PCE_PDO_MDL_SETUP.J_PASS AS Pass, MAX(AMS_PCE_PDO_MDL_SETUP.F_THICK_EXIT) AS [Model Exit Thickness], AVG(AMS_PCE_PDO_MV.F_THICKNESS) 
                      AS [Actual Exit Thickness], MAX(AMS_PCE_PDO_MDL_SETUP.F_FORCE_TAIL) AS [Model Force], AVG(AMS_PCE_PDO_MV.F_FORCE) AS [Actual Force], 
                      MAX(AMS_PCE_PDO_MDL_SETUP.F_TENSION_BACKW) AS [Model Back Tens], AVG(AMS_PCE_PDO_MV.F_DECOILER_TEN) AS [Actual Back Tens], 
                      MAX(AMS_PCE_PDO_MDL_SETUP.F_TENSION_FORW) AS [Model Fwd Tens], AVG(AMS_PCE_PDO_MV.F_COILER_TEN) AS [Actual Fwd Tens], 
                      MAX(AMS_PCE_PDO_MDL_SETUP.F_RUN_SPEED) AS [Model Speed], AVG(AMS_PCE_PDO_MV.F_SPEED) AS [Actual Speed], 
                      MAX(AMS_PCE_PDO_MDL_SETUP.J_ROLL_DIRECTIVE) AS [Rolling Directive], MAX(AMS_PCE_PDO_MDL_SETUP.J_ROLL_DIRECTIVE_VER) 
                      AS [Directive Version]
FROM         AMS_PCE_PDO_MDL_SETUP INNER JOIN
                      AMS_PCE_PDO_MV ON AMS_PCE_PDO_MDL_SETUP.C_PCE_TRK_NUM = AMS_PCE_PDO_MV.C_PCE_TRK_NUM AND 
                      AMS_PCE_PDO_MDL_SETUP.J_PASS = AMS_PCE_PDO_MV.J_PASS INNER JOIN
                      AMS_PCE_PDI ON AMS_PCE_PDO_MDL_SETUP.C_PCE_ID = AMS_PCE_PDI.C_PCE_ID INNER JOIN
                      AMS_PCE_PROD ON AMS_PCE_PDO_MDL_SETUP.C_PCE_TRK_NUM = AMS_PCE_PROD.C_PCE_TRK_NUM
GROUP BY AMS_PCE_PDO_MDL_SETUP.C_PCE_ID, AMS_PCE_PDO_MDL_SETUP.J_PASS
[COLOR=red yellow]HAVING      (AMS_PCE_PDO_MDL_SETUP.C_PCE_ID = @Param1)[/color]
ORDER BY MAX(AMS_PCE_PDO_MDL_SETUP.C_PCE_TRK_NUM) DESC

For testing I just enter a valid value for the parameter @Param1 in the query editor in SMSS or Reporting services in the criteria pane.

Pat Russell

 

[gmmastros]

I would suggest a minor change to your query. Whenever I write an aggregate query, I usually put "simple" conditions in the where clause and "complex" conditions in the having clause. For example, your having clause condition is about as simple as it gets. I reserve the having clause for conditions that involve an aggregate: For example:

Having Avg(SomeColumn) > 20

By moving your condition to the where clause, the query engine will likely filter out some (or a lot) of rows without having to perform calculations on some of them. I recommend you change...

[tt]
GROUP BY AMS_PCE_PDO_MDL_SETUP.C_PCE_ID, AMS_PCE_PDO_MDL_SETUP.J_PASS
HAVING (AMS_PCE_PDO_MDL_SETUP.C_PCE_ID = @Param1)
ORDER BY MAX(AMS_PCE_PDO_MDL_SETUP.C_PCE_TRK_NUM) DESC
[/tt]

To:

Where (AMS_PCE_PDO_MDL_SETUP.C_PCE_ID = @Param1)
GROUP BY AMS_PCE_PDO_MDL_SETUP.C_PCE_ID, AMS_PCE_PDO_MDL_SETUP.J_PASS
ORDER BY MAX(AMS_PCE_PDO_MDL_SETUP.C_PCE_TRK_NUM) DESC

Please let me know if this makes a consider difference in the execution time.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[patrussell]

That made an incredible difference in time.

I went from timing out to under 3 seconds. Considering that there are ~90 million rows to work through I'm happy with it.

Thank you!

Pat Russell

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom