Opinions, performance implications of 1 bidge table vs 2 bridge tables

Question

Opinions, performance implications of 1 bidge table vs 2 bridge tables

ccakinberk

Valued Member

Points: 55
More actions
December 16, 2008 at 12:25 pm

#383586

I have 2 bridge tables,
PROD_SKU (PID,SKUID)
SKU_Category (SKUID,CatID)
I find myself joining to both tables often, this is how I have written all the stored procedures, speed is good.
However
WOuld it be better to create one table that is called
PROD_SKU_Category (PID,SKUID,CatID) instead of the above?
I realize it would have more rows because of some of the duplication, is it
better to have one large bridge table vs 2 smaller bridge tables?
I would have to modify all my stored procedures, so this will take some work to implement but if it is a real benefit, I would like to possibly do it
Thank You

Viewing 7 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is a consultants answer; It Depends.

You'd have to look at the queries you have written in your stored procedures to determine if one table or two is the best.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

ccakinberk (12/16/2008)
...I find myself joining to both tables often, this is how I have written all the stored procedures, speed is good...WOuld it be better to create one table that is called
PROD_SKU_Category (PID,SKUID,CatID) instead of the above?

From a pure data modeling perspective, if PID and SKUID are not a 1 to 1 mapping (which I'm assuming they aren't or you wouldn't need a bridge table) then this is probably not the way to go, since you've changed the level of granularity in the table. Having the separate bridge tables would reflect how the data is logically related much better.

From a performance perspective, If you had a query that gets all the SKUs in a category, you'd now have to do a DISTINCT on the list if there was any SKU that could have more than one PID associated to it. Doing a DISTINCT easily slows down a qeury as SQL Server needs to do extra steps in the execution plan to sort and filter the data. It would only help performance of a query that needed to get all the products of a category, or all the categories of a product. I don't know what percentage of your queries currently use both bridge tables in them.

ccakinberk Valued Member Points: 55 More actions · Answer 3

Thank You.

I think I will keep the 2 bridge tables as this represents the data modeling better, I suspect there is not a big perfermance difference between the two.

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

You could also make a view from the two tables without much, if any, impact on performance because it'll just be doing the same joins that you're already frequently doing in you code. It will, however make writing code a little easier.

--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)

raj acharya SSCrazy Points: 2805 More actions · Answer 5

hi,

I am also in support to keep your existing settings keep your two tables... don't make one table ....it will improve your performance if you have two tables and join them with meaningfull query to improve performance ...

like you can join tables and take only necessary data with necessary columns ... and also you can create view for better performance.

Raj Acharya

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

Just to be sure... creating a view doesn't necessarily improve any performance and, if it's done badly, can be quite the performance hog.

--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)