Tuesday, March 13, 2018

Beware of MIN, MAX, and TOP in Dynamics GP SQL queries!

By Steve Endow

A few weeks ago I started some research to compare the performance of MAX vs. TOP(1) in SQL Server queries.

After finding some unexpected results, I created a second video showing some odd behavior of MAX and TOP on one particular Dynamics GP table.  At that time, I couldn't figure out what was causing the performance issue with the MAX function.

Well, thanks to some very generous help and amazing insight from Kendra Little, I finally have a definitive explanation for the performance issue.

Wednesday, March 7, 2018

SQL MAX vs TOP 1: Part 2! The Revenge of IV30500!

By Steve Endow

I just can't let it go.  I need to know.  I need answers.  I need to solve the mystery.  The riddle.  The enigma.

Why does the MAX function sometimes perform very poorly compared to TOP 1?

I really thought that "MAX vs TOP 1" was a simple question. An easy question.  A spend 10 seconds on Stack Overflow and get the answer type of question.

But I just couldn't leave it alone and had to go and test it for myself.  And open a veritable Pandora's Box of SQL riddles.

In Part 1 of this series, I delved into how MAX and TOP 1 behave in several random queries, and I ended on a query that showed MAX performing quite poorly compared to TOP 1.

After that video, I stumbled across an even simpler query that produced an even more dramatic performance difference, where MAX performed miserably.  But I couldn't figure out why.

In this video, I discuss what I learned about the query and the specific Index Scan that is causing the MAX query to performing so poorly. 

Here's the recap:

When querying some fields, such as the IV30500 POSTEDDT, with no WHERE clause, both MAX and TOP 1 perform virtually the same, with both using a very efficient Index Scan.  50% vs 50% relative costs.

But when I query the TRXSOURCE field, with no WHERE clause, MAX shows 100% relative cost, whereas TOP 1 shows 0% relative cost.


The POSTEDDT query uses an Index Scan.  The TRXSOURCE query uses an Index Scan.  But for some reason with the TRXSOURCE query, MAX is much more costly.

I stared at this result for a few hours trying to figure out why.  I eventually found this tiny little detail.

Notice that the Actual Number of Rows for the MAX Index Scan is 147,316.  That's every row in the table.

By contrast, the TOP 1 Index Scan has Actual Number of Rows = 1.

What is going on?

For some reason, the MAX is having to scan the ENTIRE AK1IV30500 index.  It isn't getting much benefit from the index.

But why?

Unfortunately, I don't yet know.

"Maybe SQL is caching the query execution plan?"

Apparently not.  I tried DBCC FREEPROCCACHE, and saw no change.

"Maybe your statistics are stale and need to be updated?"


"C'mon Steve, clearly you need to re-index!"

Did that.  I tried DBREINDEX on the specific AK1IV30500 index, as well as the entire IV30500 table.  No change in the execution plan.

I didn't find any standard maintenance task that changed the behavior of the MAX Index Scan.

As a last resort, I used the Import/Export wizard to export all of the data from the IV30500 table into a new table that I called IV30500REBUILD.  I then ran scripts to create all of the same indexes on the REBUILD table, making it identical to the original IV30500 table.

I then ran the MAX and TOP 1 queries on the new REBUILD table.

And like magic, the MAX Index Scan returned just one row.

Same table structure.

Same data.

Same indexes.

But the Index Scan on the new REBUILD table behaves properly.

So there is apparently something about my IV30500 that is causing this problem, and rebuilding the entire table resolves it.  But rebuilding a table isn't exactly a typical SQL maintenance task, so it's not really a solution.

But this is way past my SQL skill level, so I don't yet know what conventional maintenance task might be able to achieve the same results.

I've asked for help from a true SQL expert, so I'm hoping that she will assist and help me figure out this mystery.

You can also find him on Twitter, YouTube, and Google+

Saturday, March 3, 2018

"Can you add just one more little feature?": A Story About Software and Home Improvement

By Steve Endow

Wife: "Steve, can you install an exhaust fan in the small bathroom?"
Steve:  "Sure, hunny, no problem. I just ordered the fan and I'll call Sam to install it."

Customer:  "Can you add this simple little feature to our application?"
Developer:  "Sure, no problem.  I'll get right on that."

No big deal.

Sam: "Steve, I cut a hole in the bathroom ceiling for the fan, but something is strange. There's an extra layer of drywall on the ceiling, and it's not attached properly and it's sagging."
Steve: "Hmmm, that doesn't look right. Let's remove the extra drywall and see what the prior homeowner was covering up."

Customer: "So how's that new simple little feature coming along?"
Developer: "Well, I looked through the code, and the original developers didn't design the software to handle this feature, so it's going to require some redesign of the customization."

I think the project scope just changed.

Friday, March 2, 2018

Sample Dynamics GP eConnect XML for RM Apply (RMApplyType / taRMApply)

By Steve Endow

A customer asked for sample XML for the RMApplyType / taRMApply eConnect transaction type.  I couldn't find one handy during a search, so I had to cobble together some .NET code and generate the XML.

I'm wondering if there is an easier way to generate the sample eConnect XML.  In theory, eConnect Requester with the eConnect Outgoing Service can send certain XML documents to MSMQ, but that is a hassle to setup properly, and I don't know that all transaction types are supported by eConnect Requester--such RMApplyType.

So, here is a sample Dynamics GP eConnect XML document for RM Apply (RMApplyType / taRMApply)

<?xml version="1.0"?>
<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                        <eConnectProcessInfo xsi:nil="true" />
                        <taRequesterTrxDisabler_Items xsi:nil="true" />
                        <taAnalyticsDistribution_Items xsi:nil="true" />

Discount Taken, Apply Date, and GL Post Date are optional, and do not have to be assigned or included in the XML if they are not needed.

Here is the eConnect documentation on taRMApply.

Tuesday, February 27, 2018

I give away my source code to my customers

By Steve Endow

Dynamics GP partners and customers often hire me to develop custom Dynamics GP integrations, GP customizations, Visual Studio Tools AddIns for GP, or even custom web APIs for Dynamics GP.

I develop the solution, usually using .NET, then I prepare a deployment guide and deployment package that can be installed on the customer's servers.  The solution is tested, I fix some bugs and refine the solution, I prepare new deployments that get installed, and once everything looks good, the customer goes live.

Everybody's happy, and I'm all done, right?

Except for one critical piece.

Any guesses?

What about the source code?

"What about the source code?", you might reply.

I diligently check in my source code to Git, and the code is pushed to an online Git repository for safe keeping and accessibility.  And I have backups of backups of backups, both on site and off site.  Great.  So I'm all done, right?

Not really.

What if I disappear?  What if I win the lottery?  What if I decide that this whole modern civilization thing is overrated and go live off the grid?

Sunday, February 25, 2018

T-SQL: MAX vs. TOP 1 - Which is better??

By Steve Endow

If you need to get the largest value for a field, should you use MAX in your query?  Or should you use TOP 1 with ORDER BY?

Which is better?  Which is faster?  Is that always true?

Do you think you know the answer?

Place your bets, and then check out my video below, where I compare MAX vs TOP 1 on several Dynamics GP tables.

The results may surprise you!

Did I miss anything or make any mistakes in my testing?  Are there other considerations when choosing between MAX vs. TOP 1?

How to improve Dynamics GP with a little bit of VBA

By Steve Endow

I've had a few Dynamics GP customers that purchase software from me every few years, and a few of them have mailed checks to my old mailing address from 4 years ago. How can this happen?

Well, the Dynamics GP Payables Transaction Entry window does not display the vendor Remit To address, so verifying the vendor address is not an obvious natural step in the invoice entry process.  Yes, there is a link to open the Vendor Address Maintenance window, but what if internal controls prevents the user who enters vendor invoices from editing vendor addresses?  The user would need to go through a separate process to verify the vendor address...for every invoice.  Not ideal.

How can VBA help?

In just a few minutes, VBA can be added to the Payables Transaction Entry window to check if the vendor has not had a transaction in over 60 days, prompt the user to verify the vendor address, and even open the Vendor Inquiry window to review the current Remit To address in Dynamics GP.

It's really easy!

Here's a video discussing the background and walking through the entire process of adding the VBA to Dynamics GP.

First, within Dynamics GP, you add the desired windows to Visual Basic by clicking on Tools -> Customize -> Add Current Window to Visual Basic.

After the window is added, I click on Add Fields to Visual Basic and then click on the Vendor ID field.

Since I am assuming that the user entering invoices will not have access to edit vendors or vendor addresses, I'm going to add the Vendor Inquiry window to Visual Basic and add 3 fields on the Inquiry window to VB:  Vendor ID, Address ID, and the Address ID Next button.

Once I have those windows and fields added to Visual Basic, I press CTRL+F11 to open the Dynamics GP Visual Basic Editor.  If you don't have access to the VB Editor, you may not be licensed to use it, or you may not have permissions--in which case, talk with your GP administrator or GP partner.

In the VB Editor window, I'll select the PayablesTransactionEntry window on the left, then select the VendorID field and the AfterUserChanged event.

I wrote the simple VBA code below to demonstrate how you can quickly and easily add some VBA to add some valuable functionality to Dynamics GP to save users time and improve data entry.

The code finds the most document date for any vendor transaction in Dynamics GP, and if that date is over 60 days ago, it opens the Vendor Inquiry window and displays the vendor Remit To address for the user to review and verify.

Private Sub VendorID_AfterUserChanged()
    Dim strVendorID As String
    Dim strSQL As String
    Dim dtLastDocDate As Date
    strVendorID = VendorID.Value
    'Find the most recent document date for the vendor
    strSQL = "SELECT COALESCE(MAX(DOCDATE), '1900-01-01') AS DOCDATE FROM PM00400 WHERE VENDORID = '" & strVendorID & "'"
    Set oConn = UserInfoGet.CreateADOConnection
    oConn.DefaultDatabase = UserInfoGet.IntercompanyID
    Set rsResult = oConn.Execute(strSQL)
    strLastDocDate = Trim(rsResult.Fields("DOCDATE").Value)
    'Get the Remit To Address ID for the vendor
    strSQL = "SELECT VADCDTRO FROM PM00200 WHERE VENDORID = '" & strVendorID & "'"
    Set rsResult = oConn.Execute(strSQL)
    strRemitID = Trim(rsResult.Fields("VADCDTRO").Value)
    dtLastDocDate = CDate(strLastDocDate)
    'If Doc Date is 1/1/1900, vendor has no transactions
    If dtLastDocDate = CDate("1900-01-01") Then
        Exit Sub
        intDays = DateDiff("d", dtLastDocDate, DateTime.Date)
        'If the last doc date is > X days ago, display a dialog
        If intDays > 60 Then
            msgResult = MsgBox("This vendor has not had a transaction since " & strLastDocDate & " (" & intDays & " days ago)." & vbNewLine & vbNewLine & "Please review the current vendor Remit To address and compare to the invoice address", vbOKOnly, "Verify Vendor Address")
            VendorInquiry.VendorID.Value = strVendorID
            While VendorInquiry.AddressID.Value <> strRemitID
                VendorInquiry.NextButtonWindowArea.Value = 1
        End If
    End If

End Sub

In just a few minutes, you can have this customization running in Dynamics GP without any additional development tools.

If you have more complex requirements, you can easily add more advanced functionality using VBA.  If you prefer using a separate development tool, you could also develop this customization using .NET or Dexterity, but the appeal of VBA is its simplicity and ease of use.

So if you have some small problem or additional business requirement that you'd like to handle in Dynamics GP, VBA might come in handy.

You can also find him on Twitter, YouTube, and Google+