Marcus Aurelius
asked on
Why is SQL query faster than using the VIEW...?
Experts:
I'm a little confused. I have the following VIEW created. When I run the literal SQL scrip within the view...it completes in about 3 seconds.
But when I do this:
Select *
from View [dbo].[View_Skip_Pay_Count _By_Custom erID_CompS hip]
The query takes about 3 minutes....?????
Any ideas as to what is going on...? THANKS MikeV
USE [BI_Reporting]
GO
/****** Object: View [dbo].[View_Skip_Pay_Count _By_Custom erID_CompS hip] Script Date: 10/10/2007 11:45:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[View_Skip_Pay_Count _By_Custom erID_CompS hip]
AS
SELECT DISTINCT
TOP 100 PERCENT rcaf.CustomerID AS CUSTOMERID
, SUM(CASE WHEN cse.eventdate >= vmrs.max_rca_sentdate THEN 1 ELSE 0 END) AS SkipPayCount
FROM dbo.View_RCA_Fulfillment_B y_Customer ID AS rcaf
LEFT OUTER JOIN report_weboms.dbo.CSEvent AS cse
ON cse.CustomerID = rcaf.CustomerID
LEFT OUTER JOIN report_weboms.dbo.Orders AS o
ON o.CustomerID = rcaf.CustomerID
LEFT OUTER JOIN dbo.View_Max_RCA_SentDate AS vmrs
ON vmrs.CUSTOMERID = rcaf.CustomerID
WHERE (cse.ActivityID IN (98, 113, 117, 152))
AND (o.WebOrderNumber NOT LIKE '%-CT%')
AND (o.OrderID NOT IN (SELECT OrderID
FROM dbo.View_CANCELLED_ORDERID S_LITERAL
GROUP BY OrderID))
GROUP BY rcaf.CustomerID
ORDER BY rcaf.CustomerID
I'm a little confused. I have the following VIEW created. When I run the literal SQL scrip within the view...it completes in about 3 seconds.
But when I do this:
Select *
from View [dbo].[View_Skip_Pay_Count
The query takes about 3 minutes....?????
Any ideas as to what is going on...? THANKS MikeV
USE [BI_Reporting]
GO
/****** Object: View [dbo].[View_Skip_Pay_Count
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[View_Skip_Pay_Count
AS
SELECT DISTINCT
TOP 100 PERCENT rcaf.CustomerID AS CUSTOMERID
, SUM(CASE WHEN cse.eventdate >= vmrs.max_rca_sentdate THEN 1 ELSE 0 END) AS SkipPayCount
FROM dbo.View_RCA_Fulfillment_B
LEFT OUTER JOIN report_weboms.dbo.CSEvent AS cse
ON cse.CustomerID = rcaf.CustomerID
LEFT OUTER JOIN report_weboms.dbo.Orders AS o
ON o.CustomerID = rcaf.CustomerID
LEFT OUTER JOIN dbo.View_Max_RCA_SentDate AS vmrs
ON vmrs.CUSTOMERID = rcaf.CustomerID
WHERE (cse.ActivityID IN (98, 113, 117, 152))
AND (o.WebOrderNumber NOT LIKE '%-CT%')
AND (o.OrderID NOT IN (SELECT OrderID
FROM dbo.View_CANCELLED_ORDERID
GROUP BY OrderID))
GROUP BY rcaf.CustomerID
ORDER BY rcaf.CustomerID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The possible reason I can think of is that it's not a indexed view and hence uses table scan when the view is used in a subquery. Also if an indexed view has to be created then it should satisfy some of the criteria. One of them which I remember is that it cannot contain Distinct, Sum or Count functions.
Hope this helps to understand.
Hope this helps to understand.
Is there is a difference in the output when you enable the query options:
set statistics IO
set Statistics time