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_SIZE> = 10003
SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_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
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_SIZE> = 10003
> SQLSetStmtAttr <SQL_ATTR_PARAM_STATUS_PTR>
> SQLSetStmtAttr <SQL_ATTR_PARAMS_PROCESSED_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
2012年2月13日星期一
订阅:
博文评论 (Atom)
没有评论:
发表评论