RE: slowness execution of sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

RE: slowness execution of sql query

Author

Topic

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-21 : 03:03:50

Hi guys kindly help,

Ihave used following table lined functions in sql

USE [EEMSPROD]
GO
/****** Object: UserDefinedFunction [dbo].[OpenTRF] Script Date: 08/21/2010 11:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[OpenTRF]() RETURNS @TblOpenTRF TABLE ([Req No] NVARCHAR(50),[Travel from Date] datetime,[Travel to Date] datetime,[Employee ID] varchar(50),[Employee Name] varchar(50),
Designation varchar(50),[Travel Purpose] varchar(50), Approver varchar(50),[Booking Type] varchar(50), [Amount Payable to TA] float, [Total Estimated Approved Amount] float,
[Est Travel Cost] float,[Est L and B] float,[Est Incidental Cost] float, Zone varchar(50), Location varchar(50), Department varchar(50), Status varchar(50), [Cost Center] varchar(50) collate database_default)
as
begin
declare @ActNo int
declare @BookId varchar(50)
declare @maxDate datetime
declare @minDate datetime
declare @CurrDate datetime
declare @TVBDate datetime
declare @PassName varchar(50)
declare @PassCName varchar(50)
declare @Desig varchar(50)
declare @TVLPurpose varchar(50)
declare @Approver varchar(50)
declare @BookType varchar(50)
declare @AmtPayable float
declare @TotEstAmt float
declare @EstTvlCost float
declare @EstLanB float
declare @EstIncCost float
declare @Zone varchar(50)
declare @Location varchar(50)
declare @Depart varchar(50)
declare @CostCenter varchar(50)
declare @AirCost float
declare @TrainCost float
declare @HotelCost float
declare @Status varchar(50)

set @CurrDate=getdate()
DECLARE FrmTicket CURSOR FOR select Book_Id,Activity_no,TVB_Date,Pass_Name,Pass_Contac t_Name,Purpose,Approver_Contact_Name,Book_Type,Dep artment,Cost_Center,isnull(Incidental_Cost,0),Stat us from [TATA_TRAVEL_TICKETINFO] where Status in ('Submitted','Approved','Invoiced','Booked') and Book_Id not in (select Book_Id from Tata_Travel_ExpenseInfoNew)
OPEN FrmTicket
FETCH NEXT FROM FrmTicket INTO @BookId,@ActNo,@TVBDate,@PassName,@PassCName,@TVLP urpose,@Approver,@BookType,@Depart,@CostCenter,@Es tIncCost,@Status
WHILE @@FETCH_STATUS = 0 Begin
select @Desig=Designation,@Zone=Zone_Name,@Location=Locat ion_Name from [TATA_Master_EmployeeMaster] where Emp_Code=@PassName
select @AirCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_AirwayDetails] where Temp_Activity_No=@ActNo
select @TrainCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_TrainDetails] where Temp_Activity_No=@ActNo
select @HotelCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_HotelDetails] where Temp_Activity_No=@ActNo
select @minDate=min(convert(datetime,tdate,105)),@maxDate =max(convert(datetime,tdate,105)) from [TATA_TRAVEL_TRAVELPLAN] where Temp_Activity_No=@ActNo and Trip_Status!='Cancelled'
set @TVBDate=@maxDate
if(@maxDate < @CurrDate)
begin
set @AmtPayable=isnull(@AirCost,0)+isnull(@TrainCost,0 )
select @EstTvlCost=sum(isnull(Est_cost,0)) from [TATA_TRAVEL_TRAVELPLAN] where temp_activity_no=@ActNo
select @EstLanB=sum(isnull(Estimated_cost,0)) from [TATA_TRAVEL_BoardingPlan] where temp_activity_no=@ActNo
set @TotEstAmt=isnull(@EstTvlCost,0)+isnull(@EstLanB,0 )+isnull(@EstIncCost,0)
set @EstLanB=isnull(@EstLanB,0)
INSERT INTO @TblOpenTRF ([Req No],[Travel from Date],[Travel to Date],[Employee ID],[Employee Name],Designation,[Travel Purpose],Approver,[Booking Type],[Amount Payable to TA],
[Total Estimated Approved Amount],[Est Travel Cost],[Est L and B],[Est Incidental Cost],Zone,Location,Department,[Cost Center],Status)
VALUES(@BookId,@minDate,@TVBDate,@PassName,@PassCN ame,@Desig,@TVLPurpose,@Approver,@BookType,@AmtPay able,@TotEstAmt,@EstTvlCost,@EstLanB,@EstIncCost,
@Zone,@Location,@Depart,@CostCenter,@Status)
End
FETCH NEXT FROM FrmTicket INTO @BookId,@ActNo,@TVBDate,@PassName,@PassCName,@TVLP urpose,@Approver,@BookType,@Depart,@CostCenter,@Es tIncCost,@Status
END
CLOSE FrmTicket
DEALLOCATE FrmTicket
return
end;

after creating this table inlined functions i have created views to get data from @tblOpentrf which is follows

ALTER VIEW [dbo].[OpenTRFView]
AS
SELECT [Req No], [Employee ID], [Employee Name], Designation, [Travel Purpose], Approver, [Booking Type], [Amount Payable to TA],
[Total Estimated Approved Amount], [Est Travel Cost], [Est L and B], [Est Incidental Cost], Zone, Location, Department, Status, [Cost Center],
[Travel from Date], [Travel to Date]
FROM dbo.OpenTRF() AS OpenTRF_1

after that i have used following query to display in front end

Select OpenTRFView.[Req No][Req No],OpenTRFView.[Travel from Date][Travel from Date],OpenTRFView.[Travel to Date][Travel to Date],OpenTRFView.[Employee ID][Employee ID],OpenTRFView.[Employee Name][Employee Name],OpenTRFView.[Designation][Designation],OpenTRFView.[Travel Purpose][Travel Purpose],OpenTRFView.[Approver][Approver],OpenTRFView.[Booking Type][Booking Type],OpenTRFView.[Amount Payable to TA][Amount Payable to TA],OpenTRFView.[Total Estimated Approved Amount][Total Estimated Approved Amount],OpenTRFView.[Est Travel Cost][Est Travel Cost],OpenTRFView.[Est L and B][Est L and B],OpenTRFView.[Est Incidental Cost][Est Incidental Cost],OpenTRFView.[Zone][Zone],OpenTRFView.[Location][Location],OpenTRFView.[Department][Department],OpenTRFView.[Cost Center][Cost Center],OpenTRFView.[Status][Status] from OpenTRFView

My problem here is if there is huge number of records the query takes huge time to execute around one hour i need reduce this into 10 or 15 seconds kindly help

Aravind.T

Aravind.T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:21:11

duplicate please dont cross post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149111

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources