x

Stored Procedure taking more execution time

Hi All,

My Stored Procedure is taking more time please let me know the optimized way to reduce the execution time here i am using one transaction table which has more record i believe because of it taking more time

Hi Fatherjack below is my table and index created on it

USE [Psql-1aug-2012]
GO

/****** Object:  Table [dbo].[Transactions]    Script Date: 08/15/2012 13:46:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Transactions](
 [SiteId] [varchar](3) NULL,
 [TransNum] [varchar](50) NULL,
 [Date] [datetime] NULL,
 [TermCode] [varchar](50) NULL,
 [SeqNum] [varchar](50) NULL,
 [AcctNum] [varchar](50) NULL,
 [Card1] [varchar](50) NULL,
 [Card2] [varchar](22) NULL,
 [Pump] [varchar](50) NULL,
 [ProdId] [varchar](2) NULL,
 [Qty] [float] NULL,
 [PPG] [money] NULL,
 [BasicAcctNum] [varchar](50) NULL,
 [ProdId2] [varchar](2) NULL,
 [Qty2] [float] NULL,
 [PPG2] [money] NULL,
 [MPG] [varchar](50) NULL,
 [MercProdId] [varchar](3) NULL,
 [MercTtl] [varchar](10) NULL,
 [TransTotal] [money] NULL,
 [Odometer] [varchar](50) NULL,
 [BadOD] [bit] NULL,
 [MiscKB] [varchar](50) NULL,
 [Receipt] [bit] NULL,
 [OdomMod] [bit] NULL,
 [Reconciled] [bit] NULL,
 [ManualEntry] [bit] NULL,
 [DontIncludeInRec] [bit] NULL,
 [NetworkTrans] [bit] NULL,
 [ManualFix] [bit] NULL,
 [Exported2Fleet] [bit] NOT NULL,
 [UserEntry1] [varchar](30) NULL,
 [UserEntry2] [varchar](30) NULL,
 [UserEntry3] [varchar](30) NULL,
 [UserEntry4] [varchar](30) NULL,
 [UserEntry5] [varchar](30) NULL,
 [UserEntry6] [varchar](30) NULL,
 [UserEntry7] [varchar](30) NULL,
 [UserEntry8] [varchar](30) NULL,
 [UserEntry9] [varchar](30) NULL,
 [DrvrMsg] [varchar](30) NULL,
 [VehcMsg] [varchar](30) NULL,
 [CardMsg] [varchar](30) NULL,
 [AcctName] [varchar](9) NULL,
 [DrvrName] [varchar](50) NULL,
 [VehcName] [varchar](50) NULL,
 [K800Trans] [bit] NULL,
 [PromoDiscount] [varchar](10) NULL,
 [SalesTax] [varchar](5) NULL,
 [MilesTraveled] [int] NULL,
 [FltLkUserID] [varchar](10) NULL,
 [ODReason] [smallint] NULL,
 [Time] [varchar](50) NULL,
 [ImportSource] [varchar](2) NULL,
 [ImportDate] [datetime] NULL,
 [ChgTimeStamp] [timestamp] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Transactions] ADD  CONSTRAINT [DF_Transactions_Exported2Fleet]      DEFAULT ((0)) FOR [Exported2Fleet]
GO



CREATE NONCLUSTERED INDEX [IX_t] ON [dbo].[Transactions] 
(
 [SiteId] ASC
)
INCLUDE ( [Date],
[Time],
[Qty],
[Pump]) 
WHERE ([DontIncludeInRec]=(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [Psql-1aug-2012]
GO

/****** Object:  StoredProcedure [dbo].[stp_GetTransactions]    Script Date: 08/15/2012 10:17:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--                                              
--                    
ALTER  PROCEDURE [dbo].[stp_GetTransactions]          
-- stp_GetTransactions 'ALL','ALL','ALL',NULL,NULL,'Time','Asc',1,1,50,0                     
 @vTypeofTransaction VARCHAR(15) ,
 @vSiteId VARCHAR(3) ,
 @vAccountNumber VARCHAR(25) ,
 @dStartDate DATETIME = NULL ,
 @dEndDate DATETIME = NULL ,
 @vSortBy VARCHAR(100) ,
 @vSortDirection VARCHAR(20) ,
 @vGroupId VARCHAR(5) ,
 @start_Record VARCHAR(10) ,
 @End_Record VARCHAR(10) ,
 @no_results_total INT OUTPUT
AS 
 BEGIN 

/*This procedure is used for retrieve all Transactions datas*/                                                             
 SET DATEFORMAT 'DMY'                      
 DECLARE @vQuery1 VARCHAR(8000)                                                    
 DECLARE @vQuery2 VARCHAR(8000)                  
 DECLARE @vQuery3 VARCHAR(8000)               
 DECLARE @vQuery4 VARCHAR(8000)                
 DECLARE @vQuery5 VARCHAR(8000)                    
 DECLARE @vQuery6 VARCHAR(8000)           
 DECLARE @vCommonTransQuery VARCHAR(800)          
 DECLARE @vCommonIncomplete VARCHAR(400)          
 DECLARE @vCommonTransIncomplete VARCHAR(600)          
 DECLARE @vGroupAccess VARCHAR(200)                                        
 DECLARE @vWhere VARCHAR(800)               
 DECLARE @startRecordvar VARCHAR(10) ,
 @EndRecordvar VARCHAR(10)              
 DECLARE @orderby VARCHAR(100)                                                   
