[archive] SQL Server speed issues

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

This is exactly what we found about 8 years ago when we first trialled Acu4GL with Informix. We studied what it was doing by using the file tracing facility in the runtime.

The problem seems to arise with the method it uses to do a START.

What we found was this:

Say you have a table whose main index is composed of three fields, FIELD1, FIELD2 and FIELD3. Let's say that there are a few records at the beginning of the file with FIELD1='A' that you want to READ thru, which are followed by a few hundred thousand records with FIELD1='B'.

When you put 'A' in FIELD1 and do a START, the Acu4GL code translates this into a CURSOR SELECT on the table with a where clause of: WHERE FIELD1='A' AND FIELD2=' ' AND FIELD3 >= ' '. This will probably not select any records because FIELD2 is usually not blank, so it retries with a SELECT with WHERE FIELD1='A' AND FIELD2 >= ' '. This is better and gets you the records that you want to read.

But because your Cobol program is expecting to READ until you find a record that does not have FIELD1='A', the READ NEXT after the last 'A' record results in a new CURSOR SELECT with WHERE FIELD1 >= 'A', which, of course, selects the rest of the file! The irony is that you only want to read the first of these SELECTed records to be able to determine that you have 'over-shot' the 'A's.

A classic example was one of our programs that, before it started, checked that there was at least one record in a particular table. This was done by INITIALIZEing the record and doing a START, READ NEXT. It would, of course, select the whole file just to check for one record!

Now you are supposed to be able to use the WHERE_CONSTRAINT variable to force a particular WHERE format, but that means you have to go through all your code to make sure that all STARTs have approriate constraining WHERE clauses defined for them.

Our local Acucobol distributor at the time told us that Acu4GL was "a square peg for a round hole".

Of course, this may have changed drastically in the last 8 years, but by the sound of it, nothing much has changed.

If you want to satisfy yourself what Acu4GL is actually doing with the SELECTs, set environment variable FILE_TRACE=9 before doing a test.

Cheers.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

We have the same issue. I.e. SQL was going through the whole table when all we want is the first few rows.

To get round this you can you the A_MSSQL_ROWCOUNT environment variable which I believe has the effect add putting a "TOP x" in the SQL server statement.

Saying that I have been unable to get the performance we require out of Acu4GL for MSSQL so far although we are going to look at this again. The oracle version seems to be much better in terms of performance with our application.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

We solved the problem by using a combination of A_MSSQL_ROWCOUNT and the WHERE constraint.
For example, we had a program that ran 130 minutes against a particular MS-SQL database. It would read thru an entire table in the database, and supply that key as a partial key in another table and do a start and read next. By setting the ROWCOUNT to 1 before accessing the second table, the program ran in 80 minutes. Setting it to 100 made it run in 63 minutes, 1000 and it ran in 59 minutes. You can see the diminishing returns here. We then experimented with the WHERE constraint. Since we already knew part of the key we were looking for in the 2nd table, we computed a WHERE constraint with a key greater than the last record we knew we would need. The program ran in 15 minutes. Be careful how it is used though, because the constraint causes an AT END condition even if you haven't actually reached the end of the table.

So our solution was to use the A_MSSQL_ROWCOUNT when we are doing a start, read next and do not know any part of the key (for example, filling a box with 8 or 10 records), and use the WHERE constraint when we knew the beginning portion of the key, but not the entire key. We always have used copybooks to access files and tables, so it meant only changing the text copied in once for each table where it is needed.

That said, we found it advantageous to NOT use either in some situations. For example where we WANT to return the entire table each time, such as a table of all valid country or state codes recognized by the US Government, or other tables with a limited number of records.

We would be happy to help anyone who has any questions about how it works.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

