August 9, 2012 at 5:20 am
Hi All
I am working on a search stored procedure for a website,
I have following input parameters:
@Region: A region name
@Country: A country name or collection of countries
@Province: A province name or collection of provinces
@city: A single city name
@Zip: A single zip code
I have table for each say Region, Country, Province and City(it contains both city and corresponding zip)
Now i want to pick a single record of city(there may be multiple identical records but want first one) to get it's latitude and longitude.
The best approach i thought of so far is to use dynamic sql query string.
But if there is another better way to do this then please suggest me.:-)
Thanks
August 9, 2012 at 6:38 am
Why is a regular query not sufficient?
Is is unknown whether any of the given parameters will contain a value?
August 9, 2012 at 10:06 pm
Yes this is not sure whether any of parameter will contain value or not except @city or @zip.
and also I guess IN clause won't work with a variable containing multiple values
like the following code will not work:
select blah from blah where countryId IN (@country)
August 9, 2012 at 10:44 pm
Here is another pattern for "catch-all" queries:
select *
from t1
where (@p1 is null or @p1 =t1.c2)
option (recompile)
Not sure if better or not for your particular case, you'll want to benchmark it both ways.
August 9, 2012 at 11:06 pm
Rather than using a Dynamic SQL query that substitutes in the table you want to SELECT FROM, why not instead consider using IF/ELSE IF/END with static SQL queries?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 11:49 pm
@dwain.c
I can Use IF/Else but there i'll have to use too many iterations,
Because @Country and @province may contain more that one value.
and each table City, Region, Country and Province contains a translation and also a synonym table.
It will make too many iterations.
@SpringTownDBA
In this solution how can i pass multiple values in parameter
declare @p1 varchar(100) = 'Evere,anvers'
select top 10 *
from TB_City ci
where (@p1 is null or @p1 =ci.CityDefaultName)
option (recompile)
August 9, 2012 at 11:54 pm
You can use a table valued parameter.
... And ( t1.c in (select id from @p2) or not exists (select * from @p2) )
August 10, 2012 at 1:24 am
rajawat.niranjan (8/9/2012)
@dwain.cI can Use IF/Else but there i'll have to use too many iterations,
Because @Country and @province may contain more that one value.
and each table City, Region, Country and Province contains a translation and also a synonym table.
It will make too many iterations.
@SpringTownDBA
In this solution how can i pass multiple values in parameter
declare @p1 varchar(100) = 'Evere,anvers'
select top 10 *
from TB_City ci
where (@p1 is null or @p1 =ci.CityDefaultName)
option (recompile)
First of all, I think you should have posted a better subject to the question as it seems more likely the case of dynamic search 😉
I agree that IF/ELSE is not the way to go. It would make the code too long and would make it very hard for the optimizer to come up with a good plan unless the options like procedure recompile etc are used.
For multiple values in parameter, as mentioned by SpringTownDBA, TVP is an option. Another option could be to use Jeff Moden's 8k splitter function[/url]
But if you have opted for the dynamic sql, then it seems fine to me. (Although I do not like dynamic sql) I have seen proper implementation can get you a very optimized solution for dynamic searches. I would strongly recommend reading this great article by Erland Sommarskog on dynamic search.
August 10, 2012 at 2:07 am
Personally, I'd like to see the Dynamic SQL version posted before we go jumping to hasty conclusions or generalizations like "too many iterations" or "would make it very hard for the optimizer to come up with a good plan unless the options like procedure recompile" and compare it against what could be done with IF/ELSE IF.
Edit: Corrected the second quotation.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 10, 2012 at 2:41 am
Yes this is not sure whether any of parameter will contain value or not except @city or @zip.
and also I guess IN clause won't work with a variable containing multiple values
like the following code will not work:
select blah from blah where countryId IN (@country)
You can use the below query if a variable containing multiple values,
select blah from blah where
CHARINDEX(LTRIM(RTRIM(CountryId)), @country) > 0
August 13, 2012 at 12:22 am
Hi All
right now I am using following dynamic sql code
to select a city Lat Long:
(Please suggest if there is better solution)
IF LTRIM(rtrim(@PostCode)) <> ''
BEGIN
select @ParameterList = ''
set @PtSql = 'insert into #LatLong Select CityLatitude, CityLongitude from TB_City ci'
set @PtJoinString = ''
set @PtWhereString = ' where'
IF LTRIM(RTRIM(@City)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' ((CityZipCodes = ''' + @PostCode + ''' and CityDefaultName = ''' + @City + ''' and ci.CityStatusID = 1) or (CityZipCodes = ''' + @PostCode + ''' and CityName = ''' + @City + ''' and cits.CityTranslationStatusID = 1)) '
set @PtJoinString = @PtJoinString + ' left join (select CityInternalID,CityName,CityTranslationStatusID from TB_CityTranslation union select CityInternalID, CityName, CitySynonymStatusID from TB_CitySynonym ) cits on ci.CityInternalId = cits.CityInternalID'
END
ELSE
set @PtWhereString = @PtWhereString + ' ci.CityZipCodes = ''' + @PostCode + ''''
IF LTRIM(RTRIM(@Region)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and re.RegionDefaultName = ''' + @Region + ''' and re.RegionStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select RegionInternalID,RegionDefaultName,RegionStatusID from TB_Region union select RegionInternalID,RegionName,RegionTranslationStatusID from TB_RegionTranslation union select RegionInternalID, RegionName, RegionSynonymStatusID from TB_RegionSynonym) re on ci.CityRegionID = re.RegionInternalID'
END
IF LTRIM(RTRIM(@strCountry)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and co.CountryDefaultName in (' + @strCountry + ') and co.CountryStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select CountryInternalID,CountryDefaultName,CountryStatusID from TB_Country union select CountryInternalID,CountryName,CountryTranslationStatusID from TB_CountryTranslation union select CountryInternalID,CountryName,CountrySynonymStatusID from TB_CountrySynonym) co on ci.CityCountryId = co.CountryInternalID'
END
IF LTRIM(RTRIM(@strProvince)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and pr.ProvinceDefaultName in (' + @strProvince + ') and pr.ProvinceStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID from TB_Province union select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID from TB_ProvinceTranslation union select ProvinceInternalID,ProvinceName,ProvinceSynonymStatusID from TB_ProvinceSynonym) pr on ci.CityProvinceID = pr.ProvinceInternalID'
END
set @PtSql = @PtSql + @PtJoinString + @PtWhereString
Print @PtSql
exec sp_executesql @PtSql, N'@City nvarchar(1000) OUTPUT, @Region nvarchar(1000) OUTPUT, @strCountry nvarchar(1000) OUTPUT, @strProvince nvarchar(1000) OUTPUT', @City , @Region, @strCountry , @strProvince
select @VacSearchLocLatitude = Latitude, @VacSearchLocLongitude = Longitude from #LatLong
END
August 13, 2012 at 7:43 pm
A little basic formatting would go a long way to improving readability:
IF LTRIM(rtrim(@PostCode)) <> ''
BEGIN
select @ParameterList = '' -- Not used anywhere
SELECT @PtSql = 'insert into #LatLong Select CityLatitude, CityLongitude from TB_City ci'
,@PtJoinString = ''
,@PtWhereString = ' where'
IF LTRIM(RTRIM(@City)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' ((CityZipCodes = ''' + @PostCode + ''' and CityDefaultName = ''' + @City + ''' and ci.CityStatusID = 1) or (CityZipCodes = ''' + @PostCode + ''' and CityName = ''' + @City + ''' and cits.CityTranslationStatusID = 1)) '
set @PtJoinString = @PtJoinString + ' left join (select CityInternalID,CityName,CityTranslationStatusID from TB_CityTranslation union select CityInternalID, CityName, CitySynonymStatusID from TB_CitySynonym ) cits on ci.CityInternalId = cits.CityInternalID'
END
ELSE
set @PtWhereString = @PtWhereString + ' ci.CityZipCodes = ''' + @PostCode + ''''
IF LTRIM(RTRIM(@Region)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and re.RegionDefaultName = ''' + @Region + ''' and re.RegionStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select RegionInternalID,RegionDefaultName,RegionStatusID from TB_Region union select RegionInternalID,RegionName,RegionTranslationStatusID from TB_RegionTranslation union select RegionInternalID, RegionName, RegionSynonymStatusID from TB_RegionSynonym) re on ci.CityRegionID = re.RegionInternalID'
END
IF LTRIM(RTRIM(@strCountry)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and co.CountryDefaultName in (' + @strCountry + ') and co.CountryStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select CountryInternalID,CountryDefaultName,CountryStatusID from TB_Country union select CountryInternalID,CountryName,CountryTranslationStatusID from TB_CountryTranslation union select CountryInternalID,CountryName,CountrySynonymStatusID from TB_CountrySynonym) co on ci.CityCountryId = co.CountryInternalID'
END
IF LTRIM(RTRIM(@strProvince)) <> ''
BEGIN
set @PtWhereString = @PtWhereString + ' and pr.ProvinceDefaultName in (' + @strProvince + ') and pr.ProvinceStatusID = 1'
set @PtJoinString = @PtJoinString + ' inner join ( select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID from TB_Province union select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID from TB_ProvinceTranslation union select ProvinceInternalID,ProvinceName,ProvinceSynonymStatusID from TB_ProvinceSynonym) pr on ci.CityProvinceID = pr.ProvinceInternalID'
END
set @PtSql = @PtSql + @PtJoinString + @PtWhereString
Print @PtSql
exec sp_executesql @PtSql, N'@City nvarchar(1000) OUTPUT, @Region nvarchar(1000) OUTPUT, @strCountry nvarchar(1000) OUTPUT, @strProvince nvarchar(1000) OUTPUT' , @City , @Region, @strCountry , @strProvince
select @VacSearchLocLatitude = Latitude, @VacSearchLocLongitude = Longitude from #LatLong
END
A few issues I see with the above:
1. The variable @ParameterList is not used anywhere.
2. Where you have co.CountryDefaultName in (@strCountry) should probably be using =
3. Where you have pr.ProvinceDefaultName in (@strProvince) should probably be using =
4. All of your UNIONs can probably be UNION ALLs
Not necessarily recommending this (and ignoring variable declarations), but this is the "catch all" query approach:
SELECT @PostCode=LTRIM(rtrim(ISNULL(@PostCode, '')))
,@City=LTRIM(RTRIM(ISNULL(@City, '')))
,@Region=LTRIM(RTRIM(ISNULL(@Region, '')))
,@strCountry=LTRIM(RTRIM(ISNULL(@strCountry, '')))
,@strProvince=LTRIM(RTRIM(ISNULL(@strProvince, '')))
Select @VacSearchLocLatitude=CityLatitude, @VacSearchLocLongitude=CityLongitude
from TB_City ci
left join (
select CityInternalID,CityName,CityTranslationStatusID
from TB_CityTranslation
union ALL
select CityInternalID, CityName, CitySynonymStatusID
from TB_CitySynonym ) cits
on ci.CityInternalId = cits.CityInternalID AND ci.CityZipCodes = @PostCode AND
(@City = '' OR (CityDefaultName = @City and ci.CityStatusID = 1))
inner join (
select RegionInternalID,RegionDefaultName,RegionStatusID
from TB_Region
union ALL
select RegionInternalID,RegionName,RegionTranslationStatusID
from TB_RegionTranslation
union ALL
select RegionInternalID, RegionName, RegionSynonymStatusID
from TB_RegionSynonym) re
on ci.CityRegionID = re.RegionInternalID AND
(@Region = '' OR (re.RegionDefaultName = @Region and re.RegionStatusID = 1))
inner join (
select CountryInternalID,CountryDefaultName,CountryStatusID
from TB_Country
union ALL
select CountryInternalID,CountryName,CountryTranslationStatusID
from TB_CountryTranslation
union ALL
select CountryInternalID,CountryName,CountrySynonymStatusID
from TB_CountrySynonym) co
on ci.CityCountryId = co.CountryInternalID AND
(@strCountry = '' OR (co.CountryDefaultName = @strCountry and co.CountryStatusID = 1))
inner join (
select ProvinceInternalID,ProvinceDefaultName,ProvinceStatusID
from TB_Province
union ALL
select ProvinceInternalID,ProvinceName,ProvinceTranslationStatusID
from TB_ProvinceTranslation
union ALL
select ProvinceInternalID,ProvinceName,ProvinceSynonymStatusID
from TB_ProvinceSynonym) pr
on ci.CityProvinceID = pr.ProvinceInternalID AND
(@strProvince = '' OR (pr.ProvinceDefaultName = @strProvince and pr.ProvinceStatusID = 1))
Personally I think it's quite a bit more readable but I would sacrifice that for performance. I suggest you run a test to see which performs better.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 15, 2012 at 2:01 am
Hi dwain.c
I am using IN clause because i have multiple values for countries and provinces.
and IN does not work with a variable without using dynamic query.
like select * from tb_country where countryName IN (@country) does not work.
please suggest solution for this other than dynamic sql.
Thank you.
August 15, 2012 at 2:29 am
SET @strCountry = 'Sweden,Portugal'
(@strCountry = '' OR (co.CountryDefaultName IN (SELECT Item FROM dbo.DelimitedSplit8K(@strCountry, ',')) and co.CountryStatusID = 1))
DelimitedSplit8K can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 15, 2012 at 11:13 pm
Thanks dwain.c
I have following queries regarding this:
1. Here I don't have all parameters compulsory except city, so if i use this single query every time I will be join all the tables whether there is some value in parameter or not. If I use If-else then I guess there will be many iterations.
2. Does this query give better performance compared to previous dynamic query?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply