SQL Server Performance Forum – Threads Archive
View Optimization
Hey All
<br /><br />I have some views gathering data from monthly tables (same definitions) with UNION ALL.<br />for example:<br /><br />CREATE VIEW Sales<br />AS<br />SELECT * FROM Sales200501<br />UNION ALL<br />SELECT * FROM Sales200502<br />UNION ALL<br />SELECT * FROM Sales200503<br />UNION ALL<br />SELECT * FROM Sales200504<br /><br />Of course when many tables involved performance is weak… and unfortunately I can’t use Partitioned views…<br /><br />any suggestions for performance boosting (through the view itself or the base tables)??<br /><br />will putting a where clause on each table make a change? <br />I mean:<br /><br />CREATE VIEW Sales<br />AS<br />SELECT * FROM Sales200501 WHERE SaleDate BETWEEN ‘20050101’ and ‘20050131’<br />UNION ALL<br />SELECT * FROM Sales200502 WHERE SaleDate BETWEEN ‘20050201’ and ‘20050228’<br />UNION ALL<br />SELECT * FROM Sales200503 WHERE SaleDate BETWEEN ‘20050301’ and ‘20050331’<br />UNION ALL<br />SELECT * FROM Sales200504 WHERE SaleDate BETWEEN ‘20040101’ and ‘20050430’<br /><br />thanks <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">will putting a where clause on each table make a change?<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Pomela, <br /><br />You posted the code, so all that’s left is to beg the question, "have you tried it?" <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> On the subject of adding a where clause to improve performance, I’m going to assume you know that it’s always a good rule of thumb to apply a where clause if you can. But what exactly do these tables hold? Your sql implies that you’re holding "sale" data per month. If so, then the where clause you’re using seems obselete, and I don’t think it would help performance (ultimately, I’d test it though). What I’m curious to know is, do you really need to use select *? If you only need certain fields, then don’t make the db do more work than it needs to. Just seeing the view coded as…<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE VIEW Sales<br />AS<br />SELECT * FROM Sales200501<br />UNION ALL<br />SELECT * FROM Sales200502<br />UNION ALL<br />SELECT * FROM Sales200503<br />UNION ALL<br />SELECT * FROM Sales200504<br /></font id="code"></pre id="code"><br />…is pretty vague. What you’re code is telling me, is that this view always needs to return every single field and every single row from the above tables. This may be exactly what you need, though I’ve rarely worked w/qry’s that absolutely needed to use select *. I’d like to hear more about what the view is being used for, and what the select statements running on it are doing.<br /><br />edit: spelling correction.
Hey Null…<br /><br />1. I made some tests and checked the execution plan, but there was no difference.. <br /><br />2. I don’t use * on my production db, i just used it here because the fields names are irrelevant..<br /><br />thanks anyway <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
It’s hard to recommend anything without knowing the queries that are accessing the view.
]]>