July 31, 2012 at 9:54 am
Hi all,
This is more of a generalized question but I can't find an answer anywhere...
I have 10 tables all containing a Unique account number field [Account] and some additional information (about 10 columns).
Each of these tables contains between 100,000 and 1.5 Million rows.
For my front end programming I am going to do a "Select Where Account = 123456789" query call every time page is refreshed based on current account being viewed.
With the new coding, it is a LOT cleaner and more logical for these 10 tables to be combined into 1 large table with an identity seed and indexing the Account field
My question is, from a performance basis, does it make more sense for me to make this gigantic table or just create a stored proc performing a UNION across the 10 tables every time the page is refreshed?
We have about 75 users doing this constantly throughout the day and I don't want to unnecessarily tax the database server if one is far superior to the other?
Any thoughts or suggestions?
Thanks in advance!
Andrew
July 31, 2012 at 10:33 am
You should test both approaches.
But, in general, I would expect you'd get better performance by putting all the data into a single table. That assumes that the index on Account# is selective enough to result in seeks to retrieve the data. In general, you'll get a single pass through one index instead of a pass through 10 different indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2012 at 11:58 am
Thanks Grant!
There should only be a maximum of 10 results per query and it looks like it's much quicker than the Union in all the tests I've done so far. I just wanted to be sure there wasn't some trick with Union / Union All that makes it quicker to search many smaller tables instead of one large.
Looks like I'm good to go.
Thanks again Grant and sqlservercentral!
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply