Function vs. Sub-Query

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
BjornI've seen this too. In my case when I looked at the execution plan and the server trace it appears the udf is called for each row returned where the sub query doesn't. I was using a udf to calculate the status of the records. I ended up using a view instead to calculate the status and joined my original query to the view. This is similiar to a sub query and much much faster than the udf.|||Thanks! It makes sense. It seems that the sub query runs first and only once in an sproc, extracting all the data needed for the main query.

Bjorn

function selects

I have some code for a duntion that looks like the following
alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
@.sectorID VARCHAR(4),
@.manufacturerID VARCHAR(4),
@.rangeID VARCHAR(4),
@.bodyID VARCHAR(4),
@.transmissionID VARCHAR(4),
@.fuelID VARCHAR(4),
@.doors VARCHAR(4),
@.derivativeID VARCHAR(10),
@.vehicleTypeId VARCHAR(4),
@.levelName varchar(20))
returns @.levelTable table(surveyID VARCHAR(10),
code varchar(100),
description varchar(100)) AS
begin
if @.levelName = 'Sector'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblMarketSector.sectorID as code,
tblMarketSector.sector_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
ON tblMarketSector.sectorID = tblDerivative.sectorID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
else if @.levelName = 'Manu'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblManufacturer.manufacturerID as code,
tblManufacturer.manufacturer_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
What it does is based upon what the @.levelname variable is, it picks the
corresponding select statement and puts the result into a table to be
returned later to a stored procedure, the problem is I have only shown 2 of
these select statements but there are quite a few, I wanted to know if there
is a better way of writting this, perhaps using something like a case
statement.
Hope this is enough information.
ThanksIt looks like the only thing different between all the select statements is
which table you are getting the 2nd and 3rd column data from.. If that's the
case, (I don;t know how much simpler this will help or not, but)
you can include the joins from all the tables into one query, (They will
have to be Left [Outer] Joins), and then use a case statement to determine
which table to extract the data value from, based on the @.levelname
variable...
Insert @.levelTable(SurveyID, Code, Description)
Select Distinct tblSurveyDerivative.surveyID,
Case @.levelName
When 'Sector' Then tblMarketSector.sectorID
When 'Manu' Then tblManufacturer.sectorID
End,
Case @.levelName
When 'Sector' Then tblMarketSector.sector_desc
When 'Manu' Then tblManufacturer.sector_desc
End
From tblSurveyDerivative
Join basedata.dbo.tblDerivative tblDerivative
On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
Left Join basedata.dbo.tblMarketSector tblMarketSector
On tblMarketSector.sectorID = tblDerivative.sectorID
Left Join basedata.dbo.tblManufacturer tblManufacturer
On tblMarketSector.sectorID = tblDerivative.sectorID
Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
"Phil" wrote:

> I have some code for a duntion that looks like the following
> alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
> @.sectorID VARCHAR(4),
> @.manufacturerID VARCHAR(4),
> @.rangeID VARCHAR(4),
> @.bodyID VARCHAR(4),
> @.transmissionID VARCHAR(4),
> @.fuelID VARCHAR(4),
> @.doors VARCHAR(4),
> @.derivativeID VARCHAR(10),
> @.vehicleTypeId VARCHAR(4),
> @.levelName varchar(20))
> returns @.levelTable table(surveyID VARCHAR(10),
> code varchar(100),
> description varchar(100)) AS
>
> begin
> if @.levelName = 'Sector'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblMarketSector.sectorID as code,
> tblMarketSector.sector_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_
code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
> ON tblMarketSector.sectorID = tblDerivative.sectorID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
> else if @.levelName = 'Manu'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblManufacturer.manufacturerID as code,
> tblManufacturer.manufacturer_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
> ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
>
> What it does is based upon what the @.levelname variable is, it picks the
> corresponding select statement and puts the result into a table to be
> returned later to a stored procedure, the problem is I have only shown 2 o
f
> these select statements but there are quite a few, I wanted to know if the
re
> is a better way of writting this, perhaps using something like a case
> statement.
> Hope this is enough information.
> Thanks|||Hi CBretana,
Thanks very much for that, I had the principal right just couldn't seem to
write it down, thanks again for you help!!
Phil
"CBretana" wrote:
> It looks like the only thing different between all the select statements i
s
> which table you are getting the 2nd and 3rd column data from.. If that's t
he
> case, (I don;t know how much simpler this will help or not, but)
> you can include the joins from all the tables into one query, (They will
> have to be Left [Outer] Joins), and then use a case statement to determine
> which table to extract the data value from, based on the @.levelname
> variable...
> Insert @.levelTable(SurveyID, Code, Description)
> Select Distinct tblSurveyDerivative.surveyID,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sectorID
> When 'Manu' Then tblManufacturer.sectorID
> End,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sector_desc
> When 'Manu' Then tblManufacturer.sector_desc
> End
> From tblSurveyDerivative
> Join basedata.dbo.tblDerivative tblDerivative
> On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> Left Join basedata.dbo.tblMarketSector tblMarketSector
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Left Join basedata.dbo.tblManufacturer tblManufacturer
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
>
> "Phil" wrote:
>