Tag: Always

DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always

Schema Summary

A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables’ FKs are indexed.

This looks like a star configuration. TableA has fairly static personal info such as name, & DOB. The surrounding tables each have lists of items about the person in TableA that change or grow over time: for example, a person might have several emails, several addresses, several phone numbers, etc…

In the unusual event that I want to delete from TableA (test data that gets inserted during performance checks, for example), the FKs all have CASCADE DELETE to handle removing all subordinate data lists if they exist in any of the surrounding tables. I have three environments to play with: DEV, QA, and UAT (well, four if you count PROD, but “play” is not something I would want to do to PROD). DEV has about 27 million people in TableA with various counts upward of 30M in the surrounding tables. QA and UAT are only a few hundred thousand rows.

The Problem

The simple “delete from TableA where Id = @Id” takes < 1ms on DEV (the big one) and the execution plan looks fine, lots of tiny thin lines and all index seeks… but here’s the rub: infrequently on DEV, and ALWAYS on QA and UAT, the simple delete takes about 1 second and the plan shows almost all the indexes are being scanned, with big fat lines showing the entire row counts.

Observations

The delete statement is issued by Entity Framework Core running inside an API so I have limited capability to mess with it (making it into a stored procedure, index hinting, using a different predicate, and other ideas…)

Despite all three environments being identical (same script created all three environments), nothing I have done so far has improved QA and UAT, but DEV is usually fast.

When DEV becomes slow, it remains slow until “something” happens. I haven’t figured out what the “something” is, but when it occurs, the performance reverts to fast again and remains that way for days.

If I catch DEV at a slow time, and use SSMS to manually run a delete statement, the plan is fast (<1ms); but the deletes coming from the API use a slow plan (1s). Entity Framework is (as best I can tell) using sp_executesql to run a parameterized “delete from tableA where Id = @Id”. The manual query is “DELETE FROM TableA WHERE Id = 123456789”

The row being deleted is always a recently-added row, meaning that the Id is right at the “top” and probably not within the range of the index statistics (although I speak from a position of profound ignorance on that topic and probably have my wires crossed…)

What I have tried so far

Reading up on FK cascade delete issues, it seems all the FKs need to be indexed, so I did that.

Rebuild (not just Reorg) every index.

Selectively delete the bad plans from the plan cache using DBCC FREEPROCCACHE (plan handle)

Running the excellent tools from Brent Ozar got me checking that the FKs were all is_not_trusted = 0

Looked at these (and other) previous StackExchange questions:1, 2, 3, 4

Of those, I suspect that the last one, with a description of how the cardinality estimator gets confused, might be pointing to the source of the problem, but I need help figuring out what to do next…

The plan shot below (from ssms) shows the slow plan: some of the FK indexes are being scanned (but not all) and there is an excessive memory grant. The fast plan would show all index seeks. The whole plan is at ShowMyPlan Link

I hope someone can point out what I have missed, or what I can investigate further.

Thanks!

enter image description here

Bad Plan

Can there exist Tables with no Non-Prime Attributes? If so, are they always normalised to 3NF?


Can there exist a table with no non-prime attributes?

Here is an example of a table which I believe has no non-prime attributes:

I came to this conclusion because the list of attributes in the relation is [id, tag_desc_id, name] and the list of candidate keys is [{id}, {tag_desc_id, name}]. There are no attributes in the list of attributes that are in none of the candidate keys. Thus, there are no non-prime attributes.

Is my reasoning correct?

If so, is it normalised to 3NF?

The criteria for 3NF is:

  • 2NF

  • Every non-prime attribute of R is non-transitively dependent on every key of R

There are no non-prime attributes, thus 3NF is satisfied

The criteria for 2NF is:

  • 1NF

  • Every non-prime attribute is (…)

Again, there are no non-prime attributes.

Is this true generally?

For any table, assuming the table has a primary key id as above (and so 1NF is satisfied), and the table has no non-prime attributes, is it always the case that the table is normalised to the third normal form (3NF)?

Is it safe to assume that my computer’s clock will always be synced with actual time within the second or a few seconds at the worst?

Years ago, I was running a service where the moderators were able to do various actions with massive privacy implications if the accounts or contributions were less than a short period of time. I did this by checking the timestamp against the current Unix epoch, allowing for X hours/days. Normally, this worked well.

One day, the server where this was hosted on had been “knocked offline” in the data centre where I was renting it, according to the hosting company. When it came back on again, its clock had been reset to the factory default, which was many years back.

This resulted in all my moderators potentially being able to see every single account’s history and contributions in my service until I came back and noticed the wrong time (which I might not even have done!) and re-synced it. After that, I hardcoded a timestamp into the code which the current time had to be more than or else it would trigger “offline mode”, to avoid any potential disasters like this in the future. I also set up some kind of automatic timekeeping mechanism (in FreeBSD).

You’d think that by now, not only would every single computer be always auto-synced by default with tons of fallback mechanisms to never, ever be in a situation where the clock isn’t perfectly synced with “actual time”, at least down to the second, if not more accurately; it would be impossible or extremely difficult to set the clock to anything but the current actual time, even if you go out of your way to do it.

I can’t remember my Windows computer ever having been out of time for the last “many years”. However, I do important logging of events in my system running on it. Should I just assume that the OS can keep the time at all times? Or should I use some kind of time-syncing service myself? Like some free HTTPS API, where I make a lookup every minute and force the system clock to me whatever it reports? Should I just leave it be and assume that this is “taken care of”/solved?

Experiments Always Fail

I started using scrapebox 2 weeks ago. nothing I have done has worked except the keyword scraper.

I was trying to find an available domain through domainlookup. and an error message indicates a timed connection error. can you help what should i do?
Sleepy
https://prnt.sc/qh27vx
https://prnt.sc/qh284u

What are the balance ramifications of always preventing creatures from taking reactions before their first turn?

I’ve recently been toying with some rule modifications to 5e’s Initiative rules in an attempt to resolve the rule/reality inconsistency where a player “gets the jump” on a monster, but the monster is able to act first via a Reaction.

The rule changes I’m considering is the following:

A creature cannot use its Reaction before the start of its first turn.

Note that this is not a modification to Surprise rules. This rule would apply any time that Initiative is rolled.

Clearly this will add greater emphasis on rolling well for Initiative. However, for the sake of completeness, I’d like to know if there are there any larger balance ramifications that might occur under this change?

For context: the following is the intent of the change:

  • Deliver on the thematic of being the “first to draw” in combat; i.e., support the intuitive understanding that Initiative defines which creatures are quickest to act.
  • Deliver on my players’ desire to feel like they can surprise their opponents without needing to roll for stealth.

Why do I always have something missing in my understanding of topics which always lead me to solve problems incorrectly?

I am computer science masters student, i come from background of engineering and not cs, my problem is whenever i have a problem set, a programming task or an exam. i always try hard to understand the question and think for the right answers, but i usually either get stuck or have a wrong answer, and when i seek help i figure out i wasn’t completely understanding the topic of the question itself, missig some part of the information in or even having a wrong understanding to some parts.
So my question is, how i can approach a computer science topic “for e.g. operating systems” and have a good understanding with the right depth to have a better comprehension and to be able perform better at programming tasks and exams.

Rotate object always at same speed on screen, no matter camera distance?

I am rotating a globe like XCOM’s hologlobe,

enter image description here

I rotate it using Quaternion.RotateTowards(Quaternion from, Quaternion to, float maxDegreesDelta).

I found a good value for maxDegreesDelta, in my case it is 5.0f.

There is a limit on how close or far the camera can be, let’s assume clos is 1.0f and far is 2.0f.

I want to be able to zoom into the globe, but obviously when I do, it rotates a bit too fast then.

When zoomed out, rotation speed is satisfying:

enter image description here

When zoomed in, rotation is too fast, making it more difficult to manipulate:

enter image description here

And the problem is even more evident as game view size gets bigger, i.e. fullscreen.

Using Mathf.Lerp and Mathf.InverseLerp, I’ve tried to make maxDegreesDelta and mouse delta proportional to the distance the camera is but it’s hardly convincing.

Note: I rotate the globe, not the camera.

Question:

How can I ensure object rotates at same speed on screen, no matter how close or far camera is ?