Help optimising SQL Query

[Andrew]

Hi,
I have a problem I would really appreciate help with. I am generating
dynamic SQL and need to optimise it. The specific example I am trying to
optimise looks like this:

SELECT DISTINCT DataHeaderID FROM TB_DataDetailTe xt T1 WHERE
(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND
(DataHeaderID=( SELECT DISTINCT DataHeaderID FROM TB_DataDetailTe xt CT2
WHERE T1.DataHeaderID = CT2.DataHeaderI D AND (EntityFieldID IN ( 34)
AND (Data LIKE 'SIDE BY SIDE%' )) ))AND
(DataHeaderID=( SELECT DISTINCT DataHeaderID FROM TB_DataDetailTe xt CCT3
WHERE T1.DataHeaderID = CCT3.DataHeader ID AND (( Data LIKE 'church%' ))))

I was OK optimising it with just 2 criteria and changed:
SELECT DISTINCT DataHeaderID FROM TB_DataDetailTe xt T1 WHERE

(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND

(DataHeaderID=( SELECT DISTINCT DataHeaderID FROM TB_DataDetailTe xt CT2

WHERE T1.DataHeaderID = CT2.DataHeaderI D AND (( Data LIKE 'church%' ))))

which took 26 seconds to using a derived table

SELECT distinct T1.DataHeaderID FROM TB_DataDetailTe xt as T1

inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailTe xt )
CT2

on T1.DataHeaderID = CT2.DataHeaderI D

WHERE

(T1.EntityField ID IN ( 31) AND (T1.Data LIKE '12BORE%' ))

and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.

My problem is I need to write code to generate the SQL for 1 to n criteria
and am struggling to write the query for more than 2

Best regards,

Andrew

[Ryan]

Completely untested...(I have two lovely young ladies ripping up some
cardboard behind me which is about an annoying a noise as you can get,
so if it's wrong, you have my excuse. I started on this before they
starting on the cardboard).

I've tried to tidy up your code a bit in the hopes that this points you
in a better direction for solving the problem with n criteria. Maybe
someone could take this further.

SELECT
T1.DataHeaderId

FROM
TB_DataDetailTe xt T1
LEFT JOIN TB_DataDetailTe xt CT2
ON T1.DataHeaderId = CT2.DataHeaderI d
LEFT JOIN TB_DataDetailTe xt CCT3
ON CT2.DataHeaderI d = CCT3.DataHeader Id

WHERE
T1.EntityFieldI d = 31 AND
T1.Data LIKE '12Bore% AND
CT2.EntityField Id = 34 AND
CT2.Data LIKE 'Side By Side%' AND
CCT3.Data LIKE 'Church%'

Ryan

[Andrew]

Hi Ryan,

Thanks very much. Tidying it up like that makes it much more straightforward
to see what I should do. I have it cracked now and yes, that does work and
blindingly fast (there was a bit of a whinge about quotes but that may have
been me and was easily fixed). It also lets me see a second way that I can
do it.

Thanks again,

Andrew

[Ryan]

No problem.

Ryan
Andrew wrote:

Hi Ryan,

Thanks very much. Tidying it up like that makes it much more straightforward to see what I should do. I have it cracked now and yes, that does work and blindingly fast (there was a bit of a whinge about quotes but that may have been me and was easily fixed). It also lets me see a second way that I can do it.

Thanks again,

Andrew