November 18, 2009 at 8:35 am
Good morning. I have a table with about 15 million rows. I am running a query against that table and, from my testing, it takes anywhere from 28 to 73 seconds to return results. Not too bad, considering. Is this a pretty reasonable wait time for results? Also, can anything be done to improve upon this? The specs of the table and the query are below and attached.
query:
select
candidateid,matchpercentage
from seocandidatematch
where jobid = 'xxxx'
Any help given is appreciated. Thank you.
Chris
November 18, 2009 at 8:49 am
It would be better if you supplied the table and index information as DDL (CREATE TABLE and CREATE INDEX) statements. From your Excel spreadsheet it is a bit difficult to tell the order of columns in your indexes.
Looking at your query, the first question I have about your indexes is the JobID the first column of either index? If not, an index on JobID would be helpful.
November 18, 2009 at 9:07 am
Thanks for the reply. Here's the information, as requested:
Table definition:
CREATE TABLE [dbo].[SEOCandidateMatch](
[ID] [uniqueidentifier] NOT NULL,
[CandidateID] [uniqueidentifier] NOT NULL,
[JobID] [uniqueidentifier] NOT NULL,
[MatchPercentage] [int] NOT NULL,
[RowStamp] [bigint] NULL CONSTRAINT [DF_SEOCandidateMatch_RowStamp] DEFAULT ((0)),
CONSTRAINT [PK_dbo.SEOCandidateMatch] PRIMARY KEY CLUSTERED
(
[CandidateID] ASC,
[JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Index definition:
CREATE UNIQUE NONCLUSTERED INDEX [IX_SEOCandidateMatch] ON [dbo].[SEOCandidateMatch]
(
[JobID] ASC,
[CandidateID] ASC,
[MatchPercentage] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
November 18, 2009 at 9:32 am
For the query posted, based on the execution plan and the indexes I saw, I really don't see anything else you can do. The nonclustered index is a covering index, so the data pages themselves aren't even accessed.
November 18, 2009 at 12:47 pm
CGSJohnson (11/18/2009)
I am running a query against that table and, from my testing, it takes anywhere from 28 to 73 seconds to return results.
How many rows returned? What's the total data size of that rowset? Do you know how much of that 28 sec is running the query and how much of it is transferring the data to the client and displaying it? (hint: use SET STATISTICS TIME ON)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2009 at 8:21 am
Sorry for the delay in my response...power went off here yesterday.
It seems that I've managed to resolve this issue, by the way.
I was mistaken;the query that I thought was causing the performance issue wasn't the one posted above. That query is the 1st query used in a stored procedure that does paging. So, modifying indexes on the temp table into which the query results are deposited (I know that I can use a cte, as well) and modifying indexes on other tables used later on in the stored procedure have dramatically dropped execution time to under 5 seconds.
Thanks for your help, Lynn and Gail.
P.S. I just noticed that you both are Star Wars fans, huh?! 🙂
November 21, 2009 at 8:59 am
Hey Lynn - CONGRATS ON PASSING THE 10000 POINT MARK!!!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply