SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Optimizing SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Adagio
Starting Member

5 Posts

Posted - 07/30/2013 :  06:56:16  Show Profile  Reply with Quote
I'm looking for a way to optimize my SQL server stored procedures and functions, hopefully there's a kind soul here who could help me along the way.

Here is a simplified version of my Stored procedure:

SELECT BookingId, RandomBookingData
(SELECT BookingData1 FROM FunctionGetBookingData(BookingId)),
(SELECT BookingData2 FROM FunctionGetAdvancedBookingData(BookingId)),
(SELECT BookingData3 FROM FunctionGetAdvancedBookingData(BookingId))
FROM BookingDataMainTable INNER JOIN BookingDataSecondTable ON BookingDataMainTable.BookingId = BookingDataSecondTable.BookingId
WHERE BookingDataMainTable.BookingDate = @BookingDate

And here is a simplified example of one of the functions:

ALTER FUNCTION FunctionGetAdvancedBookingData
(
@BookingID int
)

RETURNS TABLE
AS
RETURN

SELECT BookingData2, BookingData3
FROM FunctionGetEvenMoreAdvancedBookingData(@BookingItemID)


As you can see there are several performance problems with this method. A typical run of the stored procedure returns thousands of records and for each record it will call several functions several times. In our project there are 3 different functions, one function will be called twice to get two different columns. And all of the functions will also call some subfunctions

I'm thinking that it might be a good idea to change the functions to receive a table (or list) of bookingIds and then call the functions in the FROM...Inner join part of the stored procedure instead of calling them for each record. But this would require me to retrieve all booking information from the stored procedure before I call the functions in a different stored procedure I assume.
Is that the best way or does anyone have a better idea? How would I approach this?

Edited by - Adagio on 07/30/2013 07:01:12

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2013 :  07:01:21  Show Profile  Reply with Quote
You dont need to call UDfs recursively like this. You may be better off using recursive CTEs for implementing it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30214 Posts

Posted - 07/30/2013 :  07:10:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		mt.BookingId,
		st.RandomBookingData,
		x.BookingData1,
		y.BookingData2,
		y.BookingData3
FROM		dbo.BookingDataMainTable AS mt
INNER JOIN	dbo.BookingDataSecondTable AS st ON st.BookingId = mt.BookingId
CROSS APPLY	dbo.FunctionGetBookingData(mt.BookingId) AS x
CROSS APPLY	dbo.FunctionGetAdvancedBookingData(mt.BookingId) AS y
WHERE		mt.BookingDate = @BookingDate;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2013 :  07:16:25  Show Profile  Reply with Quote
In any case posting some information on what you're trying to do inside UDFs will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Adagio
Starting Member

5 Posts

Posted - 07/30/2013 :  07:44:14  Show Profile  Reply with Quote
Thanks SwePeso, that seems to have helped a bit on the performance :)
Go to Top of Page

Adagio
Starting Member

5 Posts

Posted - 07/30/2013 :  07:46:05  Show Profile  Reply with Quote
visakh16 >> What is CTE and UDF?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2013 :  07:47:49  Show Profile  Reply with Quote
quote:
Originally posted by Adagio

visakh16 >> What is CTE and UDF?


CTE - Common Table Expression
UDF - User Defined Function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000