2

I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data.

I also need the writing to be superfast.

While checking the options on improving the writes (asuming reading time is nu so important) a friend of mine told me that I need to check the Transaction Isolation Level that is appropriate for my logic. after reading some articles regarding this issue what I understand is that this property influence reading.

Is there a Transaction Isolation Level that affect writing? Which isolation level is "best" for lots of Threads running on lots of connections?

Am I looking in the wrong place?

What can I do to improove massive writing?

Im using SQL server, the threads are coming from TomEE server that writes data that came via HTTP requests via JDBC and JPA (ORM).

Thanks!

3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction.

No amount of tinkering with isolation levels will eliminate these 2 or 3 calls.

If you want more performance, then the best concept stored procedure to do an atomic write (in a transaction) to both tables in one database call. This means 50% or 66% reduction in database calls

Going further, you have to decide what "superfast" really means

For example, should you be using SSDs for your transaction log files: the drive hosting the log files determines overall write speed because of write ahead logging.

Then there the design: are you using IDENTITY columns or GUIDs or Hibernate style nvarchar keys? Has the ORM designed the database for you?

6
  • I designed The DB and used a reversemapping tool to get the objects representation of the data. the IDENTITY columns are autoincrement integers. does IsolationLevel influance the performance in my case? Do you think the best practice in my case is by using SP?
    – yossico
    Oct 23 '14 at 7:54
  • 2
    Isolation Level will not help you. Yes use a stoerd procedure
    – gbn
    Oct 23 '14 at 8:05
  • +1 Nice answer, I was thinking of trigger on Insert, will that help in this case? that will not work incase data are different in both the table.
    – vijayp
    Oct 23 '14 at 8:09
  • How would the trigger on first table know what data is to go into the second table? A stored procedure does the same with more visibility
    – gbn
    Oct 23 '14 at 8:20
  • right, stored procedure is the best option here.
    – vijayp
    Oct 23 '14 at 8:21
2

The only isolation level that influences writes is SNAPSHOT (and the READ_COMMITTED_SNAPSHOT). Snapshot isolation requires row versioning and row versioning requires extra writes. Read Understanding Row Versioning-Based Isolation Levels.

Now about the 'super-fast' part of the question: the 'super-fast' option for INSERT is the bulk insert path. This requires a client API that understands bulk-insert: IRowsetFastLoad in OleDB, ODBC send_row or managed .Net SqlBulkCopy, EnableBulkLoad=true for JDBC. You do not want 'lots of threads running lots of connections', you want one thread doing lots of inserts, in bulk API mode. Use a producer-consumer pattern in the app to funnel all your app threads to one single bulk writing thread.

0

Bulk writes indeed are fast but will not be logged. If that is not what you want, consider procedure calls with table valued parameters. That way we have realised significant write improvements.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.