|
Home > Archive > MS SQL Server ODBC > April 2005 > Batch Insert With SQL_ATTR_PARAMSET_SIZE Slow
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Batch Insert With SQL_ATTR_PARAMSET_SIZE Slow
|
|
|
| The following ODBC Tracing extract shows the approach used to batch insert
thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.520
Manager & SQL Server 2000:
SQLAllocHandle
SQLSetStmtAttr < SQL_ATTR_PARAMSET_SI
ZE> = 10003
SQLSetStmtAttr < SQL_ATTR_PARAM_STATU
S_PTR>
SQLSetStmtAttr < SQL_ATTR_PARAMS_PROC
ESSED_PTR>
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLBindParameter(...) // column-wise binding
SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
The same test is executed on Oracle, Sybase Adaptive Server Anywhere and MS
SQL Server. Unfortunately, SQL Server is ten times slower than the other two
databases. Here are the times in seconds:
Oracle 9i = 1 second
Sybase ASA 8 = 1 second
SQL Server 2000 = 10 seconds
All tests are executed on the same machine, the test table is empty before
the test is run. Why is SQL Server so slow? What's wrong here?
--
Spike
| |
| Aleksey Dmitriyev 2005-04-14, 1:23 pm |
| What transaction mode do you use (auto/manual? Is it the same for Oracle,
Sybase, and SQL Server?
"Spike" wrote:
> The following ODBC Tracing extract shows the approach used to batch insert
> thousands of rows, using the SQL_PARC_BATCH feature with ODBC 3.0 over 3.520
> Manager & SQL Server 2000:
>
> SQLAllocHandle
> SQLSetStmtAttr < SQL_ATTR_PARAMSET_SI
ZE> = 10003
> SQLSetStmtAttr < SQL_ATTR_PARAM_STATU
S_PTR>
> SQLSetStmtAttr < SQL_ATTR_PARAMS_PROC
ESSED_PTR>
>
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
> SQLBindParameter(...) // column-wise binding
>
> SQLExecDirect "INSERT INTO TEST(SSS,NNN,DDD) VALUES(?,?,?)"
>
> The same test is executed on Oracle, Sybase Adaptive Server Anywhere and MS
> SQL Server. Unfortunately, SQL Server is ten times slower than the other two
> databases. Here are the times in seconds:
>
> Oracle 9i = 1 second
> Sybase ASA 8 = 1 second
> SQL Server 2000 = 10 seconds
>
> All tests are executed on the same machine, the test table is empty before
> the test is run. Why is SQL Server so slow? What's wrong here?
> --
> Spike
|
|
|
|
|