StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Turning off default PK Clustered index

Turning off default PK Clustered index

Charles R Hankey — Wed, 18 Jul 2007 20:20:55 GMT

I see that my new best friend - the DDT - creates PK indexes as clustered.

Is there a good way to turn this off, either globally ( in source code ? ) or perhaps there is something I'm missing in the current interface ( writing this before installing 1.61 so if it's there already just point me to it )

I use a lot of UID PKs in tables I am converting from VFP and my understanding is a clustered index isn't a good idea.

TIA

Charles

RE: Turning off default PK Clustered index

Charles R Hankey — Wed, 18 Jul 2007 20:20:55 GMT

You guys are terrific. I'm really glad I asked about this. Clustered indexes here I come :D

RE: Turning off default PK Clustered index

Paul Chase — Mon, 16 Jul 2007 18:02:26 GMT

Awesome Ben I knew the property descriptor was there but didn't think to use it! :)

Thanks

RE: Turning off default PK Clustered index

StrataFrame Team — Mon, 16 Jul 2007 16:09:57 GMT

Yep, Paul is right... Just override the OnSetDefaultValues() method and set your PK in there by calling whatever method you create to get the sequential guid from the date.

[quote]Dim LoPkField As Reflection.PropertyInfo = Me.GetType.GetProperty(Me.PrimaryKeyField)[/quote]

There is a faster way to do that, Paul :)

Imports MicroFour.StrataFrame.Business

...

Protected Overrides Sub OnSetDefaultValues()
MyBase.OnSetDefaultValues()

    '-- Set Value for GUID PK
    '-- Get a reference to the property descriptor (which doesn't use reflection)
    Dim desc As FieldPropertyDescriptor = Me.GetPropertyDescriptor(Me.PrimaryKeyField)

    '-- If the PK is a Guid Generate a Sequential GUID to prevent Index fragmentation
    If desc.PropertyType Is GetType(Guid) Then
        desc.SetValue(Me, Common.NewSeqID())
    End If
End Sub

This way uses the FieldPropertyDescriptors created in the partial class... which are much faster than using reflection ;) Obviously, you're way will work, but the slowness of reflection was the exact reason that we created those descriptors.

RE: Turning off default PK Clustered index

Paul Chase — Mon, 16 Jul 2007 13:48:22 GMT

Charles,

I am using guids as primekeys as well. I don't think StrataFrame needs to change any source code, you can override the onsetdefaultvalues in your base class Biz object and call out to a function to generate sequential guid based on date.

Protected Overrides Sub OnSetDefaultValues()

MyBase.OnSetDefaultValues()

'Set Value for GUID PK

'Get Reference To Primary Key Field Property

Dim LoPkField As Reflection.PropertyInfo = Me.GetType.GetProperty(Me.PrimaryKeyField)

'If the PK is a Guid Generate a Sequential GUID to prevent Index fragmentation

If LoPkField.PropertyType Is GetType(System.Guid) Then

LoPkField.SetValue(Me, Common.NewSeqGuid, Nothing)

End If

End Sub

RE: Turning off default PK Clustered index

Charles R Hankey — Mon, 16 Jul 2007 10:31:28 GMT

Actually, I don't want the server to assign the values. My goal would be to generate the guid in the new() of the bizobj (is this not the way SF works now?) Suggestion was to modify whatever algorithm creates the guid now to use a generated sequential guid. I realize this has to be done without reference to what is currently on the server, so it would probably have to be one of the getdate() methods, but it seems it would add a lot of benefit to using UIDs.

I use the newid() as a default on the backend anyway, so that records created outside of SF ( or VFP ) for testing have the UID but my understanding is this isn't going to matter if the new record created by my SF app already has a PK when it hits the server for the insert

So I guess my question is : Do I understand correctly that currently when you use a UID PK in a table and generate a new() in a SF BO the guid is created on the front end? And if so is this code in the source code?

TIA

Charles

RE: Turning off default PK Clustered index

StrataFrame Team — Mon, 16 Jul 2007 08:53:48 GMT

Yes, using the NewSequentialID() method is awesome, but if you want to use SF with GUIDs and have the server assign the values, you will have to use stored procedures. There is not any way to retrieve the newly assigned value from the DB if you put NewID() or NewSequentialID() in the default values field... it has to be retrieved, stored and then explicitly inserted when you insert the record. So, with SF, that means use sprocs for INSERT and make the PK's parameter an output parameter so it can be retrieved by the DbDataSourceItem and put back into the BO.

RE: Turning off default PK Clustered index

Charles R Hankey — Fri, 13 Jul 2007 12:49:30 GMT

I kind of geeked out on this one as GUIDs are such a part of my VFP life. PKs generated on the backend require a roundtrip to the server before the pk is available for fks in children.

My understanding is currently when you do a NEW() in a SF bizobj on a UID field the UID is generated client-side.

As Ben said, the random nature of UIDs means inserts require a lot of disc thrashing. But this problem has been solved for years by people using one of many flavors of sequential guids ( just google ) There is even a function on SQL 2005 newsequentialid()

http://www.sqlmag.com/Article/ArticleID/50164/Dont_Overlook_the_New_SQL_Server_2005_NEWSEQUENTIALID_Function.html

There are other third party algorithms - written for SQL 2000 before the new function - that use getdate() as part of the function which I think could be adapted to be used client-side and would therefore be sequential without the round trip.

My friend Scott Bellware has posted a really neat solution in his blog

http://codebetter.com/blogs/scott.bellware/archive/2006/12/27/156671.aspx

which will probably be the first thing I try.

Suggest if the MicroFour get a chance they take a look at it and consider implementing something like this in the framework.'

Now you have best of both worlds (except for the disk space part ) Client side surrogate keys that can be used with clustered indexes ( and replication )

Thoughts? :)

RE: Turning off default PK Clustered index

StrataFrame Team — Thu, 12 Jul 2007 09:04:39 GMT

The problem with clustered indexes in a GUID field are that the GUIDs are random, so when a new record is inserted, a significant portion of the data on disk has to be moved to insert the records into the middle of the table. However, with integer-based clustered indexes, the integers are normally sequential (like with an IDENTITY spec), so they just get added to the end an no data needs to be moved around.

Now, that being said... clustered indexes are not always bad on GUIDs... it all depends upon the needs of your application. If you need to be able to SELECT records quickly, then use a clustered index... the INSERT speed will suffer, but the SELECT speed will be improved. But, if you insert a bunch of records and don't use them much after that... like in a log file or something, then don't use a clustered index on a GUID field, because you care more about your INSERT speed than your SELECT speed.

Or at least that's what I've always understood from what I've read on clustered indexes :)

RE: Turning off default PK Clustered index

Keith Chisarik — Wed, 11 Jul 2007 22:34:27 GMT

Found it, clustered indexes are bad for GUID's, like you said because they take too long to build.

Learn something every day.

RE: Turning off default PK Clustered index

Keith Chisarik — Wed, 11 Jul 2007 20:54:55 GMT

Where did you hear that clustered indexes were bad? and what was the reason if you don't mind?

The reason I ask is because I just got done creating a SQL mobile app, in SQL Mobile you can only have a single clustered index per table and I found that the access times for clustered versus non-clustered were about 20x faster (in my environment).

Just curious, because I actually ended up splitting my data into more table than I normally would to be able to have more clustered indexes.

RE: Turning off default PK Clustered index

Trent L. Taylor — Sat, 07 Jul 2007 11:14:04 GMT

Yeah...you got it figured out! I am glad that you are enjoying it...we like it pretty good too :D

RE: Turning off default PK Clustered index

Charles R Hankey — Fri, 06 Jul 2007 21:55:25 GMT

Of course you guys are way ahead of me. Don't know if it was there in 1.6 but in 1.61 editing a pk lets you turn the cluster off and a whole lot of other stuff.

Probably just missed it before.

LOVE the DDT !!!! :D