Sql-server – Slow running query with inner SELECT in where clause

performancequery-performanceselectsql-server-2008-r2

I have a query that looks like the following:

USE DATABASE3;
GO
SELECT t1.COL1 AS COLUMN1, 
      t2.COL2 AS COLUMN2, 
      t1.COL3, 
      t1.COL4 AS COLUMN4, 
      t1.COL5 AS COLUMN5, 
      t1.COL6, 
      t1.COL7, 
      t1.COL8, 
      t1.COL9, 
      t1.COL10, 
      t1.COL11, 
      t1.COL12, 
      t1.COL13, 
      t1.COL14, 
      t1.COL15, 
      t1.COL16,
      t1.COL17,
      COL18,
      COL19
FROM dbo.TABLE1 t1 with (nolock)
INNER JOIN dbo.TABLE2 t2 with (nolock)
ON t1.COL1 = t2.COL1 
WHERE  (t1.tran_type_cd = [smallint value])
            and t2.COL2 in (SELECT COL20 FROM database1.dbo.TABLE3 with (nolock)
                        WHERE     (COL21 = 'varchar 250 text'))
            and t1.COL3 >= (select COL22
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t1.COL3 <= (select COL25
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t2.COL26 = (SELECT COL24 FROM dbo.TABLE5 with (nolock))

and it's running ridiculously slow. Here's a quick rundown on some relevant details:

The following tables both just have one record –

database2.dbo.TABLE4
database3.dbo.TABLE5

t1.COL3
Table4.COL22
Table4.COL23
Table4.COL25
Table5.COL24
t2.COL26

are all datetime fields.

What's weird is that when I put in exact dates for instead of the inner SELECTs 

t1.COL3 >= 'exact date'
t1.COL3 <= 'exact date'
t2.COL26 = 'exact date'

the query runs instantaneously.

Initially thinking it's utilizing a bad execution plan, I've cleared the plan cache via the following t-sql:

DBCC FREEPROCCACHE;

Also, couldn't view the execution plan of the complex query because it's just keeps running & running. This is a production server so a reboot needs to be avoided if at all possible.

Any help is greatly appreciated. Thank you!

P.S. Sorry for the bad formatting with the sql statement…still trying to figure things out around here.

Best Answer

Try this:

USE database2;
go
CREATE INDEX ix4_23 ON TABLE4(Col23) INCLUDE (Col22, Col25);
go
USE database1;
go
CREATE INDEX ix3_21 ON TABLE3(Col21) INCLUDE (Col20);

And in the sub-queries put "TOP (99999999)" in there. You want to persuade the sub-queries to pull all the rows from the other databases separately. TOP with a number should persuade it... :)

Related Question