September 28, 2010 at 7:36 am
frfernan (9/27/2010)
Hello all,I would suggest two changes to the original view, maybe these changes help.
First: really there is no need for a CROSS JOIN, the query could be rewritten using INNER JOIN only.
Second: whit the CONVERT this query clears hours, minutes and seconds from the original datetime, there is another option to do this that might be less expensive.
So I changed the original view to
SELECTdateadd(d, datediff(d,0,AdvStats.create_date), 0) AS createDate,
AdvStats.ad_name AS adName,
AdvStats.advertisement_id AS adId,
COUNT(AdvStats.trip_ad_id) AS numAdPlayed,
DayTransMapping.DayTransId AS dayTransId,
DayTrans.dayTransDesc,
Trip.vehicle_id AS vehicleId
FROM DayTrans
INNER JOIN DayTransMapping
ON DayTrans.DayTransId = DayTransMapping.DayTransId
INNER JOIN Trip
ON Trip.StartHour BETWEEN DayTransMapping.StartHour AND DayTransMapping.EndHour
INNER JOIN AdvStats
ON AdvStats.shift_number = Trip.shift_number
AND AdvStats.trip_number = Trip.trip_number
GROUP BY dateadd(d, datediff(d,0,AdvStats.create_date), 0),
AdvStats.advertisement_id,
AdvStats.adName,
DayTransMapping.DayTransId,
DayTrans.DayPartDesc,
Trip.vehicle_id
Hope this helps,
Francesc
Hi Francesc,
I tried running this code 3 times and comparing it to the old one...it seems that the old one was still faster for like 3 minutes average. 🙁
September 28, 2010 at 8:41 am
Hello James,
well, not all solutions work all times :crying:. That means that SQL Server solved the CROSS JOIN as a INNER JOIN, then changing the view source code has no effect. And changing the way to calculate createDate has little effect too (and negative!).
In my previous post I suggested you to change some indexes in order to contain all needed data within the used indexes, Lutz suggested the same thing in more detail. Have you tried this way?.
I will ask you for a new option. Do you use all rows in the tables Trip ans advStats?. I explain: both tables contains a date, that for me looks like a movement date; in your query you select rows for a day, a not-so-old one.
In what way users access these tables?, they select ALWAYS some not-so-old dates?. What is the date range contained in these tables?, maybe you can clean the tables, moving old dates to a history backup, and you can improve performance this way.
Well, all is trying, trying, trying... at some point you will success :).
Regards,
Francesc
September 28, 2010 at 3:42 pm
frfernan (9/28/2010)
Hello James,well, not all solutions work all times :crying:. That means that SQL Server solved the CROSS JOIN as a INNER JOIN, then changing the view source code has no effect. And changing the way to calculate createDate has little effect too (and negative!).
In my previous post I suggested you to change some indexes in order to contain all needed data within the used indexes, Lutz suggested the same thing in more detail. Have you tried this way?.
I will ask you for a new option. Do you use all rows in the tables Trip ans advStats?. I explain: both tables contains a date, that for me looks like a movement date; in your query you select rows for a day, a not-so-old one.
In what way users access these tables?, they select ALWAYS some not-so-old dates?. What is the date range contained in these tables?, maybe you can clean the tables, moving old dates to a history backup, and you can improve performance this way.
Well, all is trying, trying, trying... at some point you will success :).
Regards,
Francesc
Hi Francesc,
Still in the process of changing the indexes, I will let you guys know the status...*crossing fingers*.
Users access these tables basing from the date range they supply (old or new). If this was written in stored procedure, can you show me how to do so in an efficient way?
Maybe I can derive something using stored procedures.
Thanks for the help!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply