Calling function on select statement

Question

Post reply

Calling function on select statement

muthukrishnan.e

SSC Enthusiast

Points: 154
More actions
November 2, 2012 at 5:29 am

#266507

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 ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

BriPan SSCrazy Points: 2061 More actions · Answer 1

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

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 2

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

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 3

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

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 4

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

BriPan SSCrazy Points: 2061 More actions · Answer 5

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?

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 6

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.

Ant-Green SSC Guru Points: 112965 More actions · Answer 7

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?

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 8

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'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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