Big Nasty Mess, please help
-
Hello, I hope that everyone is having a fabulous monday. I have a huge sproc that I was asked to try and re-code. This was taking way too much time on the processor. After looking thru the code, I have found that the sproc is actually pulling data from the same table three times. Each time it gets a new alias such as prev_day, last_month, current_day. The table is very large, there is no changing that at the moment. People hate change
How can I effeciently code this where I can get data from all the tables, yet still be able to query out the data from all three "dates" One single small table holds the dates, and the data that is being pulled is from one large table. However, I need to be able to pull the same data for all three dates, current_day, last_month, and prev_day, without pulling the same data from the very large table three different times. This result will be inserted into an empty table as the last step. The slowness comes from the left outer joins where the same table is being quieried three times.
I hope this is not too confusing. I appreciate any and all help, and want to say thank you in advance.
Have a good one
Andrew SQLDBA
-
Here's an exemple on how to filter out your select so you get all the data you need in only 1 select... but also no more data than you need.
SELECT
O.OrderDate
, SUM(O.OrdersAmount)
FROM dbo.orders O
WHERE YEAR(O.OrderDate) = YEAR(GETDATE())
AND 1 =
--last month's days
CASE WHEN MONTH(O.OrderDate) = MONTH(GETDATE()) - 1 THEN 1
--today and yesterday
WHEN O.OrderDate between DATEADD(D, -1, DATEDIFF(D, 0, GETDATE())) AND GETDATE() THEN 1
END
GROUP BY O.OrderDate
DATEADD(D, -1, DATEDIFF(D, 0, GETDATE())) returns yesterday at 12H00 AM
-
Thanks,
Let me play around with that, putting in al this code that I have. I understand the logic, and greatly appreciate your assistance.
Andrew
SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply