What do the zDP_ Auto Generated Stored Procedures do?


David MeegoIf you look at a SQL Server installation of Microsoft Dynamics GP, in the Stored Procedures folder underneath the Dynamics system database or a company database you will find a large number of stored procedures starting with the initials”zDP”. This article explains what the zDP stored procedures do and why they exist.

Before Microsoft SQL Server 

When Dexterity was first created it was designed so that the developer was isolated from the technical aspects of the database platform used.  The developer asks Dexterity to perform particular database actions and the actual implementation depending on the platform being used is handled by the Dexterity runtime.  This allowed the same code written in Dexterity to run on Btrieve (later Pervasive SQL), Ctree and Faircom Server (for the C/S+ client/server version) without any changes or re-compilation.

These older database platforms were flat file or ISAM (Indexed Sequential Access Method) databases and provided no relational or referential integrity.  This meant that all the handling of parent/child relationships and preventing creation of orphans etc. was written into the Dexterity code by the developer.

Microsoft SQL Server is here 

Then Microsoft SQL Server 6.0 arrives on the scene and Great Plains looks at how it can implement Dexterity running on a SQL Server platform. The initial versions were designed to just get Dynamics working on SQL Server without specifically leveraging any of the other benefits that SQL Server could provide.

To make Dynamics compatible with SQL Server changes needed to be made to the code and the way SQL was used. Below are a few examples of these changes:

  • The login code was changed to actually log in to SQL Server and connect to the system (DYNAMICS) database and the company database.
  • Dates were implemented as a datetime datatype with a time of 00:00:00.
  • Times were implemented as a datetime datatype with a date of 01/01/1900.
  • All the table columns are defined as NOT NULL as Dexterity does not understand the concept of NULL.
  • The DEX_ROW_ID identity column was added to all tables to provide an extra unique field which could be added to table keys when the table had no unique key at the Dexterity level.
  • DYNGRP Security Role was implemented to easily provide complete access to all resources in a database.
  • DEX_LOCK and DEX_SESSION tables were added to the tempdb database to facilitate Dexterity’s Optimistic Concurrency Control (OCC), also know as passive locking, which allows for the equivalent of field level locking.

Why was it so slow? 

So Great Plains Dynamics C/S+ for SQL version 3.15 was released and it was so slow compared to the ISAM platforms that it was unusable.

This prompted the development to work with Microsoft’s SQL Server team to identify where the performance was being lost.  It turned out that the parsing of the Transact-SQL (T-SQL) queries from text to something the SQL Server code could actually execute was taking the largest percentage of the time when a table command was issued.  Once SQL Server had completed the translation into machine code it could perform the query quickly.

Because Dexterity works with pre-defined keys know in advance what the select statements and where clauses issued are going to be for the different table actions for each key. So if we create pre-compiled stored procedures for each of these queries with the data as parameters we would avoid having to parse text queries and hopefully the application would run faster.

Enter the Dexterity auto generated stored procedures or zDP stored procedures. What is even better is that they worked, at the time the performance improvement was about 300%.  The newer SQL versions are better are parsing now, but there is still a performance improvement when the zDP stored procedures are used.

Dexterity is actually smart enough to realise if there is a problem with the zDP procedures and will drop back to text T-SQL commands if they are missing.  So while the code will still run, the performance gained from the stored procedures is lost. 

The Performance is fixed

Great Plains Dynamics C/S+ for SQL version 3.17 was released using the zDP stored procedures and the performance problem was solved.

The following is an extract from a white paper on Dexterity with SQL Server which explains the naming of the zDP stored procedures:

To optimize database performance, Dexterity takes advantage of several stored procedures that exist for the SQL tables used by your application. If Dexterity is used to create a SQL table, by default these stored procedures will be generated automatically. They are often referred to as auto-generated stored procedures, and are internally used by Dexterity when reading, writing, or deleting records from a SQL table. These stored procedures are not required, but allow Dexterity to more efficiently work with the records within each table. The names of the stored procedures always begin with “zDP_” and contain the table’s physical name along with information about the purpose of the stored procedure. The following table lists the various types that may be created.

Stored Procedure Purpose Code Quantity
Insert SI One procedure per table
Delete SD One procedure per table
Select SS One procedure for each key
First F One procedure for each key
Next N One procedure for each key
Unpositioned Next UN One procedure for each non-unique key 

For example, the following stored procedures exist for the Customer Master table in Dynamics C/S+ for SQL:

zDP_RM00101SI
zDP_RM00101SD
zDP_RM00101SS_1
zDP_RM00101SS_2
zDP_RM00101SS_3
zDP_RM00101SS_4
zDP_RM00101SS_5 
zDP_RM00101SS_6
zDP_RM00101F_1
zDP_RM00101F_2
zDP_RM00101F_3
zDP_RM00101F_4
zDP_RM00101F_5
zDP_RM00101F_6 
zDP_RM00101N_1
zDP_RM00101N_2
zDP_RM00101N_3
zDP_RM00101N_4
zDP_RM00101N_5
zDP_RM00101N_6
zDP_RM00101UN_2

Please note that referential integrity was still handled by the code and not at the SQL Server level.  Also, now that Dynamics is running on the SQL Server platform, developers could start to leverage the performance benefits of SQL Optimisation with pass through SQL and stored procedures…. but that is for another post.

NOTE: Prior to SQL Server 2000 ad hoc select statements were not cached, but stored procedures were cached for repeated use. By creating stored procedures for data extraction, the performance was improved. The Database query engine in SQL Server 2000 (or later) now stores execution plans of select statements, so the performance difference is no longer as significant.

Post a comment to let me know if you enjoyed this little lesson in the history of the product.

David

19-May-2009: Added note about caching of execution plans from SQL Server 2000 onwards.

Comments (11)

  1. Jivtesh says:

    Fascinating to read how the performance was optimized from 3.15 to 3.17. Having developed Windows Forms Applications which dealt with a lot of data and having faced performance problems, it gives me a lot of courage, to know that you guys had these problems too.

    I love how you pick the point up from the beginning of time to how it is today … 🙂 … they make pretty interesting lunchtime discussions around here! Really !

  2. Felipe C says:

    Dave,

    This is a great post. I really enjoy reading the history behind products that I need to work with everyday and why things are the way they are. I will share this with my team tomorrow (I am a little late since I am just catching up after a long vacation).

    A question I have on the subject … other than the performance improvements gained by utilizing these SP’s would another reason for these to still be around is that it makes it really hard for users to reverse engineer how the system works?????

    It might sound a little cynic but I have seen this in many apps that run on top of DB’s. By this I mean having a complicated naming scheme and flow of code to prevent users from reverse engineering how the system works and giving consulting partners the opportunity to make some money.

    What are your thoughts on this … I am curious!

  3. David Musgrave says:

    Hi Felipe

    There is still a performance benefit provided by the pre-compiled stored procedures.

    However, if you delete them, the Dexterity code will fall back to generating the SQL Select statements itself. The application will still work, just slower.

    They were only created after v3.15 had performance issues, there was no plan to hide the way the application works.

    Dexterity, Modifier and Report Writer already exposes much of the application even without the source code.  If you want access to the source code that can be organised by joining the source code program.

    Sorry, but I don’t agree with your theory.

    Don’t ever stop being curious.

    David

  4. Dave Mugrave gets all geeky with a deep dive into the zDP_ Auto Generated Stored Procedures . I love

  5. Many people who have not been working with Microsoft Dynamics GP since the early non-SQL versions of

  6. Juan says:

    What the store procedure in sql that post a transaction to the financial module

  7. Patrick Roth [MSFT] says:

    Juan,

    Most of Dexterity posting code is not done with stored procedures – it is all Dexterity based.  As it happens, gl transactions are the one module that is stored procedure based.

    By doing a sql trace or a dexsql.log in Dynamics, you should be able to see what the procedure is that does this.

    One caveat, I believe that there is some stuff done outside the proc (before/after the stored proc) that also would need to be done.  So I don’t believe what you are looking at is quite as simple as you hope.

  8. David Musgrave says:

    Posting from Mariano Gomez, The Dynamics GP Blogster

    dynamicsgpblogster.blogspot.com/…/microsoft-sql-server-performance.html

Skip to main content