Urgent: slowness of execution of query

Question

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

Answer 1

Your problem, MOST LIKELY, should be in your function!

The view and the query from the view SEEM to be straight forward.

Some common possible reasons which may be causing the slowness:-

1. Use of CURSOR
2. SubQuery with IN clause
3. SubQuery within Subquery with a NOT IN clause

All of the above can notoriously slow down query execution. However, this is just based on LOOKING at your query. It is the QUERY EXECUTION plan which will really give you information as to what might be causing the slowness!!

Things like indexes, updation of statistics, size of your table and VARIOUS other factors could POTENTIALLY be causing your problem

Answer 2

I'm going to vote your question up simply as a shining example of how NOT to write good code in SQL Server.

This is a classical procedural solution to a problem in a relational database which clearly can be solved relatively easily with what relational database do well: declarative set-based programming.

What you are doing is loading into a table variable, row by agonizing row, and for each row doing multiple queries to get further information.

This should very easily be simplified by eliminating the cursor and using joins - even correlated subqueries may be far faster than multiple statements within a cursor. Also, it may be possible to have an indexed views for some things you are SUMming which would make those sub-queries quicker.

Basically:

SELECT columns_you_need
FROM [TATA_TRAVEL_TICKETINFO] AS t1
INNER JOIN [TATA_Master_EmployeeMaster] AS t2
    ON t2.Emp_Code = t1.Pass_Name

etc.

Because your TVF is parameterized, if you can't get the entire thing into one view, aim to turn it into an inline table valued function - inline TVFs are equivalent to views in terms of performance, with the optimizer able to push things around a lot more easily.

So on top of this poorly performing procedural technique you are using, a multi-statement TVF is not generally going to perform particularly well, because it is a black box to the optimizer.

If you provide a full script to create and populate the tables, and example results, I will convert it into a set-based solution for you here, but without that, it's simply too time-consuming to attempt to provide more detailed solution.