Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Composite Index performance


Composite Index performance

Author
Message
jjf112
jjf112
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
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?
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
jjf112
jjf112
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
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.
Attachments
explainplan.sqlplan (13 views, 500.00 KB)
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
jjf112
jjf112
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
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.
Attachments
sqlddlmerge.sqlplan (8 views, 5.00 KB)
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
--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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6671
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
jjf112
jjf112
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
Thanks for the help. I will try the temp tables, indexing the temp tables and using the covered index.
The Dixie Flatline
The Dixie Flatline
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6899
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? Everybody look what's going down. -- Stephen Stills
jjf112
jjf112
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
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.
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