Optimizing SQL - SQL Server Forums

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)

Optimizing SQL

Author

Topic

Adagio
Starting Member

5 Posts

Posted - 2013-07-30 : 06:56:16

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 07:01:21

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-30 : 07:10:08

[code]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;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 07:16:25

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

Adagio
Starting Member

5 Posts

Posted - 2013-07-30 : 07:44:14

Thanks SwePeso, that seems to have helped a bit on the performance :)

Adagio
Starting Member

5 Posts

Posted - 2013-07-30 : 07:46:05

visakh16 >> What is CTE and UDF?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 07:47:49

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources