| Author |
 Topic  |
|
|
sql-lover
Yak Posting Veteran
59 Posts |
 Posted - 07/25/2012 : 12:21:04
|
Got a big main store procedure developed by one of our programmers that it looks is having some issues with this particular query:
SELECT TicketID
FROM Ticket
WHERE
(Name like 'HOLA' OR Title like 'HOLA' OR Description like 'HEY') AND
DateDeactivated IS NULL
GROUP BY TicketID
No matter what, this query keeps doing an Index scan.
The table has a Clustered Index on TicketID column, which I do not believe I can alter or modified (following some instructions) But I created a non clustered Index on my own, in order to improve the performance, however, no luck.
Here's the non clustered Index I created
CREATE NONCLUSTERED INDEX [NCI_Name] ON [dbo].[Ticket]
(
[Name] ASC,
[DateDeactivated] ASC
)
INCLUDE ( [TicketID]) ON [PRIMARY]
GO
It's worse with it. Even without the covered column.
"Title" dataype is text, by the way. I should also mention that the result set will be inserted on a temporary table.
The "Hola" and "Hey" are parameters I just invented. ;-)
Any hints? |
Edited by - sql-lover on 07/25/2012 12:22:10
|
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2113 Posts |
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 07/25/2012 : 14:00:57
|
| LIKE without wildcards will translate to equality, but that's only if you can guarantee no wildcards. Nothing you can do about the text column though. And having OR conditions pretty much guarantees a scan too. |
 |
|
|
sql-lover
Yak Posting Veteran
59 Posts |
Posted - 07/25/2012 : 14:17:36
|
Thanks
What about a Full text catalog or a Full text search? I got the filling that's the major bottleneck here: Title column.
Here are the actual stats for that particular query:
Table 'Ticket'. Scan count 1, logical reads 3065, physical reads 0, read-ahead reads 0, lob logical reads 115996, lob physical reads 6804, lob read-ahead reads 0. |
Edited by - sql-lover on 07/25/2012 14:19:41 |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 07/25/2012 : 14:19:57
|
| That's going to be your best choice, assuming you can't modify the table schema. |
|
|
|
sql-lover
Yak Posting Veteran
59 Posts |
Posted - 07/25/2012 : 16:01:24
|
quote:
Originally posted by robvolk
That's going to be your best choice, assuming you can't modify the table schema.
So far, so good... :-) ... let me test more and I will share my solution if it actually works.
Here's before the Full Text Index and catalog:
Table 'Ticket'. Scan count 1, logical reads 3066, physical reads 0, read-ahead reads 0, lob logical reads 115981, lob physical reads 0, lob read-ahead reads 0.
Here's after, notice the lob logical reads new value...
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ticket'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Looking good... |
Edited by - sql-lover on 07/25/2012 16:02:30 |
|
|
| |
Topic |
|
|
|
Help tuning this query
