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


Insert is Slow


Insert is Slow

Author
Message
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387
I am having 10 Master table with 10 million records each.

For unique(primary key) i have use uniqueidentifier as column data type.
While inserting more records on this table insert is slow.

Is this because of uniqueidentifier column?
if so then what can be the alternative.

My table consists of 5 float 10 varchar,3 bit, 8 int,2 uniqueidentifier,4 char, 1 bigint.

uniqueidentifier column is fk for other tables , while joining the columns have used uniqueidentifier column as a primary key.

say
TableA C1 uniqueidentifier (PK), C2 uniqueidentifier, C3 ......

TableB C1 uniqueidentifier , C2 uniqueidentifier (FK_TableA_uniqueidentifier ), C3 ......
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63935 Visits: 17974
yuvipoy (3/4/2014)
I am having 10 Master table with 10 million records each.

For unique(primary key) i have use uniqueidentifier as column data type.
While inserting more records on this table insert is slow.

Is this because of uniqueidentifier column?
if so then what can be the alternative.

My table consists of 5 float 10 varchar,3 bit, 8 int,2 uniqueidentifier,4 char, 1 bigint.

uniqueidentifier column is fk for other tables , while joining the columns have used uniqueidentifier column as a primary key.

say
TableA C1 uniqueidentifier (PK), C2 uniqueidentifier, C3 ......

TableB C1 uniqueidentifier , C2 uniqueidentifier (FK_TableA_uniqueidentifier ), C3 ......



You said your uniqueidentifier is your primary key. Is it also you clustered index? I am not a fan of using uniqueidentifiers as a primary key for almost every situation. But if there is a different column used as the clustered index they aren't horrible. A clustered index on a uniqueidentifier will exceed 90% fragmentation in as few as 1000 rows. I am guessing this is what you are running into. Find another column(s) to contain your clustered index and your performance will improve greatly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387
Thanks for your quick replay.
uniqueidentifier column(with primary key (now)) has been used in table joining in current design.

so if i remove the current primary key on uniqueidentifier, will the join perfomace degrade or grade(improve).
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63935 Visits: 17974
yuvipoy (3/4/2014)
Thanks for your quick replay.
uniqueidentifier column(with primary key (now)) has been used in table joining in current design.

so if i remove the current primary key on uniqueidentifier, will the join perfomace degrade or grade(improve).



I realize it is your primary key. That as a primary key is another topic. Is it also the clustered index? That is the problem here.

I would recommend NOT removing your primary key on several 10 million row tables. Your performance will be even worse.

What does this query return?


select *
from sys.indexes
where OBJECT_NAME(object_id) = 'YourBaseTable' --whatever the name of your table is



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387
It is Non-Clusterindex

object_id   name                                                                                                                             index_id    type type_desc                                                    is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks has_filter filter_definition

1890105774 PK_XXXXXX 2 2 NONCLUSTERED 1 1 0 1 0 80 0 0 0 1 1 0 NULL


Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63935 Visits: 17974
yuvipoy (3/4/2014)
It is Non-Clusterindex

object_id   name                                                                                                                             index_id    type type_desc                                                    is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks has_filter filter_definition

1890105774 PK_XXXXXX 2 2 NONCLUSTERED 1 1 0 1 0 80 0 0 0 1 1 0 NULL



You aren't giving me a lot to go on here. Is this table a heap or is there a clustered index? How many rows are you trying to insert? What is the source of the data for the insert?

You have to remember that I can't see your screen and have no idea what your system is like. I am shooting blind in the dark.

Maybe you should take a look at this article that explains how to post questions with performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387
As i said
TableA C1 uniqueidentifier (PK), C2 uniqueidentifier, C3 ......
PK is a cluster index
TableB C1 uniqueidentifier(PK) , C2 uniqueidentifier (FK_TableA_uniqueidentifier ), C3 ......
Column c1 PK is a cluster index and C2 is non clusterindex

Trying to insert some million of rows
C++ is the used to insert the data.
In C++ the "sql statements" are used to insert the data.
Since there where no stored procedure used , unique identifer is kept as source for uniquness in the table.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63935 Visits: 17974
yuvipoy (3/6/2014)
As i said
TableA C1 uniqueidentifier (PK), C2 uniqueidentifier, C3 ......
PK is a cluster index
TableB C1 uniqueidentifier(PK) , C2 uniqueidentifier (FK_TableA_uniqueidentifier ), C3 ......
Column c1 PK is a cluster index and C2 is non clusterindex

Trying to insert some million of rows
C++ is the used to insert the data.
In C++ the "sql statements" are used to insert the data.
Since there where no stored procedure used , unique identifer is kept as source for uniquness in the table.


I am trying to help you here but you just aren't providing details. I asked several questions previously none of which you answered.

I think you are inserting data into TableA which has a uniqueidentifier as the clustered index? That is source of your problem. The index fragmentation is going to be through the roof.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387

I think you are inserting data into TableA which has a uniqueidentifier as the clustered index? That is source of your problem. The index fragmentation is going to be through the roof.


How it can be index fragmentation ? it is uniqueidentifier right , we should not put cluster index to uniqueidentifier?
for child tables it is non cluster-index only.
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2448 Visits: 1387
Thanks Sean!
I got it we should not go with cluster index for guid since this will cause physical ordering of the data on the disk which is pointless one.

One more thing

how about Selecting the statement
Select <set of columns> from table where Col_uniqueidentifier={uniqueidentifier}
say now if the column is with py key non cluster index with 10 million record will the query will be faster to return data ?

or the query with col_bigint column instead of col_uniqueidentifier

Select <set of columns> from table where col_bigint ={bigint }
say now if the column is with py key (non) cluster index with 10 million record will the query will be faster to return data ?

Can you suggest on this
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