SQL Server: Seperate queries return in less than a second but combining those with INTERSECT takes 3 mins

Question

I'm not sure if this is an SQL Server bug or my mistake but here is the situation:

QUERY 1

select PK FROM PI_INFORM  WHERE RECORD_DATE>DATEADD(hour,-48, GETDATE())

this takes less than 0.5 secs and returns 20,000 records

QUERY 2

SELECT PK FROM dbo.getInformFullTextPKs('"HELLO"')

this takes less than 1 secs and returns 500 records

PROBLEM If we join those two queries with INTERSECT, takes more than 3 minutes. Execution plans shows everything is performed using INDEX SEEK.

SELECT * FROM PI_INFORM WITH (NOLOCK)
WHERE PK IN (

select PK FROM PI_INFORM  WHERE RECORD_DATE>DATEADD(hour,-48, GETDATE())

INTERSECT

SELECT PK FROM PI_INFORM WHERE PK IN (SELECT PK FROM dbo.getInformFullTextPKs('"HELLO"'))
)

Are you using NOLOCK on purpose, with full understanding of its limitations, or as a pixie dust turbo button? — Aaron Bertrand, Jul 07 '12 at 20:47

score 1 · Accepted Answer · answered Jul 07 '12 at 13:16

1

the following query is equivalent and should run faster

SELECT * FROM PI_INFORM WITH (NOLOCK)
WHERE 
PK IN (SELECT PK FROM dbo.getInformFullTextPKs('"HELLO"')) AND 
 RECORD_DATE>DATEADD(hour,-48, GETDATE())

answered Jul 07 '12 at 13:16

iruvar

22,736
7
53
82

Good idea. One of the joins in the original query was redundant. – usr Jul 07 '12 at 15:11

SQL Server: Seperate queries return in less than a second but combining those with INTERSECT takes 3 mins

1 Answers1