Urgent: Regarding query in sql

Discussion in '.NET' started by kvtaravind, Aug 21, 2010.

  1. kvtaravind

    kvtaravind

    Joined:
    Aug 21, 2010
    Messages:
    1
    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_Contact_Name,Purpose,Approver_Contact_Name,Book_Type,Department,Cost_Center,isnull(Incidental_Cost,0),Status 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,@TVLPurpose,@Approver,@BookType,@Depart,@CostCenter,@EstIncCost,@Status
    WHILE @@FETCH_STATUS = 0 Begin
    select @Desig=Designation,@Zone=Zone_Name,@Location=Location_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,@PassCName,@Desig,@TVLPurpose,@Approver,@BookType,@AmtPayable,@TotEstAmt,@EstTvlCost,@EstLanB,@EstIncCost,
    @Zone,@Location,@Depart,@CostCenter,@Status)
    End
    FETCH NEXT FROM FrmTicket INTO @BookId,@ActNo,@TVBDate,@PassName,@PassCName,@TVLPurpose,@Approver,@BookType,@Depart,@CostCenter,@EstIncCost,@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
     
    kvtaravind, Aug 21, 2010
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. sunnyz
    Replies:
    1
    Views:
    363
    Victor Garcia Aprea [MVP]
    Oct 24, 2004
  2. Edward
    Replies:
    4
    Views:
    4,663
    William \(Bill\) Vaughn
    Apr 10, 2006
  3. Anonymous
    Replies:
    0
    Views:
    1,548
    Anonymous
    Oct 13, 2005
  4. sanju
    Replies:
    0
    Views:
    401
    sanju
    Dec 18, 2006
  5. ecoolone
    Replies:
    0
    Views:
    800
    ecoolone
    Jan 3, 2008
Loading...

Share This Page