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.