Unique Index Causing Slow Read Performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Unique Index Causing Slow Read Performance

23 posts, Page 1 of 3

1 2 3 »»»

Unique Index Causing Slow Read Performance

Rate Topic

Display Mode

Topic Options

Author

Message

ben.mcintyre

Posted Wednesday, August 26, 2009 7:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

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

Post #777457

Dave Ballantyne

Posted Wednesday, August 26, 2009 7:31 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

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
Kent user group

Post #777464

GilaMonster

Posted Wednesday, August 26, 2009 7:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

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 2008, MVP
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

Post #777480

ben.mcintyre

Posted Wednesday, August 26, 2009 7:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

Thanks for link. I'll post the plans in another day or so.

Post #777507

ben.mcintyre

Posted Wednesday, August 26, 2009 8:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

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

Post Attachments

Diag.JPG (18 views, 93.66 KB)

Unique Query.xls (17 views, 66.50 KB)

NonUnique Query.xls (5 views, 64.50 KB)

Post #778002

ben.mcintyre

Posted Wednesday, August 26, 2009 8:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

STATISTICS IO and STATISTICS TIME

See Attachments

Post Attachments

Unique.txt (13 views, 20.88 KB)

Nonunique.txt (6 views, 20.80 KB)

Post #778005

ben.mcintyre

Posted Wednesday, August 26, 2009 8:52 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

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 Smile

Post #778011

ben.mcintyre

Posted Thursday, September 03, 2009 7:41 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:20 PM
Points: 76, Visits: 370

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

Post #782679

GilaMonster

Posted Friday, September 04, 2009 1:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,741, Visits: 30,019

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 2008, MVP
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

Post #782746

TheSQLGuru

Posted Friday, September 04, 2009 8:18 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 3,582, Visits: 5,130

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 at GMail

Post #782949

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions