« first day (2055 days earlier)      last day (39 days later) » 
00:00 - 20:0020:00 - 00:00

12:00 AM
RELOAD!
[Minesweeper] Games Played: 62, Bombs Used: 32, Moves Performed: 7844, New Users: 8
RELOAD!
[rubberduck-vba/Rubberduck] 24 commits. 11231 additions. 6024 deletions. 9 issue comments
[Zomis/Server2] 1 commits. 194 additions. 21 deletions
 
 
9 hours later…
9:32 AM
> I tried a repair and that makes no difference.

I'm always careful to install the office products in the right order as have come across issues with that before. However, I'm not sure about the VB6 and Office 2010 order.

Currently I'm not in a position to be able to try a reinstall for a while.
> I thought I would try VBA, so started an Excel which also crashes (no VB6).

`2020-01-21 09:27:30.9972;DEBUG-2.5.0.5305;Rubberduck.Common.LogLevelHelper;
Rubberduck version 2.5.0.5305 loading:
Operating System: Microsoft Windows NT 6.2.9200.0 x64
Host Product: Microsoft Office 2010 x86
Host Version: 14.0.7244.5000
Host Executable: EXCEL.EXE;
2020-01-21 09:27:31.2355;INFO-2.5.0.5305;Rubberduck.UI.Command.VersionCheckCommand;Executing version check...;
2020-01-21 09:27:32.0853;DEBU
 
 
4 hours later…
1:55 PM
anyone seen excel turn dumb and insisting that you press enter after you paste?
 
2:20 PM
0
Q: What is the fastest way to determine if a table spans two pages?

jclasleyThe initial problem that led me to learn VBA is as follows: You have a table that can be up to 10,000 rows (several hundred pages) long in a Word document. The table has a title in the form of a paragraph above the first row. This title is styled such that it links to a Table of Contents (Style ...

 
@this hm? Excel turning dumb, yes; insisting I press enter after pasting, nope
 
it's probably the chrome remote desktop but I wasn't sure if it was some weird Excel setting. Everytime I try to paste, it shows "Select the destination and either press ENTER or paste" to complete (?) the paste operation. Confusing AF
at other point, it said "There was a problem with the clipboard but you still can paste." I'm not making this up.
 
Have seen the 2nd message before.
 
2:41 PM
Reading more about Docker, @this, it just shares the kernel. So you can run a CentOS image on an Ubuntu installation.
And the Linux kernel is backward compatible, so even though CentOS 7.2 uses kernel 3.10 and Ubuntu 16.04 uses 4.4, it still works.
Also, Postgres is annoying.
You run create table FooBar(...), and it does create table foobar(...) (all lowercase).
But then Entity Framework apparently isn't case-insensitive, so it doesn't work.
But then when I run the EF migration tool instead of manually creating the tables, lo and behold--the tables are named correctly.
 
@Hosch250 I'm not 100% sure but I think it's to do with the filesystem
on a case-sensitive filesystem which Linux usually are, that would be a PITA
 
Ah.
I've got basically everything working now except one thing.
 
on MySQL on Linux, you could get errors with SELECT * FROM foo; if the file name is named Foo
 
Pressing my Super key doesn't open my application list--it opens the activities panel.
 
I think PostgreSQL simply lcase everything to avoid that but obviously that confuses the tooling.
(but don't quote me - it's been a while since I toyed with PostgreSQL)
 
2:46 PM
I've found methods that apparently work on 16.04, but not on 18.04, or whatever I'm currently on.
And yes, I've played around with the Gnome Tweaks and dconf tools.
 
3:07 PM
@Vogel612 Just curious. We can run CentOS on Ubuntu because Ubuntu is newer. But could we run Ubuntu on CentOS?
What if Ubuntu asks for the kernel to do something that the CentOS kernel doesn't provide?
 
huh?
 
In a Docker image.
 
docker is responsible for the abstraction
if you run an Ubuntu docker image on docker on CentOS, then docker spins up a virtual kernel for the image
at least in theory
 
That's not what I'm reading.
 
hmm ...
 
3:09 PM
I'm reading that the kernel is shared, but everything else is isolated.
That's why Docker is lighter than a normal VM.
 
if kernel's shared, that impiles that you must have compatible system DLLs or it'll blow to hell
 
Yep. The Linux kernel is backwards comaptible.
 
but not necessarily forward, I think
 
Correct.
Hence my question.
 
that'd require Linus to be prescient.
 
3:20 PM
Or at least not ship until he's done implementing :P
 
3:45 PM
@this can confirm
 
@MathieuGuindon the fun thing, though is that it doesn't exist if it's MySQL on Windows (because case insensitive filesystem)
 
 
1 hour later…
5:13 PM
Hope everyone's having a good week. I am.
 
> @MDoerner When I changed the instancing I tested it both ways. 1) Only changed the instancing, nothing else. 2) Changed instancing as well as a class members contents. Both ways resulted in a non-accurate instancing being shown in the extract interface dialog. The only way I've found to have instancing be accurate is by following the steps I wrote out above.
 
5:41 PM
@this so, I commented-out the insert and left the select in place; running the query I see "batches" of records slowly coming in into the results view, with each "batch" taking longer than the previous one.. any ideas? what could cause select to yield results by the drop like this?
I suppose I can wait for the results to come in (that will take a while) and then finally view the actual execution plan (hopefully it has answers the estimated plan doesn't)
 
sounds like it's doing the painter's algorithm one way or other
 
yeah... I just can't see why it's being stupid :(
presumably the inner joins are being done with nested loops instead of hash lookups
18 minutes, <50K rows so far
 
did iTVF change since the last post?
 
nope
also I'm dumping it into a @table, it's not being invoked as part of the query
60K rows
 
lemme look at it again... (if I can find it)
 
5:55 PM
that bottleneck is why the whole thing takes 50 minutes to run
		declare @issuedBuckets table (
			 SourceRowId int not null
			,SizeIndex int not null
			,Units int not null
			,primary key (SourceRowId,SizeIndex)
		);
		insert into @issuedBuckets
		select * from dbo.NormalizeSizes(@bucketsIssued,4)
		; -- 281597 rows
 
^^ that's where I'm bucketizing the sizes ...runs pretty much instantly
@this oh, actually I did change it - made the indexes 0-based so that I don't need to do any math in the subsequent join
78.5K, 25mins
saw something on SO about nested queries in the select clause, I don't think it applies here :(
		--insert into @issuedUnits
		select
			 OrderDetailId = dw._Id
			,SizeId = sz._Id
			,Units = issued.Units
		from Staging.dbo.YYF641SAV f641
			inner join @issuedBuckets issued on f641.id = issued.SourceRowId
			inner join Staging.dbo.YYF391 f391
				on f641.f1 = f391.f1
				--and f641.f2 = f391.f31 -- f31 is empty & cannot be used for this join.
				and f641.f3 = f391.f35
				and f641.f5 = f391.f42
				and f641.f12 = f391.f5
				and f641.f13 = f391.f6
			inner join NDS.dwd.FiscalCalendars t on f391.OrderDate = t.CalendarDate and t.FiscalYear >= @cutoffYear
 
6:10 PM
i'd be more concerned with the # of joins
joins are basically exponential - n * m * l * o * p = whole lotta of rows
More especially if they can't push down the predicates.
 
on error resume next... what are some legit uses? Oftentimes it's just due to laziness, but I feel that if we have set-up enuogh error handling for everything we can plan for, but would still want to add that as a "catch all" in case something in the future arises. I feel like that's a legit reason, but could just be a bum.
 
I do usually do a small OERN & OEG0 block
 
^^ OERN is legit in any scope that's invoking something something that has a reason to fail
 
For Each foo in bar
  On Error Resume Next
  foo.DoSomethingThatMightFail
  On Error Go 0
Next
 
problems happen when that scope is too large and has too many reasons to fail
 
6:14 PM
That's a typical case.
 
i've used it in functions when i'm intending to cause errors, then catch that error to produce an output (lazy use of application.match, which i could "technically" set-up a function to loop for)
 
Yes, agree with Mat. If you OERN a whole block, that most likely need to be its own function.
 
i'm thinking OERN for a whole subroutine... a legit use for that (not just a block of it)
 
well, sure?
 
also, thanks for the short hand... would not have thought that would be recognizable, but here we are
 
6:16 PM
@Cyril #ItDepends
 
Public Function TableExists(TableName As String) As Boolean
  On Error Resume Next
  TableExists = Len(CurrentDb.TableDefs(TableName))
End Function
 
same boat... i didn't put a OEG0 as it terminates with the subroutine (implicit... sue me)
 
@Cyril well.... it can cause weirdness.
 
^ you might want to reconsider that
 
You end up leaking the Err state
 
6:17 PM
file under "things that shouldn't happen but actually do"
 
Public Sub DoIt()
  Derp
  Debug.Print Err.Number, Err.Description
End Sub

Public Sub Derp
  On Error Resume Next
  Debug.Print 1 / 0
End Sub
 
copy
 
^ if you run the code, you can see how Err leaks out reporting an "error" in the DoIt procedure
 
128K rows, 45 mins
(still running)
 
i hear you; wasn't thinking abotu affecting other functions/subs outside of the designed... added in the oeg0
 
6:19 PM
@MathieuGuindon Do you have enough RAM?
 
@this on the SQL Server?
 
Yeah
Thinking if you can fit it all in RAM, memory optimized table might be a good candidate.
 
what's "enough RAM" anyway?
hm
 
is the hang-up on the inner join Staging.dbo.YYF391 f391 ?
 
What you'll probably want to do is create a natively compiled stored procedure that takes the crappy source, then denormalize it into the memory table. You may be able to do the inserts as a single scan, avoiding the painter's algorithm
 
6:21 PM
18.3/26GB as per task manager :)
@Cyril no idea, still waiting for it to complete to I can get the actual execution plan
144K, 48mins
 
So yeah, I think you might wanna to look into doing it all in the memory. Cut out the I/O access
since you must scan the table; there's no way around it.
might as well scan it in the memory so you can come out with a normalized scheme to then use in later queries.
 
how do I nudge SQL Server to do that?
 
well you have to write natively compiled stored procedure & create memory table.
Bad news: I never had an actual need to do it myself so I'd have to tell you to google up articles on how to create the memory table & author a natively compiled procedure
 
@MathieuGuindon copy; wasn't sure if you were able to try with a truncated version of the db and extrapolate for durations (not at the same time as your existing fun)
 
would be nice if query plan shows up and says "missing index, 99% impact" or something like that
 
6:26 PM
I would try with truncation but not too truncated or the query plan may change
keeping 10% of the data might suffice, as long it's a fairly big number
 
eh, at 52 minutes it's going to finish any time now
 
i mean... 128k lines, if it's got 15k lines, that's sufficient for longevity... as i typed that you said 10% so we're on the same page
 
53
150,227 rows so far
 
I don't expect query plan to change between 15K and 150K
unless the shape is wildly different
(llke, the first 10% is all about reds and 90% is all about blues. That would be crappy representation of the data overall)
 
so, troubleshooting with one specific order ID was a bad idea
 
6:28 PM
it's fine for developing but yes, it won't tell you how well it performs in bulk.
 
160K / 55 mins
damn it's painful
 
feeling agonized now?
 
How many rows total?
 
@Hosch250 good question, so far I've always killed it before it could complete
 
That's probably like a month worth of work for the painter.
 
6:30 PM
Also, if you are returning 160k+ rows, I bet a great chunk of the problem is IO.
 
@this Why not wrap with OERN; For Each ... ; foo.DoSomethingThatMightFail; Next; OEG0;? That way you're not turning error handling on and off as the collection is iterated through?
 
That's going to be eating a LOT of bandwidth.
 
I just want to know why it's "batching" the results, this is nuts
@issuedBuckets (the normalized data) is 281597 rows
 
@IvenBach you could do that, too. There's times where I need to collect the error for individual members so it has to be inside the loop. But you're right for that particular example, where there's no distinction, we could just put it outside the loop.
The other reason is that if the loop body does more than few things and only one method may fail, then we want the OERN's scope to be limited to that part, not the entire loop body, which may end up hiding other errors.
 
@IvenBach I would argue that the loop body constitutes a scope in its own right
 
6:32 PM
Noted about individual usage. Glanced at your example and knee-jerk reacted.
 
basically OERN/OEG0 has no reason to encompass more than it should
ever
 
@MathieuGuindon if the body has a whole bunch of methods that could go haywire, then yes, it should be its own function/procedure.
but if it's only one method, seems pointless
 
6:43 PM
202K, 70 minutes
fml
@ticker SO is turning into Yahoo! answers
> When I start a new subroutine, do I create a new module for it, or do I create the new sub under the last one? If I put it under the last one, do I do anything special to signal that this is a new sub?
i.e. "haven't bothered trying anything whatsoever, haven't bothered reading anything about it, but please spoonfeed me everything there is to know about procedures in VBA"
 
@MathieuGuindon Yahoo! Overflow
 
"YO" for short. this might work.
 
BTW, I find the name eminently punchable
 
hi, I work for a company that's still stuck in 90s with pogs and parachute pants!
 
6:47 PM
might have up to another 70K left
 
That sounds painful.
 
wait, was yahoo a search engine in pre-Google era?
 
oh my goodness. I must have blocked out the memory.
It's that obnoxious.
 
6:52 PM
wait a second... yahoo answers is still a thing? i thought that got shutdown because of child pornography
 
OMG IT COMPLETED
221445 rows in 1:14:03
 
CONGRATULATIONS. you found a way to complete it... now to optimize!
 
^
wouldn't be surprised if the execution plan doesn't report a missing index, though.
 
it's weird to say that back to mug, considering that's about where he leaves me haha
 
ok wtf, it costs the insert into @bucketsIssued at 78%
 
6:54 PM
@this Does it report a missing index? I've only noticed it have inordinate amount of times on scans.
@MathieuGuindon Is there an index on that?
 
> Clustered index insert: 57%
 
Might it be trying to add new items to an index?
 
well, yes?
 
I mean, if it has to change the index a ton, that could be adding a ton of overhead.
 
so it's "batching" the output because ....it starts the select before the insert finished??
 
6:55 PM
I'd maybe leave the index off until after the insert is finished, if you can, then add it afterward so it only has to process it once?
 
if I don't run the select, and only run up to this insert into @bucketsIssued part, everything completes within seconds
 
What's the particular insert statement?
 
		declare @issuedBuckets table (
			 SourceRowId int not null
			,SizeIndex int not null
			,Units int not null
			,primary key (SourceRowId,SizeIndex)
		);
		insert into @issuedBuckets
		select * from dbo.NormalizeSizes(@bucketsIssued,4)
		; -- 281597 rows
^ that's pretty much instant
 
@Hosch250 funny, that was the suggestion yesterday - i've seen people claim that adding index after insert works.
wiat, no, not yesterday. probably last friday
 
and if I run the select after that, then supposedly that's where all the processing is occurring
I'm stumped
 
6:57 PM
@this Yes, I've read that myself. Because if you do it afterward, it's a single scan and reorder.
 
@MathieuGuindon probably because it has to know how to insert the data.
 
Whereas before, it has to scan and reorder for each item.
@MathieuGuindon What's the select after that?
 
@Hosch250 see, my doubt is that if it's an empty table (and by definition, it'll always be), I don't think there's much difference.
 
		--insert into @issuedUnits
		select
			 OrderDetailId = dw._Id
			,SizeId = sz._Id
			,Units = issued.Units
		from Staging.dbo.YYF641SAV f641
			inner join @issuedBuckets issued on f641.id = issued.SourceRowId
			inner join Staging.dbo.YYF391 f391
				on f641.f1 = f391.f1
				--and f641.f2 = f391.f31 -- f31 is empty & cannot be used for this join.
				and f641.f3 = f391.f35
				and f641.f5 = f391.f42
				and f641.f12 = f391.f5
				and f641.f13 = f391.f6
			inner join NDS.dwd.FiscalCalendars t on f391.OrderDate = t.CalendarDate and t.FiscalYear >= @cutoffYear
 
@this Hmm. But if the select doesn't insert the batch in one blow, but item by item?
 
6:59 PM
there's another step in-between pupolating @cutJobs, which is also pretty much instant
so, going to try with a #table instead
 
OK, try removing the joins.
Figure out if there's one or two particular joins that slows it down.
 
@Hosch250 I don't see how you'd avoid that.
 
Unless you are just joining on ids.
 
I am, except where I simply can't
 
7:01 PM
I mean, index is a b-tree; unless your table is already sorted, it'll have to be inserted one by one
@MathieuGuindon hey, is the select in the insert sorting the output?
 
no
should it?
 
no, I don't think we want a sort operator.
Sort is basically "stop the world" operation. Sucky.
but it also means that the index insert are going to be done one by one.
 
all the joins are nested loops (0%) btw
 
And what's @issuedUnits?
 
except the @issuedBuckets join, which is a hash match apparently using tempdb
 
7:04 PM
@MathieuGuindon and make sure you work with only 10%, not the whole data. Don't want to wait 1 hour + for it to complete. ;-)
 
		declare @issuedUnits table (
			 OrderDetailId int not null
			,SizeId int not null
			,Units int not null
			,primary key (OrderDetailId,SizeId)
		);
@this lol yeah
 
Why the complex key?
 
because I need to join with these keys?
 
Make one the primary key and the other a plain index?
 
wouldn't work if both columns have duplicates within
 
7:05 PM
I figured joining on a clustered index would be more efficient than a plain nonclustered index
 
hmm. @MathieuGuindon confirming was it clustered originally?
 
@MathieuGuindon It isn't necessarily.
 
@this the 75% cost clustered index insert tells me it's clustered :)
 
If anything, it could possibly slow it down because it's sorted by the combination of the two columns together instead of doing a simple binary search.
 
		declare @issuedUnits table (
			 OrderDetailId int not null
			,SizeId int not null
			,Units int not null
			,unique (OrderDetailId,SizeId)
		);
 
7:07 PM
@MathieuGuindon into the @issuedUnits, right?
 
Unless those columns together are actually the key, I'd just do a one-column key and normal indexes on everything else.
 
@this yeah
@Hosch250 a one-column key would be useless
 
OK.
Basically, what I'm trying to say is make that table be in 3rd NF.
 
I have order details with 20 size buckets in a varchar(80) column that I'm normalizing
the goal is to get a SizeId for each of these 20 buckets
with the quantity in the varchar(80) in the correct bucket
in order to get the size ID I need to get the ID for the size range, using the size range code in the order details table
 
16
Q: Why does SQL Server work faster when you index a table after filling it?

Dan BlairI have a sproc that puts 750K records into a temp table through a query as one of its first actions. If I create indexes on the temp table before filling it, the item takes about twice as long to run compared to when I index after filling the table. (The index is an integer in a single column, ...

 
7:12 PM
nice
so, dump @table and use #table, insert without index, create the unique index, proceed to select
 
yeah that'd be hte next step before the more drastic change to memory optimized table.
 
oh, and pray there aren't any dupes (hit a PK violation yesterday, haven't figured that one out yet)
 
though TBH I'm still dubious about that. It just doesn't make sense to me.
but hey I don't make sense so it works out?
 
well if 75% of that hour was the clustered index insert...
 
I know, I'm just not convinced that you avoid the work by indexing it after inserts because you're going to resort the data.
One poster there cites statistics as a reason which seems plausible but #NeedEvidence
 
7:18 PM
HOLY SHH
put the cutoff year at 2018 instead of 2014, change @ to #, removed the clustered index / moved its creation to post-insert
89.6K rows (all of them) in 9 seconds
ha, and now the joins are all hash matches
 
Now try the whole thing again :)
So, it'll be like 30sec, or so?
 
cutoff at 2014
22 seconds
221,445 rows
 
I guess that's the evidence that the posters weren't blowing smokes out of their posterios.
OTOH, it feels absurdly stupid that we have to jiggle the handle like that.
SQL Server should be able to infer that the table is empty so it can just apply the index after the insert
 
guessing the most impactful thing was @ vs #
 
> Yes you should reorganize it since after such a huge insertion your indexes will be fragmented.
 
7:22 PM
You think so? Try and keep the index creation originally and see.
 
Looks like it's such a huge insert the indexes get fragmented.
 
@this doing that now :)
 
@Hosch250 which to me, that's a lousy design. That's a engine concern, not user's concern.
 
22 seconds again
 
Wowza. So it's not even order of index.
 
7:23 PM
@this Yeah, well, that's how it is.
 
But the lack of statistics.
@ = no stats; # has full stats
 
clustered index insert: 28%
 
Complaining about it doesn't get us anywhere.
 
@Hosch250 I reject it! I will complain about it endlessly! I will bitch about it! I will write Bill Gates nasty letters!
I am so ragetweeting it right now!
 
Hey, might make it easier for the rest of us.
 
7:24 PM
:D
 
so, I guess my takeaway from this adventure should be to stop using @tables for >10K rows
 
Yeah. Sounds right.
 
22 seconds vs ~80 minutes.
unreal
 
I've found that @ sucks in complex joins even with small amount of data because of lack of stats.
 
7:25 PM
# and ## tables are actual tables represented in the temp database. These tables can have indexes and statistics, and can be accessed across sprocs in a session (in the case of a global temp table, it is available across sessions).

The @table is a table variable.
Looks like # stores it very differently internally, too.
 
One thing I might have been mistaken, though - I though the primary keys and uniques had statistics.
 
TBH, I never use @ tables unless I need to pass data in as an argument.
 
I knew that, just didn't realize how massively impactful the implications were
 
If you recall, we were talking about that last Friday.
 
@this Didn't pay too much attention to that convo, TBH.
 
7:26 PM
@Hosch250 @tables are very, very useful and normally work very well
 
Me neither. When I looked at it, I noticed you were just joining on the PKs so I thought it wouldn't be a problem.
 
just, when they don't, they really don't :)
 
^
So yeah, let's just not use @ for any significant amount of data or complex joins/filtering.
 
thanks for the help!!
 
glad you got it solved!
 
7:28 PM
And now you can have real-time reporting, right?
 
lol not quite
 
How much faster need it get?
 
I'll have to wait for SSIS to finish running (had to manually start it earlier... for some reason the MySQL source wasn't available last night and the overnight ETL bombed)
 
FWIW, every time I hear about overnight jobs, I think someone screwed up somewhere.
 
but, basically looking at 50 minutes out of 3 hours just vaporizing
 
7:30 PM
There are very very few jobs that realistically take all night to process when done correctly.
Oh, so you are down to 2 hours? I thought that was the whole job.
Either way, it sure frees your servers up to handle other stuff.
 
the ETL pulls essentially everything from a MySQL database that's populated at 12:30AM every night. not making this an overnight job would be useless
there are plans to change the thing to write directly to my SQL Server Staging db instead, but that's low priority
 
Why does it pull everything? You aren't still getting new 2014 data, are you?
Shouldn't it just pull new rows?
 
_DateDeleted needs a timestamp if a customer, order, or whatever, is no longer in the source system
and no, it can't just pull new rows
 
Put an "updated on" timestamp column with a write trigger.
 
@MathieuGuindon why not attach triggers and write to a log table in MySQL?
or what Hosch suggested
 
7:34 PM
Then you can just filter by the rows that were actually created/updated.
 
^
 
(FWIW, I've seen this used done to create "shadow" tables to log data changes. 10/10 do NOT recommend, LOL.)
 
because I don't have any kind of control on the MySQL source, plus that schema gets completely dropped and re-created every night
with potentially different column metadata
 
Wow. I was right. Someone screwed up.
 
@Hosch250 sorry I didn't follow - you say you don't recommend shadow tables over metadata columns?
 
7:35 PM
dude, sometimes you just have to accept that legacy systems work the way they do, and do what you can to make things better for everything else
the MySQL database's only purpose today is to feed my ETL
 
Could be worse. You could be in a large corp working with 3 or 4 legacy systems and you have to keep it all in sync
 
@this Correct. I prefer a version stamp on the column.
 
(I've done that, unfortunately)
 
before, it was feeding everything everyone ever did
it's an operational db, a version stamp makes no sense
 
@MathieuGuindon Yeah, still, someone should've looked past the retina on their eyeball about the process.
 
7:37 PM
@Hosch250 Meh, I see it as just 2 different ways. The separate log table means you don't need to touch the original table schema.
 
@Hosch250 you're missing a huge part of the story
 
@this Well, it's a long story. We were basically capturing versions of the data so the DAs could manually restore it if the client botched up.
 
and besides, you get a full history of changes whereas in-place metadata column can only have one version which is the last time, not every time changed.
Right, so that's a legit reason to have a history table, no?
 
The correct solution would have been to implement a versioning system so the client could roll back bad changes manually without getting us involved.
I did that in another place. Worked like a charm.
 
clarify - versioning system in the SQL layer?
 
7:39 PM
No, data versioning.
So they can see if the data was changed and roll it back if necessary.
 
that implies you have to set up the schema to support that, no?
 
The client was driving the changes.
@this Correct, but we owned this schema anyway.
 
so it's still on the SQL layer? Or did you mean you used a third party utility to handle this for you in SQL?
 
No, I mean we were using triggers and basically taking live backups of the table that only we could see.
Instead of simply only doing inserts, never updates, and exposing the versioning to the client--who drove all the rollbacks anyway.
Basically, instead of making it so the client calls and says "I screwed up, please fix this for me."
They can just go in and change the version from a dropdown.
And get the old content back instantly.
 
Right but the point is that you still have to program all that into the SQL layer, and create the shadow tables
to maintain the versions.
 
7:42 PM
Well, no, you don't need a shadow table. You just need a text column and make them provide a name whenever they commit a change.
And then make the table create/read only.
Instead of implementing update/delete functionality.
The biggest change would've been the C# driving the feature.
Much like my versioned widget settings in my Amethyst project.
 
to clarify - so clients always inserts, never update or delete?
 
The client could change the widget display, but they could also roll it back themselves.
Yes. Because that's what was happening anyway with the shadow table.
We were inserting a record for each CUD operation.
 
Ok, I now follow; that was the missing piece.
 
So basically, instead of having a main table and a shadow table, we just combine them and expose it to the client.
 
though, wouldn't it kind of suck when you need to sift out all the intermediate versions?
 
7:45 PM
Yes, if there were a lot of changes.
 
with the shadow table, you avoid the penalty when doing range queries since you don't have the old versions in the main table
 
That was one problem I never got around to solving, but there really weren't that many changes to the same system.
 
Right. In our case if we do a lot of aggregating, it would royally suck if we had to create filtered index for every conceivable combination of PK & last version & amount to be aggregated.
 
Alternately, I could enable deleting old items after a period of time has passed since the next version was created.
@this Oh, yeah. These items could only be used standalone.
 
Right. I can see why that would work great with the approach of inserting only into the table.
 
7:48 PM
So, for older items, it could enable the deletion 1mo after the next version was created. Could certainly help with things like typos.
In the general case, I've found a soft-delete column, a created date, and an updated date stamp generally help. Then you only need a couple extra indexes.
 
Yeah. I've used the soft-delete column but they aren't panaceas.
 
No. Nothing is.
Hence my "general case" comment.
 
Right. In my case, I find shadow table to be a okay default for general case.
that way they are out of sight, out of mind. But I agree with you that they are painful if clients keep screwing up
 
Sure. In our case, the whole point of it was because the client screwed up.
Our other tables didn't have shadows.
 
when the call's only once or twice a year, no biggie. But yes I would get tired of it if it was weekly or daily.
 
7:52 PM
Even if it was twice a year, it could be a nice sale point.
Because you never know when the next client would be once a day.
Which is why I built it into my system. If I were to sell that at scale, I couldn't possibly handle that kind of thing even once a week while I was a small business.
So, I had to expose it to the client so they could fix their own mistakes.
 
00:00 - 20:0020:00 - 00:00

« first day (2055 days earlier)      last day (39 days later) »