Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Composite Index performance Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:28 AM
Points: 6, Visits: 69
Hello, thanks in advance for any help. I have a large table with 500M records. It has a primary clustered index on it's key and a non-clustered unique index on the surrogate key that consists of 3 varchar(200) fields. My ETL merges from a extract table to the final large table on the 3 large text fields. Typically this consists of a merge of 10M records into the 500M record table. Performance has become terrible. It takes almost 12 hours to complete in out SQL Server 2012 database.

Any recommendations on how I can improve performance?
Post #1592294
Posted Monday, July 14, 2014 12:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 2,978, Visits: 3,005
You probably have a wide index scan, and indexe(s) don't contribute to the query. Can you post the execution plan and someone give you a good advise.
If you can use a static and narrow index (numeric is better) it should be better.

Some reffs:
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
http://www.sqlskills.com/blogs/kimberly/more-considerations-for-the-clustering-key-the-clustered-index-debate-continues/

Maybe a partitioning of the table will help a lot. You could take in consideration that as well because 500M is a big number.





Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1592306
Posted Monday, July 14, 2014 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:28 AM
Points: 6, Visits: 69
Thanks for the reply. I attached the explain plan for your review. The three varchars are a necessary join. I'll look into the links you provided.

  Post Attachments 
explainplan.sqlplan (13 views, 500.61 KB)
Post #1592308
Posted Monday, July 14, 2014 12:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 2,978, Visits: 3,005
97% of the total query cost goes for Table scan and Sort, in the most right-upper part of the execution plan.
Can you additionally post the tables structure and the indexes definitions?




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1592315
Posted Monday, July 14, 2014 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:28 AM
Points: 6, Visits: 69
The table scan actually doesn't take that long. I ran the "select" part of the merge statement and that part returned 10M rows into a temp table is 4 minutes. Attached is the table structure and the merge.

  Post Attachments 
sqlddlmerge.sqlplan (8 views, 5.42 KB)
Post #1592316
Posted Monday, July 14, 2014 1:17 PM This worked for the OP Answer marked as solution


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:04 AM
Points: 2,978, Visits: 3,005
--Extend this index to make it coverable for the query:
CREATE UNIQUE NONCLUSTERED INDEX [UIX_did_uid_sid] ON [Dim].[UserDevice]
(
[DeviceID] ASC,
[UserID] ASC,
[SubscriberID] ASC
)
INCLUDE(
Locale,[LastUsedDate]
)

--Create this index
CREATE UNIQUE NONCLUSTERED INDEX [UIX_NCSTransaction_IDs] ON [Ext].[NCSTransaction]
(
[SubscriberID],[UserID],[DeviceID]
)
INCLUDE ([Locale],location,[TransactionStartDateTime])

If the "select" part of the merge statement takes 4 minutes, then you'd better replace that code with a temp table and put the second index on the temp table.

I think there is a chance to improve it a lot. So it's your turn




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1592321
Posted Monday, July 14, 2014 1:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 2,121, Visits: 3,203
I strongly agree that you should substitute numeric values in place of the (very) long varchar key columns: [SubscriberID],[UserID] and [DeviceID].

But, until then, based on what you've posted, you should very likely cluster the tables as follows. (Yes, forget clustering on the identity column).

UserDevice: [SubscriberID],[UserID],[DeviceID]

NCSTransaction: [SubscriberID],[UserID],[DeviceID],TransactionStartDateTime

For best possible performance, it's very important that both indexes have exactly the same order of columns.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1592330
Posted Monday, July 14, 2014 1:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:28 AM
Points: 6, Visits: 69
Thanks for the help. I will try the temp tables, indexing the temp tables and using the covered index.
Post #1592331
Posted Monday, July 14, 2014 11:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
I'd like to suggest an alternative, since making multiple columns a clustered key increases the size, and degrades performance of all your non-clustered indexes on the target table. Remember, the cluster key gets stored in all non-clustered indexes whether you ask for it or not.

Merges perform best when both source and target tables are in the same sequence order. Presumably you have your source table ordered by the compound key. So read through it, joining to the target table using the compound key. But write your data out to a new working table that includes the single (primary) key, and is indexed on that key. This working table will be the source of your merge.

Yes, you are reading through your input values twice, but as you pointed out, scanning a table of 10 million rows doesn't take that long at all.

Something to try anyway.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1592409
Posted Tuesday, July 15, 2014 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:28 AM
Points: 6, Visits: 69
Using a temp table, indexing the temp tables using a covered index and selecting from the temp table worked extremely well. The merge is down to 20 minutes. Thanks again.
Post #1592492
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse