Link to home
Start Free TrialLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

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_CustomerID_CompShip]

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_CustomerID_CompShip]    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_CustomerID_CompShip]
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_By_CustomerID 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_ORDERIDS_LITERAL
                               GROUP BY OrderID))
GROUP BY rcaf.CustomerID
ORDER BY rcaf.CustomerID
Avatar of Crag
Crag
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you compared the sql plan from the direct query to the one used by the view?
Is there is a difference in the output when you enable the query options:
set statistics IO
set Statistics time
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of assyst
assyst

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.