SQL join Performance

I was messing with SQL query this week and it had been running in less than 10 seconds, then suddenly it was taking over 20 minutes. The entire query was over 150 lines, but we’ll just focus on the area that was problematic:

–31:34 execution time

And here’s the same query, with just a few changes:

–00:02 execution time.

So, usually when I write a post it’s with a problem, an explanation and a solution. I’ve looked at the execution plan and it cannot explain this. So how do I troubleshoot something like this? By running different sections of the overall query, I’m able to determine where the performance is affected and then hone in on that. In this case I was filtering the join with more fields than needed. I was filtering on 4 fields: jobid + wrkctrid + shiftdate + description, and that’s a 30 minute query. after making some changes to the previous subqueries I was able to join on just jobId which is 2 seconds. Further testing revealed that jobid + wrkctrid is fine, and jobid + shiftdate is fine, but jobid + wrkctrid + shiftdate is still not done after 35 minutes.

Probably could be improved with an index if I hadn’t already found a solution for it.

SQL Readability

Yesterday I created a view that queries 21 tables. When you’re trying to do something like this it’s very important that you know how to write readable SQL code. Here are some tactics I’ve found:

1. Naming conventions. Some people think that they can use single letter variables for table names and that this will save time. It doesn’t. The average piece of code is read 7 times. It’s more important to make code easy to understand than it is to make it easy to write. A naming convention needs to balance readability with brevity. In my current role I’m the only developer, but I came up with my own naming convention for tables in my first month on the job. InventDim -> iDim, InventTable -> iTab, EcoResProductAttributeValue -> erpaValue. I take the last word in the table name and add the first letter of every preceding word to it. This naming convention has served me well both in SQL and in X++ code. I can also instantly recognize whether code was written by me, by my predecessor, or by the product team.
2. Indentation. Some languages like Python enforce indentation, but SQL is often all over the place. Unfortunately I’m not as consistent with this as I’d like to be, but it’s something I work on.
3. Subqueries. There are three types of subqueries: Inline, View, and With.

Inline subqueries are typically used when you want to aggregate on a table before joining on it. These are messy to look at, hard to indent properly, and generally hard to read, although they do keep all the code in one place.

Views are database objects that have a query associated with them, but can be referenced in queries the same way as other tables. The whole purpose of this 21 datasource query is to create a view that can then be used in an excel spreadsheet. It actually calls a view that I created in Axapta, although from the screenshot above you wouldn’t know that it was a view:

This view guarantees that if a material has multiple status changes, we only get the most recent one.

Views are great at hiding complexity that doesn’t need to change often and less useful when it comes to viewing code that you might have to change. They offer flexibility in that they can be used by multiple queries or by excel or Tableau and as such they’re really useful. Typically you want them to be a little more refined, more of a finished standalone product than an inline subquery.

With clauses allow us to take a portion of SQL code and create a tablelike object with it. Essentially think of it as being useful for the same reason that creating a method is useful in a programming language like c++. It allows us to organize and compartmentalize our code, and keeps us from having as many indents to deal with. Here is my final query, having two with clauses:

Pygame Brick Game

I had this idea for a computer game. Everyone knows breakout, but I’ve never seen a breakout game where the bricks move around on the screen. I’ve had a lot of fun creating this game in python.

Github is here: https://github.com/josephdittli/Breakout

So, here are the classes:

Position, AnimationFrames, FreeMovement, RotatingCircle. These classes allow me to define the position of an object at any point in time.

CollidableObject, Brick, Ball, Paddle, Walls. These classes represent objects that can interact with the ball, have a position, and be shown on the screen.

Level, TestLevel, CarLevel. Each level contains a method to create a number of bricks as well as methods to display them and check for collisions, level complete.

Launcher. This is the method we call. Handles the timer, game events, etc.

I created a nice video of what the game looks like… but unfortunately, WordPress won’t let me put video on my blog unless I pay them more money, so poo. What you’ll see if you download the project from github is an orange and black sportscar made out of bricks, with wheels that spin and pavement that moves beneath the car.

There was definitely more I was planning on doing with this project. I’d like the tire bricks to rotate as they go around their circle. I’d like the angle of the ball impact to be affected by the angle of the brick, and I’d like the speed of the brick to impact the speed of the ball coming off that brick. Lastly, I wanted to introduce ball spin, although I’m really not sure exactly how the physics of this work. Unfortunately, I’m putting this project on hold because I discovered why people never make breakout games with moving bricks. Turns out, it’s super distracting to have something moving on the screen that isn’t the ball. Makes the game annoying, at least to me. I want to sit back and enjoy the animation, but I have to focus on the ball instead. So essentially, the entire thing I thought was going to be unique and cool about my game is in fact a huge negative. Live and learn I guess.

Tableau Bridge

We’ve had Tableau for a few months now, and I’ve been playing around in it, seeing what I can do with it. A few weeks ago I published a dashboard to two of the managers. But after a week, he went to refresh it and discovered that it wouldn’t refresh. So, how do you keep a dashboard up to date?

Well, you could go into the dashboard and publish it again. But that’s not something I want to do every day, no matter how easy it is. How do I automate it? The answer is Tableau Bridge.

Tableau bridge is a free software released by the makers of Tableau. It doesn’t just come with Tableau though, so you’ll have to download it and install it.

But did you actually read the license agreement?

Then you get a nice little icon in the bottom right corner of your screen for bridge:

If it’s grey then you need to configure it a bit, once it’s white then that means it’s active.

Now, you can access Bridge through all sorts of ways:

• From the icon in the bottom right corner of your screen
• From inside tableau dashboard
• From the tableau management website.

You’ll want to make your datasource extract mode instead of live, and you’ll – and this is where I messed up – want to schedule the extract for a time when your computer is running. I scheduled the extract for 4am and it didn’t work because my computer is a laptop and at 4am it’s in sleep mode. I know it didn’t work because I get these lovely email messages:

I also tried 9am and turns out there’s a time-zone issue and it’s running my 9am refresh at 8am, so sometimes that fails too if I’m running late getting into the office. I’ve now got it scheduled at 11am and that works great.

A few screenshots of me creating an extract after publishing a datasource:

Null enum in View

Alright, so we have a need to be able to categorize material as being on quality hold or waiting for recycling, or another status. I solved this by creating a new MaterialStatus table, with a status enum and a serial number which I can use to link it. If material is good/normal then it will not have a row in the MaterialStatus table unless it’s been given another status and then later specified to be good status after all.

Previously for raw materials used a view named utRaw, so I just added my new table to the view using an outer join:

As you can see, for most records MaterialStatus on the right is null. This means that it’s good material. When allocating material or telling the forklift driver which material to bring, it’s important that we only bring good material.

Now, the following document explains that null values are treated as 0 (the default value) for enums:

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/null-values-for-data-types

Well, with my Enum 0 corresponds to the Good value, so this query should return all rows that have no link, ie null in their material status.

But it doesn’t. Turns out this only returns rows where materialstatus is not null and materialstatus == 0.

I tried everything I could, but there seems to be no way to get the inline query to work. However, if the check isn’t in the SQL statement then it works fine:

I view this as a failure of the inline SQL in AX. Does anyone know a way to write a query for this?

Pessimistic Locking in AX:

I was doing more work on the Quality Hold project I mentioned in my previous post, and I came across this error:

Cannot edit a record in <table description> (tablename). Cannot call NEXT, update(), or delete() on buffer where data is selected or inserted in another transaction scope.

This is what my code looked like:

Now the line that calls this.createPickListJournal() is calling a bunch of functions that are going to perform the return to stock, and that’s certainly going to change InventSum and do so in a transaction. However, that transaction hasn’t been created before the method is called, and is certainly closed by the time the function returns. So why is it locking my table? I tried moving my code above this.createPickListJournal();, and that didn’t make any difference.

I did a google search and several of the pages weren’t helpful, until I came across this page by Martin Drab:

https://community.dynamics.com/365/financeandoperations/b/goshoom/posts/ax-support-for-pessimistic-locking

Turns out, the InventSum Table has a special flag set to make it so my code doesn’t work:

Because of this flag, the InventSum table uses pessimistic locking instead of optimistic locking. In other words, since the table can be used in so many places Axapta requires you to perform additional steps to ensure that you have the most recent version before you save it.

Luckily there’s an easy solution to this. By rereading the table in my transaction I’m able to convince AX that I really do have the current version and that I should be allowed to proceed with my update:

Alternatively I could have put select statement inside my transaction statments, and that would have worked as well.

Union Lookup?

Axapta has an InventSum table for raw material, and we’ve created a WIPMaterial table for wip. Both of them have a serial number, which is a 20 character string. I’ve been tasked to create a new workflow to flag a roll as ‘Quality Hold’ meaning that there’s something wrong with it and we cannot use it until we have a supervisor look at it. I used as a starting point the return to stock process.

The return to stock process however is started from a point where we know the serial. I wanted a dropdown that would allow the user to pick any serial that had been scanned onto the job.

Now, a lookup function is used to create a query that will populate the list of possible values for a dropdown. I’d never created a lookup for a dialog before, but it’s not difficult:

My initial goal was to make a lookup that could provide serials for both the raw material and the WIP material. Now, I’d never used the query builder classes to do a union query, but it is possible.

Unfortunately, when it came to adding lookup fields, if I added the lookup field from one table then I would get an error saying that the other table didn’t know what that field was, and vice versa:

the first enabled data source <tablename> in union query cannot have disabled field(UNKNOWN).

There doesn’t seem to be any way around this is the sysTableLookup class. So, my possible options were:

1. Create an actual form that uses a temp table and populate the temp table before calling the lookup.
2. Do something similar, but with the process class (extends RunBase).
3. Separate the WIP and Raw material into two different workflows that share a class but differ in the parameters passed to the main function.

I went with solution 3, as it was the first solution I was able to get working, and it will also run the fastest, although it does require the user to correctly choose whether they’re putting their quality hold on WIP or Raw material.

Still, if anyone knows a way to use SysTableLookup with a union query, I’d be very interested in seeing their solution.

Query performance Analysis Example

So, last week I was playing with a script to show me bad queries and I came upon something very interesting. Of the five worst queries via elapsed time, four of them looked very similar. They all had average worker time of just 14 ms, but nearly a million executions. And they were all on the same table.

It didn’t take me long to track down my code:

So, what is this doing? For a given shift and job, it’s getting the amount of time that they coincide. They can coincide in four ways:

Well, this code can and should be rewritten to use a single query:

So, in this code we’re simply worried about finding the shifts that have any sort of overlap. Then we use the code to figure out which dates to use.

I was also able to reduce the amount of times this function was called by 50%.

One last change was that I changed the select statement to only return two fields instead of the entire table. I was wondering if this would make a difference. Turns out, the new query also runs at 14 ms. So, that wasn’t a significant difference.

Error executing code: Wrong Argument Types for Comparison

So, this week I had a project where I collect information about product flagging where we mark that a product has an issue. For instance, we might have a 40,000 feet roll and there’s a roll splice at 15,000 feet, and we had been only marking it physically with red tape. By adding it to the application, we have the ability to provide more information and report on it. I also created a new label called a ‘Flag Report’ that prints out the same time as the roll label and shows information about all the flags.

As soon as I checked in my code, people started getting errors while printing pallet labels:

My code touched two classes: Ibis_ShopFloorLabels and Ibis_ShopFloorLabelsFeedBack. However it didn’t touch this checkOverrideProdId method, and it didn’t do anything to the pallet class (Ibis_ShopFloorLabelsArrival). The OverrideWO variable is a ProdId (ie, a string), and its type hasn’t changed, so I see no reason why axapta would claim that the variable is the wrong type. So, what’s happening?

Well, in this case since I knew I’d modified the class, I had a pretty good place to go on. Clearly this was some sort of import or compile error. Solution? Compile forward.

There are certainly other situations where this error could occur besides as a compile error. If you’re upgrading Axapta and a variable used to be type utcDatetime and now it’s type Date or vice versa, then you could get this error.

Query Analysis

If you’ve read my previous posts you know I periodically run a system query to identify the queries that are presenting the heaviest load for the SQL server. Today, the query on the top was one that I wrote a few months ago related to a dashboard report. Here’s the query:

When I displayed the query execution plan, I got two nodes that were worth talking about:

The second one is from a subquery where I union of the finished items table and the wip table that gets the total production. We’re going to focus on the first one, that’s 59%. This is related to the center of the query, where we figure out how long a job has run.

The TemProductionTime table has a row for each timestamp every time a job’s active status is modified. In order to determine how long a job was run, you have to look at two rows – the first will have a status of ‘Run’ and will have the start date, whereas the next one with the same job number will contain the Run’s end date. In the query, I’m using a left join with a third not exists join/clause to make sure that the runEnd is the very next timestamp.

I frequently gain insights on my work either when I’m sleeping, when I’m on my commute, or when I’m at the toilet. This was one of the latter. I decided, why not try min(timestamp) on the second table and eliminate the not exists clause?

This didn’t initially work – it got a different number of rows. But upon further investigation, turns out there were some duplicate rows being returned by the old query. So the new one is actually more correct! With this change, the entire query went from 24 seconds to 6.