Help tuning this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help tuning this query

Author

Topic

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-25 : 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?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-07-25 : 13:28:13

LIKE operator is killing you. If you can get rid of it, you should be able to improve performance.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-25 : 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

99 Posts

Posted - 2012-07-25 : 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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-25 : 14:19:57

That's going to be your best choice, assuming you can't modify the table schema.

sql-lover
Yak Posting Veteran

99 Posts

Posted - 2012-07-25 : 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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources