question

sravan.434 avatar image
sravan.434 asked

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
stored-proceduresoptimization
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
6 Likes 6 ·
It took me longer than 6 seconds to scroll through the question.
4 Likes 4 ·
@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
0 Likes 0 ·
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
0 Likes 0 ·
sure Fatherjack i will tick the Questions which have helped me
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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)
2 Likes 2 ·
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.
2 Likes 2 ·
+1 simply because I cant do +10
1 Like 1 ·
@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
0 Likes 0 ·
@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
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
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.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Now update your statistics on the tables.
1 Like 1 ·
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?
1 Like 1 ·
No Luck @Greg
0 Likes 0 ·
@Blackhawk it has reduced to 5 secs now is it possible to still reduce?
0 Likes 0 ·
@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
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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?
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
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.
2 Likes 2 ·
@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
0 Likes 0 ·
Thanks Fritchey for ur suggestion but i heard Group by will be Good at performance
0 Likes 0 ·
Krishjkc avatar image
Krishjkc answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.