[RESOLVED] Return to very slow query

[szlamany]

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.

[Zvoni]

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.
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

[sapator]

I have an issue with the CTE Zvoni
I suppose it's an update so it will only update one column but this:

	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)

produces 4 exact same lines of:

LoyaltyMemberCode LoyaltyMemberCode LoyaltyClubID LoyaltyClubID
NULL xxx 0 10

While this:

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;

produces 16 exact same lines of:

LoyaltyMemberCode LoyaltyMemberCode LoyaltyClubID LoyaltyClubID
NULL xxx 0 10

[Zvoni]

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....

[sapator]

It's day to night values. It really should not make a difference at the specific select.

[sapator]

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.

[Zvoni]

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 SELECT
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

[sapator]

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

[sapator]

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

[sapator]

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 ?

[Zvoni]

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

I wanted to see if there are duplicates of CinemaCode and TransNumber

[Zvoni]

Change to and try again

C  AS (SELECT DISTINCT CinemaCode, TransNumber  --here with DISTINCT
        FROM tblDWConcession 
        INNER JOIN DA ON 1=1

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,
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

[sapator]

Ye with distinct it will work but isn't that a "group by"? Aren't we decrease performance by doing so?

[sapator]

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.

[sapator]

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.

[Zvoni]

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"

Yeah, i'd run the UPDATE (Query, whatever) on its own, too, just to see, if it's this Query, or something else

[sapator]

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.

[sapator]

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.