Will Tables affect Performance...?

Question

Will Tables affect Performance...?

ard5karthick

SSCommitted

Points: 1603
More actions
April 30, 2012 at 7:11 am

#392148

Hi Guys,
I have a doubt will creation more Tables affect the performance of the Database.
Say i have a table in the Below Structure containing few data in a single table.
ID Code Value
1 CU India
2 CU U.S.S
3 CU Germany
4 CU England
5 CU Russia
6 CU Iraq
7 CP Apple
8 CP Google
9 CP IBM
10 CP Microsoft
11 CP Facebook
12 CP Amazon
13 SP Tennis
14 SP Soccer
15 SP Rugby
16 SP Cricket
17 SP Snooker
18 SP Hockey
19 SP Shooting
20 SP Boxing
If i split the Records of each Code in a single Table will it reduce the performance.
Table 1
ID Code Name
1 CU India
2 CU U.S.S
3 CU Germany
4 CU England
5 CU Russia
6 CU Iraq
Table 2
ID Code Name
1 CP Apple
2 CP Google
3 CP IBM
4 CP Microsoft
5 CP Facebook
6 CP Amazon
Table 3
ID Code Name
13 SP Tennis
14 SP Soccer
15 SP Rugby
16 SP Cricket
17 SP Snooker
18 SP Hockey
19 SP Shooting
20 SP Boxing
Say i have some 1000 Codes will it be better to have all of them in a single Table or to have them in 1000 Separate Table...
Please help me in understanding it...
Thanks in Advance.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Nothing to do with performance.

Your first design is what's called 'The One Lookup Table' design, and it's usually a complete disaster. With that design it's all too easy for data to get mangled and for things like Country of Cricket and sport of England to find their way into the database (and don't say the app will handle that, heard that before and cleaned up the mess afterwards)

Tables should contain one thing and one thing only, not an amalgamated collection of multiple unrelated objects

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323482 More actions · Answer 2

-edit: just saw Gail identified your logic as an Entiry-value table, and not a single table of similar values.

~~single table for sure.~~

That's a basic premise of normalization of your data: you keep data that is the same together.

the key to performance here is going to be putting a proper index(or indexes) ont the table based on how it gets searched with a WHERE statement.

so if that table gets searched via a WHERe statement by NAME='India', for example, you want an index on that, if it seems to be unique enough.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

I'm not a fan of this kind of generic lookup tables - I would create a table for each one of the "codes" you have to describe.

Doing it properly e.g. as many "code" tables as needed - will not have a negative effect on performance and will make you design more elegant and easy to maintain.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

ard5karthick SSCommitted Points: 1603 More actions · Answer 4

ard5karthick

SSCommitted

Points: 1603

May 2, 2012 at 12:34 am

#1482197

Hi Guys,

Thanks for the reply.

Was help full...