RE: Batch Insert With SQL_ATTR_PARAMSET_SIZE Slow

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
.

Relevant Pages

  • Re: Migrating SQLServer DBs to Sybase
    ... Server-generated scripts to create the structure in Sybase, and SQL Server ... AseConnection objCon = new AseConnection; ... objCon.ConnectionString = strConnectionString; ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Scheduling DTS job
    ... If you try to transfer the same rowsfrom SQL Server to SQL Server ... > How do i see if there is any difference in the driver ... >>> The SQL server agent is running as Local System Account. ... >>> Nothing has been changed in sybase box. ...
    (microsoft.public.sqlserver.dts)
  • RE: Numeric Overflow of Column of datatype Money
    ... Sybase driver is failing on the column, long before it reaches SQL Server. ... That I have serveral column with datatype money that didnt have the ...
    (microsoft.public.sqlserver.dts)
  • RE: Connecting to Sybase
    ... Have you tried pushing the data directly into AS using SSIS, ... This paper describes push mode processing in more detail: ... issue with pulling the data from Sybase on a daily basis and putting it into ... a Sql server table then having ssas get the data from there. ...
    (microsoft.public.sqlserver.olap)
  • Re: MS SQL server & Sybase interoperability
    ... > I have an application build on Sybase database, and we waant to install MS ... We are using Sybase ASA and SQL Server 2000 on the same machine. ... Installing SQL Server 2000 AFTER installing Sybase did not cause ... caused problems with the ODBC dlls. ...
    (microsoft.public.sqlserver.server)
Loading