|
|
Posted by Erland Sommarskog on 11/28/78 11:58
dunleav1 (jmd@dunleavyenterprises.com) writes:
> I wrote a tsql transaction to insert 200000 records at a time and it
> was approx. 20% slower than Oracle. I have Oracle configured with a
> large log cache and large redolog - so lot happens in the cache before
> a write. Is there any way to configure the log cache to be larger in
> sql server or do I just keep giving the entire engine memory? I tried
> altered the recovery interval hoping it was part of the computation for
> the size of log cache within the engine but with no real improvement.
So how do this transaction look like? Do you send one INSERT statement
at the time from the client? Is it one big batch of INSERT VALUES
statements?
The fastest way to load a lot of data is by bulk insert. I don't know
if bulk operations are exponsed in the Java APIs, but you can always
use command-line BCP.
If you don't want to deal with bulk-load, you could consider composing an
XML document of the data (or a couple, to insert batchwise) which you can
unpack in the server and insert in a single statement. This is slower rhan
If you don't want to deal with XML, but run plain statements, the fastest
is this way:
INSERT tbl(col1, col2, col3, ....)
EXEC('SELECT val1a, val2a, val3a
SELECT val1b, val2b, val3b
....')
What you should not try is:
INSERT tbl (col1, col2, col3, ...)
SELECT val1a, val2a, val3a
UNION ALL
SELCET val1b, val2b, val3b
UNION ALL
...
For moderate daa sizes this is faster than many INSERT statements, but
as the number of rows increase, this becomes disastrously slow. You get
faster disk I/O - the bottleneck is in the compilation of the statement.
I'm aware of that the alternatives I have suggested are not portable,
but if you need to insert that many rows, what's in standard SQL is simply
not a good idea, at least not SQL Server.
> Fourth issue - When going through jdbc - either the jdbc driver or the
> engine jdbc/java api is a lot slower than Oracle.
I don't know which JDBC driver you use, but I believe that Microsoft's
free driver is a fairly lightweight thing and far the best on the
market. The JDBC driver for SQL 2000 was based on 2.0 of DataDirect's
driver, and they have newer versions out.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|