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:
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.