BR SQL performance tweaking

[Mikhail]

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?

[GomezL]

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:

Code: Select all

Select * From [Customer File]

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.

[Mikhail]

This is great, Luis, thank you.

This gives me something to work with. I'll try your suggestions and report back results.

[Mikhail]

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?

[GomezL]

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:

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

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.

[GomezL]

Code: Select all

INSERT INTO table2
SELECT * FROM table1
WHERE condition;