To optimize database performance, Dexterity automatically generates several stored procedures for each table in an application. Typically, Dexterity generates these auto-generated stored procedures the first time it opens a table. If you create a table by some means other than Dexterity, you must use the Table_CreateProcedures() function to generate the stored procedures for the table.
Stored procedures created by Dexterity have the form zDP_ followed by the table physical name and a code indicating the purpose of the stored procedure. The following table lists the various stored procedures created by Dexterity.
![[spacer]](1p.gif){kind=small}
|
Stored procedure |
Purpose code |
Quantity |
Comments |
|---|---|---|---|
|
Insert |
SI |
One per table |
Created only if the table doesn’t contain text or picture fields. |
|
Delete |
SD |
One per table |
Created only if active locking is not allowed for the table. |
|
Select |
SS |
One for each key |
|
|
First |
F |
One for each key |
|
|
Last |
L |
One for each key |
|
|
Next |
N |
One for each key |
|
|
Unpositioned next |
UN |
One for each non-unique key |
|
For example, the Seller_Data table has the physical name SELLDAT. The table has three keys, one of which is unique. Dexterity creates 13 stored procedures for this table. The stored procedures are listed in the following table.
|
Stored procedure |
Description |
|---|---|
|
zDP_SELLDATSI |
Insert stored procedure |
|
zDP_SELLDATSD |
Delete stored procedure |
|
zDP_SELLDATSS_1 zDP_SELLDATSS_2 zDP_SELLDATSS_3 |
Select stored procedures (one for each key) |
|
zDP_SELLDATF_1 zDP_SELLDATF_2 zDP_SELLDATF_3 |
Select first stored procedure (one for each key) |
|
zDP_SELLDATL_1 zDP_SELLDATL_2 zDP_SELLDATL_3 |
Select last stored procedure (one for each key) |
|
zDP_SELLDATN_1 zDP_SELLDATN_2 zDP_SELLDATN_3 |
Select next stored procedure (one for each key) |
|
zDP_SELLDATUN_2 zDP_SELLDATUN_3 |
Unpositioned next procedures (one for each non-unique key) |
When optimizing the performance of your application, it is sometimes useful to not use some or all of the auto-generated stored procedures. Refer to SQL Auto Procedure Disable Options for information about situations when disabling auto-generated stored procedures can improve application performance.