Improve the Performance of this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Improve the Performance of this query

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2007-12-15 : 12:03:50

SQL Experts,

I'm facing a performance issue with the following query...

The Output of the following Query is 184 Records and it takes 2 to 3 secs to execute the query.

SELECT DISTINCT Column1 FROM Table1 (NOLOCK) WHERE NOT EXISTS
(SELECT T1.Column1 FROM Table1 T1(NOLOCK) JOIN Table2 T2 (NOLOCK)
ON T2.Column2 = T1.Column2 WHERE T2.Column3= <Value>)

Data Info.
No of records in Table1 --> 1377366
No. of distinct records of Column1 in Table1 --> 33240

Is there any way the above query can be rewritten to improve the performance, which should take less than 1 sec...

(I'm using DISTINCT because there are Duplicate records of Column1 inTable1 )

Any of your help in this regard will be greately appreciated.

--
ash

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-15 : 12:14:59

Try this and also try adding an index on Column2 on both tables
SELECT DISTINCT T1.Column1 FROM Table1 T1(NOLOCK)
LEFT OUTER JOIN Table2 T2 (NOLOCK)
ON T2.Column2 = T1.Column2
AND T2.Column3= <Value>
WHERE T2.Column2 IS NULL

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-15 : 12:57:01

quote:
Originally posted by sqlilliterate

SQL Experts,

I'm facing a performance issue with the following query...

The Output of the following Query is 184 Records and it takes 2 to 3 secs to execute the query.

SELECT DISTINCT Column1 FROM Table1 (NOLOCK) WHERE NOT EXISTS
(SELECT T1.Column1 FROM Table1 T1(NOLOCK) JOIN Table2 T2 (NOLOCK)
ON T2.Column2 = T1.Column2 WHERE T2.Column3= <Value>)

Data Info.
No of records in Table1 --> 1377366
No. of distinct records of Column1 in Table1 --> 33240

Is there any way the above query can be rewritten to improve the performance, which should take less than 1 sec...

(I'm using DISTINCT because there are Duplicate records of Column1 inTable1 )

Any of your help in this regard will be greately appreciated.

--
ash

Besides the LEFT OUTER JOIN, I dont see how your outer table Table1 is joined with the inner query?

SELECT ..
FROM Table1 (nolock)
WHERE NOT EXISTS ( Select.... WHERE T1.somecolumn = Table1.Somecolumn)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-12-15 : 13:02:09

Could you send existing indices ?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

sqlilliterate
Starting Member

40 Posts

Posted - 2007-12-16 : 00:59:28

@visakh16,
thnx for ur query... it returns me the correct result, but takes more time(almost 40 secs) than the one I menioned :(

@dinakar,
sorry... i missed a ref. in my query... the actual one is
SELECT DISTINCT a.Column1 FROM Table1 a(NOLOCK) WHERE NOT EXISTS
(SELECT T1.Column1 FROM Table1 T1(NOLOCK) JOIN Table2 T2 (NOLOCK)
ON (T2.Column2 = T1.Column2 AND T1.Column1=a.Column1) WHERE T2.Column3= <Value>)

Still it takes 3 secs for execution :(

@jackv,
I've non-clustered indexes created on the columns Column1 and 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-16 : 07:43:43

Did you try putting indexes on column2 of both tables and Column3 of T2?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources