Why is a cross-application applied, making the request slow?

Question

I have a request that takes about 11 seconds to run on a single date. I want to run the same query in a few days. In other words, I want to be able to return snapshots in a few days. Here is my original request:

SELECT COUNT(*) AS 'Number of Cars', d.ManufacturerName AS 'Make', d.Name AS 'Model', c.name AS 'Car Class' FROM CarRating a INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID INNER JOIN CarClass c ON a.CarClassID = c.CarClassID INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID WHERE @myDate < a.ExpiredWhen AND @myDate >= a.EffectiveWhen GROUP BY d.Name, c.name,d.ManufacturerName 

As I said, the request takes 11 seconds. To execute this query for multiple dates, I use a date table and cross-apply it to the above query:

 SELECT [DATE], b.* FROM DimDate CROSS APPLY (SELECT COUNT(*) AS 'Number of Cars', d.ManufacturerName AS 'Make', d.Name AS 'Model', c.name AS 'Car Class' FROM CarRating a INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID INNER JOIN CarClass c ON a.CarClassID = c.CarClassID INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID WHERE dimDate.Date < a.ExpiredWhen AND dimDate.Date >= a.EffectiveWhen GROUP BY d.Name, c.name,d.ManufacturerName) b WHERE DimDate.Date between @StartDate and @EndDate 

This request takes 49 seconds, even for one day. Why is it slow? Is there a better way to do this?

Answer 1

Your query is slower because it connects to the dimension table, significantly increasing the amount of data being processed. You can probably fix this query by making sure you have the appropriate indexes:

• OwnedCar (OwnedCarId)
• CarClass (CarClassId)
• BaseCare (BaseCareID)
• CarRating (EffectiveWhen, ExpiredWhen)

If this does not help, you will need to reconsider the request. There is an alternative way to write, but indexes can solve the problem more simply.

Answer 2

I myself am not familiar with CROSS APPLY , but I can’t just add ExpiredWhen and the band group, something like this:

 SELECT a.ExpiredWhen AS dimDate , COUNT(*) AS 'Number of Cars' , d.ManufacturerName AS 'Make' , d.Name AS 'Model' , c.name AS 'Car Class' FROM CarRating a INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID INNER JOIN CarClass c ON a.CarClassID = c.CarClassID INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID WHERE a.ExpiredWhen between @StartDate AND @EndDate GROUP BY a.ExpiredWhen, d.Name, c.name, d.ManufacturerName 

Answer 3

Can you try changing your query as shown below and then share the results (if you notice any improvements)

 SELECT [DATE], b.* FROM DimDate CROSS APPLY (SELECT **COUNT(1)** AS 'Number of Cars', d.ManufacturerName AS 'Make', d.Name AS 'Model', c.name AS 'Car Class' FROM CarRating a INNER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID **AND dimDate.Date < a.ExpiredWhen AND dimDate.Date >= a.EffectiveWhen** INNER JOIN CarClass c ON a.CarClassID = c.CarClassID INNER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID GROUP BY d.Name, c.name,d.ManufacturerName) b WHERE DimDate.Date between @StartDate and @EndDate 

Answer 4

Do I need to be cross-application / subquery? It seems like he is doing more work this way. Could they be combined?

 SELECT dimDate.[Date] COUNT(1) AS 'Number of Cars', d.ManufacturerName AS 'Make', d.Name AS 'Model', c.name AS 'Car Class' FROM DimDate LEFT OUTER JOIN CarRating a ON dimDate.[Date] < a.ExpiredWhen AND dimDate.[Date] >= a.EffectiveWhen LEFT OUTER JOIN OwnedCar b ON a.OwnedCarID = b.OwnedCarID LEFT OUTER JOIN CarClass c ON a.CarClassID = c.CarClassID LEFT OUTER JOIN BaseCar d ON b.BaseCarID = d.BaseCarID GROUP BY dimDate.[Date], d.Name, c.name,d.ManufacturerName 

Answer 5

I had similar experience with CROSS APPLY on MS SQL server when used on a large table in a medical dataset. I am talking about several orders of magnitude increase in execution time when adding CROSS APPLY.

After a little research, I found that using PIVOT was extremely effective by taking a query s> 18 hours to less than 2 minutes (!), So I decided to share this 2c tip. Joe.