Hi everyone. I've been experimenting with BR SQL features, and got everything working, but performance comparison between BR and SQL is turning out almost 50 times faster in favor of BR.
BR WRITE 120,000 records takes 3 seconds
SQL INSERT 120,000 rows takes 125 seconds
Things are even worse when it comes to SQL SELECT and UPDATE.
At first I used the "SQL Server" driver, but realized this is outdated and switched to using "SQL Server Native Client 11.0" driver which is latest. However, this did NOT boost performance
My question is: how can we optimize doing lots of INSERT, SELECT, UPDATE queries?
BR SQL performance tweaking
Moderators: Susan Smith, admin, Gabriel
Re: BR SQL performance tweaking
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:
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
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!)
This returns about 6,000 records and takes BR about 1 second to read through all of them.
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:
Code: Select all
Select * From [Customer File]
Why not code something like
Code: Select all
Select * From [Customer File] Where Country = 'USA' and Last_Contact<='2017-01-01'
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'
Re: BR SQL performance tweaking
This is great, Luis, thank you.
This gives me something to work with. I'll try your suggestions and report back results.
This gives me something to work with. I'll try your suggestions and report back results.
Re: BR SQL performance tweaking
Hi, Luis. I was able to get great performance with BR SQL SELECT statements.
But for some reason, INSERT was actually slower when inserting 1000 rows at a time then it was when inserting 1 row at a time. How could this be?
Also, you mentioned "Selecting from other SQL sources" to get better performance... Can you expand on that? Do you mean I should open multiple connections to the same database and use them for different queries?
But for some reason, INSERT was actually slower when inserting 1000 rows at a time then it was when inserting 1 row at a time. How could this be?
Also, you mentioned "Selecting from other SQL sources" to get better performance... Can you expand on that? Do you mean I should open multiple connections to the same database and use them for different queries?
Re: BR SQL performance tweaking
Ther is a balance 1000 is definitely slower then 1, but there is a value that is better.
I have a function that I use:
You can tweak for specific files/systems.
The Insert from another table is "SQL to SQL":
https://www.w3schools.com/sql/sql_inser ... select.asp
It doesn't help if the data isn't already in SQL.
I have a function that I use:
Code: Select all
58890 DEF Fn_Get_Max_Insertrecs(F_Handle)
58920 IF Rln(F_Handle)<=200 THEN
58930 LET Max_Insert_Records=40
58940 ELSE IF Rln(F_Handle)<=999 THEN
58950 LET Max_Insert_Records=30
58960 ELSE IF Rln(F_Handle)<=2999 THEN
58970 LET Max_Insert_Records=15
58980 ELSE IF Rln(F_Handle)<=9999 THEN
58990 LET Max_Insert_Records=10
59000 ELSE
59010 LET Max_Insert_Records=5
59020 END IF
59030 LET Fn_Get_Max_Insertrecs=Max_Insert_Records
59040 FNEND
The Insert from another table is "SQL to SQL":
https://www.w3schools.com/sql/sql_inser ... select.asp
It doesn't help if the data isn't already in SQL.
Re: BR SQL performance tweaking
Code: Select all
INSERT INTO table2
SELECT * FROM table1
WHERE condition;