Pooling Command Object (Prepared)

  • Thread starter Thread starter Michael Angelo Libio
  • Start date Start date
M

Michael Angelo Libio

I was wondering if anyone has tried or has anything
against the idea of pooling a collection of command
objects that has been prepared. I'd like to access/execute
a method from multiple threads (executing the same exact
sql command with varied parameters on each execution).
Will this improve the performance of my application? What
ramifications should I look out for? Here's a sample code
flow:
I have a static method that creates a static instance of
pooled command objects (prepared).
On each incoming thread/request (via asp.net)
1. I pass the parameter(s) to the static method
2. Locate a prepared command object from the pool and mark
it busy
3. Execute the query with new set of parameter objects
4. Get the data from dataset
5. Return the object to the pool
6. Exit the method (ready for the next one)
Any input/feedback is greatly appreciated.
 
Michael Angelo Libio said:
I was wondering if anyone has tried or has anything
against the idea of pooling a collection of command
objects that has been prepared. I'd like to access/execute
a method from multiple threads (executing the same exact
sql command with varied parameters on each execution).
Will this improve the performance of my application? What
ramifications should I look out for? Here's a sample code
flow:
I have a static method that creates a static instance of
pooled command objects (prepared).
On each incoming thread/request (via asp.net)
1. I pass the parameter(s) to the static method
2. Locate a prepared command object from the pool and mark
it busy
3. Execute the query with new set of parameter objects
4. Get the data from dataset
5. Return the object to the pool
6. Exit the method (ready for the next one)
Any input/feedback is greatly appreciated.

That will work, provided you have one open connection for each command
(prepared commands are bound to the connection they were created on).

However I don't think your design is quite right. You may well suffer more
through serialization than you gain by reusing the prepared commands. Users
would be waiting for commands.

Here's a better pattern:

Implement your own connection pool, except don't just pool connections, pool
a connection and a collection of prepared commands.

eg
class ConnectionHolder
{
IDbConnection conection_;
IDBCOmmand commands = new Hashtable();
ConnectionHolder( System.Data.IDbConnection con)
{
connection_ = con;
}

public System.Data.IDbCommand GetCommand(string sql)
{
return commands[sql];
}
public void SaveCommand(System.Data.IDbCommand cmd)
{
commands[cmd.CommandText] = cmd;
}

}

Then check one of these out of the pool. Prepared commands will accumulate
on each connection as threads first use them.

A caviat, however, is that the Oracle provider doesn't prepare commands at
all, and the SQLServer provider only prepares ad-hoc queries, not calls to
stored procedures. So you might not gain that much.

David
 
Thanks for the reply, David.

So, you don't think it's worth it?

This is my reasoning for thinking of this design...
I figured, once a command object has been prepared (with a dedicated
connection object, bound to the command), executing queries would be much
faster than having to re-establish another command object. And I'm not just
referring to the time it takes to instantiate another command object, but
the fact that once it's prepared, the command object has a handle on a
compiled query that is ready to execute many times. I was also hoping that
the database server would also keep the query in its memory. But if this is
not the case, it may not be worth going this route.

So, here's an example of what I want to do :)

public class Person
{
public string Name;
public string Email;
public int Id;

private Person() { }

public static Person GetPerson(int id)
{
Person person = null;
System.Data.IDbCommand command;
try
{
command = commandPool.Dequeue() as System.Data.IDbCommand;
}
catch (InvalidOperationException ioe)
{
//queue is empty...none are available at the time
command = new ....whatever it takes to create the command object
command.CommandText = "SELECT NAME, EMAIL FROM PERSON WHERE
PERSON_ID = @id";
command.Connection = new ...whatever it takes to establish a
connection object
command.Connection.Open();
//i think i'm suppose to define parameter(s) first
command.Prepare();
}
command.Parameters.Add(id); //i don't have the exact syntax :)
IDataReader dataReader =
ExecuteReader(System.Data.CommandBehavior.SingleRow);
if (dataSet.NextResult)
{
person = new Person();
person.Name = dataSet["NAME"];
person.Email = dataSet["EMAIL"];
person.Id = id;
}
dataReader.Close();
commandPool.Enqueue(command);
return person;
}
private static Queue commandPool = Queue.Synchronized(new Queue());
}

what do u think?

David Browne said:
Michael Angelo Libio said:
I was wondering if anyone has tried or has anything
against the idea of pooling a collection of command
objects that has been prepared. I'd like to access/execute
a method from multiple threads (executing the same exact
sql command with varied parameters on each execution).
Will this improve the performance of my application? What
ramifications should I look out for? Here's a sample code
flow:
I have a static method that creates a static instance of
pooled command objects (prepared).
On each incoming thread/request (via asp.net)
1. I pass the parameter(s) to the static method
2. Locate a prepared command object from the pool and mark
it busy
3. Execute the query with new set of parameter objects
4. Get the data from dataset
5. Return the object to the pool
6. Exit the method (ready for the next one)
Any input/feedback is greatly appreciated.

That will work, provided you have one open connection for each command
(prepared commands are bound to the connection they were created on).

However I don't think your design is quite right. You may well suffer more
through serialization than you gain by reusing the prepared commands. Users
would be waiting for commands.

Here's a better pattern:

Implement your own connection pool, except don't just pool connections, pool
a connection and a collection of prepared commands.

eg
class ConnectionHolder
{
IDbConnection conection_;
IDBCOmmand commands = new Hashtable();
ConnectionHolder( System.Data.IDbConnection con)
{
connection_ = con;
}

public System.Data.IDbCommand GetCommand(string sql)
{
return commands[sql];
}
public void SaveCommand(System.Data.IDbCommand cmd)
{
commands[cmd.CommandText] = cmd;
}

}

Then check one of these out of the pool. Prepared commands will accumulate
on each connection as threads first use them.

A caviat, however, is that the Oracle provider doesn't prepare commands at
all, and the SQLServer provider only prepares ad-hoc queries, not calls to
stored procedures. So you might not gain that much.

David
 
Back
Top