none
How query work when we pass where clause in SQL RRS feed

  • Question

  • I have some tables which have millions of records and are used to Reports.

    To bring the data on report,We make use of a stored procedure which in turn forms query (Where clause)

    set @SelectQuery = 'Select * from (SELECT *, '+@totalCount+' as Count, ROW_NUMBER() OVER (ORDER BY '+@OrderByClause+' ) as RowNumber FROM ['+@viewName+']   '+ @WhereClause+'  ) Seq Where ( Seq.RowNumber BETWEEN '+@StartNumber+' AND '+@EndNumber+' )'; 

    And this sp call view

    In view it make use of cte where it first union all data from different table and then join the cte with different table return the result set.This uses 8 tables while doing union all which contain millions of data. 

    The tables have proper index on them but still this query takes huge time based on date range to bring the data

    Now for performance tuning  ( I thought that when sp is calling view based on parameter, view will first union all data and then apply whereclause.)

    I wrote a stored procedure that will apply the where clause on each table individually writing dynamic query and then do union all but still there is no improvement .

    Is my assumption that while selecting data from view based on whereclause,it  will first union all rows from all table and then apply whereclause is wrong?

    IF it is wrong how it sql engine work?

    and how can i make the query tune.

    Below is the query for view which is used by Stored Procedure 

    USE [PEMS_NSC_PRO]
    GO
    
    /****** Object:  View [dbo].[pv_EventsSummary]    Script Date: 13/01/2017 2:50:14 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    Create VIEW [dbo].[pv_EventsSummary]
    				AS
    With AllEvents as
    	(
    	SELECT CustomerID, TimeOfOccurrance AS DateTime, EventUID, MeterId AS AssetId, AreaId, EventCode , TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, '1' as [Description] , Null as TechnicianKeyID
    	FROM   dbo.HistoricalAlarms AS ha with(nolock)  
    
    	Union All
    
    	SELECT CustomerID, TimeOfOccurrance AS DateTime, EventUID, MeterId AS AssetId, AreaId, EventCode , TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, '1' as [Description] , Null as TechnicianKeyID
    	FROM   dbo.ActiveAlarms AS aa with(nolock)   
    
    	Union All
    
    	SELECT CustomerID, TransDateTime AS DateTime, EventUID, MeterID AS AssetId, AreaId, (case when isnull(TxValue,'') =''  then 2004 else 2007 end) as EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5,
    		TransactionTypeDesc AS [Description], Null as TechnicianKeyID
    	FROM   Transactions with(nolock)   inner join
    			TransactionType ON TransactionTypeId = Transactions.TransactionType
    
    	Union all
    
    	SELECT  CustomerID, DiagTime AS DateTime, EventUID, MeterId AS AssetId, AreaId, 2001 as EventCode,  TimeType1, TimeType2, TimeType3, TimeType4, TimeType5 ,'2' AS [Description], Null as TechnicianKeyID
    	FROM    MeterDiagnostic with(nolock )
    
    	Union
    
    	SELECT  CustomerID, CollDateTime AS DateTime, EventUID, MeterId AS AssetId, AreaId, 2005 as EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, NewCashBoxID AS [Description], Null as TechnicianKeyID
    	From    CollDataSumm with(nolock)
    
    	Union all
    
    	SELECT  CustomerID, DateTimeRead AS DateTime, EventUID, MeterId AS AssetId, AreaId, 2006 as EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, CashBoxId AS [Description], Null as TechnicianKeyID
    	From CashBoxDataImport  with(nolock)
    
    	Union all
    
    	SELECT  CustomerID, EventDateTime AS DateTime, EventUID, MeterId AS AssetId, AreaId, EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, '1' AS [Description] , TechnicianKeyID
    	From EventLogs with(nolock) -- where  et.EventTypeId = 20 
    
    	Union all
    
    	Select CustomerId,  MeterTime as EventDateTime, EventUID, MeterId AS AssetId, AreaId, 2003 as EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, RequestName AS [Description], Null as TechnicianKeyID
    	from metercomm with(nolock)  left outer join 
    			UdpRequestType u on metercomm.UdpRequestTypeId  = u.UdpRequestTypeId
    	Union all
    
    	Select CustomerId, StartDateTime as EventDateTime, EventUID, MeterId AS AssetId, AreaId, 2003 as EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5 , Memo AS [Description], Null as TechnicianKeyID
    	from gsmconnectionlogs with(nolock) 
    	where portno = 0
    			
    	)
    select av.CustomerID,AssetId, [DateTime], EventUID, et.EventTypeId as EventClassCode, et.EventTypeDesc AS EventClass , av.EventCode, TimeType1, TimeType2, TimeType3, TimeType4, TimeType5, 
    		TechnicianKeyID, (case isnull([Description],'') when '1'  then em.EventDescVerbose when '2' then et.EventTypeDesc else isnull([Description],'') end)  as [Description] ,
    		mmh.AssetTypeDesc as AssetType, mmh.AreaId2, mmh.ZoneId, mmh.street AS Street, mmh.Suburb AS Suburb, mmh.DemandZoneDesc AS DemandArea, mmh.ZoneName AS Zone, 
    		mmh.AreaName AS Area, mmh.MeterName AS AssetName, mmh.Latitude, mmh.Longitude, mmh.BayCount, es.EventSourceCode , es.EventSourceDesc , mmh.Areaid 
    from 
    	AllEvents av  with(nolock)   inner join
    	MeterMapHelperV  AS mmh ON mmh.Customerid = av.CustomerId AND mmh.Areaid = av.AreaId AND mmh.MeterId = av.AssetId  inner join
    	Eventcodemaster em  on em.EventCode = av.EventCode inner join
    	EventType AS et ON et.EventTypeId = em.EventType inner join
    	EventSources as es on em.EventSource = es.EventSourceCode
    
    
    GO
    
    
    


    Friday, January 13, 2017 3:21 AM

Answers

  • It will apply it to each table individually before the union. Study the execution plan. Hover over the table node and you will see that the where clause is applied to each table individually.

    Friday, January 13, 2017 3:58 AM

All replies

  • Can we see what the entire query would look like and the execution plan. The Query processor will apply the where clause on each table and then union all the results together.

    I take it all the tables are on the same server. If they are on different servers the behavior might be different.

    Friday, January 13, 2017 3:44 AM
  • Hi Hilary,

    All the tables are on same server

    As you mentioned :The Query processor will apply the where clause on each table and then union all the results together.

    This applies for view also?

    as i have mentioned the code for view where it union all data from all data in cte and return resultset .

    so when i run the view 

    select * from view where datetime '2016-05-12' and '2016-08-12'

    this will run all the table store the data in cte and at last apply whereclause? 




    Friday, January 13, 2017 3:54 AM
  • It will apply it to each table individually before the union. Study the execution plan. Hover over the table node and you will see that the where clause is applied to each table individually.

    Friday, January 13, 2017 3:58 AM
  • What are the ways in such scenario where we can achieve performance tunning?

    Which is better creating view or storedProcedure?

    Friday, January 13, 2017 4:45 AM
  • "Which is better creating view or storedProcedure?"

    The answer is "It depends"

    If the script you posted is reusing many times and you want the results to JOIN with other tables/views then go for the view. But the logic is much more specific then go for a stored procedure and you may get more opportunities to fine tune the query.

    But before that, please verify the execution plan of the view after applying the possible WHERE clauses. Check whether the indexes are using properly, is that index scan or seek etc. and rewrite the query if needed.


    Krishnakumar S

    Friday, January 13, 2017 5:01 AM