Vendr - 1.8.6-beta0007
Company I'm working with at the moment has a copy of Vendr in prod running on SQL Azure. I noticed today that primary key columns do not have clustered indexes set.
Wondering if Vendr is like that OOTB or whether it is something they have done for some reason.
It's probably mostly the fact that our primary keys are Guids and so I'm not sure that an index would help that much, but this said, we did in a later release switch to using COMBS to allow an effective index to be applied so this probably could be added now.
I will confess I'm not a SQL expert so I'm open to suggestions on what index's make most sense.
Been reading up a bit. It seems that clustered indexes on GUIDS can cause fragmentation because, as they are random, data is never inserted at the end of a table. Inserts will be slower. But lookup, delete and update speed would improve. I wouldn't consider myself an expert either though.
I would have thought that, with COMBS, the clustered index GUID issues would mitigate some of the fragmentation issues as the GUIDS are sequential.
Weird, I could have sworn I back-ported it to v1, but it does indeed seem I only released it in Vendr v2. Vendr v2 still works with U8.17 though so it should be somewhat upgradable (but v2 is obs a big release with breaking changes).
Yea, we did have a customer having SQL issues with LOTs of entities and I think it boiled down to this and so hence why I got into looking at COMBS. In v2 there is a new GuidFactory which by default uses RT.Comb to generate SQL Server friendly combs which should allow efficient indexs to be added. The GuidFactory uses a Vendr IGuidProvider though which can be replaced if you use some other DB type that requires an alternative COMB strategy.
Matt
EDIT: Looking back at my private git repo, it looks like I did do the work for this to be added to v1 in a feature branch but I think I must have sent a custom build to the client to test and the plan was to eventually merge it back into v1 but I don't think I heard back from the client, so I instead added to to v2 by default and waited to see if any problems arose from people making the move.
I think the problem is though, I think COMBS are only efficient if you use them from the start of a site build. Customers upgrading with a bunch of entities in the DB will already have ID's that will be just random GUIDs so those are always going to affect index efficiency. But I though it was at least worth adding to v2 for new site builds moving forward.
Vendr - Clustered indexes for Primary Key columns
Vendr - 1.8.6-beta0007 Company I'm working with at the moment has a copy of Vendr in prod running on SQL Azure. I noticed today that primary key columns do not have clustered indexes set.
Wondering if Vendr is like that OOTB or whether it is something they have done for some reason.
Regards
Julian
Hi Julian,
It's probably mostly the fact that our primary keys are Guids and so I'm not sure that an index would help that much, but this said, we did in a later release switch to using COMBS to allow an effective index to be applied so this probably could be added now.
I will confess I'm not a SQL expert so I'm open to suggestions on what index's make most sense.
Matt
Thanks Matt
Did you only switch to COMBS for U9 releases?
Been reading up a bit. It seems that clustered indexes on GUIDS can cause fragmentation because, as they are random, data is never inserted at the end of a table. Inserts will be slower. But lookup, delete and update speed would improve. I wouldn't consider myself an expert either though.
I would have thought that, with COMBS, the clustered index GUID issues would mitigate some of the fragmentation issues as the GUIDS are sequential.
Any sql gurus out there?
Julian
Hi Jules,
Weird, I could have sworn I back-ported it to v1, but it does indeed seem I only released it in Vendr v2. Vendr v2 still works with U8.17 though so it should be somewhat upgradable (but v2 is obs a big release with breaking changes).
Yea, we did have a customer having SQL issues with LOTs of entities and I think it boiled down to this and so hence why I got into looking at COMBS. In v2 there is a new
GuidFactorywhich by default uses RT.Comb to generate SQL Server friendly combs which should allow efficient indexs to be added. TheGuidFactoryuses a VendrIGuidProviderthough which can be replaced if you use some other DB type that requires an alternative COMB strategy.Matt
EDIT: Looking back at my private git repo, it looks like I did do the work for this to be added to v1 in a feature branch but I think I must have sent a custom build to the client to test and the plan was to eventually merge it back into v1 but I don't think I heard back from the client, so I instead added to to v2 by default and waited to see if any problems arose from people making the move.
I think the problem is though, I think COMBS are only efficient if you use them from the start of a site build. Customers upgrading with a bunch of entities in the DB will already have ID's that will be just random GUIDs so those are always going to affect index efficiency. But I though it was at least worth adding to v2 for new site builds moving forward.
is working on a reply...