A Fine Slice Of SQL Server
Erik Darling jumps to the heart of the issue:
How many times have you seen something like this?
You can replace “Update” with “Insert” or “Delete”, but the story is the same.
Large modifications are typically not fast modifications, for a number of reasons. To understand why, we need to think about the process as a whole.
Click through for several possible reasons.
Comments closedRidhima Kumar (re-)introduces us to decile analysis:
Decile analysis was once a popularly used technique, however the convention of teaching and bucketing machine learning problems into either ‘classification’ or ‘Regression’ types, lead people to forget Decile analysis type analyses. I am pretty sure, most freshly minted data scientists would not have even heard of Decile analysis. So, coming back to what is Decile Analysis.
Decile Analysis is used to categorize dataset from highest to lowest values or vice versa. (Based on predicted probabilities)
As obvious from the name, the analysis involves dividing the dataset into ten equal groups. Each group should have the same no. of observations/customers.
It ranks customers in the order from most likely to respond to least likely to respond.
Read on to learn the steps and how this ties with the fact that logistic regression is regression.
Comments closedJacqueline Cardoso announces a new version of Ludwig:
Ludwig abstracts away the complexity of combining all these disparate systems together through its declarative approach to structuring machine learning pipelines. Instead of writing code for your model, training loop, preprocessing, postprocessing, evaluation, and hyperparameter optimization, you only need to declare the schema of your data as a simple YAML configuration:
I’ve long been a fan of declarative approaches to problem-solving, so I’m going to need to dig into this a bit.
Comments closedDavid Fowler takes us through a mental exercise:
Well, there is something else that I’ve seen have a habit of filling TempDB. If you’re using Read Committed Snapshot or RCSI then you’ll also have a version store in your database. I’m not going to go into the details of exactly how version store works, there is plenty of documentation on it out there if you’re interested (perhaps I’ll write a post on it sometime).
The key bit that you need to know is that although SQL will keep version store trimmed down and only keep the rows that are needed, it can only clear rows that are older than the oldest transaction. This is because SQL has no way of knowing what rows are going to be needed by the transaction. Do you see a potential issue here?
Read on for enlightenment.
Comments closedFor years, I thought that native backups of databases using Transparent Data Encryption (TDE) couldn’t be compressed. Between TDE being limited to Enterprise Edition until SQL Server 2019 and my own lack of experience with TDE in prior positions, I hadn’t really experimented with this myself. Some people have even gone so far as to skip compression in their backup jobs for TDE-enabled databases because there’s no need to burn those CPU cycles if you won’t get any compression, right?
But a curious thing happened after I upgraded a portion of my environment to SQL Server 2019 in late 2020. I observed that scheduled backups were compressing for some of my TDE-enabled databases, most notably the newer instances. And when I took ad hoc backups in any environment, they were compressed. So why wasn’t it working everywhere?
Read on for the explanation, though one correction: MAXTRANSFERSIZE is 1MB by default only when the database is not encrypted using TDE (and you aren’t backing up to a tape drive). If the database is encrypted using TDE, the default max transfer size is 64KB, and I think that’s what got Andy.
Erik Darling points out a weird bug:
At some point in the past, I blogged about a silent bug with computed columns and clustered column store indexes.
In this post, I’m going to take a quick look at a very loud bug.
Normally, you can’t add a filtered index to a computed column. I’ve always hated that limitation. How nice would that be for so many currently difficult tasks?
Click through to see how you can create a filtered index against a computed column, as well as all of the pain it provides.
Comments closedChris Webb gives us another reason to curse NULL:
Recently I’ve been asked by colleagues with various different types of performance problems why Power BI is generating SQL in a particular way, and the answer has been the presence of nullable columns in the underlying database – whether it’s SQL Server, Snowflake or Databricks. Now I’m not a DBA or any kind of database tuning expert so I can’t comment on why a SQL query performs the way it does on any given platform, but what I can do is show you two examples of how the presence of nullable columns changes the way Power BI and Power Query generate SQL.
Click through to see what happens.
Comments closedSang-Heon Lee looks at reinvestment risk:
From this post, we can learn the reinvestment risk of coupon bond. It is worth noting that 1) YTM is attainable when roll rate is the same as YTM and 2) The argument that coupon rate is equal to YTM at issuance (par yield) is only applied to standard coupon bond with in arrears interest payment schedule. Unlike standard coupon bond, coupon bond with in advance interest payment has a higher YTM than coupon rate at an issuance.
Click through for the explanation as well as the R code used. H/T R-Bloggers.
Comments closedManoj Pandey explains an error message in Spark SQL:
UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge, when multiple source rows match on the same target row, the result may be ambiguous as it is unclear which source row should be used to update or delete the matching target row. You can preprocess the source table to eliminate the possibility of multiple matches. Please refer to https://docs.microsoft.com/azure/databricks/delta/delta-update#upsert-into-a-table-using-merge
The above error says that while doing MERGE operation on the Target table there shouldn’t be any duplicates in the Source table. This check is applied implicitly by the SQL engine to avoid unnecessary updates and avoid inconsistent data.
Read on for a reproduction and what you can do to resolve the issue.
Comments closedJessica Kerr explains why software from large firms is so often terrible:
Software is hard to get right. And every time we don’t, customers leave.
Appointment scheduling that sends a calendar invitation “Join the Zoom” without a link. Checkout screens that delete my credit card number when I change the shipping address. Complete Order that comes back with “Please try again later.” Items can’t all be shipped, can’t all be picked up, and this is a maze to figure out (also Lowe’s). A shopping cart that pops up a generic error modal when any single call to the server fails.
Read the whole thing. A lot of this sounds like an incentive alignment problem: each sub-group within a large firm optimizes for its own benefits, but the sum total of those choices leads to a sub-optimal result for the firm itself, as in the case of bad software driving customers to Amazon.
Comments closed