Returning result set from function to stored procedure is very slooooooooow 
Author Message
 Returning result set from function to stored procedure is very slooooooooow

Hi !

I'm having strange problem that I'm sure it's trivial for some guru,
but after few hours I just cannot solve it. Here it is:

I've created function which is basically parametrized view: based on
(lots of) parameters it returns record set of results. Something like
this:

create function pfn(<list of parameters>)
RETURNS TABLE
AS
  RETURN (SELECT   ....)

Then I have two stored procedures: first one calls function and just
returns number of hits:

CREATE  PROCEDURE dbo.sp_RecordCount (<same parameters>)
AS
BEGIN
  SELECT count(<primary key column>) FROM [dbo].[pfn](<parameters>)
END

and second one returns actual data:

CREATE  PROCEDURE dbo.sp_RecordCount (<same parameters>)
AS
BEGIN
  SELECT * FROM [dbo].[pfn](<parameters>)
END

I've implemented this approach because I'm working with huge tables
and I want to prevent users to execute queries with big results set.
So client application first checks if query based on parameters would
return too many hits. In that case, it aborts the execution of 'main'
query.

So far, so good. Everything was working as expected on testing
database. When I installed application on production server, it turned
out that function executes in less than one second, but stored
procedure takes more than one minute !?!?!?

I've checked both query execution plans and they are totaly different.
I use just one parameter which is value of primary key column in the
main table. When I call function, it uses index seek as expected, but
when I'm calling procedure, server uses cluster index 'SCAN' !?!?
Table holds over 20 million of records, so you can imagine how slow
everything is.

Any ideas ? Like I said: I'm sure that this must be very easy to
solve.

thnx a lot !
Jure



Mon, 02 Jan 2006 10:29:20 GMT
 Returning result set from function to stored procedure is very slooooooooow

This was posted by Andrew Kelley a while back:

This blurb from Bart may be of interest to you:

The reason for the performance difference stems from a feature called
"parameter sniffing".  Consider a stored proc defined as follows:



     GO

Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute.  In 6.5, at compile time SQL

when compiling a plan.  Suppose all of the actual parameter values for

integers that were greater than 0, but suppose 40% of the [c1] values in
[table1] were, in fact, 0.  SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates.  A table scan would
be the best plan if the parameter value was 0, but unfortunately it

stored proc for more typical parameters suffers.

In SQL 7.0 or 2000, suppose you executed this proc for the first time

10".  Parameter sniffing allows SQL to insert the known value of


compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --

execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".

In your case, the problem is that you have default NULL values for your

the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column.  If the first execution of this stored proc doesn't pass in an

be NULL.  When SQL compiles the plan for this sp it substitutes NULL for

Unfortunately, after execution begins the first thing the stored proc

unfortunately SQL doesn't know about this at compile time.  Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.

So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation.  If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:

1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself.  SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).

2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan.  There are several
possibilities here. for example:
    a. Put the affected queries in a different "child" stored proc.  If

has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
    b. Use sp_executesql to execute the affected queries.  The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.

An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.

Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice.  Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach :

   - If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
   - If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective.  The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution.  This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.

HTH -
Bart
------------
Bart Duncan
Microsoft SQL Server Support

Please reply to the newsgroup only - thanks.

This posting is provided "AS IS" with no warranties, and confers no
rights.

Quote:

> Hi !

> I'm having strange problem that I'm sure it's trivial for some guru,
> but after few hours I just cannot solve it. Here it is:

> I've created function which is basically parametrized view: based on
> (lots of) parameters it returns record set of results. Something like
> this:

> create function pfn(<list of parameters>)
> RETURNS TABLE
> AS
>   RETURN (SELECT   ....)

> Then I have two stored procedures: first one calls function and just
> returns number of hits:

> CREATE  PROCEDURE dbo.sp_RecordCount (<same parameters>)
> AS
> BEGIN
>   SELECT count(<primary key column>) FROM [dbo].[pfn](<parameters>)
> END

> and second one returns actual data:

> CREATE  PROCEDURE dbo.sp_RecordCount (<same parameters>)
> AS
> BEGIN
>   SELECT * FROM [dbo].[pfn](<parameters>)
> END

> I've implemented this approach because I'm working with huge tables
> and I want to prevent users to execute queries with big results set.
> So client application first checks if query based on parameters would
> return too many hits. In that case, it aborts the execution of 'main'
> query.

> So far, so good. Everything was working as expected on testing
> database. When I installed application on production server, it turned
> out that function executes in less than one second, but stored
> procedure takes more than one minute !?!?!?

> I've checked both query execution plans and they are totaly different.
> I use just one parameter which is value of primary key column in the
> main table. When I call function, it uses index seek as expected, but
> when I'm calling procedure, server uses cluster index 'SCAN' !?!?
> Table holds over 20 million of records, so you can imagine how slow
> everything is.

> Any ideas ? Like I said: I'm sure that this must be very easy to
> solve.

> thnx a lot !
> Jure



Mon, 02 Jan 2006 10:47:45 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Using oracle stored procedures/functions to return result sets

2. function/procedure returning result set

3. MSSQLServer->Oracle: Identity and returning a result set using oracle procedure or function

4. returning result sets from stored procedures

5. Returning result sets in oracle stored procedure (OLEDB)

6. return multiple result sets from a stored procedure

7. Can't update result set returned from stored procedure using RDO

8. best way to return a paged result set from a stored procedure

9. Returning a result set from a Stored Procedure

10. returning result sets from stored procedures

11. How not to return multiple result sets from a stored procedure

12. Stored Procedure/Multiple Return Results/Set Statement


 
Powered by phpBB® Forum Software