change of function

Hi All,
I am wondering if someone can help me, I will try and provide as much
information as possible, I have a function, most of the code listed below
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
I have shown only 2 combinations of what the @.levelname could be there are
quite a few more, rather than using (ELSE IF) I have tried to re-write it
using case statements in one large select, like below :-
begin
Insert @.levelTable (SurveyID, Code, Description)
Select Distinct tblSurveyDerivative.surveyID, --tblMarketSector.sectorID
as code, tblMarketSector.sector_desc as description,
Case @.levelname
When 'Sector' Then tblMarketSector.sectorID
When 'Manu' Then tblManufacturer.manufacturerID
else 'error'
End,
Case @.levelname
When 'Sector' Then tblMarketSector.sector_desc
When 'Manu' Then tblManufacturer.manufacturer_desc
else 'error'
End
--select *
From tblSurveyDerivative
Join basedata.dbo.tblDerivative tblDerivative
On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = 0--@.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
Although both work and return the same number of results the second one is
a lot slower, is there a better way to re-write the second or even the
first for better performance.
Thanks in advance,
Phil
Message posted via http://www.webservertalk.comPhilip via webservertalk.com wrote:
> Hi All,
> I am wondering if someone can help me, I will try and provide as much
> information as possible, I have a function, most of the code listed
> below
>
<SNIP>
What do your tables look like? Your indexes? Why is the second query
taking longer? have you examined the execution plans to see what the
differences are? If not check out the differences and post the results.
Also, what does this mean for your data:
@.vehicleTypeId = 'null'
Are you actually storing a string in the columns with the text "null" or
are you looking for NULL values? If you want to check for NULL, that
code won't work and if you are storing "null" as text in the column,
that makes me scared... unless you have some business reason for doing
so.
To check for a null value, you must use:
@.vehicleTypeId IS NULL
You cannot use what you wrote or:
@.vehicleTypeId = NULL
Nothing is equal to NULL. Even NULL.
David Gugick
Imceda Software
www.imceda.com