469,933 Members | 2,452 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,933 developers. It's quick & easy.

Help optimising SQL Query

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
Jul 23 '05 #1
3 1394
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

Jul 23 '05 #2
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
Jul 23 '05 #3
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


Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jasper Bryant-Greene | last post: by
1 post views Thread by BobLaughland | last post: by
5 posts views Thread by Chris Weston | last post: by
7 posts views Thread by Martin Eyles | last post: by
21 posts views Thread by c | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.