Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can we optimize a query having TABLE VALUED function in SELECT? Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 11:06 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
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.
Post #1438166
Posted Wednesday, April 3, 2013 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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)
Post #1438360
Posted Wednesday, April 3, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 5,330, Visits: 9,777
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
Post #1438379
Posted Wednesday, April 3, 2013 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1438399
Posted Wednesday, April 3, 2013 9:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 23,299, Visits: 32,050
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

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)
Post #1438414
Posted Wednesday, April 3, 2013 9:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 23,299, Visits: 32,050
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);





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)
Post #1438417
Posted Wednesday, April 3, 2013 9:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 4,351, Visits: 6,166
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
Post #1438419
Posted Wednesday, April 3, 2013 9:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 23,299, Visits: 32,050
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.



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)
Post #1438421
Posted Wednesday, April 3, 2013 9:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 4,351, Visits: 6,166
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!!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1438424
Posted Wednesday, April 3, 2013 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1438426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse