jcolebrand

Cos he's only ever replacing it once on the root

@ErikDarling I had him switch to a substring, and it took it from ~5m to ~1.5m

He's always going to throw away (in this case) chars 36-97, so why not just substring the cast-to-string from the 98th character onward, and drop the first 97, and then prepend with the new string

I think he could potentially get faster with a substring since it's a fixed width

And learn.microsoft.com/en-us/sql/t-sql/xml/… says you can't replace the xmlns

they wanna convert <UserSubscriptionDefinition xmlns="http://schemas.datacontract.org/2004/07/blah.Data.Definitions" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1"> to <UserSubscriptionDefinition xmlns="http://schemas.datacontract.org/2004/07/blah.Contracts.Models" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1">

The URI only appears once per entry, so I wonder if an indexof would be faster, but it's XML on the type as-is, so you gotta suffer the cast, even with an indexof, right?

Aside from "I would just not do that" do you see any obvious "this would make it faster" parts?

Someone has decided that for purity reasons their C# has to change the XML namespace, and so they need to update some odd 300k rows in a table as they insert them into some other table, because ... idk

insert into messagesubscription.SubscriptionData
(SubscriptionId, SerializedUserSubscriptionDefinition)
SELECT ms.Id, CAST( REPLACE(CAST(d.SerializedUserSubscriptionDefinition AS NVARCHAR(MAX)),'http://schemas.datacontract.org/2004/07/blah.Data.Definitions','http://schemas.datacontract.org/2004/07/blah.Contracts.Models') AS XML)
FROM messagesubscription.Subscriptions ms
join event.Subscriptions es on ms.UserId = es.UserId and es.ProviderId = ms.ProviderId
join event.SubscriptionData d on es.Id = d.SubscriptionId

This is not my code, so don't shoot the messenger, I'm just curious how many red-flags y'all see on this?

@JohnK.N. and @HannahVernon the issue is specifically "we added a non null no default value column in v2.1, so v1.8 [still running in the same environment] now breaks"

(also I have evil chaos monkey plans to just randomly apply migrations as they commit them to the QA environment to force the QA environment to break when devs do stupid shit, and also to not-apply them to dev environments for some period of time so that they also have to live with the pain of "why didn't my thing work?" because production is rarely so forgiving as WOMM)

Keep in mind we now live in the age of docker and k8s so there can be many versions of a service in use at one time, v1.8, v2.2, v3.0.1-alpha, ya know?

Aside from face planting 300 times to learn how to skateboard, as it were, can you all think of any blogs that talk about how you need 3 version release cycles (maybe it makes sense to talk in longer cycles) for things like deleting old schema whatevers?

Because v1.8 of the app may still need to be running in some user-facing environment, and this is a model change for v2, so it won't be included in v1 codebases

Now, you all here understand exactly why that's a face-palm, I'm sure

They say "ah-ha, when I release v2, I can include a migration [ed: we use FluentMigrator as we are a C# shop, and so that's how they get applied in environments beyond local dev] that will create a column with a default value of 0 in an update statement, and make it not-null, so that we always have the value"

Say that my developers are working on v1 of their application + schema. They want to add some column like return_ratio and for v2 of their application they want that value so they can use it

I figured I can't really sort the googleterms required to magic this up, so I would try collective wisdom

I could use help finding what I'm sure is a book or blog that talks about how developers should approach database changes wrt services/apis that rely on them

Hallo consultants

I'm sure Paul will be happy to just nuke my comments :p

@mustaccio wrong way to go on this? dba.stackexchange.com/questions/313649/… He flagged with "what? why?" more or less. This is the part about SE that I'm not as happy with. He's still got a legitimate desire to understand the thing, but I agree, it doesn't fit the format.

@HannahVernon :D

We are checking with the dba team, it's possible they were running updates at the same time /facepalm surely not

This is a new table in a transacted create script and nothing else was running against that database as the web app was offline

@HannahVernon Right? And the last full backup was end of month

There are 4 consraints but it's a create table command, do those have to execute something long-ish when creating the table?

We had automation applying a migration (fluent migrator) and the migration timed out after like 90m (don't ask, we're gonna fix the time window on that) but the only thing it was doing was "create table, create index, update sql metadata for descriptions" and I'm just lost how this would've gotten into a locked state for 90m

Ok, kind of a dumb question, but can a log backup break an index create sript?

@McNets Thanks. I mostly asked that last night when it was later in the day and I was walking into a series of meetings, and this morning before coffee :D

Personally, I have too many threads open atm

Altho, now that I think about it more, I think I need to query a specific table to ensure the database exists and that my user has access to that table ...

Like, I think it just allocates on the threadpool

I want the simplest thing that works, cos I think that conn.open doesn't actually do anything

Yeah, I just am not sure if the native behavior is to actually open a connection. Guess it's time to go write some code to test and look at my netstats tbh

Worth asking on the site you reckon? Or is that more of a SO type Q?

And that's probably the fastest for testing connectivity?

using (var conn = SomeSqlConnection.Open()) {
  var cmd = conn.NewCommand()
  cmd.CommandText = "USE [database]"
  cmd.ExecuteNonReader()
}

@HannahVernon So effectively if I were to

I haven't dug into the bowels of the .net code enough to know for sure

I don't want to know that I have this role or that role, I want to know that I can connect at all (think, is the host offline)

Does sqlConnection.Open() verify connectivity to a database or should I issue a select 1 type query?

Random as heck TSQL connection question, .net oriented, could be worth posting on the site ...

@Vérace Former mods have done worse, sadly, across the network. oO

@J.D. Also just kind of worth noting, the concept of downvote-then-comment isn't something relegated to the mods, that's a pretty typical behavior on the entire network. That some may downvote without a comment is something that has been addressed over the past 10+ years on meta without a lot of success in finding a new way to conduct business. Can't really force the comment with the downvote. Some downvotes just don't need comments.

Is that at all hintable?

(If you think I'm signing a moderation agreement that's 103 pages long or that names specific people, then this joke was lost on you the reader and I can't help that my absurdity is believable)

:p