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.
Back in January I gave you an introduction to the calendar interface I’ve been building. There have been a few changes, now it looks something like this:
Each row has a machine name, a start date, and then 7 shift definitions. Here’s what the shift definition looks like:
What this is saying is that the Day shift starts at 5am and runs 12 hours with two breaks, while the night shift starts at 5pm and also runs for 12 hours with two breaks, finishing up the next morning. The fact that part of the night shift occurs the next day is captured with the ‘Day Offset’ variable.
Now, I need to be able to combine these. I need to know whether machine X currently has people operating it, or if they’re on break or unscheduled. Also, suppose a job ran over the course of a week. I need to be able to compute how many hours the machine was active over that time. These requests could come from SQL that feeds into excel reports or Alteryx (we aren’t on Alteryx yet, but it’s one of my goals for 2021). Alternatively, we will have the need to answer these questions inside Axapta. If we want our query to be able to be used for all of these, we cannot create a SQL query, nor can we use an inline Select statement or use query builder. The only way we can do this without code reuse is the Application object tree in Axapta.
Unfortunately, this isn’t the easiest way to write a query. One of the first things I wanted to do was to restrict the date range. I found a nifty range function that restricts the query to only dates of this month and the last 6 months:
This is the code this function uses:
Finally, this is what the T-SQL for the range looks like:
Do you see the problem? Look at those dates. If you’re just reading it then they might make sense, but what if you’re looking at my blog in 2022, or 2024? Those dates will never change because the SQL to create the view is not going to be run except when I’m doing the development. So DayRange() might work fine if you’re running the application and want to filter the results on a screen, but it’s completely inappropriate for building a view in the AOT. If we want to do that we need a function that creates a SQL view using the SQL function getDate() but unfortunately the Axapta function uses DateTimeUtil::getSystemDateTime() and then passes the date as a hard value. None of the date functions in SysQueryRangeUtil are any better, and I couldn’t figure out how to code one on my own.
Well, that sucks, but I can still write the query without any date restriction and just filter on date as needed when I use it later. The next problem I came across was Datetime related. You see, there were four different variables that effected when a shift period was starting. First, the callender week had a startDate – the date of the Monday for that week. But if we’re looking at Friday’s shift, then we’d need to add four days to that. Third, that period of the shift might actually be on Saturday, since our shifts can run all the way to 5am on the next day. Lastly, we needed to take the date summed from the other three variables and add the Time component from the startTime variable on the Shift Period.
That’s all easy enough to do in SQL or an inline select statement, but how does one create a field involving arithmetic for a AOT query? I’d gotten in deep in this one! Fortunately, DuckDuckGo came to my rescue. Turns out, you can create a special method on a view that calls some very specific AX methods that translate AX variables and logic into SQL code. The big class involved is SysComputedColumn. Here’s what my view method ended up looking like:
Looks easy enough, doesn’t it? Well, looks can be deceiving, this took several iterations before I could get it right. If you haven’t done this before, then be sure to budget some extra time to get familiar with it. Sql Server Management Studio is your friend, it’s how you’ll know whether what you’re doing is working.
Once that is done then you can add a new field to your view – but this field isn’t a normal field, but rather a computed column:
I looked at perhaps a dozen different sites and every one told me to use a string computed column. Makes sense, as my method returns a string. However, this is what my query and view looked like:
So, SQL already had a datetime, and was casting it to NVARCHAR. Look how ugly it makes Start and End! Who wants to mess with converting those strings back into dateTime? Not Me. Turns out, the UTCDateTime Computed Column exists for a reason: Sometimes the internet is wrong. With the field changed to UTCDatetime Computed Column, then the T-SQL doesn’t cast into a Varchar and datetime data looks much better:
That’s enough for today. Next week we’ll talk about union joins.
Joseph's Code of many colors 