A Problem with Boolean Logic

存储架构 2018-03-04

Your requirement: to find the things where either A or B is true and C is true. Your first draft might be this:

SELECT ... FROM ...
WHERE a OR b AND c

(field, table, function, logic all anonymised to protect the perpetrator)

What’s the problem? Operator Precedence, that’s the problem.

Operator Precedence?

The order in which calculations are done – not just reading from left to right, but remembering that things like multiplication and division happen before addition and subtraction. My son tells me that kids nowadays are being taught something called “BIDMAS” – which stands for “Brackets, Indices, Division, Multiplication, Addition, Subtraction”. Or it can be BODMAS – Brackets, Operations, Division… (Operation is a fancy new way of describing indices – ie x y )

Unsurprisingly, there are similar rules for Boolean operators. (Boolean Operators LogicaL Order of CalculationS? BOLLOCS? So close…) And these all mix in together, with AND being at the same level as multiplication, and OR being at the same as addition. Microsoft has kindly provided a list of the operator precedence rules it uses in SQL Server .

So What? I hear you cry

Well, we had a malfunctioning server. Running sp_WhoIsActive against the server showed 400-odd queries blocked by five instances of a malformed bit of code like the statement at the top of this post.

We, like many places, have a variety of third-party applications that allow users to write their own queries at a high level, and then the applications fill in the blanks, and add all the close brackets and things, to make the query actually parse… What these apps don’t do, of course, is fix the broken logic for you. Anyway, our Joe Punter had been asked to run a query to find customer results (or something) where condition A or condition B were true, and condition C was also true. And he had come up with the A OR B AND C logic above, and was puzzled when he fired off the report that it didn’t come back instantly, so he fired it off again. And again. And a couple more times for good luck.

Reproducing the Problem For Fun & Blogging Purposes

We are using the AdventureWorks Database, as that’s easy to get hold of. (NB: MS has moved all this stuff to github – you may need to update your bookmarks accordingly.)

This query below is a cut-down hack that illustrates the problem. In the data sense, it’s garbage, but it illustrates the problem albeit with several orders of magnitude less data.

SELECT SUM(LineTotal)
FROM
       Sales.SalesOrderDetail AS sod,
       Sales.SalesOrderHeader AS soh
WHERE
       soh.OnlineOrderFlag = 1
    OR soh.BillToAddressID = soh.ShipToAddressID
   AND soh.SalesOrderID = sod.SalesOrderID;

The query plan generated is…unpleasant

As you can see, the query plan is dominated by that Nested Loop operator. Let’s have a look what’s going on there:

Yikes.

Let’s rewrite the query properly (ie add a bracket or two):

SELECT SUM(LineTotal)
FROM
       Sales.SalesOrderDetail AS sod,
       Sales.SalesOrderHeader AS soh
WHERE
       (soh.OnlineOrderFlag = 1 OR soh.BillToAddressID = soh.ShipToAddressID)
   AND soh.SalesOrderID = sod.SalesOrderID;

And the query plan:

Spot the difference.

Yes, the query returns different answers – that’s kinda the point inasmuch as the original query would not have returned what the user wanted… But we can now see a more appropriate join operator, and one that’s not taking all the query time.

The moral of the story? Get your logic right, and your server won’t fall around your ears.

责编内容by:The Lone DBA (源链)。感谢您的支持!

您可能感兴趣的

一起去云栖大会吧,我带着你,你带着这份参会指南!... 距离 2017 年 10 月 11 日云栖大会开幕还有一个月左右的时间,今年,大会主题定为“飞天 · 智能 Apsara Intelligence”,作为全球最具影响力的科技展会之一,本届大会不仅有阿里集团专家以及各企业行业领袖的精彩演讲,众多黑科技也将集体亮相。 10 月 11-...
HPTS’17 day 2 On HPTS day 2, there were 4 presentation sessions (I mention 2 below) and an evening session on remembering Jim Gray and Ed Lassettre. (Follow thes...
November 29th-December 1st Post Mortem Over the past year, Coinbase has invested significant effort to improve the scalability of our systems during periods of peak traffic. Despite these e...
【180425】统一配置中心 对于配置文件,我们不陌生,它提供我们可以动态修改程序运行能力。引用别人的一句话就是: 系统运行时(runtime)飞行姿态的动态调整 我可以把我们的工作称之为 在快速飞行的飞机上修理零件 。我们人类总是无法掌控和预知一切。对于我们系统来说,我们总是需要预留一些控制线条,以便在我们需要的时候...
PentesterLab新手教程(四):文件包含 PentesterLab 简介 Web for pentester 是国外安全研究者开发的的一款渗透测试平台。 这个平台包含的主要漏洞如下: Code injection (代码注入) Commands injection(命令行注入) XSS(跨站脚本) SQL injectio...