January 24, 2013 at 10:50 pm
In my procedure i need to store temporally around 400 rows, 50 columns data. At current i have stored in a temp table. I used this temp table to select different columns 6 times in the procedure. And i am suffering performance issue with this procedure.
So i want convert it from temp table to CTE, will it improve my performance ?
Or if you have any other suggestion, please let me know.
January 24, 2013 at 11:07 pm
purushottam2 (1/24/2013)
So i want convert it from temp table to CTE, will it improve my performance ?
there is NO guarantee that performance will improve, CTE is basically used to handle recursive operation not a performance boost alternative to temp table.
Please post table defintion , index defintion plus your query. Also attached the exec plan.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2013 at 11:57 pm
There will be no diff instead when ever u want to use this CTE to read data from it, u have to define its sturcture again and again....
Try to use Table variale
January 25, 2013 at 12:16 am
padhu.mukku (1/24/2013)
Try to use Table variale
Depends.... if you are using heavy volumne data and you would be using same resultset in later part of SP too (in JOINs ) then better use temp table as you can out indexes based on JOIN's query requirement.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2013 at 12:53 am
Why dont you post some code to see if anyone can help in optimising it ?
January 25, 2013 at 12:54 am
Bhuvnesh (1/24/2013)
..., CTE is basically used to handle recursive operation not a performance boost alternative to temp table.
CTEs are not always used for recursive operations. You can also just use them to make your code more clearer, instead of writing nested SQL statements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2013 at 1:42 am
Below is SP, it may be difficult to analyse due to text arrangement.
REATE procedure [dbo].[usp_SearchVehicles]
-- Add the parameters for the stored procedure here
@siteId int,
@pageIndex int,
@pageSize int,
@total int output,
@make varchar(50) = null,
@model varchar(50) = null,
@body varchar(100) = null,
@color varchar(100) = null,
@transmission varchar(100) = null,
@newCar bit = null,
@usedCar bit = null,
@certifiedPreOwnCar bit = null,
@fromYear int = null,
@toYear int = null,
@minPrice decimal = null,
@maxPrice decimal = null,
@minMiles int = null,
@maxMiles int = null,
@minMilesPerGallon int = null,
@maxMilesPerGallon int = null,
@customerId int = null,
@zipcode varchar(10) = null,
@maxDistance int = null,
@bodyType varchar(50) = null,
@fuelType varchar(50) = null,
@sortBy int = null,
@randomSeed int = 1
as
begin
set nocount on;
declare @vehicleType as int
if (@newCar = 1 and (@usedCar = 0 or @usedCar is null))
begin
set @vehicleType = 1
end
else if ((@newCar = 0 or @newCar is null) and @usedCar = 1)
begin
set @vehicleType = 2
end
else
begin
set @vehicleType = null
end
if (@maxDistance is null) set @maxDistance = 30
DECLARE @userLocation geography;
SET @userLocation = (SELECT Location FROM zipdata with(nolock) WHERE ZipCode=@zipcode);
declare @certified varchar(2) = null;
if (@certifiedPreOwnCar = 1 ) set @certified = '1'
if (@sortBy is null) set @sortBy = 1;
select v.VehicleId,
v.StockNumber,
'' as [CarFax],
'' as [Rooftop],
v.[Year],
m.MakeName as [Make],
v.ModelName as [Model],
v.TrimName as [Trim],
vt.NAME as [Type],
v.ExtColor,
case
when v.ExtColor like '%White%' then 'White'
when v.ExtColor like '%Black%' then 'Black'
when v.ExtColor like '%Blue%' then 'Blue'
when v.ExtColor like '%Gray%' then 'Gray'
when v.ExtColor like '%Silver%' then 'Silver'
when v.ExtColor like '%Red%' then 'Red'
when v.ExtColor like '%Gold%' then 'Gold'
else 'Other'
end [Color],
v.Transmission,
v.SellingPrice,
v.MSRP,
v.VIN,
v.IntColor,
v.Engine,
v.Certified ,
v.Miles [Mileage],
c.City [LocationCity],
c.[State] [LocationState],
l.LocationCode + '/' + convert (varchar, v.VehicleId) + '/' + vp.[FileName] as DefaultPhotoPath,
v.CityMPG,
case
when cast (v.SellingPrice as money) < 10000 then 'Less than $10,000'
when cast (v.SellingPrice as money) between 10000 and 19999 then '$10,000 - $20,000'
when cast (v.SellingPrice as money) between 20000 and 29999 then '$20,000 - $30,000'
when cast (v.SellingPrice as money) between 30000 and 39999 then '$30,000 - $40,000'
when cast (v.SellingPrice as money) between 40000 and 49999 then '$40,000 - $50,000'
when cast (v.SellingPrice as money) between 50000 and 59999 then '$50,000 - $60,000'
else 'More than $60,000'
end [PriceRange],
case
when cast (isnull(v.CityMPG,0) as int) < 10 then 'Less than 10'
when cast (isnull(v.CityMPG,0) as int) between 10 and 19 then '10 - 20 MPG'
when cast (isnull(v.CityMPG,0) as int) between 20 and 29 then '20 - 30 MPG'
when cast (isnull(v.CityMPG,0) as int) between 30 and 39 then '30 - 40 MPG'
else 'More than 40'
end [MPGRange],
v.Body,
c.CustomerId,
c.Logo,
cf.Value [PremimumDealer],
case
when DATEDIFF(hh,v.[PriceReduced],GETUTCDATE()) < 24 then cast( 1 as bit)
else cast( 0 as bit)
end [PriceReduced],
case
when DATEDIFF(hh,v.[CreatedDate],GETDATE()) < 24 then cast( 1 as bit)
else cast( 0 as bit)
end [NewlyListed],
c.ZipCode[LocationZipCode],
Ad.AdvertisingMakeModelId,
md.ModelName[ModelOwn],
case
when cast (isnull(v.Miles,0) as int) < 10000 then 'Less than 10,000'
when cast (isnull(v.Miles,0) as int) between 10000 and 19999 then '10,000 - 20,000'
when cast (isnull(v.Miles,0) as int) between 20000 and 29999 then '20,000 - 30,000'
when cast (isnull(v.Miles,0) as int) between 30000 and 39999 then '30,000 - 40,000'
else 'More than 40,000'
end [MilesRange],
l.LocationId,
v.CreatedDate,
v.StdTransmissionType,
v.StdBodyType,
v.FuelType,
v.stdModel,
ROW_NUMBER() over ( order by
case when @sortBy = 1
then cast (v.SellingPrice as money)
end desc,
case when @sortBy = 2
then cast (v.SellingPrice as money)
end asc,
case when @sortBy = 3
then case
when cf.Value = 'True' then 'Z' + cast(checksum(v.vin)* rand(v.vehicleId*@randomSeed) as varchar(50))--100.0 + RAND(c.CustomerId * @randomSeed )
else 'A' + cast(checksum(v.vin)* rand(v.vehicleId*@randomSeed) as varchar(50))
end
end desc
) as RowNum
into #TempData
from Vehicles v with(nolock)
inner join SiteLocations sl with(nolock) on sl.LocationId = v.LocationId and sl.SiteId = @siteId
inner join Locations l with(nolock) on l.LocationId = v.LocationId
inner join Customers c with(nolock) on c.CustomerId = l.CustomerId
inner join ZipData z with(nolock) on z.ZipCode = c.ZipCode
left outer join CustomerPreferences cf with(nolock) on cf.CustomerId = c.CustomerId and cf.[Name] = 'PremiumDealer'
left outer join VehiclePhotos vp with(nolock) on vp.vehicleId = v.vehicleId and vp.VehiclePhotoId =
( select top 1 p.VehiclePhotoId from VehiclePhotos p with(nolock) where coalesce(p.ImageSequenceId,0) = 0 and p.vehicleId = v.vehicleId
)
--left outer join VehiclePhotos vp on vp.VehicleId = v.VehicleId and coalesce(vp.ImageSequenceId,0) = 0
inner join Makes m with(nolock) on m.MakeId = v.MakeId
inner join VehicleTypes vt with(nolock)on vt.VehicleTypeId = v.VehicleTypeId
left outer join AdvertisingMakeModels Ad with(nolock) on Ad.MakeId = m.MakeId and Ad.SiteId = sl.SiteId and Ad.CustomerId = c.customerId --sl.LocationId
left outer join Models md (nolock) on md.ModelId = Ad.ModelId and md.MakeId = Ad.MakeId and Ad.SiteId = sl.SiteId and Ad.CustomerId = c.customerId
where sl.SiteId = @siteId and coalesce(vp.ImageSequenceId,0) = 0
and v.makeId not in (67,68,74,53,46)
--and m.MakeName = coalesce(@make,m.MakeName)
and (@make is null or m.MakeName = coalesce(@make,m.MakeName) )
--and v.ModelName = coalesce(@model,v.ModelName)
and (@model is null or v.stdModel = coalesce(@model,v.stdModel) )
and (@body is null or v.Body = coalesce(@body,v.Body) )
and (@fuelType is null or v.FuelType = coalesce(@fuelType,v.FuelType) )
and (@bodyType is null or v.[StdBodyType] = coalesce(@bodyType,v.[StdBodyType]) )
and (@customerId is null or c.CustomerId = coalesce(@customerId,c.CustomerId))
--and (@userLocation is null or z.Location.STDistance(coalesce(@userLocation,z.Location))/1609.344 <= @maxDistance)
and (cast (v.[Year] as int) between coalesce(@fromYear,cast (v.[Year] as int)) and coalesce(@toYear,cast (v.[Year] as int)))
and (cast (v.SellingPrice as money) between coalesce(@minPrice,cast (v.SellingPrice as money)) and coalesce(@maxPrice-1,cast (v.SellingPrice as money)))
and (cast (isnull(v.[Miles],0) as int) between coalesce(@minMiles,cast (isnull(v.[Miles],0) as int)) and coalesce(@maxMiles,cast (isnull(v.[Miles],0) as int)))
and (cast (isnull(v.[CityMPG],0) as int) between coalesce(@minMilesPerGallon,cast (isnull(v.[CityMPG],0) as int)) and coalesce(@maxMilesPerGallon - 1,cast (isnull(v.[CityMPG],0) as int)))
and (@transmission is null or v.StdTransmissionType = coalesce(@transmission,v.StdTransmissionType))
and (@vehicleType is null or v.VehicleTypeId = coalesce(@vehicleType,v.VehicleTypeId))
and (@certified is null or v.Certified = coalesce(@certified,v.Certified))
and (( @color is null or (v.ExtColor like '%' + coalesce(@color,v.ExtColor) + '%')) or
(@color = 'Other' and ( v.ExtColor not like '%White%'
and v.ExtColor not like '%Blue%'
and v.ExtColor not like '%Red%'
and v.ExtColor not like '%Gray%'
and v.ExtColor not like '%Black%'
and v.ExtColor not like '%Silver%'
and v.ExtColor not like '%Gold%')))
set @total = (select MAX(RowNum)
from #TempData);
if (@make is not null)
begin
-- Get top 3 vehicles for make ownership
select top 3 *
into #TopVehicles
from #TempData
where AdvertisingMakeModelId is not null
declare @topVehicleCount int
select @topVehicleCount = count(*) from #TopVehicles
end
if (@pageIndex = 1)
begin
if (@make is null)
begin
select *
from #TempData
where (RowNum between (@pageIndex) and @pageIndex + @pageSize - 1)
order by #TempData.RowNum asc;
end
else -- try to prepare vehicle cube for vehicle ownership
begin
-- Prepare the results keep the above top 3 vehicles in the top of the list
select * from
(
select *, ROW_NUMBER() over ( order by RowNum) [NewRowNum]
from #TopVehicles
union
select *,ROW_NUMBER() over ( order by RowNum) + @topVehicleCount [NewRowNum]
from #TempData
where VehicleId not in (select vehicleId from #TopVehicles)
) as [t1]
where ([t1].NewRowNum between (@pageIndex) and @pageIndex + @pageSize - 1)
order by [t1].NewRowNum asc;
end
/*Get make list*/
select [Make] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[Make][Value] from #TempData
group by [Make]
order by [Make]
/*Get valid list of years*/
select [Year] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[Year][Value] from #TempData
group by [Year]
order by [Year] Desc
/*Get valid list of transmission types*/
select [StdTransmissionType] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[StdTransmissionType] [Value] from #TempData
group by [StdTransmissionType]
order by [StdTransmissionType]
/*Get valid list of colors*/
select [Color] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[Color][Value] from #TempData
group by [Color]
order by [Color]
/*Get valid list of PriceRanges*/
select [PriceRange] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[PriceRange][Value] from #TempData
group by [PriceRange]
order by cast (MIN(SellingPrice) as money)
/*Get valid list of MPGRanges*/
select [MPGRange] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[MPGRange][Value] from #TempData
group by [MPGRange]
order by cast (isnull(MIN(CityMPG),0) as int)
/*Get Body list*/
select [StdBodyType] + ' (' + cast(count(*) as varchar(15)) + ')'[Text],[StdBodyType][Value] from #TempData
where [StdBodyType] is not null and [StdBodyType] <> ''
group by [StdBodyType]
January 25, 2013 at 5:17 am
padhu.mukku (1/24/2013)
There will be no diff instead when ever u want to use this CTE to read data from it, u have to define its sturcture again and again....Try to use Table variale
Oooh, I'd be careful about that. From the sounds of things the OP is searching and filtering against the temp table which means he's taking advantage of statistics. Table variables have none, so it could be a major performance hit, not an enhancement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2013 at 5:23 am
There are probably lots of other issues in and around this, but the repeated pattern that looks like this:
where coalesce(p.ImageSequenceId,0)
is absolutely killing performance. Every single one of those is going to lead to table scans.
Also, have to say it, you do know that NOLOCK will lead to dirty reads, which means you can see extra rows or missing rows in your data set? I strongly recommend against using it as a magic run-faster switch. It can lead to bad data which most of the businesses I've worked with frown upon.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2013 at 5:56 am
Hi Grant,
Thanks for your reply,
Surely i will remove coalesce. Should i remove nolock, does it also affect my performance. Performance is our first priority.
January 25, 2013 at 6:19 am
purushottam2 (1/25/2013)
Hi Grant,Thanks for your reply,
Surely i will remove coalesce. Should i remove nolock, does it also affect my performance. Performance is our first priority.
NOLOCK will probably enhance performance, because it ignores any locks. However, as Grant mentioned, this can lead to dirty reads, which can compromise the integrity of your data. I think integrity and consistency is more important than the possible performance improvement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2013 at 6:27 am
purushottam2 (1/25/2013)
Hi Grant,Thanks for your reply,
Surely i will remove coalesce. Should i remove nolock, does it also affect my performance. Performance is our first priority.
Personally I would have thought Acuracy was the First priority with Performance second, Slow Accurate data is Better than Fast Crap data.
Having looked at the query I would suggest breaking it down into smaller atomic pieces creating a series of temp tables that you then join back to the vehicle on vehicle Id, I would start with the filtering on the colours and work backwards.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 25, 2013 at 6:32 am
you mean instead of storing in single temp table, should store in multiple temp table?
January 25, 2013 at 6:38 am
Posted in error
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 25, 2013 at 6:42 am
in a nutsehell yes, or at least look trying to split up the query so that its more manageable, I often find that complex queries will perform better if they are split up.
Its counter intuitive at times but often works.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply