April 9, 2002 at 4:05 pm
This is perhaps a very, very simple question, but does anybody out there know of any limit to the number of UNION's allowed in a select statement (used in a VIEW)? My VIEW is a simple union of a number of tables, but am concerned that it may be limited to something like 256. Thanks in advance.
Chuck
April 9, 2002 at 4:16 pm
I believe the total is 256 per select. So I thnk this is the max you can use.
Steve Jones
April 9, 2002 at 4:54 pm
Chuck, could you show us what you're using the union for? Sometimes we can offer alternatives if we have a good idea of the problem.
Andy
April 9, 2002 at 7:21 pm
This is for a very high volume database for check records (Over 10,000,000 per day), being received from multiple banks. Each bank has their own table, which is UNION'ed together in one big view (for reporting purposes with Crystal Reports). In addition, new tables are created each month.
So, the main application simply deals with individual tables, but the reporting function uses the VIEW that ties them all together.
April 10, 2002 at 4:44 am
Long term you'll be better off putting all that data in one table and just adding a additional column to indicate which bank. No limit, more efficient than adding a union every time you add a bank.
Andy
April 10, 2002 at 8:30 am
That's basically what we came up with, since there is a practical limitation. However, we want to 'roll off' each month as it becomes stale (basically, eliminate to long term archive the entire month from four months ago). So we're looking at using filegroups for this purpose.
The theory here is that we'd create a filegroup per month, so backups and restores can be done on entire months at one time. Is this a good plan?
Also, how do you guys feel about separating the data and index files into separate filegroups for performance reasons? The production database is running on a big Raid 10 system, so I'm not sure it will benefit from overlapping I/O.
April 10, 2002 at 10:32 am
I dont have enough hardware or data to find out for sure - my philosphy so far is to use filegroups only when I can't get it all on one drive or when can't back the entire thing up in the time allowed. Luckily I have neither problem right now! Having filegroups makes tuning VERY interesting in a not pretty kind of way. I think I'd avoid it until all other options were exhausted.
Andy
April 10, 2002 at 11:46 am
quote:
Also, how do you guys feel about separating the data and index files into separate filegroups for performance reasons? The production database is running on a big Raid 10 system, so I'm not sure it will benefit from overlapping I/O.
I have several large databases setup like this and have notice some performance gains, RAID level has more to do with write efficiency not read. I have not run into any issues with filegroups (each having a seperate group name), my biggest benefit cam from having static data in one filegroup and read/write tables in another so when the file needs to expan again it actually does not effect the other files and there is less fragmentation on the drive array.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply