Improve query speed

[FMJO]

Hello All,



This is my first post in this forum!

I run a query using a form and I extract some parameters from the form. If I run the query without extracting the parameters from the form, it runs way more faster than when I do it through the form.

How can I improve the speed of the query when it runs extracting parameters from the from???

Thanks for your help!

Orlando

[orange]

Please show us some code.

[FMJO]

Here's the SQL statement:

SELECT a.GENERATION, b.failcode, b.failcode2, Count(*) AS Amount, Count(*)/(SELECT Count(*) FROM LTO_FR_LTO_PRODUCT_DESCRIPTIONS AS a INNER JOIN LTO_FR_TRIAGEA AS b ON a.PN=b.PN WHERE (((b.DT)>=[Forms]![QP]![txtTaFromDate] And (b.DT)<DateSerial(Year([Forms]![QP]![txtTaToDate]),Month([Forms]![QP]![txtTaToDate]),Day([Forms]![QP]![txtTaToDate]))+1) And (a.generation)=[Forms]![QP]![cmbTaGen])AND ((a.FORM_FACTOR)=IIf([Forms]![QP]![Height]=1,"HH",IIf([Forms]![QP]![Height]=2,"FH",[a.FORM_FACTOR])))) AS Percentage, a.FORM_FACTOR
FROM LTO_FR_LTO_PRODUCT_DESCRIPTIONS AS a INNER JOIN LTO_FR_TRIAGEA AS b ON a.PN=b.PN
WHERE (((b.DT)>=Forms!QP!txtTaFromDate And (b.DT)<DateSerial(Year(Forms!QP!txtTaToDate),Month(Forms!QP!txtTaToDate),Day(Forms!QP!txtTaToDate))+1) And ((a.generation)=Forms!QP!cmbTaGen) And ((a.FORM_FACTOR)=IIf(Forms!QP!Height=1,"HH",IIf(Forms!QP!Height=2,"FH",[a.FORM_FACTOR]))))
GROUP BY a.GENERATION, b.failcode, b.failcode2, a.FORM_FACTOR
ORDER BY Count(*) DESC , a.FORM_FACTOR DESC;

As you can see I extract some values from the form QP. However, If I define the values explicitly (w/o using the form) the query runs faster.

Orlando

[FMJO]

Beside the issue with the speed, feel free to make any suggestion to improve the SQL statement.