Press "Enter" to skip to content

Day: January 12, 2022

Debugging Code in Python

Adrian Tam takes us through debugging options with Python:

The purpose of a debugger is to provide you a slow motion button to control the flow of a program. It also allow you to freeze the program at certain point of time and examine the state.

The simplest operation under a debugger is to step through the code. That is to run one line of code at a time and wait for your acknowledgment before proceeding into next. The reason we want to run the program in a stop-and-go fashion is to allow us to check the logic and value or verify the algorithm.

For a larger program, we may not want to step through the code from the beginning as it may take a long time before we reached the line that we are interested in. Therefore, debuggers also provide a breakpoint feature that will kick in when a specific line of code is reached. From that point onward, we can step through it line by line.

This is something I definitely need to get better at when doing Python development.

Comments closed

Solutions for Matching Supply with Demand

Itzik Ben-Gan has some solutions to show:

This month, I’m going to start exploring the submitted solutions, roughly, going from the worse performing to the best performing ones. Why even bother with the bad performing ones? Because you can still learn a lot from them; for example, by identifying anti-patterns. Indeed, the first attempt at solving this challenge for many people, including myself and Peter, is based on an interval intersection concept. It so happens that the classic predicate-based technique for identifying interval intersection has poor performance since there’s no good indexing scheme to support it. This article is dedicated to this poor performing approach. Despite the poor performance, working on the solution is an interesting exercise. It requires practicing the skill of modeling the problem in a way that lends itself to set-based treatment. It is also interesting to identify the reason for the bad performance, making it easier to avoid the anti-pattern in the future. Keep in mind, this solution is just the starting point.

Click through for a solution which is straightforward but slow.

Comments closed

Preconceived Notions with Filtered Indexes

Aaron Bertrand has learned a thing or two about filtered indexes:

Confession time. For filtered indexes, I have long held the following impressions:

1. That the definition of the filter had to exactly match the predicate in the query.

2. That col IN (1, 2) was not supported in a filter definition, since it’s the same as col = 1 OR col = 2, which is definitely not supported.

If I were to take a wild guess, I’d think impression 1 was probably influenced by the extreme limitations filtered indexes have with parameterized queries. Anyhow, read the whole thing and learn why both of these are wrong.

Comments closed

Preconceived Notions on Rules

Richard Swinbank is a rule-breaker:

When I started out writing T-SQL, I learned rules – things I should do, things to avoid – from other, more experienced developers. For example, when writing stored procedures:

– don’t use dynamic SQL

– don’t set variables wih SELECT

– never use WITH RECOMPILE

There are often great reasons for these. Dynamic SQL can leave you prone to SQL injectionSELECTing into variables can have unexpected effects, and RECOMPILE can bring an OLTP system to its knees.

I think most rules of this sort tend to fit into something like four categories: convenience, take caution, observe in the breech, or shibboleths. Some rules, like formatting rules, are for convenience, whether that be for yourself or others. Not using dynamic SQL is a cautionary rule, for the reasons Richard mentions. The final two categories are variants on rules which exist to show that you are one of us, with observation in the breech being rules that we say we follow but only do when others are looking, and shibboleths being rules we follow regardless of whether people are looking. Formatting might fit into this as well, or having rules prohibiting (or enforcing) use of subqueries in the SELECT clause.

Very rarely are prohibitional rules 100% applicable, though as Richard notes, rules are a very useful shorthand.

Comments closed

Preconceived Notions around Temp Tables

Two overlapping posts on temp tables? Order up! First, Deb Melkin realizes tempdb is like a hotel room:

I was always taught to clean up after myself so I thought dropping my local temp tables after creating them in a proc did that. I knew that once the session was dropped, the table no longer was there so SQL Server would eventually take care of it. But then you had the cases where you ran the same stored proc multiple times in the same session so you wanted to make sure the table was gone at the end of your procedure to prevent issues.

Second, Deepthi Goguri learns the importance of getting temp tables right the first time:

I always had an opinion of changing the schema of the temp tables or adding indexes later after the temp table creation doesn’t affect in any way but it’s actually not a good idea. Yes, you heard that right. Altering or adding indexes later after temp table creation within the stored procedures will be marked for deletion and so the cached temp table metadata cannot be reused. It is suggestable to create the index as you create the temp table using the inline index syntax.

Both reference Pam Lahoud’s tempdb talk at EightKB (video), so check that out too.

Comments closed

Preconceived Notions of Business Logic in the Database

Mala Mahadevan discusses business logic:

There are many things on which I’ve changed my opinion in my journey as a data professional. For this blog post, I picked a common one – where business logic should reside. During my years as a production DBA – there was (and probably is) a hard-held belief that databases are meant for CRUD operations only and business logic belongs in the application/middle tier. This belief has its place – DBAs don’t like debugging code that is application-specific, or be tasked with why data looks a certain way or what code caused it. Business logic can also be very complex and get deeply embedded in one place (the database). I have believed in this and fought for this at most places I’ve worked as a DBA.

Over time though, my opinion has changed. 

Building on top of Mala’s point, people often conflate “business logic” with “data logic.” Rules governing the shape of data are as much data logic rules as they are business logic. The simplest cases include things like nullability, data sizes and lengths, and how data elements interact. It can also include ensuring the non-existence of invalid states through constraints. There can be practical reasons why we wouldn’t implement some piece of data logic in the database itself—for example, perhaps the particular data logic rule would be prohibitively expensive or becomes too complex to maintain as triggers—but in general, I’d estimate that people and firms under-specify their data logic in the database rather than over-specifying business logic.

One last bit that I’d hit on is that it’s okay to have overlapping logic in both, especially when it comes to data validation. We shouldn’t blithely accept inputs from client-side scripts—we evaluate it server-side and ensure that nobody has fiddled with the form and tried to create an invalid state. Then, when writing to the database, it can still make sense to check again for the exact same reason.

Comments closed