January 25, 2013 at 7:17 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.
Most of the filtration and formatting of this query applies to one table - Vehicles. I'd split up the query and work on the Vehicles table in isolation from the other tables. Run the results into say #Vehicles, index appropriately and reference it in the original query. What you then have is a greatly simplified model to work with - and there's plenty of work to do if you want to improve performance. Many of the filters in the WHERE clause will prevent SQL Server from using indexes - they can be rewritten.
Your first query will then look something like this:
SELECT
v.VehicleId,
v.StockNumber,
'' as [CarFax],
'' as [Rooftop],
v.[Year],
v.ModelName as [Model],
v.TrimName as [Trim],
v.ExtColor,
x.ColourLike [Color],
v.Transmission,
v.SellingPrice,
v.MSRP,
v.VIN,
v.IntColor,
v.Engine,
v.Certified ,
v.Miles [Mileage],
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,
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],
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],
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 #Vehicles
FROM Vehicles v
CROSS APPLY (
SELECT ColourLike =
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
) x
WHERE v.makeId not in (67,68,74,53,46)
and (@model is null or v.stdModel = @model)
and (@body is null or v.Body = @body)
and (@fuelType is null or v.FuelType = @fuelType)
and (@bodyType is null or v.[StdBodyType] = @bodyType)
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 = @transmission)
and (@vehicleType is null or v.VehicleTypeId = @vehicleType)
and (@certified is null or v.Certified = @certified)
and (( @color is null or (v.ExtColor like '%' + @color + '%')) or
(@color = 'Other'
and (x.ColourLike NOT IN ('White','Blue','Red','Gray','Black','Silver','Gold'))
))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2013 at 7:26 am
Yes, vehicle is main table. So you mean first i should select data from vehicle table based on all filters into a temp table with index.
Then for other filters should make join with #vehicle table and apply filter of other tables.
am i right?
January 25, 2013 at 7:36 am
purushottam2 (1/25/2013)
Yes, vehicle is main table. So you mean first i should select data from vehicle table based on all filters into a temp table with index.Then for other filters should make join with #vehicle table and apply filter of other tables.
am i right?
Yes, that's exactly right. Simplify your model.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2013 at 8:47 am
Sorry, my email was offline so I wasn't getting messages. Sounds like Chris largely has it in hand.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 12, 2013 at 4:37 pm
Hello all, First off thanks for posting this site is a great help.
I have a follow up question as I have a similar issue.
if you select into the temp table as in the example above will that include the indexes that were in the source tables?
the reason I ask is that I was reading another article and it says that if you declare the indexes after the table is created they will not be used, the solution they gave was to include constraints when declaring the table so you can get the indexes you want, and then selecting into it ,like so
CREATE TABLE #temp_employee_v2
(emp_id int not null
,lname varchar (30) not null
,fname varchar (30) not null
,city varchar (20) not null
,state char (2) not null
,PRIMARY KEY (lname, fname, emp_id)
,UNIQUE (state, city, emp_id) )
http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables
any help will be appreciated, I too started down the path of limiting my results before each join using CTEs for performance but I have seen that as the number of records returned in the cte goes up the performance drops. I am hoping to overcome this by switching to temp tables so as to keep up the performance gained by limiting my results before I join on the next table, even with large sets of data. does my logic seem sound?
April 12, 2013 at 4:39 pm
akrounda (4/12/2013)
if you select into the temp table as in the example above will that include the indexes that were in the source tables?
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 12, 2013 at 7:11 pm
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
Be careful now. Many people think that Table Variables are "memory only" and Temp Tables are "disk only". Neither is true. They both start out in memory and they both spill to disk if they get too big. Table Variables can sometimes be faster but they're limited because they'll typically show up in the execution plan as having only a single row and there's no statistics on Table Variables.
And, there's actually a huge difference between a CTE and a Temp Table. The CTE must be recalculated for each and every usage even if used multiple times within the same query. A temp table only needs to be created once meaning the query that created it only needs to be executed once.
--Jeff Moden
April 12, 2013 at 7:18 pm
Bhuvnesh (1/24/2013)
CTE is basically used to handle recursive operation not a performance boost alternative to temp table.
Not quite right. Although a CTE can certainly be used in a recursive fashion, that's not what they're normally used for.
What they are used for is "derived tables" just like the ones you used to put in a FROM clause. The big advantage for most is the "top down" programming style and the ease in which they may be cascaded.
--Jeff Moden
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply