Guid FK vs Int FK

Question

Guid FK vs Int FK

JKSQL

SSCrazy Eights

Points: 8323
More actions
December 9, 2014 at 2:32 pm

#292707

I ran into a performance issue the other day and was really confused.
The stored procedure filled a temp table with the parent objects. If the particular child was the only thing referenced by the parent object it removed the parent. The parent table was a guid key FKed to the child. the Child Id was nullable. the child field was all null for all 5000 rows. We were trying to delete around 5K in children rows. This portion of the query was taking 80% of the time to check the FK before it deleted. It was null though. I wanted to see what would happen if I changed it to int based. It dropped the time in a 1/4. I really thought that was strange that even a null guid had so much overhead.
Does anyone know of a reference that could help describe what is happening in the FK where the guid takes longer even though it is null? I want to know if this is a fact or if my test was flawed.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

JKSQL (12/9/2014)
I ran into a performance issue the other day and was really confused.
The stored procedure filled a temp table with the parent objects. If the particular child was the only thing referenced by the parent object it removed the parent. The parent table was a guid key FKed to the child. the Child Id was nullable. the child field was all null for all 5000 rows. We were trying to delete around 5K in children rows. This portion of the query was taking 80% of the time to check the FK before it deleted. It was null though. I wanted to see what would happen if I changed it to int based. It dropped the time in a 1/4. I really thought that was strange that even a null guid had so much overhead.
Does anyone know of a reference that could help describe what is happening in the FK where the guid takes longer even though it is null? I want to know if this is a fact or if my test was flawed.

There is nowhere near enough information here to offer much. Tables structures, index definition, index fragmentation, constraint setup all could play into this.

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 2

Sorry, I can give more information.

In the parent the table is a table that has a PK on the guid (non clustered). The table is empty.

the Child table has an int based PK clustered.

There are no other indexes on these tables

This is from a restored DB on my local machine. there is no fragmentation. The reason it was non-clustered is because inserts were supposedly slow. The table is empty so the index should be a shared index anyways. The FK is nullable. Does that help?

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

JKSQL (12/9/2014)
Sorry, I can give more information.
In the parent the table is a table that has a PK on the guid (non clustered). The table is empty.
the Child table has an int based PK clustered.
There are no other indexes on these tables
This is from a restored DB on my local machine. there is no fragmentation. The reason it was non-clustered is because inserts were supposedly slow. The table is empty so the index should be a shared index anyways. The FK is nullable. Does that help?

How about the actual ddl for these two tables?

BTW, glad to hear your guid as a PK is nonclustered. GUIDs as a PK are ok but when they are clustered it is not a good story.

The part of your story that I don't understand is this..." If the particular child was the only thing referenced by the parent object it removed the parent."

I am actually not really sure what your question is to be honest. If there something in a FK relationship not doing what you expect or is it something about the datatype being used as keys and how it affects performance? I am a bit confused.

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 4

JKSQL (12/9/2014)
I ran into a performance issue the other day and was really confused.
The stored procedure filled a temp table with the parent objects. If the particular child was the only thing referenced by the parent object it removed the parent. The parent table was a guid key FKed to the child. the Child Id was nullable. the child field was all null for all 5000 rows. We were trying to delete around 5K in children rows. This portion of the query was taking 80% of the time to check the FK before it deleted. It was null though. I wanted to see what would happen if I changed it to int based. It dropped the time in a 1/4. I really thought that was strange that even a null guid had so much overhead.
Does anyone know of a reference that could help describe what is happening in the FK where the guid takes longer even though it is null? I want to know if this is a fact or if my test was flawed.

I could be over simplifying but it's pretty simple, IMHO. A GUID has 16 bytes and an INT has 4. Sounds like a 4 to 1 ratio to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 5

I appreciate the help. I am not doing a very good job explaining this. I am going to look into it more and see if I can find a concrete question to ask. I still feel to vague in my request for a good answer.