Your results are fairly consistent with what I have found.
I generally say that BR can write 100,000 records per second, and this is roughly true for either Write # or Rewrite #.
With SQL, Inserts, Updates and Deletes are "Very Slow".
There are a few techniques to improve performance
1) use BCP or Bulk Copy to upload the data to SQL - This is much faster and along the 100,000 records per second that you are used to.
2) Use SSIS packaged in SQL to import data.
3) Select from other SQL sources.
4) Insert Multiple entries using insert into ... values (...),(...); (See Example Below)
Insert with multiple entries example:
Code: Select all
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway'),('Jones', 'Fairfield', 'USA');
With "Raw Reads", I find that I can read about 20,000 records per second from SQL, but here I have to ask a question:
What are you returning 20,000 records in your result set?
Example:
This query returns 20,000 entries (Every Customer).
Why not code something like
Code: Select all
Select * From [Customer File] Where Country = 'USA' and Last_Contact<='2017-01-01'
This would return only 100 records and As an example, and take a fraction of a second.
Even Better Create a Stored Procedure that performs a complex query and returns a small data set.
I have a 4 Billion Record SQL table (It's Huge!)
Code: Select all
SELECT * FROM Notes_File Where OURFILE = '98-0001'
This returns about 6,000 records and takes BR about 1 second to read through all of them.