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

avatar image

sravan.434
20 7 7 10

@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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

  • 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

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

@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

avatar image

Blackhawk-17
12k 30 35 42

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

avatar 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.

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:

x455
x45

asked: Aug 15, 2012 at 06:19 AM

Seen: 3488 times

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

Copyright 2016 Redgate Software. Privacy Policy