Perf Issue calling T-SQL function too many times

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 2005 Forums

Transact-SQL (2005)

Perf Issue calling T-SQL function too many times

Author

Topic

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-06 : 15:07:40

Version; 2005 - 9.00.1399.06 (Intel X86)

Scenario: A simple Transact SQL function that returns a table. It returns 12 rows for every call and does not do any database i/o. Just reads in a value, and based on the input returns 12 rows.

Issue:
The function returns fine, but when I call it using "cross apply", the fact that the function is called so many times is giving a performance problem. Notice the SQL:

select
count(*)
from
dbo.table_with_lots_of_rows p cross apply
dbo.sp_function_that_returns_12_rows_for_input_parms ( p.parm1, p.parm2, p.parm3 ) e

As the number of rows in the main table increases, the overhead becomes quite noticable. I will eventually have around 4 million rows implying that the procedure will be called 4 million times. The projections suggest that it will run for 3 hours.

Does anyone have any ideas about tuning such a scenario?

Thanks

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 15:10:09

- change how your function does what it does
- put the calculated value as a persisted column

show us the function

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-06 : 15:43:08

1. Create the user defined function
create function [dbo].[sp_test_multi_row]
(
@i_parm int
)
returns @g_return table( pk int, v_data varchar(max))
begin
declare
@ii int

set @ii = 0
while @ii < 12
begin
insert into @g_return values (@i_parm, @ii)
set @ii = @ii + 1
end

return
end

2. Create the large table and add 100,000 rows
create table dbo.test_large_table ( pk int )
set nocount on
declare @v_ii int
set @v_ii = 0
while @v_ii < 100000
begin
insert into dbo.test_large_table (pk ) values ( @v_ii )
set @v_ii = @v_ii + 1
end
select count(*) from dbo.test_large_table

3. test the UDF
select * from dbo.sp_test_multi_row ( 10 )

4. As a base line determine how long the count of the table takss
select count(*) from dbo.test_large_table
For me this took sub-second time

5. Now do the join and see the comparison
select count(*)
from dbo.test_large_table cross apply dbo.sp_test_multi_row ( pk )
For me this took > 5 min

Question!
The function is very simple involving no database i/o. Can this situation be improved?

Thanks
saad!

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 15:47:31

ok i'm not quite sure what are you trying to do with this function.
it looks like you just want to have 0 to 11 same rows for each id.
for 4 million rows this will be slow.

can you explain your business requirement?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-06 : 15:59:27

My question is more general in nature. The question is about performance of "cross apply". There are a lot of possibilities of this function; or are you suggesting that there is no use for "cross apply"?

So when using "cross apply", you will be joining a main table to a stored procedure; right?

So this implies that the stored procedure will be called for each row of the main table, right?

And 10,000, 100,000 are not big numbers when we are talking about data processing. I can come up with other ways of doing this - but the main question is that "is there a way to tune this situation where a simple stored procedure is called lots (10,000 e.g) times?".

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 16:05:51

no it doesn't call a stored procedure. it calls a user defined function. It's a bigg difference.
no there isn't a simple way. not for 4 million rows.
don't even think there's a hard way

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-06 : 18:57:13

Following changes made a huge improvement.

1. Upgraded to SP2 (major improvement)
2. Renamed the function to not have sp_ prefix.

Now the test with 100,000 executions returned in 1 minute. Previously it had kept running for over 10 minutes and I had to kill it.

--
Saad Ahmad
saad.ahmad@gmail.com

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-06 : 22:23:14

quote:
Originally posted by saad.ahnad@gmail.com

Following changes made a huge improvement.

1. Upgraded to SP2 (major improvement)
2. Renamed the function to not have sp_ prefix.

Now the test with 100,000 executions returned in 1 minute. Previously it had kept running for over 10 minutes and I had to kill it.

--
Saad Ahmad
saad.ahmad@gmail.com

What impact did renaming the procedure have on the run time?

Future guru in the making.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 04:43:42

well sql server didn't have to go looking to the master database first if the sproc with that name already exists there.
but this doesn't account for such perf gain.

i have no idea why did sp2 improve things so much.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-07 : 09:50:12

The release notes of SP2 indicate that it fixes a memory leak for "cross apply"

--
Saad Ahmad
saad.ahmad@gmail.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-07 : 10:01:37

Cool!
Didn't know that. thanx for sharing!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-07 : 10:11:09

There is a significant overhead to using a user defined function for each row of a large result set, so make sure you actually need to use one. Take a look at the link below.

Demo Performance Penalty of User Defined Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources