What's the best way to optimzie this query for less overhead and better performance?

[fletchsod]

Hi!

I wrote this sql-query script the best I could and I couldn't figure out a way to minimize the script. The script is way overkill. I'm using too many query for locations and I only need the 1st record result. I'm not proud of it.

I wonder is there a way to fetch the 1st record result and stop there instead of having to go through next query. I wonder is there a way to get the result using a few lines of code.

Thanks...

; WITH MyCte(SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, City, State, ZipCode) AS 
 ( 
     SELECT 
         SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, State, City, ZipCode 
     FROM tblSold 
     WHERE ((SoldDate >= CONVERT(DATETIME, @parmVehicleSoldDateBegin + ' 00:00:00.000')) AND (SoldDate <= CONVERT(DATETIME, @parmVehicleSoldDateEnd + ' 23:59:59.999'))) 
     AND Year = @parmVehicleYear 
     AND MakeID = @parmVehicleMake 
     AND ModelID = @parmVehicleModel 
     AND Style = @parmVehicleTrim 
 ) 
 
 SELECT TOP 1 
     CAST((CASE WHEN SalePrice1 IS NULL THEN 0 ELSE SalePrice1 END AS DECIMAL(10,2)) AS SalePrice 
     CAST((CASE WHEN RepairCost1 IS NULL THEN 0 ELSE RepairCost1 END) AS DECIMAL(10,2)) AS AvgRepair, 
     CAST((CASE WHEN Mileage1 IS NULL THEN 0 ELSE Mileage1 END) AS INT) AS AvgMileage, 
     CAST((CASE WHEN NumOfDays1 IS NULL THEN 0 ELSE NumOfDays1 END) AS INT) AS NumDays, 
     SortOrder 
 FROM 
 ( 
     --ZipCode Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         1 AS SortOrder 
     FROM MyCte 
     WHERE ZipCode = @parmVehicleZipcode 
     GROUP BY ZipCode 
         UNION 
     --ZipCode Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         2 AS SortOrder 
     FROM MyCte 
     WHERE ZipCode = @parmDealerZipcode 
     GROUP BY ZipCode 
         UNION 
     --City Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         3 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmVehicleState 
     AND City = @parmVehicleCity 
     GROUP BY City, State 
         UNION 
     --City Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         4 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmDealerState 
     AND City = @parmDealerCity 
     GROUP BY City, State 
         UNION 
     --State Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         5 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmVehicleState 
     GROUP BY State 
         UNION 
     --State Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         6 AS SortOrder 
     FROM MyCte 
     WHERE State = @parmDealerState 
     GROUP BY State 
         UNION 
     --Region Location-Search...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         7 AS SortOrder 
     FROM MyCte 
     WHERE Region = @parmVehicleRegion 
     GROUP BY Region 
         UNION 
     --Region Dealer's Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
     8 AS SortOrder 
     FROM MyCte 
     WHERE Region = @parmDealerRegion 
     GROUP BY Region 
         UNION 
     --National Location-Search/Dealer's-Address...
     SELECT 
         AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1, 
         AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1, 
         AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1, 
         AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1, 
         AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1, 
         9 AS SortOrder 
     FROM MyCte 
 ) AS tmpTable1 
 ORDER BY SortOrder

 

[gmmastros]

There are a couple of things you can do to reduce the size of the code and the execution time.

The first thing that jumped out at me was the 9 similar queries. You are combining the queries with a union statement. Instead, I would suggest you use "Union All". With a Union statement, the results of the individual queries will be combined in to a single result set, but it also filters out the duplicates. With a Union All query, there is no additional step of filtering the duplicates. Since you are only returning the top 1 in an outer query, it won't matter if there are duplicates. Union All is faster because it does not filter duplicates. This will not reduce the size of the code, but it will improve the execution time.

Then... I took a closer look at the queries. It appears as though this query is supposed to find vehicles. I imagine this is for a car dealership kind of query. For example, if I want to buy a 2011 Chrysler Sebring Convertible, this query will tell me where I can find one, right?

If this is the case, then I have a bit of a "problem" with the approach. It looks like you are first searching for a match with the same zip code, then the same city, then the same state, and finally.... anywhere. The problem I have with this query is that it will not necessarily produce the results you want. For example, if a match does not exist in the same zip code or city, you then look at state. The problem is, there could be multiple matches in the state, one match could be in the next city over, but this query is just as likely to return a vehicle on the other side of the state. Not to mention, you could be on the border between 2 states, so the closest match could be just over the border in another state (10 miles away), but this query would ignore that vehicle and send you potentially a lot further away.

Instead, I think it would be better to find the vehicle that is closest, regardless of the other criteria. My suggestion is this....

Find yourself a table that has Latitude/Longitude values for every zip code. Then you can do a proximity search based on those Latitude/Longitude values. This is basically what many sites do for their store locators. For example, go to walmart.com and click on "Store Finder". You type in a zip code and it will show you where the nearby stores are.

I really think this would give you better results, and it should only take you an hour or so to implement because I wrote a blog a couple years ago showing how this can be done. The blog shows where you can get the Latitude/Longitude data for free and also shows you the queries you can use to return your data.

SQL Server Zipcode Latitude/Longitude proximity distance search.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fletchsod]

Hmm.. I'll take a look at UNION ALL. The purpose of the query is to collect the cost or values (several category of them) on 1 vehicle. If not exists then fall back to wider location to get them. So, zipcode then city then state then national.

The sample question I posted is just for zipcode. I have another copy of this query for city (taking out the zipcode part). Then another copy of this query for state (taking out city and zip). Then display the values in zipcode, city, state and national. So there's lot of overhead when making too many query calls. So, Not a good logic here.

Maybe I'll find something that works after some trials and errors.

Thanks...

 

[gmmastros]

First, let me apologize for assuming I knew the final goal, when I clearly didn't.

I've been doing some more thinking about this... You say that you want less overhead and better performance. If you run the CTE by itself, with typical parameters, how long does it take, and how many rows are returned?

     SELECT 
         SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, State, City, ZipCode 
     FROM tblSold 
     WHERE ((SoldDate >= CONVERT(DATETIME, @parmVehicleSoldDateBegin + ' 00:00:00.000')) AND (SoldDate <= CONVERT(DATETIME, @parmVehicleSoldDateEnd + ' 23:59:59.999'))) 
     AND Year = @parmVehicleYear 
     AND MakeID = @parmVehicleMake 
     AND ModelID = @parmVehicleModel 
     AND Style = @parmVehicleTrim

The first thing I would do in an attempt to improve the code is to make sure that the first part is not the problem. Specifically, you'll want to make sure that there is a suitable index on tblSold so that you can get nice index seeks instead of scans in the execution plan. For example, if tblSold has 5 million rows, but a typical execution of this query only returns a couple hundred rows, AND SQL Server is using an index scan, this could very well account for your performance problems.

I don't think you should be embarrassed by the code. It's complex code to solve a complex problem.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fletchsod]

It's no problem. I knew I didn't go into detail on the 1st posting when I read your replay.

>>I don't think you should be embarrassed by the code. It's complex code to solve a complex problem.
Not a all. Yes, it's a complex code to solve a complex problem. The reason I posted it is I knew it take more than a few heads to solve this complex problem.

>> If you run the CTE by itself, with typical parameters, how long does it take, and
>>how many rows are returned?
It took less than a second. Got 51 rows total.

 

[fletchsod]

P.S. - In the 51 rows record, I already have the location values (ZipCode, City, State, etc.) so I figured there's got to be an easier way to categorize (or group by) with the provided location value like "State = 'Florida'" for example.

 

[fredericofonseca]

Possible solution to all those unions..

[highlight #CC0000]!!!!!!!!!!!!!!!!!!!COMPLETELY UNTESTED!!!!!!!!!!!!!!!!!!!!![/highlight]

Performance may even be worst than what you have now

And obviously all that George mentioned still applies

; WITH MyCte(SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, City, State, ZipCode, sortorder) AS 
 ( 
     SELECT 
         SalePrice, RepairCost, PurchPrice, Mileage, NumOfDays, Region, State, City, ZipCode 
         ,dense_rank() over (partition by case
                                          when ZipCode = @parmVehicleZipcode then 1
                                          when ZipCode = @parmDealerZipcode  then 2
                                          when  State = @parmVehicleState 
                                            AND City = @parmVehicleCity  then 3
                                          when State = @parmDealerState 
                                           AND City = @parmDealerCity then 4
                                          when State = @parmVehicleState  then 5
                                          when State = @parmDealerState then 6
                                          when Region = @parmVehicleRegion then 7
                                          when Region = @parmDealerRegion then 8
                                          else 9
                                          end
                                 order by case
                                          when ZipCode = @parmVehicleZipcode then 1
                                          when ZipCode = @parmDealerZipcode  then 2
                                          when  State = @parmVehicleState 
                                            AND City = @parmVehicleCity  then 3
                                          when State = @parmDealerState 
                                           AND City = @parmDealerCity then 4
                                          when State = @parmVehicleState  then 5
                                          when State = @parmDealerState then 6
                                          when Region = @parmVehicleRegion then 7
                                          when Region = @parmDealerRegion then 8
                                          else 9
                                          end
                            ) as sortorder

     FROM tblSold 
     WHERE ((SoldDate >= CONVERT(DATETIME, @parmVehicleSoldDateBegin + ' 00:00:00.000')) AND (SoldDate <= CONVERT(DATETIME, @parmVehicleSoldDateEnd + ' 23:59:59.999'))) 
     AND Year = @parmVehicleYear 
     AND MakeID = @parmVehicleMake 
     AND ModelID = @parmVehicleModel 
     AND Style = @parmVehicleTrim 
 ) 
SELECT TOP 1 
      CAST(CASE WHEN SalePrice1 IS NULL THEN 0 ELSE SalePrice1 END AS DECIMAL(10,2)) AS SalePrice 
     ,CAST(CASE WHEN RepairCost1 IS NULL THEN 0 ELSE RepairCost1 END AS DECIMAL(10,2)) AS AvgRepair
     ,CAST(CASE WHEN Mileage1 IS NULL THEN 0 ELSE Mileage1 END AS INT) AS AvgMileage
     ,CAST(CASE WHEN NumOfDays1 IS NULL THEN 0 ELSE NumOfDays1 END AS INT) AS NumDays
     ,SortOrder 
from (     
select sortorder
      ,AVG(CASE WHEN SalePrice > 0 THEN SalePrice ELSE NULL END) AS SalePrice1
      ,AVG(CASE WHEN RepairCost > 0 THEN RepairCost ELSE NULL END) AS RepairCost1
      ,AVG(CASE WHEN PurchPrice > 0 THEN PurchPrice ELSE NULL END) AS PurchPrice1
      ,AVG(CASE WHEN Mileage > 0 THEN Mileage ELSE NULL END) AS Mileage1
      ,AVG(CASE WHEN NumOfDays >= 0 THEN (CASE WHEN NumOfDays = 0 THEN 1 ELSE NumOfDays END) ELSE NULL END) AS NumOfDays1
from mycte
where sortorder = 1
group by sortorder
) t

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886