[RESOLVED] Help to make query reun faster

[sapator]

Hi.
I have a query that runs for 2 hours.
Although iterating the tables take seconds.

 UPDATE tblDWSession
    SET FWeek = CASE WHEN DATEDIFF(D, tblSYSCWeeks.CWStartDate, tblDWSession.SDate)/7+1 <= 0 THEN 1 ELSE DATEDIFF(D, tblSYSWeeks.CWStartDate, tblDWSession.SDate)/7+1 END
    FROM tblDWF, tblSYSWeeks 
    WHERE tblDWF.Code = tblDWSession.Code
      AND tblDWF.FDate BETWEEN tblSYSWeeks.CWCStartDate AND tblSYSWeeks.CWEndDate
      AND tblDWSession.IsProcessCompleted = 0 or tblDWSession.FWeek = 0;

tblDWSession has 1.5 M lines the other tables are small 50.000 lines top , of course the problem is the calculation( probably the division is killing it, along with the Datediff). I'm just not good at calculation and I don't know how can I make an alternative that is sargable.
Any ideas?
Also I'm guessing since the tblDWSession table has data from 10 years, doing a getdate - 730 will probably reduce the problem?

Thanks.

[Zvoni]

Why in blazes are you using days in DateDiff just to calculate weeks?
https://learn.microsoft.com/en-us/sq...l-server-ver16

.... DATEDIFF(week, FirstDate, SecondDate)

EDIT: And remember: Week 1 of a calendar-year is the week having the first thursday or the 4th of January (which is basically the same)

[sapator]

I, don't use anything.
It's not my query. Also will it make any difference if performance f i use week?

[Zvoni]

I, don't use anything.
It's not my query. Also will it make any difference if performance f i use week?

Then let me ask this way:
What is to be stored in FWeek? A (Calendar-) "Week" obviously, but to me it looks more like "elapsed time in Weeks"

EDIT: Just noticed: You are aware that an "AND" has precedence before an "OR"? --> Your last Line in the UPDATE-Statement

[sapator]

OR....Nop.
I had not looked to that, I must ask why there is no parenthesis there.
The problem is since it takes 2 hours and updates I don't know when to run it to get an execution plan.Probably do a getdate...Anyhow that OR seems suspicious.

[Zvoni]

how i read it:
tblSYSCWeeks (CWStartDate, CWEndDate) --> You're storing the Calender-Weeks from StartDate to EndDate??

tblDWF and tblDWSession seem to contain the same/similar data or are a parent/child-table relation,
which you INNER JOIN via "Code" and filter it via FDate between CWStartDate and EndDate

AND
As to the "missing" parantheses: Yes, that's my thought, too that they are missing
you probably want to filter "whichever is ProcessCompleted=0 or FWeek=0" which needs enclosing parantheses

Again: What is supposed to be stored in FWeek?
The "Week"-Representation of SDate?
Maybe that whole calculation is unneccessary

[sapator]

It is supposed to store the weeks an offer runs.
So it's arithmetic smallint per week 1,2,3,4,5 ...etc

[Zvoni]

It is supposed to store the weeks an offer runs.
So it's arithmetic smallint per week 1,2,3,4,5 ...etc

Ouch. IMO, such things shouldn't be stored, but "calculated" on the fly.

Do you have any "sample" how that is "manually" calculated?
Say, a StartDate (=Date of offer?), a "current" date, what should be FWeek?

an alternative might be a computed column --> https://learn.microsoft.com/en-us/sq...l-server-ver16

[sapator]

Unfortunately the data comes from servers A-Z that are remote, then calculated on the local company server.
I should first ask the OR issue and then look at computed column as I briefly have read and possibly never used one. I'm not sure how to add the update calculate in there tho. I must study that.

[Zvoni]

Unfortunately the data comes from servers A-Z that are remote, then calculated on the local company server.
I should first ask the OR issue and then look at computed column as I briefly have read and possibly never used one. I'm not sure how to add the update calculate in there tho. I must study that.

Note: AFAIK, computed column can only be used, if the calculation is from columns of the same table.

As i said: How is that FWeek really calculated? Or better: it represents the weeks an offer runs.
What does "run" mean?
How long that offer since it was made is now in the past? e.g. Offer made on 19th April --> compared to today (May 17th) = 4 Weeks
Or the offer i made just now is valid for the next 4 weeks?

btw: Is FWeek an Integer?
I'm asking, because acc. to your calculation above you should get fractional results, too.

If integer, how do you want to process fractional results? e.g. "3.6 Weeks" is that 3 or 4 Weeks (Round Down or Round Up)

[sapator]

I'll have to look it up tomorrow as I'm running like a headless chicken right now. Thanks for the help btw.

[Shaggy Hiker]

Is there any way to split the work up? In general, tweaking an Update is only going to get you so far. If you could precompute the value by any means, such that you already had a temporary table with the necessary values, that would go a long ways. That may not be possible, but thinking about it might spur some innovation. After all, optimizing code gains you microseconds, changing algorithms can gain you seconds.

Also, do you have indexes set up, and are they defragmented? With lots o updates, if the indexes aren't rebuilt occasionally, they might be quite the mess, which can really hurt performance.

[sapator]

Indexes is not an issue and the extrenal db's but there is an issue on using new indexes on the center db as it will hurt inserts and size and the DB is more than 150GB and raising.
I'll have to think about re creating but the issue is not on the first server(s) databases the problem is when they pass the data to the central database. The issues comes when that 150GB database start that calculation but now that I think of it, it may be the "OR" regardless of a bad design on the datediff.
Will have to start testing tomorrow...

[sapator]

I'm running a test with the "OR" issue on a select.
With the parenthesis it takes 15 seconds, without the parenthesis it's almost 5 minutes now so I aborted.
So I'm pretty sure this is the main problem but I have to check on what exactly they where trying to pull. Unfortunately everyone is on election leave of absence so I won't be able to do so until next week...

[szlamany]

Is your query in the OP accurate? I do not see the tblDWSession table - the one being updated - in the FROM table,table list? That's not how that works!

At any rate, you can easily break that up so that an inner SELECT pulls PRIMARY KEY's from that 1.5 million row table that need to be updated.

 UPDATE tblDWSession
    SET FWeek = CASE WHEN DATEDIFF(D, tblSYSCWeeks.CWStartDate, tblDWSession.SDate)/7+1 <= 0 THEN 1 ELSE DATEDIFF(D, tblSYSWeeks.CWStartDate, tblDWSession.SDate)/7+1 END
    From (Select PriKeyField
              FROM tblDWF, tblSYSWeeks 
              WHERE tblDWF.Code = tblDWSession.Code
                 AND tblDWF.FDate BETWEEN tblSYSWeeks.CWCStartDate AND tblSYSWeeks.CWEndDate
                 AND tblDWSession.IsProcessCompleted = 0 or tblDWSession.FWeek = 0
           ) as XYZ
    Left Join tblDWSession on tblDWSession.PriKeyField=XYZ.PriKeyField
;

[sapator]

I don't understand what the Prikeyfiled is and where the left join came up.

At any rate setting the parenthesis will make it run in 2 minutes, so it's negligible.

I now have the continuation of this issue as I open the next step.

The below will take 3 hours to complete! And I don't see some forgotten parenthesis here:

	UPDATE tblDWConcession 
	SET LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode,
		LoyaltyClubID = tblDWLoyaltyMember.LoyaltyClubID
	FROM tblDWPayment, tblDWLoyaltyMember 
	WHERE tblDWPayment.Code = tblDWConcession.Code
	  AND tblDWPayment.TransNumber = tblDWConcession.TransNumber
	  AND tblDWPayment.LoyaltyMemberCode IS NOT NULL
	  AND tblDWConcession.LoyaltyMemberCode IS NULL 
	  AND tblDWLoyaltyMember.LoyaltyMemberCode = tblDWPayment.LoyaltyMemberCode
      AND (tblDWConcession.IsProcessCompleted = 0 OR tblDWConcession.IsProcessCompleted IS NULL);

I haven't used to update this way, setting the tables in the FROM without any joins.
Will it make a difference?
I'm assuming it needs:

tblDWConcession C  inner join tblDWPayment P on P.Code = C.Code and C.transnumber = C.transnumber
inner join tblDWLoyaltyMember  L on L.LoyaltyMemberCode = P.LoyaltyMemberCode  AND P.Loyaltymembercode IS NOT NULL

?
Is that correct and will it make any difference?
I can also set some index if it will make any difference. I haven't currently looked at the indexes but I'm assuming there are non...Will have to get back on that.

Edit. OK it surprisingly have some on Code,Transnumber and IsProcessCompleted)

[Zvoni]

Index on IsProcessCompleted is non-sense, since it's a boolean.
I'd rather put Index on LoyaltyMemberCode in both tables

Rule of thumb: Put Index on any Field you use connecting two tables (Not Filter!)



You do realize that the only connection between tblLoyaltyMember and tblPayment is the Field LoyaltyMemberCode?

btw: Since your Update above translates to INNER JOIN the line checking IS NOT NULL is unneccessary.

That said: Transform it to a SELECT and check if it returns everything you need, and stop the time elapsed

[sapator]

I inserted the indexes on both LoyaltyMemberCode fields.
I have created a Select with a top 1 to see what goes on. The issue is that the first call on the "problematic" non inner join insert took 2 minutes but after that it takes 10 seconds , probably because it's cached to the SQL.
I'm waiting an hour or two to rerun it on the "fixed" inner join select with the indexes and I will also take a look at the execution plan and maybe post some issue of it here.

[szlamany]

You must UPDATE a table in the FROM and JOIN lists.

FROM tblDWPayment, tblDWLoyaltyMember

These are the two tables you reference.

How can you update tblDWConcession without it being in the list of tables pulling from? You are updating EVERY SINGLE row.

What is going on here SQL people?

[Zvoni]

You must UPDATE a table in the FROM and JOIN lists.

FROM tblDWPayment, tblDWLoyaltyMember

These are the two tables you reference.

How can you update tblDWConcession without it being in the list of tables pulling from? You are updating EVERY SINGLE row.

What is going on here SQL people?

Just looked it up: You're right
In my defence: Not a MS SQL-Guy

So, in a nutshell: You have to "repeat" the table (or alias it) you want to update in the FROM-Clause?

Kinda

UPDATE MyAlias 
SET MyAlias.SomeField=MyNew.SomeOtherField 
FROM MyTable As MyAlias 
INNER JOIN SomeOtherTable As MyNew 
ON MyNew.SomeID=MyAlias.SomeID

correct?

[sapator]

Again, not my query but I would think that this "pulls" the entire tables referenced. tblDWConcession is in the list on the update part not the from part.

[sapator]

Just looked it up: You're right
In my defence: Not a MS SQL-Guy

So, in a nutshell: You have to "repeat" the table (or alias it) you want to update in the FROM-Clause?

Kinda

UPDATE MyAlias 
SET MyAlias.SomeField=MyNew.SomeOtherField 
FROM MyTable As MyAlias 
INNER JOIN SomeOtherTable As MyNew 
ON MyNew.SomeField=MyAlias.SomeField

correct?

I would think not as this works without errors. Just extremely slow.
So probably (completely wrong and troublesome but)
FROM MyTable , SomeOtherTable
Where MyTable.SomeField = SomeOtherTable.SomeField

[sapator]

I'm now running the tables with the indexes and the inner join fixed.
Waiting to see what will happen.....

Edit: Next some time limit if it is again time consuming but then we might have the "getdate()" issue (aka 'somedate' quick, 'getdate' EXTREMELY slow)

[Zvoni]

Again, not my query but I would think that this "pulls" the entire tables referenced. tblDWConcession is in the list on the update part not the from part.

Yes, but sz points out that you have to repeat the table in the FROM-Clause.
If i understand it correctly, without it, it would generate a CROSS-Join (which you actually don't use).
But might be wrong

[szlamany]

@zvoni - yes - exactly.

I always start a query like this with a:

SELECT *
From UpdateTable
Left Join OtherTables... Where...

Then you change it to:

Update UpdateTable Set Field-...
From UpdateTable
Left Join...

If the SELECT is slow, the UPDATE will be SLOW. Fix the SELECT first.

Sometime, as in the example I posted - I pull PRIMARY KEY's that need UPDATE and then JOIN back to that population.

Queries are meant to be built a step at time - guess doing 100% stored procedures puts me in SSMS developing queries properly.

[sapator]

OK 6 minutes but I'm not sure if the cache has been dropped.
Unfortunately I would have to see that tomorrow but just for the heck of it I will run it now with the old query, although indexed...

[sapator]

Running the problematic query took 8 seconds but most probably it was cached as the new query runs at 8 seconds too.
The execution plan is exactly the same on every aspect. That leads me to believe the SQL is doing an optimization (probably inner joining).
I think indexing was a key point but again, tomorrow. If it does 6 minutes now instead of 3 hours then it's great.

[sapator]

OK.
So it runs 1 hours instead of 3. It's 66% faster but I would not call this optimal. I have indexed the LoyaltyMember and although it would make any difference I indexed it on the tblDWConcession. It didn't so I removed it.
So i don't know if it will go any faster. What I could do but they supposedly want to monitor every change is put and index on the dates and pull from a period and forth. I'll thinking of it, maybe I will just try it to see if it makes any difference as I have trouble on some queries on getdate() not initiating the index...

[sapator]

I'm going to set this as resolved.
What I did was put a time limit (indexed) getdate() - 730 . I really do not think anyone will update 2 years back .
So it runs on 35 minutes instead of 3 hours, I can live with that.
I don't understand why they do not impose a date limit to a huge database table. It's frustrating to go from department to department to find out why they would, if ever, update data from years ago.