25112
asked on
query in one big table vs many different tables
for reporting purpose , in sql server, which scenario is better?
one server that has many records for different categories, with one column making the difference.. (indicator telling what category of row of record it is)
vs
creating a separate table for each category..
for example, the column that makes the difference could have data like
'car','bus','bike','suv','
the table will result in millions of records regardless..
will one big table with proper index be the way to go? or is partitioning more beneficial for SELECT report queries?
one server that has many records for different categories, with one column making the difference.. (indicator telling what category of row of record it is)
vs
creating a separate table for each category..
for example, the column that makes the difference could have data like
'car','bus','bike','suv','
the table will result in millions of records regardless..
will one big table with proper index be the way to go? or is partitioning more beneficial for SELECT report queries?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PatelAlpesh,
I suspect you posted in the wrong thread as the comment you posted makes no sense in the context of the question posted by the author.
I suspect you posted in the wrong thread as the comment you posted makes no sense in the context of the question posted by the author.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>Are you going to be selecting the records together comparing accross categories or what?
No. They will all be processed seperately.
>>huge differences in the related columns for each category
only one or rarely a few columns will be different, most of the time.
>>how many different categories apply to any one record.
only one category for each record.
>>Twenty+ of separate tables makes the code much harder to maintain.
If it were only 5 or 10, would you suggest seperating would be better.. would you draw a line in terms of how many tables will change the decision on that.? I realize this is where Mark was referring to partitioning benefits and disadvantages.
can you commend on this:
what if the category column can be coverted into a date field (an appropriate lookup is available with correlating category to a distinct date).. will that help in resolving a firm answer for this?
each category of data is 2 m records.
thank you-
No. They will all be processed seperately.
>>huge differences in the related columns for each category
only one or rarely a few columns will be different, most of the time.
>>how many different categories apply to any one record.
only one category for each record.
>>Twenty+ of separate tables makes the code much harder to maintain.
If it were only 5 or 10, would you suggest seperating would be better.. would you draw a line in terms of how many tables will change the decision on that.? I realize this is where Mark was referring to partitioning benefits and disadvantages.
can you commend on this:
what if the category column can be coverted into a date field (an appropriate lookup is available with correlating category to a distinct date).. will that help in resolving a firm answer for this?
each category of data is 2 m records.
thank you-
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
really appreciate your input... i will provide feedback as i implement these ideas and performance on queries
thanks much.
thanks much.
It will improve performance.
If not the case then better to create diff table for good performance.
But maintainability is the drawback.