CROSS APPLY in function performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CROSS APPLY in function performance

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2014-06-25 : 10:50:21

I have two CROSS APPLY's in this query which is a function. I am trying to reduce the run time with this. Basically this returns Equipment and it's location on a daily basis within a date range. So if Rig 1 (Equipment) is at location 0510 on 1/1/2014 and moves to locaton 0530 on 3/15/2014 this will return the correct location for each date through the date range parameter. It brings back the correct results but runs slow. Not sure if there is something I can do to increase performance. If i push results into a temp table (i can't do this within an UDF can i?) would this help. Anyhow here is what i have:

SELECT
c.[date], d.RigNumber, d.NAVLocationCode
FROM(
SELECT
a.[date],
b.RigNumber
FROM
FCTN_WellService_BussDayMonth_DtRng ('1/1/2014','5/31/2014') AS a
CROSS APPLY
(
SELECT
inv.RigNumber
FROM AssetMove AS rig INNER JOIN
AssetInventory AS inv ON rig.RigNumber=inv.RigNumber
) b
) c CROSS APPLY
(
SELECT
TOP (1) DateMoved, RigNumber, NAVLocationCode
FROM(
SELECT
inv.RigNumber, rig.DateMoved, rig.MiscTextField1 AS NAVLocationCode
FROM AssetMove AS rig INNER JOIN
AssetInventory AS inv ON rig.RigNumber=inv.RigNumber INNER JOIN
Shale_Ticket.dbo.NAVLocationTbl AS loc ON rig.MiscTextField1=loc.LocationCode
) AS X
WHERE DateMoved <= c.[date] AND RigNumber=c.RigNumber
ORDER BY DateMoved DESC
) d

GROUP BY [date], d.RigNumber, d.NAVLocationCode

OPTION(MAXRECURSION 10000)

So if anyone can see something i can't that can improve my performance i would very much appreciate your thoughts. Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 12:14:39

The OPTION MAXRECURSION indiates that there is a recursive query in your function FCTN_WellService_BussDayMonth_DtRng. That recursive query and the function may be the one that is consuming most of the resources. Turn on the query plan and see which parts are resource intensive.

Also, in your cross apply you have subqueries which do not seem to be necessary. That may not necessarily improve performance, but worth examining based on what you find in the query plan. For example, the second cross apply could be simplified to this.

CROSS APPLY (
	SELECT TOP (1) rig.DateMoved ,
		inv.RigNumber ,
		rig.MiscTextField1 AS NAVLocationCode
	FROM      AssetMove AS rig
			INNER JOIN AssetInventory AS inv ON rig.RigNumber = inv.RigNumber
			INNER JOIN Shale_Ticket.dbo.NAVLocationTbl
			AS loc ON rig.MiscTextField1 = loc.LocationCode
	WHERE 
		DateMoved <= c.[date]
		AND inv.RigNumber = c.RigNumber
	ORDER BY DateMoved DESC
) d

osupratt
Posting Yak Master

238 Posts

Posted - 2014-06-25 : 12:31:00

this is the function that i pull to get dates between date range. it runs in less than a second. so would pushing these results into a temp table and then querying from this new table help in performance?

WITH DAYINMONTH AS(
SELECT DATEADD(dd,DATEDIFF(dd,0,@FromDate),0) AS [Date]

UNION ALL

SELECT DATEADD(d,1,Date) AS [Date]
FROM DAYINMONTH
WHERE DATEADD(d,1,Date) BETWEEN @FromDate AND @ToDate
)
SELECT
[Date]
FROM DAYINMONTH

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-25 : 15:09:34

Looking at the code you posted and the date ranges involved, I don't think it is the recursive query that is causing the performance issue. What you should do is to look at the query plan. Press control-m in SSMS to turn it on. Then run your query, and you will see an additional tab in the messages area. By default, it is a graphical explanation of how SQL Server performed the query. Each step in it shows you what it did, and how much resources it used and what percentage of the total query etc. That will tell you which part of the query you need to look at. The query plan also may suggest "missing indexes" which can be useful sometimes.

osupratt
Posting Yak Master

238 Posts

Posted - 2014-06-26 : 11:31:16

I show an index seek (non-clustered) as costing 51%. the seek predicate is this part of the first query i have listed:

DateMoved <= c.[date]

so i can see why it might cost this but i'm not sure if there is a way to increase performance to return the results i seek. i do use this function to show my Equipment availability. i then LEFT OUTER JOIN this to a function that pulls in revenue linking on Date and Equipment number. it takes forever to run. so if i need the results from function A (the one i have in the beginning of this post) and need to link to function B is there a better way to do this. i mean if i put results in a table and then pull and link from a table would this help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources