November 2, 2012 at 5:29 am
select col1, col2, col3, dbo.fun1(col1,col2) from tb1
function returns a string value with comma separated i.e abc1, abcd2
This is taking more time, when we call a function.
Is there an alternative way to do this ?
November 2, 2012 at 5:36 am
what's your function actual doing
can you post your function
if just concating two column values with ','
then
select col1+','+col2
i am not sure you want this or not
post function then can give proper answer
November 2, 2012 at 5:48 am
muthukrishnan.e (11/2/2012)
DECLARE @r VARCHAR(MAX)SELECT @r = ISNULL(@r+'/', '')
+ t2.col4 + ' - ' + 'Rs. ' + CONVERT(NVARCHAR,t.amount)
FROM t1
left join t2 on t1.strcol = t2.strcol3
WHERE t1.strcol = @strcol and t1.partnerid = @strcol
and CONVERT(date,t1.datecol) = @date
RETURN @r
This looks like the body of your function
Can you post the complete function along with the input parameters
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 2, 2012 at 5:56 am
ALTER FUNCTION [dbo].[GetDontionHeadsforAuditReceipts]
(
@receiptno NVARCHAR(32), @partnerid NVARCHAR(32) , @currdate NVARCHAR(32)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @r NVARCHAR(MAX)
SELECT @r = ISNULL(@r+'/', '')
+ DH.DESCRIPTION + ' - ' + 'Rs. ' + CONVERT(NVARCHAR,DPPD.Amount)
FROM DON_DPPDENTRY DPPD
left join DONATIONHEADS DH on DPPD.DONHEAD = DH.CODE
WHERE DPPD.receiptno = @receiptno and DPPD.partnerid = @partnerid
and CONVERT(date,DPPD.CURRDATE) = @currdate
RETURN @r
END
November 2, 2012 at 5:58 am
put both table structure also(function having)
looking to your function
1)CONVERT(date,t1.datecol)
why you are converting datecol to date ? why not store in date datatype itself
how may rows both table contains?
November 2, 2012 at 6:03 am
it is in datetime datatype but i need only date ....
tables 1 has more than 50 k records
tables 2 is a configuration table has 50 records.
November 2, 2012 at 6:06 am
Can you post the proper select statement which encorporates the function call?
Also have you determined if the function can be changed into a inline table function, instead of a scalar function?
November 2, 2012 at 6:11 am
SELECT DPPD.[RECEIPTNO],CASH.CASHDATE AS MAILDATE,'' AS BANKNAME,'' AS CHEQUENO,
'CASH' AS INSTTYPE,'' AS INSTDATED,'' AS BRANCHNAME, 'CASH' AS BATCHTYPE , DPPD.AMOUNT AS AMOUNT, DPPD.DONHEAD ,
DPPD.PARTNERID AS PARTNERID,DPPD.TRANSACTIONID AS TRANSACTIONID, DPPD.CURRDATE AS CURRDATE
,DBO.GETDONTIONHEADSFORAUDITRECEIPTS([RECEIPTNO],PARTNERID,CONVERT(DATE,CURRDATE)) AS DONATIONHEAD
FROM DON_CASHENTRY CASH, DON_DPPDENTRY DPPD
WHERE CASH.TRANSACTIONID = DPPD.TRANSACTIONID AND
CASH.CASHDATE = DPPD.MAILDATE AND CASH.TRANSACTIONID = DPPD.TRANSACTIONID
AND DPPD.MAILDATE BETWEEN '2012-04-01' AND '2012-04-10'
November 2, 2012 at 7:42 am
Work on the function in isolation from the rest of the code. It's a rows to columns transformation using a well-known cheat;
SELECT @r = ISNULL(@r+'/', '')
+ DH.DESCRIPTION + ' - ' + 'Rs. ' + CONVERT(NVARCHAR,DPPD.Amount)
FROM DON_DPPDENTRY DPPD
left join DONATIONHEADS DH on DPPD.DONHEAD = DH.CODE
WHERE DPPD.receiptno = @receiptno and DPPD.partnerid = @partnerid
and CONVERT(date,DPPD.CURRDATE) = @currdate
Plug in some sensible values for those parameters and check the execution plan for table scans (clustered index scans). I don't think this can be converted to an inline TVF.
You might get better performance using the FOR XML PATH trick, which IIRC can be configured as an iTVF.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply