Printable View
This is a good read:
https://devblogs.microsoft.com/azure...r-temp-tables/
And again - I'm not sure how CTE's materialize - if that's a memory object or a disk based stored object. And even with TEMP TABLES, which are TEMPDB "stored" - they can still reside in memory alone if small enough.
I don't think CTE a disk based objects, since CTE's are "temp. VIEWs", and a View is, as in that article described, just the definition of the Data queried, not the Data itself.Quote:
Originally Posted by szlamany
With CTE (compared to Views) it's even only for the lifetime of the query itself: it creates the definition, it loads the data when called, it discards everything once the end is reached
I have an issue with the CTE Zvoni
I suppose it's an update so it will only update one column but this:
produces 4 exact same lines of:Code:DECLARE @365DaysAgo DATETIME = DATEADD(DAY, -365, GETDATE());
select C.LoyaltyMemberCode, tblDWPayment.LoyaltyMemberCode,
C.LoyaltyClubID, tblDWLoyaltyMember.LoyaltyClubID
FROM tblDWConcession C
INNER JOIN tblDWPayment ON tblDWPayment.CinemaCode = C.CinemaCode AND tblDWPayment.TransNumber = C.TransNumber AND C.SalesTransCinemaDate > @365DaysAgo AND PaymentDate > @365DaysAgo --AND tblDWPayment.LoyaltyMemberCode IS NOT NULL
INNER JOIN tblDWLoyaltyMember ON tblDWLoyaltyMember.LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode
WHERE C.LoyaltyMemberCode IS NULL AND (C.IsProcessCompleted = 1 OR C.IsProcessCompleted IS NULL)
LoyaltyMemberCode LoyaltyMemberCode LoyaltyClubID LoyaltyClubID
NULL xxx 0 10
While this:
produces 16 exact same lines of:Code:WITH
DA AS (SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo),
C AS (SELECT CinemaCode, TransNumber
FROM tblDWConcession
INNER JOIN DA ON 1=1
WHERE LoyaltyMemberCode IS NULL AND (tblDWConcession.IsProcessCompleted = 1 OR tblDWConcession.IsProcessCompleted IS NULL) AND SalesTransCinemaDate>(DA.OneYearAgo)),
P AS (SELECT T.LoyaltyMemberCode, T.CinemaCode, T.TransNumber, L.LoyaltyClubID
FROM tblDWPayment T
INNER JOIN tblDWLoyaltyMember L ON L.LoyaltyMemberCode=T.LoyaltyMemberCode --Index on both LoyaltyMemberCode
INNER JOIN DA ON 1=1
WHERE T.PaymentDate>(DA.OneYearAgo))
select U.LoyaltyMemberCode, P.LoyaltyMemberCode,
U.LoyaltyClubID, P.LoyaltyClubID
FROM tblDWConcession U
INNER JOIN C ON C.CinemaCode = U.CinemaCode and C.TransNumber= U.TransNumber --I'm implying you have an ID-Field Primary Key
/* Don't forget the combined Index on both columns */
INNER JOIN P ON P.CinemaCode = C.CinemaCode AND P.TransNumber = C.TransNumber;
LoyaltyMemberCode LoyaltyMemberCode LoyaltyClubID LoyaltyClubID
NULL xxx 0 10
Crap.
A CROSS JOIN, and i can't see it on first look
EDIT: Just saw it: Why is IsProcessCompleted = 1 here?
In the other query you wrote IsProcessCompleted=0 or IS NULL
Add CinemaCode and TransNumber to the Output of the outer SELECT
SELECT C.CinemaCode, C.TransNumber, U.LoayaltyMemberCode etc....
It's day to night values. It really should not make a difference at the specific select.
Actually the first query also cross joining as it gives 4 same columns so I was really thinking if that will affect the update or not.
Add CinemaCode and TransNumber to the Output of the outer SELECTQuote:
Originally Posted by sapator
SELECT C.CinemaCode, C.TransNumber, U.LoayaltyMemberCode etc....
Execute two separate Queries:
Use the 3 CTE from above,
execute
SELECT * FROM C
and
SELECT * FROM P
and look at the output
Maybe the duplication happens in the CTE
It's the same cinemacode and transnumber on all lines.
I did not understand the SELECT * FROM C . That will give me all the table of the year
Just a note.
tblDWConcession actually have 4 lines of the transnumber, so something is happening and we get 4*4 lines.
So the first query will "correctly" give 4 lines but the second one will duplicate * 4
Possibly the first tblDWConcession on the CTE, joined with the second tblDWConcession U will do 4 on the first CTE * 4 on the second tblDWConcession ?
I wanted to see if there are duplicates of CinemaCode and TransNumberQuote:
Originally Posted by sapator
Change to and try again
EDIT: that's actually what sz was alluding to: With CTE, VIEW or temp table you can test each Subquery for itself, until it returns correct values,Code:C AS (SELECT DISTINCT CinemaCode, TransNumber --here with DISTINCT
FROM tblDWConcession
INNER JOIN DA ON 1=1
or reveal a mistake you might have.
I'm thinking about those other two Fields in that combined Index for CinemaCode and TransNumber.
Maybe they are needed to really get a unique key
Ye with distinct it will work but isn't that a "group by"? Aren't we decrease performance by doing so?
I was trying with partition by and cross apply etc and the performance is terrible.
Either way in theory CTE looks more light on the execution plan, the update is executed only once on the same selects and I do not see any memory issues, so I think I will have a go for the night batch and see what happens.
Yeah,
Unfortunately the job has not been improved.
I don't think it has to do with the execution plan , something is wrong when running the job.
My last thought is put the query on it's own step but that is just a wild guess.
Well, that's called "debugging" :DQuote:
Originally Posted by sapator
Yeah, i'd run the UPDATE (Query, whatever) on its own, too, just to see, if it's this Query, or something else
I'm wondering if there is a reason to do a WITH FULLSCAN again. It might get a new execution plain but last time it did not improve the job run. It also takes 3 hours blocking everything so...
Btw this is a tough one , I haven't encountered something similar. Query running fine a tab with any execution plan and failing on the Job run....Well, get irritated with something new every day. :D
It worked!!
158 seconds instead of 3500 seconds.
I don't know why the job was bloated in a parallel execution but this was clearly lot's of inserts and updates in the same query on a job schedule.
I don't have a clue, I haven't read a document about it, probably all those years trimming with database batch moves triggered the thought. :p