Performance of Parameterized query

Performance of Parameterized query

Postby tomcat2001 » Wed 06 May 2009 20:35

I am trying to get to the bottom of some slowness in our application and so I have seen some slowness in using Parameterized queries and needing to see if there's something we can do to improve that. I may not have complete solutions listed below as I'm trying to copy/paste from a few different places in code in how we build these queries and such. The parameters are both byte arrays. And to give you a better idea of what sizes I'm getting here. On average, I'm getting for image a byte array of length 713777 and for annotation a byte array of length 5269. As for the times on running these queries I'm getting an average time of 815ms. The problem I have is this is being run a lot and so this 815ms starts to add up a bunch. So I'm probably looking at well over 10000 of these. So that's 135 minutes I think. I'm really looking at a way to improve this if possible. I'm just not satisfied with each of these taking about 1 second.

public void PerformOperation()
{
string query =
"Insert into tablename(Col1,Col2,Col3,Param4,Param5,Col6,Col7,Col8) Values('','','',':ImageData',':AnnotationData','','','');";

//The CreateConnection method just creates and opens the connection
using (IDbConnection newConnection = CreateConnection())
{
//Just have a method that creates a new command object and
// sets the connection/query on it
IDbCommand command = CreateCommand(newConnection, query);
//I have listed CreateParameter below for reference
IDataParameter imageParameter = CreateParameter("ImageData", DbType.Binary, imageData);
IDataParameter annotationParameter = CreateParameter("AnnotationData", DbType.Binary, annotationData);
command.Parameters.Add(imageParameter);
command.Parameters.Add(annotationParameter);

command.ExecuteNonQuery();
}
}

public IDataParameter CreateParameter(string parameterName, DbType parameterType, object parameterValue)
{
PgSqlParameter param = new PgSqlParameter();
param.ParameterName = parameterName;
param.Value = parameterValue;
param.DbType = parameterType;

return param;
}
tomcat2001
 
Posts: 21
Joined: Wed 10 Sep 2008 20:29

Postby Shalex » Wed 20 May 2009 08:31

Please try using the PgSqlLoader object in the binary mode. It should increase performance. Refer to our online documentation ( http://www.devart.com/dotconnect/postgresql/docs/ ) to learn more about usage of the PgSqlLoader class. Please notify us about the results. Does it help you?
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL