I have a function that returns a table of information about
residential properties. The main input is a property type and
a location in grid coordinates. Because I want to get only a
certain number of properties, ordered by distance from the
location, I get the properties from a cursor ordered by distance,
and stop when the number is reached. (Not really possible to
determine the distance analytically in advance.) The cursor also
involves joins to a table of grid coordinates vs. postcodes (the
properties are identified mainly by postcode), and to a table
that maps the input property type into what types to search for.
Opening the cursor typically results in the creation of six to
eight parallel threads, and takes approx 1 second, which is about
half of the total time for the function.
Recently the main property table grew from 4 million to 6.5
million records, and suddenly the parallelism is lost. Taking
the identical code and executing it as a script gives parallelism.
Turning it into a SP that inserts into a #temp table and then
selects * from that table as the last statement also gives
parallelism. But when it's in the form of a function, there is
only one thread -- and the execution time has gone from ~2 sec
to ~8 sec. I updated the statistics on the table, but still
no parallelism.
I could turn it into a SP easily enough, but that would involve
a change to the C++ program that calls it, which takes a while
to get through the pipeline. In the meantime, is there some way
to induce the optimizer to use parallelism? It used to.
