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 Thursday, March 6, 2014 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
yuvipoy (3/6/2014)
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.



It is the logical order not the physical storage order. Using a guid for a clustered index produces a very high frequency of page splits which is why the index gets so fragmented.


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


In general it will be a bit faster because a guid is 16 bytes where a bigint is only 8 bytes. It is not likely to make a huge difference.


_______________________________________________________________

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 #1548427
Posted Thursday, March 6, 2014 8:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155

In general it will be a bit faster because a guid is 16 bytes where a bigint is only 8 bytes. It is not likely to make a huge difference.

You mean to say that guid with non cluster index and bigint with cluster index we behave in similar manner.
how about if there is huge volume of data say about 10 million records will the query fetching time will be same

Post #1548562
Posted Friday, March 7, 2014 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 13,078, Visits: 12,529
yuvipoy (3/6/2014)

In general it will be a bit faster because a guid is 16 bytes where a bigint is only 8 bytes. It is not likely to make a huge difference.

You mean to say that guid with non cluster index and bigint with cluster index we behave in similar manner.
how about if there is huge volume of data say about 10 million records will the query fetching time will be same



That depends. Are you returning the guid column in that 10 million rows? If so, it will be slower because the amount of data being returned is more with a guid. If not, then it would be about the same.


_______________________________________________________________

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 #1548730
Posted Tuesday, March 11, 2014 4:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
At present one of my column is having uniqueidentifier with clustered, unique, primary key located on PRIMARY

The data is inserted in the below manner

66B72949-658B-4D77-A813-01AC3278AD53
066C863E-1A14-4F0C-A4E6-053D02F98CB8
63C1E1C9-FABF-444E-AE01-0752A8C9A7DD
AE6CCB63-1764-42C6-A7E2-08F04A2CA4DB
F2D58665-301B-467F-B0A4-08F46A4B71A3
7CC0A364-C455-4FD0-A4F8-0951693312C0

Why it is not in logical order as :
066C863E-1A14-4F0C-A4E6-053D02F98CB8
63C1E1C9-FABF-444E-AE01-0752A8C9A7DD
66B72949-658B-4D77-A813-01AC3278AD53
AE6CCB63-1764-42C6-A7E2-08F04A2CA4DB
F2D58665-301B-467F-B0A4-08F46A4B71A3
7CC0A364-C455-4FD0-A4F8-0951693312C0

here logical order i mean to say as order by alpha characters 0-9 and a-z

or the inserted data is in correct formate only if so how ?
Post #1549634
Posted Tuesday, March 11, 2014 4:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 40,174, Visits: 36,570
GUID ordering is not alphanumeric. There's articles on the web which describe how GUIDs are ordered, not that it usually matters much for users.


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 #1549635
Posted Tuesday, March 11, 2014 5:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
GilaMonster (3/11/2014)
GUID ordering is not alphanumeric. There's articles on the web which describe how GUIDs are ordered, not that it usually matters much for users.


Then why the column can not be as cluster index? why not if it is not alpha numeric?
Then What is the draw back if we use cluster index on GUID column.
Post #1549656
Posted Tuesday, March 11, 2014 5:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 40,174, Visits: 36,570
yuvipoy (3/11/2014)
Then why the column can not be as cluster index?

GUIDs certainly can be used as clustered indexes, nothing prevents that.

[quote]why not if it is not alpha numeric?


Don't understand what you're asking

Then What is the draw back if we use cluster index on GUID column.


From earlier in this thread:

Using a guid for a clustered index produces a very high frequency of page splits which is why the index gets so fragmented.



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 #1549660
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse