Problems with contains and an "OR"

Question

Post reply

Problems with contains and an "OR"

stuff 56271

SSC Eights!

Points: 938
More actions
December 16, 2012 at 1:31 pm

#268113

I have a query and a couple of related tables that use a full text index.
If I run this query
DECLARE @Content NVARCHAR(2048)
SET @Content = '"Event Number 940 Severity Error Host SP_B"'
SELECT TOP 250
Ticket.ID
FROM ticket LEFT OUTER JOIN TicketFollowup ON TicketFollowup.TicketID = Ticket.ID
WHERE CONTAINS ( Ticket.*, @Content )
This query returns in < 1 second
If I run this query which has the contains on the TicketFollowup table it runs in < 1 second
DECLARE @Content NVARCHAR(2048)
SET @Content = '"Event Number 940 Severity Error Host SP_B"'
SELECT TOP 250
Ticket.ID
FROM ticket LEFT OUTER JOIN TicketFollowup ON TicketFollowup.TicketID = Ticket.ID
WHERE CONTAINS ( TicketFollowup.*, @Content )
But if I put an or in the where clause
WHERE CONTAINS ( Ticket.*, @Content ) or CONTAINS ( TicketFollowup.*, @Content )
The query then takes about a long time - I gave up after 20 minutes
The following using a union also returns in < 1 second
DECLARE @Content NVARCHAR(2048)
SET @Content = '"Event Number 940 Severity Error Host SP_B"'
SELECT TOP 250
Ticket.ID
FROM ticket LEFT OUTER JOIN TicketFollowup ON TicketFollowup.TicketID = Ticket.ID
WHERE CONTAINS ( Ticket.*, @Content )
UNION
SELECT TOP 250
Ticket.ID
FROM ticket LEFT OUTER JOIN TicketFollowup ON TicketFollowup.TicketID = Ticket.ID
WHERE CONTAINS ( TicketFollowup.*, @Content )
Any ideas of why the query has such a problem with the "OR" ?
The ticket table has 157,000 rows
The Ticketfollowup table has 650,000 rows
Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stuff 56271 SSC Eights! Points: 938 More actions · Answer 1

I decided to leave the long running query running - it has now got to 1 hour + 30 minutes and is still running.

Chris

stuff 56271 SSC Eights! Points: 938 More actions · Answer 2

The query finished - 2 hours 41 minutes and 53 seconds.

Table 'TicketFollowup'. Scan count 157439, logical reads 503584, physical reads 168, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Ticket'. Scan count 1, logical reads 73981, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 5628281 ms, elapsed time = 9713069 ms.