Query performance

The below query seems to be very slow :
select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null))
All the columns involved in the WHERE clause and JOIN conditions have index. Is there any alternative available for the above which can increase the performance ?
Please advice,
Thanks,
Smitha

The first thing you need to do is get rid of the "distinct" keyword - that alone will slow things down considerably. If you have duplicate rows in your table then you should remove them. If the number of occurances of a duplicate row is important to your application, then add that as a column, and eliminate the duplicates.

The other thing it'd seem to me to help, although I haven't tested it, would be to replace your WHERE EXISTS clause with an outer join with test3 on d.col3=a.col3. I don't know your data though, so I'm not sure of the effect of that change.

|||

How slow is 'very slow'...?
How much data is there involved in the tables?
What does the query plan look like?
Which table or tables shows the largest amount of data that's been worked on?

For an alternative, you need to provide some sample data along with the desired result and a brief explanation of what the query is supposed to do.

/Kenneth

|||The other thing you would have to get rid of is the a.*, retrieve only the columns you really need.

Jose Luis
|||

What does the query plan look like?

First are you sure you need to do a DISTINCT?

See these links for some overview

http://www.sql-server-performance.com/transact_sql.asp

http://www.sql-server-performance.com/nb_select_distinct.asp

sql