SQL Server Performance Forum – Threads Archive

help me increase query speed

I’ve got a table with 6 columns with only 160,000 records.
Here’s the table schema. labelID int,
FHID int,
pubID int,
FHIssueCode nvarchar(40),
issueID int,
status int,
CONSTRAINT labelPK PRIMARY KEY CLUSTERED (labelID)
There’s a query that’s executed against the table about once every second i would say. So this is a heavily used table. Here’s the query from our application. exec sp_executesql N’select * from labelTracker where fhID = @P1 and pubID = @P2 and fhIssueCode = @P3′, N’@P1 int ,@P2 int ,@P3 nvarchar(4000) ‘, 1701, 6505574, N’JUN 04’ We have a composite index on FHID, pubID, FHIssueCode. I know this is a pretty wide index since there’s a nvarchar(40) column included in this index. Looking at profiler, each query takes about 300 ms. Anyone has any idea how I can reduce it? i rebuilt this index every night because it gets fragmented very quickly.

Yeah, first I would remove the composite index, and index fhID, pubID capture this in a trace and see what happens. Can you post the execution plan? Also whats up with this fhIssueCode = @P3′, N’@P1 int ,@P2 int ,@P3 nvarchar(4000) if you are getting a lot of fragmentation you should set Fillfactor to lower amount. Or you can record this query in a trace during normal workload and have Index tuning wizard figure it out.

Pick this part:
select * from labelTracker where fhID = @P1 and pubID = @P2 and fhIssueCode = @P3 Paste in Query Analyzer, replace variables for tipical values, see execution plan.
Check if index are used.
Run ITW and see what recomendations, if any.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I ran the statement with show execution plan in query analyzer (although I don’t quite know how to copy and post the results here).
It says it’s using the composite index, 98% of the query cost comes from the index seek.
It says scanning a particular range of rows from a non-clustered index, object labeltracker.labelTrackerFHIDPubIDIssueCode which is my composite index that was created on those 3 columns, then it says ORDERED FORWARD. So it is using the index correctly. Will tell u the results of index wizard in a while.

ok, index wizard recommended another index on just the FHID column alone.
right now I have 3 existing indexes.
labelTrackerPK on labelCode
labeTrackerFHIDPubIDIssueCode on that 3 columns
labeTrackerStatus on status column. Don’t think it would make much of a difference, but i’ll give it a shot anyway.

Use SET SHOWPLAN_TEXT ON or SET SHOWPLAN ALL or on your query so you can post results of execution plan. Update statistics then run index tuning wizard or try this remove Composite index it is way too wide and makes for an ineffecient index, index the columns that I mentioned to you, are they indexed on the parent tables? The only times Composite indexes are really effective is when you used in a Covered Index meaning all who’s key value contains all the data needed to satify the query. Let me know how things turn out.

select * from labelTracker where fhID = 1709 and pubID = 11076580 and fhIssueCode = ‘052704’ |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[zuber].[dbo].[labelTracker])) |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[zuber].[dbo].[labelTracker].[labeTrackerFHIDPubIDIssueCode]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[labelTracker].[FHID]=Convert([@1]) AND [labelTracker].[pubID]=11076580 AND [labelTracker].[FHIssueCode]=Convert([@3])) ORDERED FORWARD)

Let me know if performance improves after changes to indexes.

no, number of reads and duration was still about the same after i dropped the composite index and created an index on only 2 columns as you suggested. I issued a dbcc dbreindex on the entire table and duration went down a lot. I put the fillfactor 0f 80, is that enough?

Fill factor is ok. How about Index suggested by ITW?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis he did mention that it is a pretty heavily loaded table, do you think a lower amount of 65-70 would be worth wile?
Tusanto just rereading you post you said each query reports 300ms in profiler. Thats not fast enough??? how many records is each query returning?

May be you are rigth, Lazy, but I don’t think the problem is in fillfactor, and yes 300ms is not to much to me.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The reason I suggested he readjust his fill factor was because he mentioned that he rebuilds his Indexes nightly due to heavy fragmentation.

Change @P3 datatype in the query from nvarchar(4000) to nvarchar(40). This is necassary for to reduce the work for OLE/DB or ODBC to call the query. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

]]>