Stored Procedures and Functions

Stored procedures and functions are objects that can be replicated to the children databases in the same manner as other objects. Yet, PartitionDB enhances these objects by using tunneled stored procedures (and functions), that will boost your database performance.

Please note, that all references to procedures are applicable to functions as well, unless otherwise noted.

Let’s start with an example

This example demonstrates the simplicity of using stored procedure along with PartitionDB solution.

MyProc is a stored procedure that is created at the gate. It is published one time with PartitionDB pdbPublishProcdure API, and once published you keep doing everything as before, however, you get much more.

This example is based on the bank example that is used through this tutorial (refer to start by example).

As you can see, one call to PdbpublishProcedure should be added to your script. Then the stored procedure will reside on all partitions: SunsetDB, VeniceDB and WilshireDB, as well as to TwoWestGlobalDB.

But not only that the stored procedure is replicated but it will run much faster than before, and will be encrypted.

Here is the message that is returned:

MSG-60151 Successfully Created Procedure dbo.MyProc on TwoWestGate
MSG-70112 Warning – Procedure is not published, please run “exec PdbpublishProcedure ‘TwoWestGate’,’dbo’,’MyProc’,@DatabaseName=”,@ParameterName=”,@IsTunnel=0,@IsSwitch=0,@IsAsync=0,@IsEncrypt=0;”

Please note, the procedure dbo.MyProc can be published with
1. @DatabaseName will publish directly into single child DB (or to all children DBs)
2. @ParameterName will tunnel calls directly into child DB by using parameter value
3. @IsTunnel will tunnel calls into children DBs from dbo.MyProc
4. @IsSwitch will tunnel calls into children DBs from dbo.PdbMyProc
5. @IsASync will tunnel calls to run parallel asynchronious on children DBs
For more information, please visit our website on http://www.partitiondb.com/stored-procedures-functions/

(1 row(s) affected)
MSG-61032 Successfully Published dbo.MyProc
MSG-60209 Successfully Created Procedure dbo.MyProc on TwoWestGlobalDB
MSG-60209 Successfully Created Procedure dbo.MyProc on SunsetDB
MSG-60209 Successfully Created Procedure dbo.MyProc on VeniceDB
MSG-60209 Successfully Created Procedure dbo.MyProc on WilshireDB
Caution: Changing any part of an object name could break scripts and stored procedures.
SunsetDB.MyProc – Duration 00:00:00
VeniceDB.MyProc – Duration 00:00:00
WilshireDB.MyProc – Duration 00:00:00:003
TwoWestGlobalDB.MyProc – Duration 00:00:00

The message reminds you not to forget to publish your procedure in order to take advantage of the capabilities of PartitionDB. Then it lists for you everything that is done behind the scene. Let go over it step by step:

Publishing a stored procedure

As noted before, publish API should be called once:

exec PdbpublishProcedure @GateName=’GateName’, @SchemaName=’dbo’,@ObjectName=‘ProcedureName’ ,@DatabaseName=”,@ParameterName”,@IsTunnel=0,@IsSwitch=0,@IsASync=0,@IsEncrypt=0;

PdbpublishProcedure requires three mandatory parameters:

  1. GateName – GateName is the name of the gate in which the procedure is created.
  2. SchemaName – Name of the schema.
  3. ObjectName – ObjectName holds the procedure name that we want to publish.

Tunneled stored procedure

Here is the beauty of PartitionDB stored procedure. Having MyProc, PartitionDB will create for you a tunneled procedure prefix_MyProc that will reside side by side with MyProc at the gate.  The object prefix is one of the gate’s attributes that you already set while creating the gate (you can read more about object prefix here). Executing the tunneled procedure will have the gate sending commands to the partition databases, such that the processing is done outside the gate. This will expedite the processing time by far.

Just pass true to IsTunnel in order for PartitionDB to create the tunneled procedure. Note that this parameter is not applicable for a stored function.

Here is how to publish the stored procedure such that a tunneled procedure will be created:

Switch the stored procedure names

Setting IsTunnel to true means that once we execute a publish procedure command, we will have two procedures. Obviously we want to take advantage of the tunneled one. But, this require us to change the call to the procedure to the tunneled procedure over the whole code, which is very time consuming and may cause lots of errors. Instead, we can choose to switch the two function names, such that calling the non tunneled procedure will result in executing the tunneled one, and vice versa.

Now, just call MyProc as you did before.

Note, if IsTunnel is set to 0, this parameter has no meaning, and, this parameter is not applicable for a stored function.

Asynchronous  mode

Using the tunneled procedure is more efficient since the command is sent down to the partition databases. The command can be sent in one of two ways:

  • Sync – A synchronous manner means  that the procedure command is sent to the partitions one by one in a sequential order (this is the default).
  • A-Sync – Asynchronous manner means that the procedure is sent to the different partitions all at once. This allows the partitions to process the queries in parallel.

Using the tunneled procedure has a great advantage over the regular procedure in the terms of speed. Obviously, the asynchronous way is the fastest.

The decision whether to use sync or a-sync is based on the nature of the procedure. There are cases in which a-sync mode cannot serve us, such as:

  • A procedure that returns values cannot be executed in an a-sync manner.
  • If the procedure is dealing with sequential data, a-sync mode may not be applicable.

Here is how to publish your stored procedure asynchronously:

As before, you just call MyProc, but your procedure is much more powerful now.

To keep you informed regarding your performance, running an a-sync procedure prints out the speed of the procedure execution times at each partition:

SunsetDB.MyProc – Duration 00:00:00
VeniceDB.MyProc – Duration 00:00:00
WilshireDB.MyProc – Duration 00:00:00:003
TwoWestGlobalDB.MyProc – Duration 00:00:00

Note, if IsTunnel is set to 0, this parameter has no meaning, and this parameter is not applicable for a stored function.

Encyption

To keep the integrity of you stored procedure, you may want to encrypt it at the partitions. Passing true (1) to this parameter will make sure to have the replicated procedure encrypted at the partition databases.

We can see below that MyProc exists on the partition databases, and that it is encrypted.

More options

Database name

@DatabaseName lets you choose to which partition to replicate the stored procedure. If you choose not to pass this parameter, the procedure will be replicated to all partitions. If you pass a partition name to replicate the stored procedure to, you will have to call pdbPublishProcdure once for every database that you wish to replicate it to.

This option is valuable when updating versions. You can update the partition that you wish at any time, such that different partitions can hold different versions of the stored procedure.

Parameter name

@ParameterName  is the name of parameter by which the partition key is tunneled across children databases. In this parameter you will pass a value that will identify the target database to tunnel through.

Updating your stored procedure

Once you publish your stored procedure or function, any change that you make to the procedure will be automatically updated at the children databases. There is no need to call publish again!