Query takes too long to run - help me optimize

[Quimbly]

Hi everyone,
I've been trying to get this query to run quicker. Currently it just takes too long. Does anyone have any suggestions about optimizing it?

SELECT DISTINCT outerquery.name, "group" FROM analog outerquery WHERE EXISTS (SELECT * FROM analog innerquery WHERE innerquery."group" = outerquery."group" AND innerquery.ptnum != outerquery.ptnum AND innerquery.name LIKE 'ZZZ_%') AND uterquery.name LIKE 'ZZZ_%'

 

[Jeremy21b]

for one....get rid of the 'select *'...just select something small like an id or something. Maybe do a 'Select Top 1 id'. I don't use subqueries much, so I don't know if that second part would help at all.

The %'s would be slowing it down a lot too. I'm not sure how you could get rid of those though.

 

[mrdenny]

Where are your indexes? Where does the execution plan say the costs are?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vongrunt]

EXISTS() tells optimizer to perform existance test only, so SELECT * or SELECT TOP 1 or SELECT 1 shouldn't matter.

LIKE 'ZZZ_%' is optimizable... assuming that [name] column is indexed. But note that '_' is also wildchar character.

How many records has each table and how many records returns this query?