How can we optimize a query having TABLE VALUED function in SELECT?

Question

How can we optimize a query having TABLE VALUED function in SELECT?

T.Ashish

SSCarpal Tunnel

Points: 4248
More actions
April 2, 2013 at 11:06 pm

#272987

Hi All,
This query is running slow due to a TABLE VALUED function in select clause. Definition of query and function is pasted below. Table structure, data definition not available. Anyone want to try this query.
=============
QUERY
=============
SELECT top 250000
x1.wo_ref AS [z13031322175522447287ca90],
sum(
(
select x.approved_ts_amount
from dbo.woccsr_ts_amt(20,x3.wo_id,x3.cc_id, x3.gl_id )
as x
)
) AS [z13031322175522447289ca90]
FROM
dbo.wo AS x1 WITH (NOLOCK)
INNER JOIN dbo.wocc AS x3 WITH (NOLOCK) ON x1.wo_id = x3.wo_id
WHERE
status != 17 and
x1.b_code in ('GOOG')
GROUP BY
x1.wo_ref
=============
FUNCTION
=============
CREATE function [dbo].[woccsr_ts_amt] (@wo_id varchar(24),
@cc_id varchar(24),
@gl_id varchar(24))
returns TABLE
as
RETURN
select
sum((case when nr.status in (1, 3, 44, 52) then
idr.detail_amount + idr.crdb_adj_amount
else
0
end)) as approved_ts_amount
from
dbo.pay_detail (nolock) as idr
inner join dbo.ts (nolock) nr on nr.ts_id = idr.incurred_id
where
nr.wo_id =@wo_id
and idr.cc_id = @cc_id
and idr.gl_id = @gl_id
Thanks.

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

T.Ashish (4/2/2013)
Hi All,
This query is running slow due to a TABLE VALUED function in select clause. Definition of query and function is pasted below. Table structure, data definition not available. Anyone want to try this query.

Without something to work with this could be absolutely anything. There are at least few dozen or more possibilities. Many of those answers come from the ddl.

I would warn you VERY strongly to stop using NOLOCK like you are. It is bad enough in specific queries but in a function that appears to be dealing with money you are asking for serious problems. You need to understand what this hint does and the dangers it can bring.

Here just a few articles that explain this hint.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://sqlmag.com/sql-server/quaere-verum-clustered-index-scans-part-iii

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Your function takes three parameters, yet in your query, you pass it four. Do you not get an error when you run the query?

We may not be able to help you with performance without DDL and sample data, but if you could post an execution plan, we may be able to spot something obvious.

John

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

Can you post the actual execution plan for this please, as a .sqlplan attachment:

SELECT TOP 250000

[z13031322175522447287ca90] = x1.wo_ref,

[z13031322175522447289ca90] = SUM(x.approved_ts_amount)

FROM dbo.wo x1

INNER JOIN dbo.wocc x3

ON x1.wo_id = x3.wo_id

CROSS APPLY (

SELECT approved_ts_amount = SUM(idr.detail_amount + idr.crdb_adj_amount)

FROM dbo.pay_detail idr

INNER JOIN dbo.ts nr

ON nr.ts_id = idr.incurred_id

AND nr.[status] IN (1, 3, 44, 52) -- quicker

WHERE nr.wo_id = x3.wo_id -- outer reference

AND idr.cc_id = x3.cc_id -- outer reference

AND idr.gl_id = x3.gl_id -- outer reference

) x

WHERE [status] != 17 -- which table does this column belong to?

AND x1.b_code IN ('GOOG')

GROUP BY x1.wo_ref

^{“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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Something like this:

SELECT top 250000

x1.wo_ref AS [z13031322175522447287ca90],

sum(wta.approved_ts_amount) AS [z13031322175522447289ca90]

FROM

dbo.wo AS x1

INNER JOIN dbo.wocc AS x3

ON x1.wo_id = x3.wo_id

CROSS APPLY dbo.woccsr_ts_amt(20, x3.wo_id,x3.cc_id, x3.gl_id) wta(approved_ts_amount)

WHERE

status != 17 and

x1.b_code in ('GOOG')

GROUP BY

x1.wo_ref

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

And this rewrite of the function should work like the one Chris provided in-line in his code.

CREATE function [dbo].[woccsr_ts_amt] (

@wo_id varchar(24),

@cc_id varchar(24),

@gl_id varchar(24))

returns TABLE

as

RETURN

select

sum(idr.detail_amount + idr.crdb_adj_amount) as approved_ts_amount

from

dbo.pay_detail as idr

inner join dbo.ts nr

on nr.ts_id = idr.incurred_id

where

nr.wo_id = @wo_id

and idr.cc_id = @cc_id

and idr.gl_id = @gl_id

and nr.status in (1, 3, 44, 52);

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

TheSQLGuru (4/3/2013)
refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

Actually, the TVF being used is an in-line TVF.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Lynn Pettis (4/3/2013)
TheSQLGuru (4/3/2013)
refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.
Actually, the TVF being used is an in-line TVF.

That was a test Lynn - you PASSED!! :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

TheSQLGuru (4/3/2013)
refactor your TVF to be an INLINE TVF.

I think it is an iTVF

Also push that SELECT ...(SELECT...) out like previous poster did.

I don't think that matters much, if at all.

^{“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