Performance improvement with i-net SQL Server driver

JDBC
1

Why is it that the i-net SQL Server driver seems to be slower than other drivers in my tests?

2
  • You need to test the performance on the console. Our driver is completely written in the Java language so that a debugger can slow it down much more than a native driver like the JDBC-ODBC -Bridge or an type 2 driver.
  • You have tested it with a profiler. A profiler does not calculate the network waiting times correctly. This is the major running time. And profilers do slow down Java components much more than native components. You should montior the running time with System.currentTimeMillis().
  • You disabled the JIT (Just In Time Compiler). Java components slow down much more as native components with a disable JIT.
  • You use the JDBC subprotocol inetdae or inetdae6 with the SQL Server 7 or higher. These are the subprotocols for SQL Server 6.5 compatibility. With the SQL Server 7.0 it can be 2 times slower and with the SQL Server 2000 it can be up to a 100 times slower in some resons.
  • You selected a large count of data (row count or field size) and you use a forward only, read only ResultSet without a cursor. In this case the driver reads all data into memory. You should test without scrolling through the ResultSet. The scrolling is very fast in this case.
  • You use the JDBC subprotocol inetdae7 with PreparedStatements or CallableStatements with String parameters. Your tables include the ASCII data types char, varchar or text, only. The driver sends all data as unicode and the server needs to convert it for every row. You should use the JDBC subprotocol inetdae7a or the mode MODE_SQLSERVER_70_ASCII.
  • You use the fixed length data types CHAR or NCHAR in the WHERE clause of a PreparedStatement. The driver uses VARCHAR or NVARCHAR (depends the mode) for parameters. The SQL Server can’t use an index because it converts the values of the table column to VARCHAR or NVARCHAR. Solutions:
  1. do not use the fixed length data types rather use the variable length data types
  2. use a cast like “… cast(? as char(xx)) …”
  3. use a simple Statement with inlined parameters instead of PreparedStatements
  • SQL Server 2000 SP4:BigDecimal parameter send by driver will processed up to 20% slower. This happens if parameter definition send by Driver don’t exactly match the column type. For example Driver sends parameter as “decimal(38,36)”, the column type is “numeric (7,0)”. It seems that the index for the numeric column won’t used with SP4. With SQL Server 2005 or earlier SQL Server version such a behavior was not seen. A workaround for that problem is to cast to column type.

E.g.
SELECT * FROM testnumeric WHERE a = CAST(? AS NUMERIC(7,0));