--SET @vQuery=''                                       
 SET @vWhere = ''                
 SET @orderby = ''              
 SET @vQuery3 = ''              
 SET @vQuery4 = ''               
 SET @vQuery5 = ''              
 SET @vQuery6 = ''          
 SET @vCommonTransQuery = ''           
 SET @vCommonIncomplete = ''          
 SET @vGroupAccess = ''          
 SET @vCommonTransIncomplete = ''          

--IF(@vSortBy <> 'Transaction#')                
--BEGIN                
-- SET @vSortBy = @vSortBy + '  '+ @vSortDirection + ',Transaction#'                
--END                 
--                                 
--  IF(@vSortBy = 'DateTime,Transaction#')              
--  BEGIN              
-- SET @vSortBy = 'DateTime' + ' ' + @vSortDirection + ',Transaction#'              
--  END                  

 IF ( @vSortBy = 'AcctNum' ) 
 BEGIN              
 SET @vSortBy = '''Account ID'''              
 END              

 ELSE 
 IF ( @vSortBy = 'Time' ) 
 BEGIN              
 SET @vSortBy = 'CONVERT(varchar,CONVERT(smalldatetime,[Time]),114)'              
 END                        

 SET @vQuery1 = 'SELECT  DISTINCT                                                             
SiteId AS ''Site ID'',                                                              
TransNum AS ''Transaction#'',                                                              
Convert(VARCHAR(10),Date,110) AS ''Date'',                                                            
dbo.FormatDateTime(Time, ''HH:MM 12'') AS Time,                                                              
CASE NetworkTrans WHEN ''1'' THEN CASE ISNULL(AcctNum,'''') WHEN '''' THEN ''Network Card'' WHEN ''Global Card'' THEN ''Network Card'' ELSE Acctnum END ELSE Acctnum END AS ''Account ID'',                                                              
card1 AS ''Driver/Single Card'',                                                               
card2 AS ''Vehicle Card'',                                                              
Pump AS ''Pump #'',                                                              
Qty AS ''Quantity'',                                                              
PPG AS ''Unit Price'',                                                              
TransTotal AS ''Total'',                                                              
Odometer,                                        
VehcName AS ''Vehicle'',                     
NetworkTrans AS ''Network Transaction'',                    
ImportDate,                                                     
''COMPLETE'' AS ''TransactionType'',Date+[Time] as ''DateTime'',CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS ''Time24hr'' FROM Transactions '                                                    


 SET @vQuery2 = 'SELECT DISTINCT                                                    
SiteId AS ''Site ID'',                                                              
TransNum AS ''Transaction#'',                
Convert(VARCHAR(10),Date,110) AS ''Date'',                                                                                                                             
dbo.FormatDateTime(Time, ''HH:MM 12'') AS Time,              
CASE NetworkTrans WHEN ''1'' THEN CASE ISNULL(AcctNum,'''') WHEN '''' THEN ''Network Card'' WHEN ''Global Card'' THEN ''Network Card'' ELSE Acctnum END ELSE Acctnum END AS ''Account ID'',                                                              
card1 AS ''Driver/Single Card'',                                                               
card2 AS ''Vehicle Card'',                                      
Pump AS ''Pump #'',                                                              
Qty AS ''Quantity'',       
PPG AS ''Unit Price'',                                                              
TransTotal AS ''Total'',                                                              
Odometer,                                                          
VehcName AS ''Vehicle'',                         
NetworkTrans AS ''Network Transaction'',                      
ImportDate,                                                  
''INCOMPLETE'' AS ''TransactionType'',Date+[Time] as ''DateTime'',CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS ''Time24hr'' FROM TransIncomplete '                    




--IF(@vGroupId<>'1')                          
--BEGIN                    

-- SET @vQuery1=@vQuery1+' JOIN SysGroup SG1 ON AcctNum=SG1.Record'                    
-- SET @vQuery2=@vQuery2+' JOIN SysGroup SG1 ON AcctNum=SG1.Record'                                                
-- --SET @vWhere='(ModuleName=''Account'''                    
-- IF EXISTS(SELECT Record FROM SysGroup WHERE GroupId=@vGroupId AND ModuleName='Site')                    
-- BEGIN                    
--  SET @vQuery1=@vQuery1+' JOIN SysGroup SG2 ON SiteId=SG2.Record'                    
--  SET @vQuery2=@vQuery2+' JOIN SysGroup SG2 ON SiteId=SG2.Record'                     
--  --SET @vWhere=@vWhere+ ' OR ModuleName=''Site'''                     
-- END                                               
-- SET @vWhere='SG1.GroupId='''+@vGroupId +''''                           
--END              

 IF ( @vGroupId <> '1' ) 
 BEGIN                    
 SET @vQuery1 = @vQuery1
 + ' JOIN SysGroup SG1 ON SiteId=SG1.Record'                    
 SET @vQuery2 = @vQuery2
 + ' JOIN SysGroup SG1 ON SiteId=SG1.Record'                     
 SET @vWhere = ' SG1.GroupId=''' + @vGroupId + ''''                           
 END                

 IF ( @vSiteId <> 'ALL' ) 
 BEGIN                            
 IF ( @vWhere <> '' ) 
 BEGIN                                            
 SET @vWhere = @vWhere + ' And '                                                    
 END                                                    
 SET @vWhere = @vWhere + 'SiteId=''' + @vSiteId + ''''                             
 END                                                     
/*Added by Amitha S--When Data has been migrated */        
--IF(@vSiteId = 'ALL')                                                    
--BEGIN                            
-- IF(@vWhere<>'')                                                    
-- BEGIN                                            
--  SET @vWhere=@vWhere+'And SiteId not in (Select SiteId From Site Where SiteManager is NULL)'+' And '                                                    
-- END                                                    
-- SET @vWhere=@vWhere+'SiteId not in (Select SiteId From Site Where SiteManager is NULL) '           
--END             
/* Added by Amitha S- When Data has been migrated */                                   
 IF ( @vAccountNumber <> 'ALL' ) 
 BEGIN                                                    
 IF ( @vWhere <> '' ) 
 BEGIN                                            
 SET @vWhere = @vWhere + ' And '                                                    
 END                                                    
 SET @vWhere = @vWhere + 'AcctNum=''' + @vAccountNumber + ''''                                                    
 END                       

 IF ( ( @dStartDate <> NULL
    AND @dEndDate <> NULL
 )
 OR ( @dStartDate <> ''
   AND @dEndDate <> ''
 )
    ) 
 BEGIN           

 IF ( @vWhere <> '' ) 
 BEGIN                                                    
 SET @vWhere = @vWhere + ' And '                                                    
 END                                               

 SET @vWhere = @vWhere + 'Date between '''
 + CONVERT(VARCHAR(25), @dStartDate) + ''' AND '''
 + CONVERT(VARCHAR(25), @dEndDate) + ''''                                                    
 END                                                     
 ELSE 
 IF ( @dStartDate <> NULL
 OR @dStartDate <> ''
    ) 
 BEGIN                     
 IF ( @vWhere <> '' ) 
 BEGIN                                                    
 SET @vWhere = @vWhere + ' And '                                                    
 END                                           

 SET @vWhere = @vWhere + 'Date >='''
 + CONVERT(VARCHAR(25), @dStartDate) + ''''                                                     
 END                     
 IF ( @dEndDate <> NULL
 OR @dEndDate <> ''
    ) 
 BEGIN                                                    
 IF ( @vWhere <> '' ) 
 BEGIN                                                    
 SET @vWhere = @vWhere + ' And '                                                    
 END                                                    

 SET @vWhere = @vWhere + 'Date <='''
 + CONVERT(VARCHAR(25), @dEndDate) + ''''                                                     
 END                   

 SET @orderby = @orderby + ' ' + ' ORDER BY ' + @vSortBy + ' '
 + @vSortDirection                 

 CREATE TABLE #list
 (
   record_num INT IDENTITY(1, 1) ,
   TempTransNum VARCHAR(25) ,
   TempAcctnum VARCHAR(25) ,
   TempDate DATETIME ,
   TempTime DATETIME ,
   TempSiteId VARCHAR(10) ,
   Tempcard1 VARCHAR(50) ,
   Tempcard2 VARCHAR(50) ,
   TempPump VARCHAR(50) ,
   TempQty VARCHAR(10) ,
   TempPPG MONEY ,
   TempTransTotal MONEY ,
   TempOdometer VARCHAR(30) ,
   TempVehcName VARCHAR(50) ,
   TempDateTime DATETIME ,
   TempTime24Hour VARCHAR(50)
 )              

 SET @vCommonTransQuery = ' INSERT INTO #list(TempTransNum,TempSiteId,TempAcctnum,TempDate,TempTime,TempDateTime,TempCard1,TempCard2,TempPump,TempQty,TempPPG,TempTransTotal,          
TempOdometer,TempVehcName,TempTime24Hour'
 + +')               
SELECT  TransNum AS ''Transaction#'',SiteId,AcctNum AS ''Account ID'',[date],[Time],Date+[Time] as ''DateTime'',Card1,Card2,Pump,          
Qty AS ''Quantity'',PPG AS ''Unit Price'',TransTotal AS ''Total'',Odometer,VehcName,CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS ''Time24hr'' FROM Transactions '           
 SET @vCommonTransIncomplete = ' INSERT INTO #list(TempTransNum,TempSiteId,TempAcctnum,TempDate,TempTime,TempDateTime,TempCard1,TempCard2,TempPump,TempQty,TempPPG,TempTransTotal,          
TempOdometer,TempVehcName,TempTime24Hour'
 + +')               
SELECT  TransNum AS ''Transaction#'',SiteId,AcctNum AS ''Account ID'',[date],[Time],Date+[Time] as ''DateTime'',Card1,Card2,Pump,          
Qty AS ''Quantity'',PPG AS ''Unit Price'',TransTotal AS ''Total'',Odometer,VehcName,CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS ''Time24hr'' FROM TransIncomplete '          

 SET @vCommonIncomplete = 'SELECT  TransNum AS ''Transaction#'',SiteId,AcctNum AS ''Account ID'',[date],[Time],Date+[Time] as ''DateTime'',          
  Card1,Card2,Pump,Qty AS ''Quantity'',PPG AS ''Unit Price'',TransTotal AS ''Total'',Odometer,VehcName,CONVERT(varchar,CONVERT(smalldatetime,[Time]),114) AS ''Time24hr'' FROM TransIncomplete '          

 SET @vGroupAccess = ' JOIN SysGroup SG1 ON SiteId=SG1.Record'          

 IF ( @vTypeofTransaction = 'COMPLETE' ) 
 BEGIN               
 IF ( @vWhere <> '' ) 
 BEGIN                   
 IF ( @vGroupId <> '1' ) 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery
 + @vGroupAccess + ' WHERE ' + @vWhere
 + '  AND ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL))   ORDER BY '
 + @vSortBy + ' ' + @vSortDirection          
 END          
 ELSE 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery + ' WHERE '
 + @vWhere
 + '  AND ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL))   ORDER BY '
 + @vSortBy + ' ' + @vSortDirection            
 END            

 EXEC(@vQuery5)                      
 END               
 ELSE 
 BEGIN              
 IF ( @vGroupId <> '1' ) 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery
 + @vGroupAccess
 + '             
   WHERE ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL)) ORDER BY '
 + @vSortBy + ' ' + @vSortDirection          
 END          
 ELSE 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery
 + '             
   WHERE ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL)) ORDER BY '
 + @vSortBy + ' ' + @vSortDirection          
 END          

 EXEC (@vQuery5)              
 END                    

 END              

 ELSE 
 IF ( @vTypeofTransaction = 'INCOMPLETE' ) 
 BEGIN                  

 IF ( @vWhere <> '' ) 
 BEGIN          
 SET @vQuery5 = @vCommonTransIncomplete
 + '                     
   WHERE ' + @vWhere
 + ' AND ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL))             
  ORDER BY ' + @vSortBy + ' ' + @vSortDirection             
 END               
 ELSE 
 BEGIN            

--EXEC(@vCommonIncomplete  + ' WHERE ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND(badOD = ''false'' OR badOD IS NULL))             
--ORDER BY '+@vSortBy+' '+@vSortDirection)            
 SET @vQuery5 = @vCommonTransIncomplete
 + '             
   WHERE ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL)) ORDER BY '
 + @vSortBy + ' ' + @vSortDirection                    


 END             
 EXEC (@vQuery5)           

 END               

 ELSE 
 IF ( @vTypeofTransaction = 'NETWORK' ) -- Network Transaction Begin                                                    
 BEGIN               

 IF ( @vWhere <> '' ) 
 BEGIN               
 SET @vQuery3 = ' WHERE ' + @vWhere
 + ' AND Transactions.NetWorkTrans = ''true'''               
 SET @vQuery6 = ' WHERE ' + @vWhere
 + ' AND TransIncomplete.NetWorkTrans = ''true'''              
 END               

 ELSE 
 BEGIN              
 SET @vQuery3 = ' WHERE  Transactions.NetWorkTrans = ''true'''               
 SET @vQuery6 = ' WHERE  TransIncomplete.NetWorkTrans = ''true'''              
 END                  
 IF ( @vGroupId <> '1' ) 
 BEGIN 
 SET nocount ON         
 SET @vQuery5 = @vCommonTransQuery
 + @vGroupAccess + @vQuery3 + ' UNION ALL '
 + @vCommonIncomplete + @vGroupAccess
 + @vQuery6           
 END          
 ELSE 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery + @vQuery3
 + ' UNION ALL ' + @vCommonIncomplete
 + @vQuery6              
 SET nocount OFF
 END            

 EXEC(@vQuery5)              

 END               

 ELSE 
 IF ( @vTypeofTransaction = 'Bad Odometer' ) -- Bad Odometer Transaction Begin                                                    
 BEGIN               

 IF ( @vWhere <> '' ) 
 BEGIN               
 SET @vQuery3 = ' WHERE ' + @vWhere
 + ' AND Transactions.badOD = ''true'''               
 SET @vQuery6 = ' WHERE ' + @vWhere
 + ' AND TransIncomplete.badOD = ''true'''              
 END   

 ELSE 
 BEGIN              
 SET @vQuery3 = ' WHERE Transactions.badOD = ''true'''               
 SET @vQuery6 = ' WHERE TransIncomplete.badOD = ''true'''              
 END                  
 IF ( @vGroupId <> '1' ) 
 BEGIN 
 SET nocount ON         
 SET @vQuery5 = @vCommonTransQuery
 + @vGroupAccess + @vQuery3
 + ' UNION ALL ' + @vCommonIncomplete
 + @vGroupAccess + @vQuery6           
 END          
 ELSE 
 BEGIN          
 SET @vQuery5 = @vCommonTransQuery
 + @vQuery3 + ' UNION ALL '
 + @vCommonIncomplete + @vQuery6            
 END          
 EXEC(@vQuery5)
 SET nocount OFF              

 END              

 ELSE 
 BEGIN           

 IF ( @vWhere <> '' ) 
 BEGIN           
 IF ( @vGroupId <> '1' ) 
 BEGIN 
 SET nocount ON          
 PRINT 'Anil'          
 SET @vQuery5 = @vQuery5
 + @vCommonTransQuery
 + @vGroupAccess + ' WHERE '
 + @vWhere + ' UNION ALL '
 + @vCommonIncomplete
 + @vGroupAccess + ' WHERE '
 + @vWhere + ' ORDER BY '
 + @vSortBy + ' '
 + @vSortDirection           
 END          
 ELSE 
 BEGIN          
 SET @vQuery5 = @vQuery5
 + @vCommonTransQuery
 + ' WHERE ' + @vWhere
 + ' UNION ALL '
 + @vCommonIncomplete
 + ' WHERE ' + @vWhere
 + ' ORDER BY ' + @vSortBy
 + ' ' + @vSortDirection           
 END  
 SET nocount OFF         
 END              
 ELSE 
 BEGIN           
 IF ( @vGroupId <> '1' ) 
 BEGIN          
 SET nocount ON        
 SET @vQuery5 = @vQuery5
 + @vCommonTransQuery
 + @vGroupAccess
 + ' UNION ALL '
 + @vCommonIncomplete
 + @vGroupAccess + ' ORDER BY '
 + @vSortBy + ' '
 + @vSortDirection           
 END          
 ELSE 
 BEGIN          

 SET @vQuery5 = @vQuery5
 + @vCommonTransQuery
 + ' UNION ALL '
 + @vCommonIncomplete
 + ' ORDER BY ' + @vSortBy
 + ' ' + @vSortDirection           
 END          
 END             
   --print  @vQuery5
 SET nocount OFF          
 EXEC (@vQuery5)              

 END              

 SET @no_results_total = @@ROWCOUNT               

 IF @end_record = 0 
 SELECT @start_record = 1 ,
 @end_record = @no_results_total                  

 SET @startRecordVar = CAST(@start_record AS VARCHAR(10))                  
 SET @EndRecordVar = CAST(@end_record AS VARCHAR(10))               





--IF(@vWhere<>'')                                             
--BEGIN                  

-- --SET @vQuery1=@vQuery1 +' JOIN #list lst ON TransNum = lst.TempTransNum AND SiteID = lst.TempSiteId AND Date = lst.TempDate AND TIME = lst.TempTime  '  + ' WHERE '+ @vWhere                                                    
-- --SET @vQuery2=@vQuery2 +' WHERE '+@vWhere               

--END               


 IF ( @vTypeofTransaction = 'COMPLETE' )          -- Complete Transaction Begin                                          
 BEGIN                      
 IF ( @vWhere <> '' ) 
 BEGIN                                    

 SET @vQuery1 = @vQuery1
 + ' JOIN #list lst ON TransNum = lst.TempTransNum AND SiteID = lst.TempSiteId AND Date = lst.TempDate AND TIME = lst.TempTime  '
 + ' WHERE ' + @vWhere                            


 EXEC(@vQuery1 + ' AND ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL))  AND lst.record_num between ' + @startRecordVar  +' and ' + @EndRecordVar +      ORDER BY '+@vSortBy+' '+@vSortDirection)                 



 END                  

 ELSE 
 BEGIN                   
 PRINT '1'                 
 SET @vQuery1 = @vQuery1
 + ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
   AND Time= lst.TempTime WHERE ((NetWorkTrans = ''false'' OR NetWorkTrans IS NULL) AND (badOD = ''false'' OR badOD IS NULL))                
   AND lst.record_num between ' + @startRecordVar + ' and ' + @EndRecordVar
 + ' AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL)'
 + ' ORDER BY ' + @vSortBy + ' ' + @vSortDirection              

 PRINT ( @vQuery1 )               
 EXEC(@vQuery1)                      
 END                  

 END                                         -- Complete Transction End              

 ELSE 
 IF ( @vTypeofTransaction = 'INCOMPLETE' )    -- Incomplete Transaction Begin                            
 BEGIN                                                    
 IF ( @vWhere <> '' ) 
 BEGIN                      


 SET @vQuery2 = @vQuery2
 + ' JOIN #list lst ON TransNum = lst.TempTransNum AND SiteID = lst.TempSiteId AND Date = lst.TempDate               
   AND Time = lst.TempTime  WHERE '               


 SET @vQuery2 = @vQuery2 + @vWhere
 + ' AND (NetWorkTrans = ''false'' OR NetWorkTrans IS NULL)  AND lst.record_num between '
 + @startRecordVar + ' and ' + @EndRecordVar
 + ' ORDER BY ' + @vSortBy + ' '
 + @vSortDirection                                       



 EXEC(@vQuery2)                     

 END                      
 ELSE 
 BEGIN                
 PRINT 'test'           
 EXEC(@vQuery2 + ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
 AND Time= lst.TempTime WHERE (NetWorkTrans = ''false'' OR NetWorkTrans IS NULL)                
 AND lst.record_num between ' + @startRecordVar  +' and ' + @EndRecordVar + ' AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL)' +' ORDER BY '+@vSortBy+'  + @v Sor tDirection)              
 END                                    
 END                                  -- Incomplete Transaction End              

 ELSE 
 IF ( @vTypeofTransaction = 'NETWORK' ) -- Network Transaction Begin                                                    
 BEGIN                                       
 IF ( @vWhere <> '' ) 
 BEGIN               
 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' AND ' + @EndRecordVar + ' AND '
 + @vWhere              

 SET @vQuery1 = @vQuery1 + @vQuery4
 + ' AND Transactions.NetWorkTrans = ''true'' '                       
 SET @vQuery2 = @vQuery2 + @vQuery4
 + ' AND TransIncomplete.NetWorkTrans = ''true'' '                         
 END                      
 ELSE 
 BEGIN                      

 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' AND ' + @EndRecordVar
 + ' AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL)'                      
 SET @vQuery1 = @vQuery1 + @vQuery4
 + ' AND Transactions.NetWorkTrans = ''true'' '                       
 SET @vQuery2 = @vQuery2 + @vQuery4
 + ' AND TransIncomplete.NetWorkTrans = ''true'' '                   

 END                



 EXEC  (@vQuery1 + ' UNION ALL '+@vQuery2 +  ' ORDER BY '+@vSortBy+' '+@vSortDirection )                                                 
 END                                   -- Network Transaction End              

 ELSE 
 IF ( @vTypeofTransaction = 'Bad Odometer' )--Bad Odometer Condition Begin                                           
 BEGIN                                       
 IF ( @vWhere <> '' ) 
 BEGIN                      
 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' AND ' + @EndRecordVar + ' AND '
 + @vWhere              

 SET @vQuery1 = @vQuery1 + @vQuery4
 + ' AND Transactions.badOD = ''true'' '                      
 SET @vQuery2 = @vQuery2 + @vQuery4
 + ' AND TransIncomplete.badOD = ''true'' '                        
 END                      
 ELSE 
 BEGIN                      
 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate              
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' AND ' + @EndRecordVar
 + ' AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL)'                       
 SET @vQuery1 = @vQuery1 + @vQuery4
 + ' AND Transactions.badOD = ''true'' '                      
 SET @vQuery2 = @vQuery2 + @vQuery4
 + ' AND TransIncomplete.badOD = ''true'' '                      
 END                 
--PRINT @no_results_total                              
 EXEC  (@vQuery1 + ' UNION ALL '+@vQuery2 +  ' ORDER BY '+@vSortBy+' '+@vSortDirection )                                                  
 END                                         -- Bad Odometer Condition End                                      

 ELSE 
 BEGIN                   
 PRINT 2            
 IF ( @vWhere <> '' ) 
 BEGIN              
 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' AND ' + @EndRecordVar + ' AND '
 + @vWhere              
 END               
 ELSE 
 BEGIN              
 SET @vQuery4 = ' JOIN #list lst ON TransNum= lst.TempTransNum AND SiteID= lst.TempSiteId AND Date= lst.TempDate               
AND Time= lst.TempTime WHERE lst.record_num between ' + @startRecordVar
 + ' and ' + @EndRecordVar
 + ' AND SiteID NOT IN (SELECT SiteId FROM Site WHERE SiteManager is NULL)'                        
 END                    


 PRINT ( @vQuery1 + @vQuery4 + ' UNION ALL '
 + @vQuery2 + @vQuery4 + ' ORDER BY '
 + @vSortBy + ' ' + @vSortDirection )                 
 EXEC  (@vQuery1 + @vQuery4 +' UNION ALL '+@vQuery2 + @vQuery4 + ' ORDER BY '+@vSortBy+' '+@vSortDirection )                 



 END              
 TRUNCATE TABLE  #list    

 PRINT ( @vQuery1 + ' UNION ALL ' + @vQuery2 + ' ORDER BY ' + @vSortBy
 + ' ' + @vSortDirection )                             
 END 

GO
more ▼

asked Aug 15, 2012 at 06:19 AM in Default

sravan.434 gravatar image

sravan.434
20 7 7 9

@sravan.434 you have asked 7 questions so far on this forum but you havent shown that any of the many answers you have received have helped you. All of your questions follow the same format - "procedure taking more time please optimize it".

You are not giving very much to us and you are not likely to get as much help as you might if you keep doing this.

The phrase "My Stored Procedure is taking more time please let me know the optimized way to reduce the execution time " doesn't mean anything to me. Its taking more time than what? More time than it used to? More time than is acceptable for your system? More time than it takes to boil an egg?

We have no idea about the database or the hardware that this is working on so it might run very quickly on our servers if we were to test it locally. We dont know anything about the tables that are involved so have no idea if you have the right indexes in place or whether it holds 10PB of data and will always be slow because of the scale.

Please give us more information when you ask questions so that we can help you.

You may also want to consider whether you have a training need if all of your questions are the same.
Aug 15, 2012 at 07:30 AM Fatherjack ♦♦
@Fatherjack all of the answers and Suggestions were helped so much to me here i am getting the data from one table which has more data abt 4 lakhs i have tried by creating an Non Clustered index but still it is taking 6 secs for 200 records and also i am checking some conditions like whether transaction complete or incomplete and selecting the data within date range sorry for inconvenience please let me know in case of any other details required
Aug 15, 2012 at 07:39 AM sravan.434

It's great that we are helping, when you get chance can you review your other questions and click the tick beside the answer that has helped you the most on each so that the other users of the forum know what helped you the most please?

So, the table has 400,000 records and the query returns 200 in 6 seconds?

Please edit you question and provide the DDL for the table and any indexes there are so we can make further investigations
Aug 15, 2012 at 08:06 AM Fatherjack ♦♦
sure Fatherjack i will tick the Questions which have helped me
Aug 15, 2012 at 08:18 AM sravan.434
It took me longer than 6 seconds to scroll through the question.
Aug 15, 2012 at 12:35 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

That's just flat out too large. There's no way I'm going to try to decipher it all.

I see you're still using DISTINCT. That's going to hurt performance. Since you're doing a UNION ALL, you could eliminate the DISTINCT and then use a UNION statement instead. That will result in similar behavior that might be faster.

Your table is a heap, meaning it has no clustered index. This type of storage is going to be slower, especially on SELECT statements, regardless of placing a non-clustered, filtered, index on it. Also, it's clearly not a normalized structure. Database normalization is actually, when done correctly, a performance enhancement.

That index, it's probably useless. The INCLUDE statement only has four or so columns, but you're retrieve lots more. So storing that stuff out at the leaf level of the index is doing nothing but adding space to the index, making it cover more pages than it has to.

In order to tune a query like this, you need to look at the execution plan in order to understand what's happening. However, because this query is completely dynamic, it's going to be different from one execution to the next. I would suggest breaking it down into much smaller pieces to understand exactly where the primary problems are.

By the way, if your other structures for your other queries are against heap tables that are also equally not normalized, I'd suggest getting to work on the structure. Normalize the storage and find candidates for useful clustered indexes. A clustered index can, and used correctly, usually does, improve performance on all your queries, including insert statements.
more ▼

answered Aug 15, 2012 at 09:46 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

+1 simply because I cant do +10
Aug 15, 2012 at 09:53 AM Fatherjack ♦♦
@Grant Fritchey thanks for your suggestion the table which i am retrieving the records have duplicates so i can't create clustered index on it so i will try modifying the Stored procedure by using group by instead of distinct ,and also will try to normalize database
Aug 15, 2012 at 09:56 AM sravan.434
@sravan.434 a clustered index does not have to be unique. It will be less effective but it can still be applied to a table without the need for unique records. It can also be created on multiple columns if that will be appropriate to its use and aid in increasing selectivity of the index (how close it is getting to unique)
Aug 15, 2012 at 10:01 AM Fatherjack ♦♦

What @fatherjack says. Don't confuse a clustered index with a primary key. Yes, the defaults are that a primary key is the clustered index, but it doesn't have to be, at all.

But, it does beg the question, why is your data so horribly duplicated? I'd work on fixing that problem and your performance issues might fix themselves.
Aug 15, 2012 at 10:18 AM Grant Fritchey ♦♦

@Fritchey it is aTransaction table so each siteid might be repeated depending on Transaction time that is my main concern,so please let me know on which columns i have to create clustered index and on which i have to create Non Clustered

below is the sample data

SiteId TransNum Date TermCode SeqNum AcctNum Card1 004 0001 2008-06-04 00:00:00.000 N 030 0023 245556 004 0001 2009-06-16 00:00:00.000 I 033 0020 072525 004 0001 2010-08-05 00:00:00.000 I 007 0020 404817 004 0002 2008-06-04 00:00:00.000 I 031 0052 246544 004 0002 2010-08-05 00:00:00.000 I 009 0020 19936290 004 0003 2009-06-16 00:00:00.000 I 034 0020 060687 004 0004 2009-06-16 00:00:00.000 I 035 0020 00030675 004 0004 2010-08-05 00:00:00.000 N 010 0020 19936290 004 0005 2008-06-04 00:00:00.000 I 034 0060 03062656 004 0005 2010-08-05 00:00:00.000 I 011 0020 102077
Aug 15, 2012 at 10:47 AM sravan.434
(comments are locked)
10|1200 characters needed characters left
It looks to me as though you're trying to use one SP to do several different reports, and using a lot of dynamic SQL within the SP. I suspect this is causing issues with the query optimiser. Have you tried splitting the functionality out so that you have one SP for each vQuery you're building up, and have those feeding your Temporary table?
more ▼

answered Aug 16, 2012 at 08:14 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

@Thomas i tried to split the Queries here i am retieving more columns from Transactions table which has 4 lakhs data so when i test that is taking more time even i tried by using the group by instead of Distinct
Aug 16, 2012 at 09:19 AM sravan.434
GROUP BY and DISTINCT are both aggregate clauses. You're not really changing behavior much between the two. You're dealing with either bad data or a bad design if you have so much duplication that you must aggregate in order to retrieve data. I would suggest, again, focusing on that issue first.
Aug 16, 2012 at 10:16 AM Grant Fritchey ♦♦
Thanks Fritchey for ur suggestion but i heard Group by will be Good at performance
Aug 16, 2012 at 12:29 PM sravan.434

GROUP BY is an aggregation function, not a performance enhancer. If you don't need to aggregate your data, then using GROUP BY is a net negative on performance. Period.

Based on the series of questions and the particular problems you're hitting, I strongly recommend reading some texts to get a better understanding of the fundamentals of T-SQL and SQL Server. Get a copy of Itzik Ben-Gan's "Inside SQL Server T-SQL Querying" to start.
Aug 16, 2012 at 12:42 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

You can probably create a Clustered Index on [Date] at a minimum, I assume it's ever-increasing. This will get rid of the heap issue and you do use it for some JOIN criteria. You could try [TransNum] but a DATETIME value is easier for the engine than a VARCHAR(50).

Non-Clustered Indexes on [NetWorkTrans] and [badOD] as they are your main WHERE parameters.
more ▼

answered Aug 15, 2012 at 01:26 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

No Luck @Greg
Aug 15, 2012 at 02:39 PM sravan.434
Now update your statistics on the tables.
Aug 15, 2012 at 02:41 PM Blackhawk-17
@Blackhawk it has reduced to 5 secs now is it possible to still reduce?
Aug 16, 2012 at 06:51 AM sravan.434

It may be possible but you probably need to follow a different design for your query.

Can you provide some details on the hardware?

Do you time the SP on the server or from a remote client and is their a web server in the mix?

Aug 16, 2012 at 08:52 AM Blackhawk-17
@Blackhawk i am using SQL Server 2008 R2 i am checking on the server itself i am using windows 7 64 bit with 3 GB RAM ,please let me know any other details required
Aug 16, 2012 at 09:17 AM sravan.434
(comments are locked)
10|1200 characters needed characters left
If its possible split the SP into pieces. you are appliying more logic and inserting into temp table in one sp. If we apply Text conditions (as orlike) then automatically Execution time will increase.
more ▼

answered Sep 04, 2012 at 01:29 PM

Krishjkc gravatar image

Krishjkc
50

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415
x36

asked: Aug 15, 2012 at 06:19 AM

Seen: 2433 times

Last Updated: Sep 04, 2012 at 01:29 PM