Skip to Content.
Sympa Menu

freetds - [freetds] Executing batched statements

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Patrick Ethier <patrick AT secureops.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Executing batched statements
  • Date: 14 Feb 2003 19:19:14 -0500


Hi everybody,

We're trying to boost performance on INSERT statements from a LINUX
application onto an M$SQL server database using FreeTDS and UnixODBC
2.2.4

I've created this application to test INSERT performance using batching
and non batching which I've posted in part (Because the remainder
contains some proprietary stuff).

I get around 185 millisecs per transactions and SQL PERFMON shows that
the database tops off at around 160 transacts a second (Non-batched
mode).

In batched mode, FreeTDS doesn't complain, things go a lot faster but
nothing get committed to the table (data remains empty).

Can somebody tell me either what we're doing wrong in this code or
whether 160 trans/sec is normal for M$SQL on inserts??? (The server is
an IBM e-server with 1GHZ processor, 2 SCSI disk and lots of RAM. Again
PERFMON demonstrates that read/write time on the disk and the processor
availability is not even close to being saturated.)

I'd appreciate ANY help on this.

Regards,

--
Patrick Ethier <patrick AT secureops.com>

#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>


#define SQL_QUERY_LENGTH 8000


/* Note that the database structure passed here contains a pointer to an HENV attribute in db->dbh */
int insert_test_auto(struct dbattr *db) {
	SQLHSTMT hstmt = NULL;
	SQLINTEGER sql_nts = SQL_NTS;
	char query[SQL_QUERY_LENGTH];
	int id = 0;
	char *string = "This is a test";

	if (db == NULL) {
		return (-1);
	}

	if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, db->dbh, &hstmt))) {
		return (-1);
	}

	snprintf(query, sizeof(query), "insert into test values (?, ?)");

	if (!SQL_SUCCEEDED(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
					SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts)) ||
		 !SQL_SUCCEEDED(SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
				   SQL_VARCHAR, strlen(string) + 1, 0, string, 0, &sql_nts))){
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return (-1);
	}


	if (!SQL_SUCCEEDED(SQLPrepare(hstmt, query, SQL_NTS))) {
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return (-1);
	}

	for (id = 0; id < 20; id++) {
		if (!SQL_SUCCEEDED(SQLExecute(hstmt))) {
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return (-1);
		}
	}
	
	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	return (0);
}


int insert_test_man(struct dbattr *db) {
	SQLHSTMT hstmt = NULL;
	SQLINTEGER sql_nts = SQL_NTS;
	SQLINTEGER commit_off = SQL_AUTOCOMMIT_OFF;
	SQLINTEGER commit_on = SQL_AUTOCOMMIT_ON;
	char query[SQL_QUERY_LENGTH];
	int id = 0;
	
	char *string = "This is a test";

	if (db == NULL) {
		return (-1);
	}

	if (!SQL_SUCCEEDED
			(SQLSetConnectAttr
			 (db->dbh, SQL_ATTR_AUTOCOMMIT, &commit_off, SQL_IS_INTEGER))) {
			fprintf(stderr, "Unable to set autocommit mode\n");
			return (-1);
	}

	if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, db->dbh, &hstmt))) {
		fprintf(stderr, "Unable to allocate statement handle\n");
		return (-1);
	}

	snprintf(query, sizeof(query), "insert into test values (?, ?)");

	if (!SQL_SUCCEEDED(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
					SQL_INTEGER, sizeof(id), 0, &id, 0, &sql_nts)) ||
		 !SQL_SUCCEEDED(SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
				   SQL_VARCHAR, strlen(string) + 1, 0, string, 0, &sql_nts))){
		SQLSetConnectAttr
			(db->dbh, SQL_ATTR_AUTOCOMMIT, &commit_on, SQL_IS_INTEGER);
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		fprintf(stderr, "unable to bind parameters\n");
		return (-1);
	}


	if (!SQL_SUCCEEDED(SQLPrepare(hstmt, query, SQL_NTS))) {
		SQLSetConnectAttr
			(db->dbh, SQL_ATTR_AUTOCOMMIT, &commit_on, SQL_IS_INTEGER);
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		fprintf(stderr, "Unable to prepare statement\n");
		return (-1);
	}

	for (id = 0; id < 20; id++) {
		if (!SQL_SUCCEEDED(SQLExecute(hstmt))) {
			SQLEndTran(SQL_HANDLE_DBC, hstmt, SQL_ROLLBACK);
			SQLSetConnectAttr
				(db->dbh, SQL_ATTR_AUTOCOMMIT, &commit_on, SQL_IS_INTEGER);
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			fprintf(stderr, "Unable to execute statement %id\n");
			return (-1);
		}
	}

	SQLEndTran(SQL_HANDLE_DBC, hstmt, SQL_COMMIT);
	SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	return (0);
}


  • [freetds] Executing batched statements, Patrick Ethier, 02/14/2003

Archive powered by MHonArc 2.6.24.

Top of Page