[RESOLVED] Return to very slow query

[sapator]

So I'm returning to this thread talk:
https://www.vbforums.com/showthread....ry-reun-faster



Query runs relatively ok locally 5-10 minutes but when put on a job it takes about 50 minutes. Initially it was maxing out at 35 minutes but probably due to large data entries it went to 50 that is aaalmost the breaking point.
From what I can see, set noncount is on , haven't touched any parallelism MAXDOP etc.
The query is run as a part of a longer list of queries in one go, so I was thinking if I use it outside of the list, individually would make any difference? Or if I temp the tblDWConcession table but I would be temping a lot of GB of data.
Or any other thoughts?

Edit , just saw tblDWConcession is 42GB stand alone.

[Shaggy Hiker]

That sure seems like a LOT of data to me, but it may not mean quite so much to SQL Server (assuming that's what this is running on, but the point would be true for any DBMS). After all, if there were a bunch of BLOB fields, which played no part in any query other than possibly being returned, then the size could be quite large without impacting the query.

How many records are there? That would impact the performance in a couple of different ways. Lots of records would mean scans would be slow, but would also likely mean that indexes would be quite large as well.

[sapator]

For one year select, that I cut the query down previously, 3.549.849 rows. Total 80.669.802 rows.

[sapator]

By updating statistics today the index seek wend from 54 to 42%.
This is something but not optimal .
What I can see is that the estimated number of executions in index seek of tblDWConcession will not change even if I reduce the dateadd to 3 or 4 days. It will still select a million rows and bring back 4-5 rows. Is there a way to find out why this is happening and possibly fix it?

[sapator]

To make it easier not to go back and forth.
This:

DECLARE @365DaysAgo DATETIME = DATEADD(DAY, -180, 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)

Will estimate 4 actual number of rows today on index seek of tbldwconcession and 1million rows on estimated number of executions

[sapator]

Yesterday I updated statistics WITH FULLSCAN.
There is some difference in running time but I could see a 15-20% at most.
So I don't know what else I can do. The only thing that comes into mind is if the @365DaysAgo is inferring and instead of having a smaller set, it actually mess the execution plan.
I can check that tomorrow. Other than that...

[Zvoni]

If i have a slow query with joins, i actually go through each join separately trying to find the culprit slowing me down

Start out with just the Query on tblDWConcession (No Join, no Fields from the Joins) with the Filters applied there.
It doesn't matter how many records it returns, just how long it takes.
The "outer" query on tblDWConcession is the "maximum" resultset for you. The Inner Joins (if set up correctly!) would just further filter down that "maximum" result.

Next i would JOIN only tblDWLoyaltyMember, since you're connecting only with one Field and rerun the query. Measure performance
Next remove tblDWLoyaltyMember from the JOIN and add tblDWPayment to the JOIN, and rerun your query. Measure performance.

My guess is that the JOINS are slowing you down, and my particular guess is tblDWPayment.

btw: I noticed in your JOIN to tblDWPayment this:
....AND C.SalesTransCinemaDate > @365DaysAgo AND PaymentDate > @365DaysAgo

1) To which table does PaymentDate belong? You are not qualifying that field
2) C.SalesTransCinemaDate > @365DaysAgo should go in the WHERE-Clause!

[sapator]

Hi.
Hence the issue.
This runs fine when testing and takes an hour on the job run.
All the fields are correctly indexed( I hope), there is no indication of a "lag", other that calling a million rows to get 4-5 (Above I specified an update statistics)
PaymentDate is unique so, yes you are correct but it's not an actual issue.
I would like to ask why I should put C.SalesTransCinemaDate > @365DaysAgo to the where clause? Doesn't it makes a difference to keep the filters in the joins if possible? I was under that impression.
Thanks.

[Zvoni]

PaymentDate is unique so, yes you are correct but it's not an actual issue.

So we would know in which table it is.

I would like to ask why I should put C.SalesTransCinemaDate > @365DaysAgo to the where clause? Doesn't it makes a difference to keep the filters in the joins if possible? I was under that impression.
Thanks.

A statement evaluates FIRST the WHERE-Clause, then any filters in a JOIN

C.SalesTransCinemaDate > @365DaysAgo doesn't provide anything to the JOIN itself, since it's a field from the "Master" of the JOIN (left side of the JOIN)

It's like this:
your current WHERE-Clause reduces from 80M available records to 10M records (those are just some numbers now)
Then your Statement starts to connect the JOINS, meaning: It takes those 10M records, and starts connecting them, and THEN uses that Filter to reduce it further down, say to 2M records

Put it in the WHERE-Clause
your statement would reduce from 80M available records to 2M records, THEN start to JOIN.
What do you think is faster?

Hard filters (..ON SomeField>1) in a JOIN only make sense for right-hand-side Fields of a JOIN

Bottom Line: Filter first, then JOIN
It's the reason why i asked for the table of PaymentDate

[sapator]

OK.
I was under the impression that putting filters in join would reduce sargability issue .
I guess that means what you can connect with joins better do it will joins rather than filters but individual date columns etc do not apply unless the timing was somehow connected (p.e. needed to join common times on both table).
That is about right?
I guess I was carried away testing stuff to fix the issue, so...

P.S. Good to know these but the main issue is the job running extremely fast and poor me doing everything to fix it

[Zvoni]

I just noticed something in your Query (which was wrong in my post above)
tblDWLoyaltyMember JOINS to tblDWPayment (not Concession)
Why?
Obviously, tblDWConcession also has the (Foreign Key?) LoyaltyMemberCode

i've rearranged your query for easier reading

SELECT 
C.LoyaltyMemberCode, 
P.LoyaltyMemberCode, -- Why do you have LoyaltyMemberCode twice?
C.LoyaltyClubID, 
L.LoyaltyClubID -- Why do you have LoyaltyClubID twice?
FROM tblDWConcession C 

INNER JOIN tblDWPayment P 
ON P.CinemaCode = C.CinemaCode AND 
P.TransNumber = C.TransNumber AND 
PaymentDate>@365DaysAgo --Still depending in which table PaymentDate is

INNER JOIN tblDWLoyaltyMember L 
ON L.LoyaltyMemberCode = P.LoyaltyMemberCode  

WHERE 
C.LoyaltyMemberCode IS NULL AND 
(C.IsProcessCompleted = 1 OR C.IsProcessCompleted IS NULL) AND 
C.SalesTransCinemaDate > @365DaysAgo

I don't understand your second JOIN connected via LoyaltyMemberCode
1) JOINS don't connect on NULL-Values
2) in your WHERE-Clause you filter on C.LoyaltyMemberCode IS NULL

HUH?!?!?!??!

EDIT: Honestly, i don't understand the purpose of that Query
Are you saying that in tblDWConcession you have records with MemberCode being NULL (your WHERE-Clause), but in tblDWPayment you have a MemberCode (which is NOT null)??

btw: is TransNumber absolutely unique across the Board (a candidate for a Primary Key!)?? If yes, you could leave out the connect on CinemaCode
Or is it possible, that two (or more cinemas) use the same TransNumber each for itself?

[sapator]

Yes there are 2 loyalty membercodes, basically it does a transportation of values to empty table rows. Transnumber cannot be joined on it's own, that's the rules. It's only a primary key with both values.
Again, even if we microfix every little detail, the issue is not there. The issue is the job doing 100 times longer than the query itself on the MSSQL tab that I'm testing it.

[Zvoni]

Yes there are 2 loyalty membercodes, basically it does a transportation of values to empty table rows. Transnumber cannot be joined on it's own, that's the rules. It's only a primary key with both values.
Again, even if we microfix every little detail, the issue is not there. The issue is the job doing 100 times longer than the query itself on the MSSQL tab that I'm testing it.

Huh? I don't understand.
Do you mean the UPDATE (see referenced Thread) is taking 100 times longer than above Query?
Do you remember what sz said, that if you have JOINS in an UPDATE you have to put the Table you're updating in the FROM-Clause, otherwise you create a Cross-Join?

UPDATE T 
SET T.SomeField=S.SomeField
FROM MyTable T 
INNER JOIN SomeOtherTable S 
ON S.ID=T.ID 
WHERE T.SomeOtherField=SomeValue

Those are the rules for MS SQL-Server!

[sapator]

I don't know if the update takes 100 times more on the MSSQL tab, I haven't try it and I cannot do that on company hours as I will block stuff but it certainly takes a huge amount of time running at the job.

The update is this, corrected:

DECLARE @365DaysAgo DATETIME = DATEADD(DAY, -365, GETDATE());
	UPDATE tblDWConcession 
    SET LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode,
	    LoyaltyClubID = tblDWLoyaltyMember.LoyaltyClubID
    FROM tblDWConcession
    INNER JOIN tblDWPayment ON tblDWPayment.CinemaCode = tblDWConcession.CinemaCode AND tblDWPayment.TransNumber = tblDWConcession.TransNumber 
    INNER JOIN tblDWLoyaltyMember ON tblDWLoyaltyMember.LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode
    WHERE tblDWConcession.LoyaltyMemberCode IS NULL AND tblDWConcession.SalesTransCinemaDate > @365DaysAgo AND PaymentDate  > @365DaysAgo AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL);

The where's are indexed and there is no obvious issues on the execution plan (run as display estimated of course)
What exactly sz implied that I need to change?
Thanks.

[Zvoni]

I don't know if the update takes 100 times more on the MSSQL tab, I haven't try it and I cannot do that on company hours as I will block stuff but it certainly takes a huge amount of time running at the job.

The update is this, corrected:

DECLARE @365DaysAgo DATETIME = DATEADD(DAY, -365, GETDATE());
    UPDATE tblDWConcession 
    SET LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode,
        LoyaltyClubID = tblDWLoyaltyMember.LoyaltyClubID
    FROM tblDWConcession
    INNER JOIN tblDWPayment ON tblDWPayment.CinemaCode = tblDWConcession.CinemaCode AND tblDWPayment.TransNumber = tblDWConcession.TransNumber 
    INNER JOIN tblDWLoyaltyMember ON tblDWLoyaltyMember.LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode
    WHERE tblDWConcession.LoyaltyMemberCode IS NULL AND tblDWConcession.SalesTransCinemaDate > @365DaysAgo AND PaymentDate  > @365DaysAgo AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL);

The where's are indexed and there is no obvious issues on the execution plan (run as display estimated of course)
What exactly sz implied that I need to change?
Thanks.

You have it correct now with the target table being in the FROM-Clause. It was different in your other Thread

Which leaves one last thing: In which table is PaymentDate? tblDWPayment?

and i still don't understand this one:
(tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL)

IsProcessCompleted is a Boolean, meaning 0 = False, 1 = True
How can that be NULL?
If that field really allows for NULL
I'd go into the Database, and set Default Value to 0 for that Field,
and then fire off a

UPDATE tblDWConcession
SET IsProcessCompleted=0
WHERE IsProcessCompleted IS NULL

to clean it up once and for all

Then you can throw the OR into the bin

[sapator]

No I can't change the 0 and 1 and Null, it's an inner mechanism to get specific data. I'm not exactly sure what I will get aka it's not in my hand. It's not Boolean I think as there are more values.
Yes PaymentDate is on tblDWPayment. I fixed it on the query as you where itching about it constantly

WHERE tblDWConcession.LoyaltyMemberCode IS NULL AND tblDWConcession.SalesTransCinemaDate > @365DaysAgo AND tblDWPayment.PaymentDate  > @365DaysAgo ...

[Zvoni]

Right.
Everything said: I don't see any other possibility to improve that UPDATE-Statement
except: You wrote the WHERE... is indexed.
I doubt this is going to improve anything, since you probably have a lot of duplicates in there

Index on C.LoyaltyMemberCode will not help you since you filter on IS NULL
Index on C.IsProcessCompleted, C.SalesTransCinemaDate and P.PaymentDate will not help you either since you will have duplicates

Do you have Indexes on the connecting Fields of the JOINS? That's the more important question.
C/P.CinemaCode, C/P.TransNumber and L/P.LoyaltyMemberCode (Aliases see Post 11 above)

EDIT: Since you wrote CinemaCode and TransNumer are together unique, i'd put an INDEX on both combined

[sapator]

Hey.
Yes on all accounts.
Can I ask, why wont C.SalesTransCinemaDate and P.PaymentDate help? I'm unclear on that. That. Maybe SalesTransCinemaDate will not help since it's date but PaymentDate is datetime so I recon there are no duplicates.

[szlamany]

I'm late to the game here - I see you are in a SPROC - correct?

With that said, you can create TEMP tables - right?

The WHERE clause of the update is kind of tough - the OR is bad for speed in general.

When presented with a speed issue like this I break down the approach to getting primary key's for update.

I load into a TEMP table the PK of the UPDATE'able record, along with other key fields for joining, if needed. Get the speed of that build down to the best I can.

Then I typically remove rows from this temp table - remove the PK's of row that we DO NOT want to update.

Once I get that TEMP table down to the proper population, then I am just UPDATE'ing FROM that TEMP TABLE and JOIN'ing just the ONE UPDATE table.

I've done this approach dozens and dozens of times.

[Zvoni]

Hey.
Yes on all accounts.
Can I ask, why wont C.SalesTransCinemaDate and P.PaymentDate help? I'm unclear on that. That. Maybe SalesTransCinemaDate will not help since it's date but PaymentDate is datetime so I recon there are no duplicates.

Ah, OK.
If PaymentDate is really a DateTime (incl. Time!) then no, you shouldn't have any duplicates

from here: https://stackoverflow.com/questions/...plicate-values

In most cases, only one index can be used to optimize a database query. If a query needs to match several indexed columns, the query planner will have to decide which of these indexes to use. Each index has a cardinality, which is roughly the number of different values across the table. An index with higher cardinality will be more effective, because selecting rows that match the index will result in very few rows to scan to match the other conditions.

An index on a gender column will only cut the table in half. Any other index will be more effective.
As an analogy, think of phone books. If you had a single phone book for an entire country, it would be huge and hard to search for the specific person you want. So phone books are usually made for just a city, or a few cities in an area, to make them reasonable sizes. But if you instead had a "Male phone book" instead of regional phone books, it would be nearly as unusable as a phone book for the entire country. The criteria for creating new phone books is that they should be much smaller than a book for the entire country. A factor of 2 reduction isn't very useful when you're starting with an enormous size.

Everything said: I think a combined (mutiple column) Index on CinemaCode and TransNumber (one each for Concession as well as for Payments) will probably be the most effective

EDIT

The WHERE clause of the update is kind of tough - the OR is bad for speed in general.

I was thinking about that one, too.
I'd go for a

.... AND COALESCE(C.IsProcessCompleted, 0)=0

...since i read somewhere that a COALESCE will result in a better Query-Plan (but not sure about that)

EDI2: Just had an idea involving CTE.
Give me a minute

[szlamany]

@zvoni - that "one index" at a time information is why I suggested the approach I did a couple posts back.

That way you can FORCE the use of EACH index, in order, thus cleaning up and removing rows from an UPDATE PK TEMP TABLE.

[sapator]

Hey.
Yes I said about temping on the OP.
I have also done temp tables and also in some threads here it helped speed up performance. Of course the issue here is that it's 80M rows so I'm not sure if it will allow me that, it may burst on the server temp DB limit. Also something I can't control. And to be blunt, I'm afraid that if it blows up during the night batch the whole company will stall for the day since they are depended on that tables . So I'm afraid to go for pro on the temp but I'm thinking it.

[szlamany]

Hey.
Yes I said about temping on the OP.
I have also done temp tables and also in some threads here it helped speed up performance. Of course the issue here is that it's 80M rows so I'm not sure if it will allow me that, it may burst on the server temp DB limit. Also something I can't control. And to be blunt, I'm afraid that if it blows up during the night batch the whole company will stall for the day since they are depended on that tables . So I'm afraid to go for pro on the temp but I'm thinking it.

It is what SQL does when it builds your query. It uses "temp space" to build working recordsets that are then trimmed down.

You can see this very act described in an EXECUTION plan.

When you find an EXECUTION plan that does not work, you make subtle changes or add hints to force it. If not successful, roll up your sleeves and go old school - use a TEMP TABLE.

If it can be sped up, then TEMP TABLE approach is a method of seeing how. If you can then put that back into a single QUERY, and actually see consistent performance similar to the TEMP TABLE, more power to you. You then learned how to make a single QUERY work well.

80 million rows is EXACTLY the reason to try this approach.

[sapator]

This is the index.
Yes there are also 2 columns for uniqueness that I forgot about but they don't apply on the specific query.
System code is for compatibility with an old pre 2009 DB . I think is V or S , don't think is needed.
But as I've said, running the query locally and not on the SQL JOB does not produce any long waits.

[sapator]

It is what SQL does when it builds your query. It uses "temp space" to build working recordsets that are then trimmed down.

You can see this very act described in an EXECUTION plan.

When you find an EXECUTION plan that does not work, you make subtle changes or add hints to force it. If not successful, roll up your sleeves and go old school - use a TEMP TABLE.

If it can be sped up, then TEMP TABLE approach is a method of seeing how. If you can then put that back into a single QUERY, and actually see consistent performance similar to the TEMP TABLE, more power to you. You then learned how to make a single QUERY work well.

80 million rows is EXACTLY the reason to try this approach.

Actually I have seen in the past SQL giving up as the temp table space was out and cold, so, granted i should try this, I first have to make sure that it will not bomb the server.

[szlamany]

TEMP table data is stored in the TEMPDB "database". It should be tuned by your DBA to work for all needs.

[sapator]

Correct. It should be tuned but it's not in my hand.
One thing I'm sure about is that a previous big query crashed the server. It was years ago, allegiantly the DB was re-tuned...Allegiantly.

[Zvoni]

OK, since we should all know, a CTE is something like a View is something like a temp. table

Untested. No idea about performance

WITH
    DA AS (SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo),
    
    C  AS (SELECT ID, CinemaCode, TransNumber
        FROM tblDWConcession 
        INNER JOIN DA ON 1=1
        WHERE LoyaltyMemberCode IS NULL AND COALESCE(IsProcessCompleted, 0)=0 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)

UPDATE U
    SET U.LoyaltyMemberCode = P.LoyaltyMemberCode,
        U.LoyaltyClubID = P.LoyaltyClubID
    FROM tblDWConcession U 
    INNER JOIN C ON C.ID=U.ID --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

[sapator]

CTE huh?
Don't know they can be quite unpredictable.
I'm still at stall on how would I test these without ruining the workday..... I will have to find a way to first test the temp and after the CTE on the actual job. I'm thinking about it but it's not really your problem

Edit.
this will do? INNER JOIN DA ON 1=1 ? What?

[sapator]

Also, no id so...

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 COALESCE(IsProcessCompleted, 0)=0 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)

UPDATE U
    SET 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

[Zvoni]

CTE huh?
Don't know they can be quite unpredictable.
I'm still at stall on how would I test these without ruining the workday..... I will have to find a way to first test the temp and after the CTE on the actual job. I'm thinking about it but it's not really your problem

Edit.
this will do? INNER JOIN DA ON 1=1 ? What?

CTE's can actually be pretty predictable, since they are basically just SELECT
My approach is something along the lines of sz' approach by trimming down "available" rows
Basically: I'm splitting up C and P, reducing both to however many records that satisfy the filters BEFORE i join them in the UPDATE

That said: I'm not sure if each CTE uses its own Indexes, but i would expect it, since CTE's are basically Sub-Queries, and Sub-Queries are executed for themselves.
I would expect C to use an Index for the SalesTransCinemaDate, P to use the Index either on PaymentDate or LoyaltyMemberCode for the INNER JOIN (or both)

Just look at the Execution Plan

INNER JOIN DA ON 1=1 is a trick i found out
The DA-CTE is returning exactly one row: The Date one year ago
the ON 1=1 basically creates a CROSS JOIN, but since you only have one row in DA you get no penalty for it ("something" multiplied with 1 is still "something").
With that JOIN i make the "OneYearAgo" available inside C and P, so i don't have to use a Variable!! --> You can't use variables inside CTE's!

[sapator]

Yes but I'm not exactly sure why you can't use this:
I mean cool trick and all but does it makes a difference here?

declare @DA as datetime = (SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo);

WITH
    
    C  AS (SELECT CinemaCode, TransNumber
        FROM tblDWConcession 
      --  INNER JOIN DA ON 1=1
        WHERE LoyaltyMemberCode IS NULL AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL) AND SalesTransCinemaDate>@DA),
    
    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)

	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

[Zvoni]

Yes but I'm not exactly sure why you can't use this:
I mean cool trick and all but does it makes a difference here?

declare @DA as datetime = (SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo);

WITH
    
    C  AS (SELECT CinemaCode, TransNumber
        FROM tblDWConcession 
      --  INNER JOIN DA ON 1=1
        WHERE LoyaltyMemberCode IS NULL AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL) AND SalesTransCinemaDate>@DA),
    
    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)

    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

because.....

With that JOIN i make the "OneYearAgo" available inside C and P, so i don't have to use a Variable!! --> You can't use variables inside CTE's!

IF you want/have to use variables, you would ghave to resolve the CTE's to Sub-Queries

declare @DA as datetime = (SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo);
    select  U.LoyaltyMemberCode,  P.LoyaltyMemberCode,
        U.LoyaltyClubID, P.LoyaltyClubID
    FROM tblDWConcession U 
    INNER JOIN 
       (SELECT CinemaCode, TransNumber
        FROM tblDWConcession 
      --  INNER JOIN DA ON 1=1
        WHERE LoyaltyMemberCode IS NULL AND  (tblDWConcession.IsProcessCompleted = 0 OR  tblDWConcession.IsProcessCompleted IS NULL) AND  SalesTransCinemaDate>@DA) 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 
       (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) P
    ON P.CinemaCode = C.CinemaCode AND P.TransNumber = C.TransNumber

[sapator]

I don't get it.
I use the @DA variable . Granted it's not inside the CTE but it's on the sp.
Remember it's a part of a long running query not individual CTE update.
If it was only the CTE for some reason then yes. Then again I could have done:
WHERE LoyaltyMemberCode IS NULL AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL) AND SalesTransCinemaDate>(SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo))
I don't know tho how the indexes will behave but I'm thinking not much difference.
Note, I'm not calling you off on the query but it seems peculiar to me.

[Zvoni]

I don't get it.
I use the @DA variable . Granted it's not inside the CTE but it's on the sp.
Remember it's a part of a long running query not individual CTE update.
If it was only the CTE for some reason then yes. Then again I could have done:
WHERE LoyaltyMemberCode IS NULL AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL) AND SalesTransCinemaDate>(SELECT DateAdd(Year, -1, GetDate()) As OneYearAgo))
I don't know tho how the indexes will behave but I'm thinking not much difference.
Note, I'm not calling you off on the query but it seems peculiar to me.

Look at my Edit above resolving the CTE to Subqueries.
as SZ said with his temp-table approach: with the separate Subqueries (or CTE's) you FORCE each Subquery to use its own (available) Indexes
compared to your "open" Query/Update where the optimizer probably uses only ONE Index from all involved tables

[sapator]

I get that, I was talking about the 1=1 approach.
Nevermind man, I think you put a whole more effort than needed so I thank you for that.
I will have to find a time to give temp and CTE a go, probably August before I live for vacations that the company works at 5-10% efficiency...

[Zvoni]

I get that, I was talking about the 1=1 approach.
Nevermind man, I think you put a whole more effort than needed so I thank you for that.
I will have to find a time to give temp and CTE a go, probably August before I live for vacations that the company works at 5-10% efficiency...

The "1=1" is basically forcing a JOIN between two tables/views/CTE's if you don't have any Fields to compare to each other.
Basically it creates a CROSS JOIN

Yes, i could have used "... ON SalesCinemaTransDate>DA.OneYearAgo" in C (removing that one from the WHERE in C)
or ".... ON PaymentDate>DA.OneYearAgo" in P (and no WHERE-Clause in P), but i dislike any operator except "=" in a JOIN
Call it personal preference

[szlamany]

I love all this talk of CTE and this and that.

CTE might use TABLE VARIABLES - which are typically memory objects - and for these purposes (8 million rows), might tank in the long run. I only offer that as a "maybe" - I'm not sure...

Regardless, there is nothing more helpful than doing it old school first - see the "fastest" you can actually make it work. Then take that proven approach and work it back into either a CTE or standard query/sub-query vehicle.

In my opinion, that is the R&D part of programming and might actually be the most interesting thing that keeps me going here, lol!

[sapator]

80 million rows, 80 not 8, don't make it easy on me! Lol.

[Zvoni]

5612419[/URL]]I love all this talk of CTE and this and that.

CTE might use TABLE VARIABLES - which are typically memory objects - and for these purposes (8 million rows), might tank in the long run. I only offer that as a "maybe" - I'm not sure...

Whoa. Didn’t know that one for MS SQL
i know from other DBMS that you cannot use outside variables inside a CTE
Nice!