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_DataDetailText T1 WHERE
(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2
WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)
AND (Data LIKE 'SIDE BY SIDE%' )) ))AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3
WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))

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

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

(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2

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

which took 26 seconds to using a derived table

SELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1

inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )
CT2

on T1.DataHeaderID = CT2.DataHeaderID

WHERE

(T1.EntityFieldID 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_DataDetailText T1
LEFT JOIN TB_DataDetailText CT2
ON T1.DataHeaderId = CT2.DataHeaderId
LEFT JOIN TB_DataDetailText CCT3
ON CT2.DataHeaderId = CCT3.DataHeaderId

WHERE
T1.EntityFieldId = 31 AND
T1.Data LIKE '12Bore% AND
CT2.EntityFieldId = 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