SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unique Index Causing Slow Read Performance


Unique Index Causing Slow Read Performance

Author
Message
ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
Hi All

This one's for the gurus out there.
I am a pretty darn experienced SQL developer. This problem cuts right to the root of my beefs with SQL, but also has the potential to open up major new tuning skills if it can be solved. I hope it is not as simple as the fact I am using SQL Server 2000 for this (as are many of our clients) and it's a bug.
OK, on with the scenario.

I have a fairly big query (short SQL but involving about 5 views or nested sub-views, probably about 15-20 tables in all), taking about 17 seconds to run on my unloaded dev PC.
Mucking around with it a bit, I found deleting a particular seemingly inconsequential table improved response to 4 seconds. Note that production machines at client sites are likely to take substantially longer due to load conditions, so this improvement could make the difference between 2 mins vs 25 sec for a user. Ah-ha, I thought, poor indexing on the table.

After considerably more experimentation, I found that no, it wasn't a lack of, or incorrect, indexing. Rather, adding this table into the query was causing a major change to the execution plan, which was resulting in the much slower performance. There was no clear way to jog the (quite complex) execution plan back in the speedier direction.
After leaving this alone for several days, I came back to it and noticed that there was a UNIQUE constraint on the table in question.
Let's get specific here:

CREATE TABLE [tblSummLocation] (
[SummaryXID] [numeric](19, 0) NOT NULL ,
[LocationID] [int] NOT NULL
) ON [PRIMARY]
GO

This is a 'glue' or 'many-to-many' table, storing relationships between two other tables.
Here is the indexing:


ALTER TABLE [tblSummLocation] WITH NOCHECK ADD
CONSTRAINT [PK_tblSummLocation] PRIMARY KEY CLUSTERED
([SummaryXID], [LocationID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSummLocation] ADD
CONSTRAINT [IX_tblSummLocation_SummaryXID] UNIQUE NONCLUSTERED ([SummaryXID]) WITH FILLFACTOR = 90 ON

[PRIMARY]
GO

CREATE INDEX [IX_tblSummLocation_LocationID] ON [tblSummLocation]([LocationID]) ON [PRIMARY]
GO


I noticed when I dropped the unique constraint [IX_tblSummLocation_SummaryXID] and replaced it with

CREATE INDEX [IX_tblSummLocation_SummaryXID1] ON [dbo].[tblSummLocation]([SummaryXID]) ON [PRIMARY]
GO

we got back to the 4 second response.


This table was one of a group of about 12 tables of very similar design, but is different from most of the others in that the SummaryXID is actually unique (in the other tables it's only the combination of the two columns tat is unique). So I realised at this point that I could simplify things and just make SummaryXID the PK:

ALTER TABLE tblSummLocation DROP CONSTRAINT PK_tblSummLocation
ALTER TABLE tblSummLocation DROP CONSTRAINT IX_tblSummLocation_SummaryXID
GO
ALTER TABLE [tblSummLocation] WITH NOCHECK ADD
CONSTRAINT [PK_tblSummLocation] PRIMARY KEY CLUSTERED
([SummaryXID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO


whoops ... back to 17 seconds.


So, to cut a long story short, whenever I have a unique index defined on SummaryXID on this table, whether it be an index, constraint or PK, I get the 17 seconds response.
With the composite PK and a nonunique index, it's 4 seconds.

So the questions that have been bugging me are:

(1) Why is this so much slower ? I can appreciate that insertions and updates might be substantially slower under a unique index, but I wouldn't imagine reads would be any different. Perhaps even faster.

(2) If as I suspect, the change is not actually to do with the indexing per se, but with the execution plan being mangled, how can I jog it back to the faster setting ?
I have tried table hints in the underlying query specifying WITH (INDEX (IX_tblSummLocation_SummaryXID1)) for the table, but no dice.
I also mucked around with JOIN hints, specifying the join algorithms, but that was a desperate and silly idea to begin with and didn't get anywhere.

I really need to speed this up, so my only option at present is to drop the UNIQUE constraint on the column. I simply don't want to do that, as it circumvents a business rule of the database.
I find it really frustrating when I know the query can run faster, and I even know exactly what is slowing it down and where, but I just don't seem to have the control over the execution plan to make it run the quick way.

Ben
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6992 Visits: 8370
Its all down to the query plans...


See this article on how to post them ,
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



Clear Sky SQL
My Blog
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219545 Visits: 46279
Need execution plans to say anything useful here.
What's the output of STATISTICS IO and STATISTICS TIME for the query with the unique index and the one without?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
Thanks for link. I'll post the plans in another day or so.
ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
OK, here are the plans and a database diagram (attached).

I have simplified the query a bit, but unfortunately as I take bits out, it all runs fast and the problem disappears.
Hopefully you can work out the difference between the plans without having to know too much else. The index on tblSummLocation is definitely one of the triggers of the major change in plans (removing bits of the query also does this).

I simplifed a bit before and I'll give the details again below:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSummLocation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSummLocation]
GO

CREATE TABLE [dbo].[tblSummLocation] (
[SummaryXID] [numeric](19, 0) NOT NULL ,
[LocationID] [int] NOT NULL ,
[BayID] [int] NOT NULL
)
GO

Have a look at the diagram to get a feel for what's happening.
Note that all the 'ID' columns in the DB are int, and all the 'XID' columns are [numeric](19, 0).
This is a stock control database and uses an abstract SummaryXID to represent a group of stock so we can apply characteristics to either a pallet, or down to individual cartons or pieces of fruit. This facilitates only breaking down records to the extent needed rather than to the smallest unit (clients pack 10 million cartons a year). Characteristics (such as product type, location, pack type, are all applied by linking through the 'Summ' tables (tblSummProduce, tblSummItems, tblSummLocation, ...).

We're only talking 50,000 rows here.

This indexing is applied in the 'Unique' case:

ALTER TABLE tblSummLocation DROP CONSTRAINT PK_tblSummLocation
GO
ALTER TABLE tblSummLocation DROP CONSTRAINT IX_tblSummLocation_SummaryXID
GO
DROP INDEX tblSummLocation.[IX_tblSummLocation_LocationID]
GO
DROP INDEX tblSummLocation.[IX_tblSummLocation_BayID]
GO
DROP INDEX tblSummLocation.[IX_tblSummLocation_SummaryXID1]
GO

ALTER TABLE [dbo].[tblSummLocation] WITH NOCHECK ADD
CONSTRAINT [PK_tblSummLocation] PRIMARY KEY CLUSTERED
([SummaryXID],[LocationID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSummLocation] ADD
CONSTRAINT [IX_tblSummLocation_SummaryXID] UNIQUE NONCLUSTERED ([SummaryXID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblSummLocation_BayID] ON [dbo].[tblSummLocation]([BayID]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblSummLocation_LocationID] ON [dbo].[tblSummLocation]([LocationID]) ON [PRIMARY]
GO
CREATE INDEX [IX_tblSummLocation_SummaryXID1] ON [dbo].[tblSummLocation]([SummaryXID]) ON [PRIMARY]
GO


And the same in the 'NonUnique' case but with the unique constraint commented out :

--ALTER TABLE [dbo].[tblSummLocation] ADD
-- CONSTRAINT [IX_tblSummLocation_SummaryXID] UNIQUE NONCLUSTERED ([SummaryXID]) WITH FILLFACTOR = 90 ON --[PRIMARY]
GO

Nonunique: 5 sec. Unique: 17 sec.

The actual query is

SELECT vSes_PivotA.*, vrpIPD.*
FROM (vSes_PivotA_6385 vSes_PivotA
INNER JOIN vrp_ItemProduceData1 vrpIPD ON vSes_PivotA.SP_SummXID = vrpIPD.SummaryXID)
WHERE vrpIPD.RecType=0;

but as I have said, that's a long piece of string (about 6 views and 20 tables); I'm really only interested in why the unique index changes the query plan so profoundly.

Happy to scipt up any more objects if you need them.

cheers

Ben
Attachments
Diag.JPG (52 views, 93.00 KB)
Unique Query.xls (47 views, 66.00 KB)
NonUnique Query.xls (24 views, 64.00 KB)
ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
STATISTICS IO and STATISTICS TIME

See Attachments
Attachments
Unique.txt (44 views, 20.00 KB)
Nonunique.txt (42 views, 20.00 KB)
ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
Just wanted to say thanks in advance, I really appreciate any time anyone spends on this one.

I've combed the internet for years over problems like this and been completely unable to find any resource to help.
Perhaps this is the point of initiation into the undocumented 'black arts' side of SQL ?

3:52 am ? I'm in Australia. It's 12:30 in the arvo over here :-)

B
ben.mcintyre
ben.mcintyre
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 403
Hmmm ....
The silence in this thread is almost deafening.
Was it what I said about being Australian ? Those bloody 'Flight of the Conchords' guys have been giving us a bad name ...

I assume everyone's been going 'OMG ... LOL ... he wants us to look at THOSE query plans !?!? Maybe when I can use 6pt font on a 24" monitor !'
Yeah, they are big. Unfortunately, when I make them smaller the problem disappears.

Anyway, the PROBLEM IS SOLVED.
In final desperation I got around to firing up both these cases in SQL2005. Both come in at just over 1 sec.
Read it and weep, folks (well, I almost did, after probably 14 hours spent trying to optimise this single query, including forum posting time).

So the final analysis: SQL 2000 has problems drawing up good query plans for complex queries. I would rate this as a bug, and I'm pretty darn certain that there is no workaround for it (in terms of hints or tuning). I tried everything.
SQL Server 2005 fixes this problem.
I'm currently in the process of contacting all our clients and recommending the upgrade to SQL2005 (even the express version would outperform the paid SQL2000 on this database by the looks).

I've always been a bit slow on the upgrade, with a 'if it ain't broke don't fix it' attitude. Particularly that the SQL2005 Management Studio is woeful compared to the old tools (Enterprise Manager). It alone cuts my productivity by a good percentage.
But anyway, it IS broke, so here I go.

cheers

Ben McIntyre
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219545 Visits: 46279
ben.mcintyre (9/3/2009)
Hmmm ....
The silence in this thread is almost deafening.


I was going to dig into this over the weekend. Been rather busy prepping and delivering a presentation and then catching up with real work

So the final analysis: SQL 2000 has problems drawing up good query plans for complex queries. I would rate this as a bug, and I'm pretty darn certain that there is no workaround for it (in terms of hints or tuning). I tried everything.
SQL Server 2005 fixes this problem.


2005 is better than 2000, it's still not perfect. The optimiser in 2000 could make some rally dumb decisions, but then so can the 2005 one. When that happens, often the best approach is 'divide and conquer' Split the query up, use temp tables, that kind of thing. Sometimes helps, sometimes doesn't.

Do you still want this investigating?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31587 Visits: 8671
1) it doesn't have to be a bug if the optimizer for 2000 derives a suboptimal query plan for a complex query involving 15 tables in a mishmash of views.

2) Gail is correct in that it can often truly benefit the optimizer to subdivide large queries such as this into smaller interim steps using temp tables (NOT table variables) because each step along the way can then have it's own set of statistics that prevents small skews from becoming large query performance problems.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search