View Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

View Optimization

Author

Topic

pomela
Starting Member

15 Posts

Posted - 2006-01-15 : 07:42:44

Hey All (:

I have some views gathering data from monthly tables (same definitions) with UNION ALL.
for example:

CREATE VIEW Sales
AS
SELECT * FROM Sales200501
UNION ALL
SELECT * FROM Sales200502
UNION ALL
SELECT * FROM Sales200503
UNION ALL
SELECT * FROM Sales200504

Of course when many tables involved performance is weak... and unfortunately I can't use Partitioned views...

any suggestions for performance boosting (through the view itself or the base tables)??

will putting a where clause on each table make a change?
I mean:

CREATE VIEW Sales
AS
SELECT * FROM Sales200501 WHERE SaleDate BETWEEN '20050101' and '20050131'
UNION ALL
SELECT * FROM Sales200502 WHERE SaleDate BETWEEN '20050201' and '20050228'
UNION ALL
SELECT * FROM Sales200503 WHERE SaleDate BETWEEN '20050301' and '20050331'
UNION ALL
SELECT * FROM Sales200504 WHERE SaleDate BETWEEN '20040101' and '20050430'

thanks :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-16 : 13:18:22

There isn't really a way to improve the performance as you are requesting all data from each of the tables, so a scan will occur. Adding a WHERE clause will not help, but it might make it worse if the column isn't indexed. Do you at least have a clustered index on these tables?

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-01-17 : 02:48:24

Could you use a stored procedure instead?

I'm sort of thinking that it could examine the parameters and INSERT INTO some #TempTable for each of the source tables but ONLY IF the parameters are relevant to that table - so the minimum number of tables would be checked.

"unfortunately I can't use Partitioned views"

Are you sure?

Is it something we might be able to help you work around?

Kristen

pomela
Starting Member

15 Posts

Posted - 2006-01-17 : 04:47:42

thank you Kristen.
I can't use a stored procedure, i need a view.
the partition view issue is not a technical problem, but thanks for your help (:

anyway, I already solved this issue.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-17 : 06:47:42

"anyway, I already solved this issue...."
....go on give us a clue!!

pomela
Starting Member

15 Posts

Posted - 2006-01-17 : 07:37:48

OK...
For the issue I first presented, a where clause won't make any difference, unless it's filtering data (and of-course you have an appropriate index).
But my situation is a bit different, I join data from several tables (for each union level) and some of the tables-data is unnecessary, so in this case the where clause made a big difference, and decreased the execution time by half and more.
Odd as it sounds, the execution plan didn't change at all, and that's why I had thoughts about it, but when tested on production data I saw the improvement.

thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources