September 19, 2017 at 6:06 am
The MT* tables will likely only have values for Branch 3 and 0 (or they should do!). 0 being the live branch and 3 being the test. If they're on any other branch, someone in the sales/client services team has done something VERY wrong.
One thing that might be a big reason for the cause is that there are no foreign keys on the database. Unfortunately, as the data is a replication of a flat file system, it just doesn't bother building any kind of relationship between the tables. Thus, the engine will have no knowledge of the relationship between the BCM/BPY and the MT* tables. Could this mean that the optimiser is making the choice to start at the MTPL/MTEL, before going to the yyclient? As you can see in the FROM clause, almost all the Tables stem off the BPY (brpolicy), which in term stems off the BCM (yyclient).
If this is going to big a big factor, then entertaining trying to implement proper keys is going to be higher on the list. Annoyinging, Clientref@ and PolicyRef@ are both nullable (although, this cannot actually happen), which means I need to DROP any existing indexes and constraints, ALTER the column, and then recreate any indexes (and this needs to happen everytime the database is rebuilt (weekly)). The work involved is going to be a good couple of weeks i imagine, so not something i want to start entertaining if no one really thinks I'll get much gain.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 19, 2017 at 6:33 am
Thom A - Tuesday, September 19, 2017 6:06 AMThe MT* tables will likely only have values for Branch 3 and 0 (or they should do!). 0 being the live branch and 3 being the test. If they're on any other branch, someone in the sales/client services team has done something VERY wrong.One thing that might be a big reason for the cause is that there are no foreign keys on the database. Unfortunately, as the data is a replication of a flat file system, it just doesn't bother building any kind of relationship between the tables. Thus, the engine will have no knowledge of the relationship between the BCM/BPY and the MT* tables. Could this mean that the optimiser is making the choice to start at the MTPL/MTEL, before going to the yyclient? As you can see in the FROM clause, almost all the Tables stem off the BPY (brpolicy), which in term stems off the BCM (yyclient).
If this is going to big a big factor, then entertaining trying to implement proper keys is going to be higher on the list. Annoyinging, Clientref@ and PolicyRef@ are both nullable (although, this cannot actually happen), which means I need to DROP any existing indexes and constraints, ALTER the column, and then recreate any indexes (and this needs to happen everytime the database is rebuilt (weekly)). The work involved is going to be a good couple of weeks i imagine, so not something i want to start entertaining if no one really thinks I'll get much gain.
Ask yourself what the impact would be if they DID do something "very wrong". Would you want that wrong to show up in the results of this query? If not, then there's no reason to worry about changing the field to NOT NULL, as that might have consequences that might be rather hard to fix if it became necessary. Your solution of changing the query to use equal to 0 instead of not equal to 3 has produced a rather good result. The most important thing now is to document the reliance on this fact, for the sake of documentation of the overall business process.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 8:27 am
Know this has gone quiet, and I have a solution,but we're testing queries on 2017 now as we're planning to upgrade in the new year. Surprisingly, the new Cardinality Estimator was more than happy with the != 3; infact, the query ran faster on a lower spec 2017 evaluation server than it did on our "meaty" 2012 production server did with the "= 0" fix.
Seems that the Estimator in 2012 just doesn't like it, and (I assume) 2014 onwards copes better. Interesting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 6, 2017 at 9:02 am
Thom A - Friday, October 6, 2017 8:27 AMKnow this has gone quiet, and I have a solution,but we're testing queries on 2017 now as we're planning to upgrade in the new year. Surprisingly, the new Cardinality Estimator was more than happy with the != 3; infact, the query ran faster on a lower spec 2017 evaluation server than it did on our "meaty" 2012 production server did with the "= 0" fix.Seems that the Estimator in 2012 just doesn't like it, and (I assume) 2014 onwards copes better. Interesting.
Have you got an actual plan for the faster query, Thom?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2017 at 9:12 am
ChrisM@Work - Friday, October 6, 2017 9:02 AMHave you got an actual plan for the faster query, Thom?
Sure. It's considerably different; for starters, the query plan begins at the MTBQ and brpolicy and then does a Clustered Index Seek (instead of a scan) on the yyclient.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 6, 2017 at 10:32 am
Thom A - Friday, October 6, 2017 9:12 AMChrisM@Work - Friday, October 6, 2017 9:02 AMHave you got an actual plan for the faster query, Thom?Sure. It's considerably different; for starters, the query plan begins at the MTBQ and brpolicy and then does a Clustered Index Seek (instead of a scan) on the yyclient.
Thanks Thom.
Both the old plan and this new plan exhibit‘query timeout’. Simplify the query to eliminate this and you’ll likely get amuch better plan in any environment.
I’d suggest, for starters, combining a bunch of thoselookups into one temporary table, like this:
SELECT
BPY.B@, BPY.Ref@,
MTBI.Agp, MTBI.Indperiod, MTBI.Si, MTBI.Odbsi, MTBI.Compawaysi, MTBI.Terrlimit, MTBI.Detstocksi, MTBI.Edlimit, MTBI.Annmot, MTBI.Motgp,
MTMY.Anncarr,
MTIT.Maxlimit, MTIT.Numveh,
MTLI.Total,
MTEL.ELYN, MTEL.ERNEXEMPTYN, MTEL.ERN, MTEL.SUBCOYN, MTEL.SUBNAME1, MTEL.SUBNAME2, MTEL.SUBNAME3, MTEL.SUBERN1, MTEL.SUBERN2, MTEL.SUBERN3,
MTBQ.Grossturnover,
MTPL.Indlimitnum
INTO#mTables
FROM (
SELECT B@,PolRef@
FROM OpenGI..ic_brpolicy
GROUP BY B@,PolRef@
) BPY
INNER JOINOpenGI..ic_BD_MTBI MTBI ON BPY.B@ = MTBI.B@ AND BPY.PolRef@= MTBI.PolRef@
INNER JOINOpenGI..ic_BD_MTMY MTMY ON BPY.B@ = MTMY.B@ AND BPY.PolRef@= MTMY.PolRef@
INNER JOINOpenGi..ic_BD_MTIT MTIT ON BPY.B@ = MTIT.B@ AND BPY.PolRef@= MTIT.PolRef@
INNER JOINOpenGI..ic_BD_MTLI MTLI ON BPY.B@ = MTLI.B@ AND BPY.PolRef@= MTLI.PolRef@
INNER JOINOpenGI..ic_BD_MTEL MTEL ON BPY.B@ = MTEL.B@ AND BPY.PolRef@= MTEL.PolRef@
INNER JOINOpenGI..ic_BD_MTBQ MTBQ ON BPY.B@ = MTBQ.B@ AND BPY.PolRef@= MTBQ.PolRef@
INNER JOIN OpenGI..ic_BD_MTPLMTPL ON BPY.B@ = MTPL.B@ AND BPY.PolRef@= MTPL.PolRef@
-whichwould eliminate six table sources from the plan and get you closer to a pointwhere you can play with join hints.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2017 at 11:25 am
Thanks Chris, but the problem was resolved. One with changing a single part of the WHERE to = 0, rather than != 3, and then my most recent post an observation on the performance on SQL Server 2017 (and that the performance issues is non-existent).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply