January 3, 2019 at 8:17 am
Hi,
I have an odd performance issue running this query against a table with 700 thousand records. This query will take 3.5 Hours to run to return 3.5 thousand rows.SELECT *
FROM Emailsent
WHERE (
CONTAINS (
Subject
,'"*2018*"'
)
OR CONTAINS (
Body
,'"*2018*"'
)
)
AND (SentOn >= '2018-11-12T00:00:00')
AND (SentOn < '2018-12-14T00:00:00')
If I split the query down to 2 parts and run it I get the same results in 3 seconds.DECLARE @EmailSentRecords TABLE (ID UNIQUEIDENTIFIER INDEX IX1 CLUSTERED)
INSERT INTO @EmailSentRecords (ID)
SELECT ID
FROM Emailsent
WHERE (SentOn >= '2018-11-12T00:00:00')
AND (SentOn < '2018-12-14T00:00:00')
DECLARE @EmailSentRecords2 TABLE (ID UNIQUEIDENTIFIER INDEX IX1 CLUSTERED)
INSERT INTO @EmailSentRecords2 (ID)
SELECT ID
FROM Emailsent
WHERE (
CONTAINS (
Subject
,'"*2018*"'
)
OR CONTAINS (
Body
,'"*2018*"'
)
)
SELECT *
FROM Emailsent
WHERE id IN (
SELECT esr.id
FROM @EmailSentRecords esr
INNER JOIN @EmailSentRecords2 esr2 ON esr.id = esr2.id
)
I have a full text index on the subject and body fields and a non clustered index on the senton field. The primary key on the Emailsent table is a sequential guid with a clustered index.
Can someone help explain why this is running slow?
January 3, 2019 at 8:29 am
I'm not sure why it's taking so long but have you tried using INTERSECT? It might be as fast as breaking the query up:SELECT *
FROM Emailsent
WHERE (SentOn >= '2018-11-12T00:00:00')
AND (SentOn < '2018-12-14T00:00:00')
INTERSECT
SELECT *
FROM Emailsent
WHERE (
CONTAINS (
Subject
,'"*2018*"'
)
OR CONTAINS (
Body
,'"*2018*"'
)
)
Also, have you looked at the execution plan?
January 3, 2019 at 8:38 am
Jonathan AC Roberts - Thursday, January 3, 2019 8:29 AMI'm not sure why it's taking so long but have you tried using INTERSECT? It might be as fast as breaking the query up:SELECT *
FROM Emailsent
WHERE (SentOn >= '2018-11-12T00:00:00')
AND (SentOn < '2018-12-14T00:00:00')
INTERSECT
SELECT *
FROM Emailsent
WHERE (
CONTAINS (
Subject
,'"*2018*"'
)
OR CONTAINS (
Body
,'"*2018*"'
)
)
Also, have you looked at the execution plan?
I have just tested this and it is very slow also, will post the execution plan.
January 3, 2019 at 8:50 am
I appear to have fixed the problem by rebuilding the index on the senton field. Very Curious. The query time is now down to 3 seconds.
January 3, 2019 at 8:59 am
john.newlands - Thursday, January 3, 2019 8:50 AMI appear to have fixed the problem by rebuilding the index on the senton field. Very Curious. The query time is now down to 3 seconds.
Probably out of date statistics.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply