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
@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.
@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
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
sure Fatherjack i will tick the Questions which have helped me
It took me longer than 6 seconds to scroll through the question.