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 12»»

Insert is Slow Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 7:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116
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 ......

Post #1547333
Posted Tuesday, March 4, 2014 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1547337
Posted Tuesday, March 4, 2014 7:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116
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).

Post #1547343
Posted Tuesday, March 4, 2014 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1547346
Posted Tuesday, March 4, 2014 9:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116
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

Post #1547633
Posted Wednesday, March 5, 2014 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1547782
Posted Thursday, March 6, 2014 6:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116
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.
Post #1548239
Posted Thursday, March 6, 2014 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1548251
Posted Thursday, March 6, 2014 10:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116

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.


Post #1548390
Posted Thursday, March 6, 2014 10:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 330, Visits: 1,116
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
Post #1548397
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse