Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
T.Ashish
T.Ashish
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 582
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7492 Visits: 15148
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9019 Visits: 19034
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
Exploring Recursive CTEs by Example Dwain Camps
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
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);




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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 at GMail
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6002 Visits: 8314
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 at GMail
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9019 Visits: 19034
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search