Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Have you checked index fragmentation and statistics of the tables involved ? Also you are doing cross database query - which is expensive. –  Kin Jan 8 at 23:29
    
Can you change the query? i.e. break it up to assign those single value sub queries to scalar variables and use OPTION (RECOMPILE). That will allow it to use the column statistics better and probably give the same performance as your query with literal values. –  Martin Smith Jan 8 at 23:34
    
And also show us the estimated plan (as you can't get the actual one) –  Martin Smith Jan 8 at 23:51

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.