Query Execution Time Management.

Nasir Abbas

I have two UDF in SQL Server 2000

f_PendingDCDetails (which gives around 1.3 M records In 3 Minutes)
f_PendingDC who uses f_PendingDCDetails functions (which gives 20 pending records but takes 10+ Minutes).

Richard Deeming

And? Apart from the fact that you've forgotten to ask a question, we can't see your database, your data, or the code for the two functions. Assuming your question was meant to be "how do I improve the performance of the second UDF", there's no way we can answer that with the information you've given us.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Nasir Abbas

Extremely sorry for incomplete question, however I mentioned that I m using SQL Server 2000. Yes you are right my question was

how do I improve the performance of the second UDF

Also I encode/attach my UDFs (I again apologize if I wrongly do the same as I don't know the exact way to attach my code, however, if u will guide I will be care full in near future.

Alter Function f_PendingDCDetails (@SID Varchar(32), @iFID varchar(32), @iTID varchar(32),
@CID Varchar(32), @EnDate Varchar(11), @iWR TinyINt, @iLoc Varchar(8), @iScrVNo Varchar(80),
@iVtp VarChar(20), @iSubType Varchar(8), @iFinancialYear Varchar(8), @iMnth VarChar(8), @ivYear Varchar(8),
@iLocation VarChar(8), @iForLocation Varchar(8), @iAorB Varchar(1), @iVNo Integer, @iSrNo Integer, @ForWhichForm VarChar(2))
Returns @T Table (
QueryNo Varchar (4),
ScrVoucher_No Varchar (101),
Vtp Varchar (20),
SubType Varchar (8),
FinancialYear Varchar (8),
Mnth Varchar (8),
vYear Varchar (8),
Location Varchar (8),
ForLocation Varchar (8),
AorB Varchar (1),
vno Varchar (4),
srno Varchar (4),
dated DateTime,
Item_ID Varchar (32),
Delivered Double Precision,
Returned Integer,
Invoiced Double Precision,
Rate Double Precision,
Remarks Text,
Project_ID Varchar (32),
Client_ID Varchar (32),
Emp_ID Varchar (32),
DeliveryChallanNo Varchar (70),
GDNDelivered Double Precision,
EngineNo Varchar (64),
FrameNo Varchar (64),
VRegNo Varchar (32),
ClaimType Varchar (32),
PrincipalCompany_ID Varchar (32),
STPercent Double Precision,
EDPercent Double Precision,
OTPercent Double Precision,
DiscP Double Precision,
DiscA Double Precision,
GrossRate Double Precision,
MainDiscP Double Precision,
Party_ID Varchar (5),
ClientPONo Varchar (200),
ClientPODate DateTime,
PaymentTerms Varchar (32),
Job_ID Varchar (32),
CostCenter_ID Varchar (32),
BatchNo Varchar (40),
ExpiryDate DateTime,
Transporter_ID Varchar (32),
BQO Double Precision,
BQD Double Precision,
Gift_ID Varchar (32),
GQO Double Precision,
GQD Double Precision,
BiltyNo Varchar (120),
BiltyDate DateTime,
OldSoftNo Varchar (32)
)
as
Begin
Insert @T
Select Max(T.QueryNo) QueryNo, Max(T.ScrVoucher_No) ScrVoucher_No, Max(T.Vtp) Vtp, Max(T.SubType) SubType, Max(T.FinancialYear) FinancialYear,
Max(T.Mnth) Mnth, Max(T.vYear) vYear, Max(T.Location) Location, Max(T.ForLocation) ForLocation, Max(T.AorB) AorB, Max(T.vno) vno,
M

Richard Deeming

I'd be inclined to make both of those in-line UDFs, rather than multi-statement UDFs:

DROP FUNCTION f_PendingDCDetails;
GO
CREATE FUNCTION f_PendingDCDetails
(
@SID Varchar(32),
@iFID varchar(32),
@iTID varchar(32),
@CID varchar(32),
@EnDate varchar(11),
@iWR tinyint,
@iLoc varchar(8),
@iScrVNo varchar(80),
@iVtp varchar(20),
@iSubType varchar(8),
@iFinancialYear varchar(8),
@iMnth varChar(8),
@ivYear varchar(8),
@iLocation varchar(8),
@iForLocation varchar(8),
@iAorB varchar(1),
@iVNo integer,
@iSrNo integer,
@ForWhichForm varchar(2)
)
Returns Table
As
Return
(
Select
Max(T.QueryNo) QueryNo,
Max(T.ScrVoucher_No) ScrVoucher_No,
Max(T.Vtp) Vtp,
Max(T.SubType) SubType,
Max(T.FinancialYear) FinancialYear,
Max(T.Mnth) Mnth,
Max(T.vYear) vYear,
Max(T.Location) Location,
Max(T.ForLocation) ForLocation,
Max(T.AorB) AorB,
Max(T.vno) vno,
Max(T.srno) srno,
Max(T.dated) dated,
Max(T.Item_ID) Item_ID,
Sum(T.Delivered) Delivered,
Sum(T.Returned) Returned,
Sum(T.Invoiced) Invoiced,
Sum(T.Rate) Rate,
Max(T.Remarks) Remarks,
Max(T.Project_ID) Project_ID,
Max(T.Client_ID) Client_ID,
Max(T.Emp_ID) Emp_ID,
Max(T.DeliveryChallanNo) DeliveryChallanNo,
Sum(T.GDNDelivered) GDNDelivered,
Max(T.EngineNo) EngineNo,
Max(T.FrameNo) FrameNo,
Max(T.VRegNo) VRegNo,
Max(T.ClaimType) ClaimType,
Max(T.PrincipalCompany_ID) PrincipalCompany_ID,
Sum(T.STPercent) STPercent,
Sum(T.EDPercent) EDPercent,
Sum(T.OTPercent) OTPercent,
Sum(T.DiscP) DiscP,
Sum(T.DiscA) DiscA,
Sum(T.GrossRate) GrossRate,
Sum(T.MainDiscP) MainDiscP,
Max(T.Party_ID) Party_ID,
Max(T.ClientPONo) ClientPONo,
Max(T.ClientPODate) ClientPODate,
Max(T.PaymentTerms) PaymentTerms,
Max(T.Job_ID) Job_ID,
Max(T.CostCenter_ID) CostCenter_ID,
Max(T.BatchNo) BatchNo,
Max(T.ExpiryDate) ExpiryDate,
Max(T.Transporter_ID) Transporter_ID,
Sum(T.BQO) BQO,
Sum(T.BQD) BQD,
Max(T.Gift_ID) Gift_ID,
Sum(T.GQO) GQO,
Sum(T.GQD) GQD,
Max(T.BiltyNo) BiltyNo,

Nasir Abbas

Thank you very much Richard for ur prompt reply (I was away and hence was unable to see it right in-time then). Previously (some days ago), these were in-line functions but as records increase, an "Insufficient Memory" error was faced although systems had around 16, 32 GBs of Rams, due to which these have to be converted to multi-statement functions. Also, I m using SQL Server 2008 too but getting almost the same results. May u pls suggest some better way to write queries of these natures.