After discovering the pot of gold that is ServerFault's What are the canonical answers we've discovered over the years?, I'm inclined to think dba.se could do with the same.
If agreeable to moderators and contributors alike, please edit your nominations for canonical question/answer pairs into t...
@ConcernedOfTunbridgeWells I guess because Oracle announced results yesterday
> Oracle Corp. missed analyst estimates for fiscal year 2022 first-quarter revenue, but managed to top earnings estimates for the period as its latest cloud businesses showed good growth.
The database giant reported non-GAAP earnings of $1.03 a share on revenue of $9.7 billion.
oof - my ETL was going great until the volume of updates for some tables went from 10-12 million rows to 22-29 million rows. And instead of taking approximately twice as long I'm looking at going from 15-20 minutes to 1-2 hours.
I gotta figure something out because whatever worked when the tables had less than a billion rows is not working now that they're over 1 billion rows.
@bbaird Has something overflowed available memory and started generating a query that spools something to disk. Have you got anything doing stuff in the query plan that looks funky now?
Could be worse. Wait until management gets so pissed off with IT that they outsource it. What could possibly go wrong?
Hint: I've seen a company that outsourced its IT function to a big company™ that you've heard of. They had about 15 departments within the business running their own completely siloed M.I. teams.
Eventually they brought the I.T. function back in house, but the relations between IT and the business were ... not the best.
Anyway, cheer up. The job market is buoyant now. Time to polish up your CV.
So tinier chunks doesn't seem to be helping, but re-examining the query plans it looks like it's doing unnecessary index seeks on the bigggggg table so I've rewritten things to use OUTER APPLY instead of a correlated subquery which cuts the estimate cost 5 fold. Fingers crossed.
Tinier chunks did reduce contention in other areas, so it wasn't a total bust.
@ConcernedOfTunbridgeWells one of the things I actually really dig about the place I'm at now is they use CI for pretty much everything. Albeit, it's all greenfield work in Azure, so that makes it easier.
we're using it to orchestrate deployments into Azure Data Factory, Azure SQL Database, and PowerBI, along with the occasional Azure Function or other code. But, I agree, that's a pretty small scope.
@HannahVernon The outfit in question had some major COTS applications in their environment, in particular a workflow system and a policy administration system, neither of which they had source code for. There were also multiple databases on different platforms and multiple middleware platforms.
They had to do a ground-up rebuild for reasons mainly to do with their being unable to meet capacity targets with their incumbent architecture. It didn't go well.
They also had a data warehouse platform that wasn't in their CI build, as was also the case with several other applications they were dependent on.
And a pricing platform supplied by a third party vendor.
yah if you're going to do CD/CI, the development part should really be all-encompassing. i.e. you can't expect a non-integrated continuous integration project to go well.
@HannahVernon I don't think CI was the root cause of their project failure, though. The project failed because they didn't manage their requirements properly. I think a backlog can work well for managing work items, but it's not really up to the job of handling a major business change programme.
I'm a bit cynical about big-A agile, more because of the antics of its fanboys than because of the methodology itself. It gets pushed as being all things to all people, and then people won't admit it has shortcomings.
For example, a backlog management system like TFS or even Jira is a miles better way to manage work than a tool like MS project.
The process works well for anything that can be managed as if it was a BAU workstream. Less so for a large scale project where you need to have decent visibility of your requirements. Going through 1,000 stories in a backlog really isn't a good way to manage scope.
@PaulWhite I've not seen it used in anger in a few years now, really since TFS and Jira got widely adopted by enterprise IT functions.
@PaulWhite There's more of that still in production than one might think.
Policy administration systems are amazingly sticky due to the cost of migrating off them.
There's an outfit called OpenGI that originally produced their flagship product on a long-dead 68k based machine running Tripos, which is best known as the platform that was adapted for the Amiga's operating system. They bought the OS when the vendor went under and still maintain an emulation layer on Linux to run their product.
One outfit I worked for had two SAS teams. There was the mainframe SAS team that had been migrated to Linux and the other mainframe SAS team that was still running it on the mainframe.
At another place I worked for once, I got to witness somebody from IT taking out a complete set of VMS manuals and dumping them into a skip.
Informix SE was a remarkably simple system. My first ever unix system had 2MB of RAM and originally been purchased by somebody intending to run Informix SE on it.
Fun fact: Informix was originally developed as a part of Project Athena, which also spawned the X windowing system.
I should form my own consultancy - if this guy can do it, surely anyone can? His qualifications appear very impressive from his profile. He has an interesting article here (comparing SQL Server with PostgreSQL) where he refers to "PostgreSQL Ayatollahs".
It's not often I get a good laugh out of a (purported) technical article. Given the amount of bold text, he should perhaps be taking what the French call "un calment" (an anxiolytic - i.e. a "calmer").
@ConcernedOfTunbridgeWells Fun fact - my first exposure to the joys of the RDBMS. Interesting architecture - it ran on top of C-ISAM tables which meant that the rdbms functionality could be "subverted" under the hood. The company I worked for had the misfortune of purchasing an entire system that was based on subverting it, which led to (ahem...) interesting bugs. You could sit the Informix 4GL on top of it!
@PaulWhite I missed this before I wrote about him! I also missed your bit about Informix! These HCL guys in India seem to be taking on the development of every db server they can lay their hands on - Informix and Ingres - what next? SQL Server? Oracle? Perhaps not - they appear to go for products with little or no market share that start with the letter "i"! Interbase? They'd have some stiff competition from Firebird... always liked that system...
HAVING Count(v.ProductID) < 20
Must be rewrite to :
HAVING Count(v.ProductID) < 20 OR Count(v.ProductID) IS NULL