We solved the problem by using a combination of A_MSSQL_ROWCOUNT and the WHERE constraint.
For example, we had a program that ran 130 minutes against a particular MS-SQL database. It would read thru an entire table in the database, and supply that key as a partial key in another table and do a start and read next. By setting the ROWCOUNT to 1 before accessing the second table, the program ran in 80 minutes. Setting it to 100 made it run in 63 minutes, 1000 and it ran in 59 minutes. You can see the diminishing returns here. We then experimented with the WHERE constraint. Since we already knew part of the key we were looking for in the 2nd table, we computed a WHERE constraint with a key greater than the last record we knew we would need. The program ran in 15 minutes. Be careful how it is used though, because the constraint causes an AT END condition even if you haven't actually reached the end of the table.

So our solution was to use the A_MSSQL_ROWCOUNT when we are doing a start, read next and do not know any part of the key (for example, filling a box with 8 or 10 records), and use the WHERE constraint when we knew the beginning portion of the key, but not the entire key. We always have used copybooks to access files and tables, so it meant only changing the text copied in once for each table where it is needed.

That said, we found it advantageous to NOT use either in some situations. For example where we WANT to return the entire table each time, such as a table of all valid country or state codes recognized by the US Government, or other tables with a limited number of records.

We would be happy to help anyone who has any questions about how it works.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

We've just run into similar speed issues once we got a customer table populated with some thousands of records. A start->read next operation takes minutes to return a result set. I'm wondering how the 'where' contraint is actually implemented though. For example, if the customer name entry field has 'DAV' entered so that DAVIS or DAVIDSON or DAVORACK would be returned, what could be put in the where contraint to prevent SQL from reading too far?

Thanks,
Tony

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

Never mind. I got it working and the performance is good now.

Thanks.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

Tony:

How did you solve this?

We are still struggling with it even thought we improved it by setting the row count to 10. We are having trouble with doing a START and then sequential read through a file. Is this the sort of thing you resolved? How did you do it?

Thanks,

John

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

Tony:

How did you solve this?

We are still struggling with it even thought we improved it by setting the row count to 10. We are having trouble with doing a START and then sequential read through a file. Is this the sort of thing you resolved? How did you do it?

Thanks,

John

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

Using a where constraint on names was something we were never able to quite accomplish. We could not come up with a single formula to use in computing the upper boundary which would guarantee reading at least one record, but not reading so many that it defeats the purpose, and would work with a table of 100 names, or 100,000 names. I would be interested to know if someone was able to accomplish that.
What we did instead was to set the A_MSSQL_ROWCOUNT and found a considerable improvement in large tables. But for our WHERE CONSTRAINT, here's what we did....

01 SQL-ID PIC X(10).
01 SQL-SORT-TEMP-X PIC X(10).
01 SQL-SORT-TEMP REDEFINES SQL-SORT-TEMP-X PIC 9(16) USAGE COMP-X.
...

Then we placed this code before the START statement:

MOVE SQL-ID TO SQL-SORT-TEMP-X.
ADD 000004375 TO SQL-SORT-TEMP.
STRING ' SQL_ID
INTO A4GL-WHERE-CONSTRAINT.

We found that 000004375 works very well with our software, which has many tables that have a 10-digit ID at the beginning of the key. We could not come up with anything that worked for us with names however.

[Migrated content. Thread originally posted on 26 January 2006]

We are in the process of implementing a system using Acu4GL for SQL Server and the performance is so slow that it is totally unusable. Our in house SQL guru looked at the SQL profiler and said that as far as he can see the SQL side is performing optimally. He also said that the bulk of the SQL Queries hitting the database was in the form of SQL Cursors.

My question is this - Is anyone using SQL server with AcuCobol? Do you have performace problems? Have you made any changes to your Cobol code to include embedded SQL?

It appears to me that the Acu4GL creates queries in the form of SQL Cursors and this brings back the entire table. Many of our programs do a START and then a sequential read. I am thinking that we may have to change this to be embedded SQL that will select the records we want first.

Any ideas or observations appreciated!

Thanks!

John Roberts
Systems Engineer
Ciber
Law & Justice Solutions

I'm maintaining a customer table with lookup on last name or phone number. I'm simply appending Zs to the last name entered for lookup and putting that in the where contstraint. So they enter 'ROB' and the constaint becomes 'ROBZZZZZZZ' and the program returns all names that begin 'ROBA*' through 'ROBZZZZZZZ'.