December 16, 2012 at 1:31 pm
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
December 16, 2012 at 2:38 pm
I decided to leave the long running query running - it has now got to 1 hour + 30 minutes and is still running.
Chris
December 16, 2012 at 6:06 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply