I'm trying to test and tune two functions that retrieve records from a
property database (housing characteristics & transactions).

One returns information about a subject property, the other about properties
near the subject. They both return tables, with identical structures. The
first will (usually) return a small number of records (one or a few), and
the second returns a number specified by one of the parameters, usually a
few hundred. The records returned are assembled from various places in the
database, and I won't trouble you with details of all the joins and
conditions.

Free-standing, the Subject function takes a small fraction of a second, the
Nearby function takes a few seconds (2-10). In order to reduce that time,
I run it in a test harness SP to get information on what sorts of cases take
long and short times. When running in the test harness, the functions take
about 10 times as long as when calling them on their own from Query Analyzer.
If the calls come from a C++ program making ODBC calls one at a time, the
calls are quick. Help -- it's more flexible to use an SP, and I would
rather not write the test harness in C++ (although it wouldn't be
terribly difficult), because it already exists in SQL. Another factor is
that occasionally the functions run fast in the test harness -- but rarely.

Here's the test harness:

(There's a table of a sample of addresses, with a 0-1 random number also on
each record, indexed by the random number.)

Create #Temp tables:
-- for Subject records
-- for Nearby records
-- for summary statistics

Create cursor to go through the sample addresses, starting at a given random
number, in order by the random number.

While more records and target test size not reached yet
Fetch a new address from the cursor
@T1 = GetDate()
Insert Subject table SELECT * FROM Subject(<address information>)
@T2 = GetDate()
If subject was found
Insert Nearby table SELECT * FROM Nearby(<info about the subject>)
Endif
@T3 = GetDate()
Compute some statistics about the subject and nearby tables (how many
records retrieved, etc,, and how long it took (@T2-@T1, @T3-@T2)
Insert the statistics into summary statistics table
Truncate subject and nearby tables
End loop
Compute and output overall summary statistics

jim_geissman (AT) countrywide (DOT) com (Jim Geissman) wrote in message news:<b84bf9dc.0307291419.6e93ea97 (AT) posting (DOT) google.com>...
<snip>

It's not entirely clear from your explanation, but it may be that
you're seeing something called parameter sniffing. This often results
in very different execution times when a query is run ad hoc, and when
it is run from a stored proc:

http://groups.google.com/groups?selm...utpu t=gplain

Simon

Jim Geissman (jim_geissman (AT) countrywide (DOT) com) writes:
There is not an overload of information here.

Simon Hayes mentioned the possibility that parameter sniffing sets in.
I'll add to this a question: are the function multi-step or inline?
If they are multi-step, I don't really see that parameter sniffing has
anyhing to do with it. But if they are inline function, this could
well be the explanation. An inline function is not function in the
true sense, but a macro.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp