[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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
0
MIKE
Asked:
MIKE
1 Solution
 
CragCommented:
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
0
 
Eugene ZCommented:
it is using differnt execution plan
with order by

try to use hint "FORCE ORDER"
http://msdn2.microsoft.com/en-us/library/ms190772.aspx

try:
Select *
from View [dbo].[View_Skip_Pay_Count_By_CustomerID_CompShip]
OPTION (FORCE ORDER)
0
 
assystCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now