January 23, 2003 at 12:35 pm
Hello Everybody--
This view is the basis for some aggregate reporting. It picks the best single value when there are multiple permutations. It returns just under 2000 records in about 3 seconds. Functionally, each combination of OccupancyID and y (year) are unique.
Even simple joins made to this view take several minutes to run. I am guessing that this is because the Query Optimizer is looking into the view to build its performance plan. I want to treat the view as if it were a table. How can I accomplish this?
Create VIEW lv_BestWorkOrderPerYear
AS
SELECT AllWOs.OccupancyID, AllWOs.y, bestWOID = COALESCE(Pass.maxWOID, NoPass.maxWOID)
FROM
(--List of all occupancy/workorder/year combinations
SELECT DISTINCT wo.occupancyID, wo.workOrderID, y = year(firstWorkDate)
FROM tblWorkOrders wo
WHERE firstWorkDate IS NOT NULL
)AllWOs
LEFT JOIN
(--Most Recent WorkOrder for each year (with a pass)
SELECT wo.occupancyID,
y = year(FirstWorkDate),
maxWOID = Max(wo.workOrderID)
FROM tblWorkOrders wo join tblSiteInspections si
ON wo.workOrderID = si.workOrderID
WHERE FirstWorkDate IS NOT NULL
GROUP BY wo.occupancyID, year(FirstWorkDate)
HAVING MIN(Result)= 'A')Pass
ON (AllWOs.occupancyID = Pass.occupancyID AND AllWOs.y = Pass.y)
LEFT JOIN
(--Most Recent WorkOrder for each year (without a pass)
SELECT wo.occupancyID,
y = year(FirstWorkDate),
maxWOID = Max(wo.workOrderID)
FROM tblWorkOrders wo join tblSiteInspections si
on wo.workOrderID = si.workOrderID
WHERE FirstWorkDate IS NOT NULL
GROUP BY wo.occupancyID, year(FirstWorkDate)
HAVING MIN(Result)<> 'A')NoPass
ON (AllWOs.occupancyID = NoPass.occupancyID AND AllWOs.y = NoPass.y)
January 23, 2003 at 1:07 pm
If you really need to keep the view, in your stored procedure create a temp table, insert into that table by selecting from the view, then do your joins against the temp table, not against the view.
January 23, 2003 at 2:47 pm
Thanks mromm--
I will need to call this chunk of code in several contexts, I was hoping to just use it as a view for ease of use (change once, call from anywhere). I coded the temp table solution while waiting for responses. Does the connection to SQL need to be dbo to drop a temp table? if #tableName? if ##tableName? I have had issues in the past with non-dbo clients not being able to drop tables (I don't think they were temp tables).
Thanks again.
--Greg
January 23, 2003 at 2:59 pm
Greg, anybody can drop a temp table as long as that user can see it. Of course, you should use #tbl, not ##tbl. By using ## you would make it a global temp table thus available to any connection, not only your current one - something you do not want.
Michael
Edited by - mromm on 01/23/2003 3:01:34 PM
January 27, 2003 at 10:06 am
You could also use a user-defined function to perform the work of selecting into a temporary table, then returning that table. I didn't run any statistics to see if this is decent in performance, but it does allow a single method since you can treat the function like a table to be referenced from various other procedures.
-Guarddata
January 27, 2003 at 11:27 am
In fact using a user-defined function that returns a table is a very good alternative to a view because it replaces functionality of a parameterized view not supported by SQL Server. With the UDF you can both create reusable code and not compromize performance. Of course, test it before commiting to it. I used it and trust it enough for myself.
Michael
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply