SQL Views with UNION Performance 2

[dfrazell]

I currently have 8 identical SQL 2000 databases, one for each regional office. I need to build some reports where I can pull data from all 8 regions so I'm considering creating SQL Views with Union operators for 4 or 5 specific tables.

Is this a decent solution?
Will these views impact the performance of the individual regional database tables?
Any other concerns I should be aware of?

For example a SQL View would look like this:

SELECT     'EM1' AS Region, Part, Cost FROM EM1.dbo.PartMstr
UNION
SELECT     'EM2', Part, Cost FROM EM2.dbo.PartMstr
UNION
SELECT     'EM3', Part, Cost FROM EM3.dbo.PartMstr
UNION
SELECT     'EM4', Part, Cost FROM EM4.dbo.PartMstr
UNION
SELECT     'EM5', Part, Cost FROM EM5.dbo.PartMstr
UNION
SELECT     'EM6', Part, Cost FROM EM6.dbo.PartMstr
UNION
SELECT     'EM7', Part, Cost FROM EM7.dbo.PartMstr
UNION
SELECT     'EM8', Part, Cost FROM EM8.dbo.PartMstr

Two tables I want to create views for have the following record count:

[b]Inventory Transactions:[/b]
Reg   Records
---   -------
EM1 – 450,000
EM2 – 125,000
EM3 – 450,000
EM4 –  50,000
EM5 –  32,000
EM6 –  20,000
EM7 –  60,000
EM8 –  30,000

[b]Part Master:[/b]
Reg   Records
---   -------
EM1 – 1,500
EM2 – 1,800
EM3 – 3,000
EM4 – 1,050
EM5 – 1,400
EM6 – 1,000
EM7 – 1,000
EM8 – 1,000

 

[IanWaterman]

You will be better off to use a stored procedure and store the recordset in a temporary table, which can be dropped after the SP is executed.

Ian

 

[RiverGuy]

Use a UNION ALL instead of UNION. UNION checks for and eliminates duplicates. And since your literal of 'EMx' is different for each environment, you won't have duplicates, so no need to check.

 

[gmmastros]

It's not necessarily bad. Mostly it depends on how you use it.

One thing you should consider is UNION vs. UNION ALL

UNION will filter out duplicates.
UNION ALL will return duplicates.

Since you are hard-coding a region (EM 1-8), you may not have any duplicates to begin with. UNION will still filter out duplicates for each query involved in a union, so if you have duplicate part/cost in any of the tables, then UNION will only return one row for them. This is likely to be your biggest performance problem. If you can, I would encourage you to use UNION ALL instead.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfrazell]

What would the advantage be to build a temporary table in a stored proc and then drop it when I'm done? Wouldn't I experience be bigger performance hit every time someone runs the report vs. having the a view already established?

 

[gmmastros]

What would the advantage be to build a temporary table in a stored proc and then drop it when I'm done?

None that I can see.

You should at least consider creating a data warehouse for the combined data (from the 8 regions). I would only do this if the performance of the UNION query is poor.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

I thought temporary tables made better use of memory over views. Though on reflection I have only used them to do a final select of data combining one of more temp tables.

Ian

 

[Welshbird]

I've become very fond of table variables recently - mostly because I really like the way they tidy up after themselves. I use this often when needed to use data from a remote but linked server to compare against the server to which I'm connected.

Hope this makes sense - I can't find a generic example to post at the minute!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[RiverGuy]

The issue with the temporary table is that for each time the query is run, the temporary table has to be filled with all of the data from each of those tables, which takes time to do. Temporary tables and table variables are often quite helpful when there is a lot of logic in the query that needs to be broken down into parts. But in this case, its just a simple select from multiple tables.