Rank: Member
Joined: 8/31/2009(UTC)
Posts: 2
|
I've inherited maintenance on this app, and I'm working my way through some database performance issues. I noticed the bvc_Product table has the "bvin" field set as the primary key with a non-clustered index, where the ID field is clustered and identity.
Is this the default setting? Or has someone prior to me done this on our installation?
I'm seeing simple queries against that table do some strange lookups and nested joins...
Thanks,
Travis
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 2,136
Was thanked: 1 time(s) in 1 post(s)
|
That is the default setting out of the box.
I don't know why there is an id field at all. I've never seen it used...though I haven' t looked for it either. bvin is used throughout the app. |
|
|
|
|
|
|
Rank: Member
Joined: 11/5/2003(UTC)
Posts: 1,786
|
The primary key is the bvin field which is a GUID type. The integer id field is there so that new product inserts end up at the last database page. Because GUID values are not sequential a large product import will shuffle all database pages without the sequential int field.
If you think there are performance issues with just selecting products from the table I'd be happy to hear about your findings. Specifically, what type of joins are you seeing when just pulling rows from the table?
|
|
|
|
|
|
Rank: Member
Joined: 8/31/2009(UTC)
Posts: 2
|
Thanks - all analysis so far points to bvc_Product_ByCategory_s as the culprit. This procedure is by far the heaviest hitter on our server.
I'm learning about your "guid as primary key" implications as well. I really need to optimzie that sproc tho.
|
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.