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 «««12345»»»

Lots of Key Lookups vs. UniqueIdentifier Clustered Index Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 12:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
dave-L (11/21/2012)
Thanks for your help opc.three.

One last thing: I hate to leave less than optimal code out there for posterity so here is my last, optimized function with all string manipulation removed

CREATE FUNCTION makeGUID
(
@guid AS UNIQUEIDENTIFIER,
@number AS BIGINT
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
RETURN CAST(CAST(@guid AS BINARY(10)) + CAST(@Number AS BINARY(6)) AS UNIQUEIDENTIFIER)
END
GO

One query on that: is @guid providing the randomness while number is just a counter? If so, I don't understand why you have them in that order in the returned value.


Tom
Post #1387570
Posted Wednesday, November 21, 2012 4:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Now wait a minute here.... I thought you had to use the UUID! If you can get away creating your own number, then why are we even worried about this problem? Preserve the external GUID in a column and do everything internally with an IDENTITY value.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1387643
Posted Wednesday, November 21, 2012 6:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:48 AM
Points: 53, Visits: 183
Hi Tom.

It's counter intuitive, but that is how SQL orders UniqueIdentifiers.
Post #1387656
Posted Wednesday, November 21, 2012 6:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:48 AM
Points: 53, Visits: 183
Jeff, there are zillions of lines of app code that have queries joining on and referencing the UUID.
Post #1387657
Posted Wednesday, November 21, 2012 7:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
dave-L (11/21/2012)
Jeff, there are zillions of lines of app code that have queries joining on and referencing the UUID.


So why are you talking about building a function? The Key Lookups just aren't going to be that bad. Certainly no worse than you maing a function.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1387660
Posted Thursday, November 22, 2012 5:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 1,945, Visits: 3,183
My question is: is it better to have all of these key lookups happening or would it be better to just build the clustered indexes on the UUIDs?


Neither. This nightmare is how non-RDBMS people design schemas to fake their familar pointer chains. Neither UUID (intented for web apps only) or IDENTITY (physical record insertion count for one file on one machine) are keys by definition.

Do you have real keys? Can you correct this non-RDBMS?

A clustered index is too valuable to waste. You wan to save it for whatever attributes you use to group your data. For example, if you report by (state, city), then they become part of the clusterd index; likewise if the date of something is the main access method.

Most often non-RDBMS people use their generated physical locator as the cluster because this is how a magnetic tape or disk access modle of data would have done it.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1387859
Posted Thursday, November 22, 2012 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
dave-L (11/21/2012)
Hi Tom.

It's counter intuitive, but that is how SQL orders UniqueIdentifiers.




One of my thicker moments!


Tom


  Post Attachments 
facepalm.jpg (93 views, 3.57 KB)
Post #1387867
Posted Thursday, November 22, 2012 10:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:48 AM
Points: 53, Visits: 183
Hi Jeff. That's my whole question really. Is there a good way to fix this and is it worth trying to fix.

True using a UDF to generate the UUIDs will have more over head than native NEWID(), but my thought was I would save so much time on the scores of thousands of key lookups that occur because my CI is on a useless identity that this would be worth it.

Dave
Post #1387955
Posted Thursday, November 22, 2012 10:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:48 AM
Points: 53, Visits: 183
CELKO,

I'm learning a lot though this process, thinking much more carefully about how I should organize my CIs.

Dave
Post #1387956
Posted Thursday, November 22, 2012 7:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
CELKO (11/22/2012)
[quote]Most often non-RDBMS people use their generated physical locator as the cluster because this is how a magnetic tape or disk access modle of data would have done it.


That may be true for non-RDBMS people but others realize the value of it in preventing page splits of the data involved in the CI. Page splits are horribly expensive things both CPU and I/O wise on heavily inserted OLTP tables. Page splits are a frequent cause of massive GUI timeouts.

If a table is mostly static, then I agree... the clustered index should be used for something else. If the table suffers a lot of inserts, then a CI on a narrow, ever increasing, and unique column such as an IDENTITY column or a DATETIME column and an IDENTITY column as a uniquefier is generally the way to go. And, yes... it's very much like mag tape requirements. It worked for mag tape and it works here.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388004
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse