@AndriyM Ok. Thank you for the pointers.

@AndriyM A space delimited string is an option, I guess. But not very elegant.

And doesn't really use the db structure, either.

@AndriyM This answer looks reasonable.

But I don't understand how the Primary Key for the users_tags table is supposed to work. The code says:

PK(user_id,tag_id)

What does that mean?

Oh, maybe make both those columns together the primary key?

BTW, does SQLite have full-fledged support for Foreign Keys?

@FaheemMitha Pretty much yes

sqlite.org/foreignkeys.html

There are some limitations at the end of the document but I think most of what you need will be supported

@TomV-TeamMonica Great. Thank you. SQLite is limited in many ways. I was not sure if that was one of them.

Morning

Morning

Is there a way to force the query estimator of SQL Server into using a specific order of joins?

I've (still) got this query that executes differently on SQL Server 2008 R2 (36 seconds) than it does on SQL Server 2016 (5 minutes and 26 seconds) even though the statistics are up to date.

I want to persuade SQL Server 2016 to use a different execution plan.

force the plan? suuuuuper easy in query store

🤷‍♂️i mean... just in case you hadn't heard the good word of our lord and saviour Query Store

(Numnber of records in tables)
object1 = 50'360'161 ;
stamm1 = 308'993 ;
sdrel = 60'542'434 ;
register2 = 28'780'386

Join Order 2008 R2:
stamm,
sdrel
object1
register2

Join Order 2016 SP2 CU11:
stamm,
sdrel,
register2,
object1

@PeterVandivier Now the order for 2008 R2 has never occurred in SQL Server 2016. So I don't think QueryStore would help.

ah, in that case no :(

but you're wandering into areas i couldn't really comment on productively as well :/

sry

sounds like a question for main

unless you're in STMGR 2.0, in which case those guys eat these kind of questions for breakfast

No frets. I have a case open with Microsoft and they wrote:

> Regarding the case. I was verifying all the case notes that we have until now with the Escalation team, and it is considered a very interesting performance issue indeed

does changing the database compatibility level clear the plan cache? (i would assume yes)

@PeterVandivier Been there, done that

naturally

Even went so far as to have the Compat_level at 140 and the query optimizer on LEGACY_CARDINALITY_ESTIMATOR = 1 (Database Scoped Configuration)

So it should essentially use the legacy cardinality estimator while benefiting from the new SQL Server 2016 features. :-)

just 'cause i'm thinking about it now... could you try to induce to different plans (on a restored copy of course) by monkeying with the underlying data / rowcounts and updating the stats?

just for funsies

just thinking that if you could get a stats setup where a good plan is induced on compat 140, you could use those to highjack the actual stats and try to induce the good plan for the actual table and pin it 🤷‍♂️

#badIdeaJeans

This might work: Forcing Execution Plan

Problem is however, I would have to have the actual statement with all white-spaces and SET options identical, otherwise it's not going to work.

good luck! :D

The Good (2008 R2)

The Bad and The Ugly (SQL 2016 SP2 CU11)

Note the number of records returned after each JOIN in the execution plans

oof

I guess if I could persuade SQL Server Query Optimizer to do the first join with the OBJECT1 table, then the amount of time required would be drastically reduced.

I'd have only 336 records passed on after the first JOIN.

maybe you could share the actual query

just saying

select distinct d.id
from object1 d, stamm1 o, sdrel x, register2 r
where x.stamm_id = o.id   -- sdrel = stamm1 (1)
and x.object_id = d.id    -- sdrel = object1 (2)
and x.register=r.id       -- sdrel = register (3)
and ((o.feld22 like '%SOMEVALUE%') and (((o.feld22 like '%SOMEVALUE%' and o.feld19 = 'DEF') or o.feld19 = 'XXX')))
and (((d.feld3 like 'Something we are looking for %' and (d.zahl17=0 or d.zahl17 is null)) or (d.feld3 like 'Something else%' and (d.zahl17=0 or d.zahl17 is null)) or (d.feld3 like 'Something else back%' and (d.zahl17=0 or d.zahl17 is null))))

Had to change some values.

I know that %VALUE% is bad, but it is the same on SQL Server 2008 and 2016.

@Johnakahot2use so if you split that condition and use a UNION ALL, does it improve performance?

Of course if that helps and you want to use in all 4 tables, you'll have a monster query with a union of 16 subqueries.

I can't change the query. That's the thing.

I want to try and convince SQL 2016 to execute the query as it used to do in SQL 2008 R2.

Adding a partial index on (id) WHERE (deleted = 0 OR deleted IS NULL) ?

@ypercubeᵀᴹ Is that the same as a filtered index?

yes

The databases are identiclal as far as that goes. Same indexes, similar statistics.

...and (d.deleted=0 /*or d.deleted is NULL*/) and (x.deleted=0 /*or x.deleted is NULL*/) and (o.deleted=0 /*or o.deleted is NULL*/) and (r.deleted=0 /*or r.deleted is NULL*/)

Commented out some of the ugly.

I'll try the filtered index thingy.

@Johnakahot2use FORCE ORDER ?

@ypercubeᵀᴹ Would probably help to display what Microsoft could do better, but doesn't help me to pin the "optimal" plan for that statement. Thanks :-)

@Johnakahot2use I've used that before, and his query looks like an AX query, so his displayed method won't work IIRC

Or at least he's leaving some stuff out of his post

think about force order with non-ansi joins is making my head hurt

although i guess they're probably just read in the order listed

but i wanted to take the opportunity to complain about it

hai

@PeterVandivier WARNING: No JOIN predicate

@PeterVandivier feel free to try it out and let us know

I get the same execution plan as on SQL Server 2008 if I put the tables in the right order and use `WITH (FORCE ORDER)' option.

Duration 36 seconds (instead of 5:23)

@Johnakahot2use The dta indexes in that plan are quite telling

@TomV-TeamMonica I know. Not mine.

_dta_ and force order

I hope your successor isn't an axe wielding psycho

eyyyyyy

Can't implement WITH (FORCE ORDER), so I don't care that much. Going to have to wait for MS feedback.

try adding NOLOCK everywhere, see if that helps

is that possible using a instance wide trigger?

I'm already doing RCSI

I can't modify any statements. Only server/instance/database settings and add/remove additional indexes.

@Johnakahot2use i was being sarcastic :p

@Johnakahot2use SET FORCEPLAN ON ?

maybe there is a trace flag for that

I have a whole bag of bad ideas since working with the limitations of Dynamics

@PeterVandivier me too :-P

@TomV-TeamMonica Would be only half of the solution as I can't switch the order of the tables in the SELECT statement.

@Johnakahot2use have you tried by burning the server? (joking obviously)

@McNets Virtual server. ahemm

Ah you mean as a way of appeasing the SQL Gods?

@Johnakahot2use well, like mine...

unless a legacy 2005, still physical..

@Johnakahot2use a kind of sacrifice, yeah

pvandivier=# select * from bar.bing;
 a
---
(0 rows)

pvandivier=# \d
             List of relations
 Schema |   Name    |   Type   |   Owner
--------+-----------+----------+------------
 foo    | bing      | table    | pvandivier
 public | bar       | table    | pvandivier
 public | foo       | table    | pvandivier
 public | foo_i_seq | sequence | pvandivier
(4 rows)

pvandivier=# show search_path ;
        search_path
---------------------------
 "$user", public, foo, bar
(1 row)

pvandivier=#

is it obvious why i can't see bar.bing on \d?

weird... pg_table_is_visible(c.oid) is false for that table 🤔

but i just copy-pasta'd the create script from foo.bing

creating another table in the bar schema is visible

is it possible "visible" in the context of pg_table_is_visible() means "the first one you see when you don't specify a schema"?

suspicion confirmed

github.com/postgres/postgres/blob/…

you guys ever write something and realize "Future me is going to be so pissed when he has to support this?"

All the time. And it's perfectly fine, if you don't keep this thought to yourself but write it down, as a comment, there and then.

Now just hope that future you (or that axe-wielding psycho who will be your successor) will understand why.