PL_SQL compares with T_SQL

Question

PL_SQL compares with T_SQL

sqlfriend

SSC Guru

Points: 52781
More actions
September 7, 2017 at 11:54 am

#325224

I think this is a place I can ask this question, because most people here may know both PL_SQL and T_SQL.
As I understand, T_SQL is a set based language, cursor are rarely used, except special cases like administrative tasks etc.
PL_SQL is a procedure based language that use a lot of cursors.
I am new to PL_SQL, and I found a lot of code in our oracle database for stored procedures can be replaced just by using SQL statements. It seems oracle create a lot of cursors, and process it row by row.
It is slow performance, I don't see what the benefits, I know sometimes if you want to log what is the specific row that creates the error, cursor is handy.
Also I know in oracle they have ForAll and Bulk Collections, the cursor mentioned above with DML statement can be converted into new way of coding using ForALL and bulk Collections, but code written by using ForAll and bulk collections usually are much longer and complicated than cursor method. I just don't understand why not just use Plain SQL set based statement to do DMLs in the stored procedure, but to write either cursor or ForALL, Bulk collections.
Can experienced PLSQL and TSQL programmers shed some lights on this?
Thanks,

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Only spent a year working with Oracle but I can tell you that if you write set based code it will outperform the cursor based code. I strived to write set based code. The only time I used a cursor was to return a result to a calling application when a stored procedure had to return data.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

SQL Server's T-SQL and Oracle's PL/SQL are extensions of standard SQL. They both have procedural methods or operators in them. Most of the database platforms have some type of their own proprietary extensions to SQL.
Oracle implements cursors differently than SQL Server so I don't know how well you can compare them. Oracle also has several different types of cursors and it's not necessarily the same thing as cursor types in SQL Server.
Cursors are used a lot in Oracle and you can see them used frequently in best practices articles and performance tuning guidelines. With Oracle, cursors are pointers to a result set in a memory area. They are implemented implicitly with SQL statements if you haven't explicitly defined one. They can be incredibly fast. It doesn't mean explicit cursors in Oracle can't be abused but with the differences from SQL Server they are used differently and perform differently.

Sue

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

But use many dml in cursors in pl_sql caused context switch between sql engine and plsql engine which affects performance. My question is why not just use standard sql statements (set based) working on set in the stored procedure instead of use cursor for row by row processing?

Sue_H SSC Guru Points: 90860 More actions · Answer 4

sqlfriends - Thursday, September 7, 2017 10:27 PM
But use many dml in cursors in pl_sql caused context switch between sql engine and plsql engine which affects performance. My question is why not just use standard sql statements (set based) working on set in the stored procedure instead of use cursor for row by row processing?

The use of standard SQL is done in Oracle. It's just like in SQL Server most people avoid cursors. In Oracle most people avoid explicit cursors. It depends on what they are doing with the results. Just like some administrative types of things in SQL Server may require the use of cursors. It does not mean every administrative task is done in cursors. Use of bulk binds , such as FORALL and bulk_collect, do operate on the result set in bulk.
In Oracle, they also recommend avoiding explicit cursors and it is considered best to use set based operations. When a cursor does need to be used for whatever reason in Oracle, they usually perform well. They are very different compared to SQL Server cursors with different memory structures. As I said, I don't think you can compare cursors in Oracle to cursors in SQL Server.

Each platform has extensions to standard SQL that are used for different reasons so the context of why something is being done needs to be taken into consideration. Moving away from standard SQL and using the extensions a database platforms SQL language is done to meet some type of need or functionality you don't have in standard SQL. If you have seen nothing but explicit cursors for everything, you probably should look else where. Same would be true if you saw CLR used for everything in SQL Server. That doesn't mean that neither of those have no purpose or use in their respective languages.

Sue

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

SQL will likely be faster as PL/SQL causes cpu-context-switches.
Perhaps they made cursors for the error-handling?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

A lot of Oracle programming is done in cursors. I found it was largely by convention...a best practice that stuck with people as they mentored younger and less experienced people. That's the way I learned (by a truly great mentor, BTW) when I was first learning Oracle. I remember years later when my employer switched from using Oracle Forms and Reports (version 7.3!!!) to using packages served out over a DAD. Nearly everything, even code to get a single row, was written in a cursor in the original form. I rewrote a lot of them to make the package code simpler and suffered no ill affects.

PL/SQL cursors are handled in Oracle the way Sue described above, which makes them faster than T-SQL cursors. However, you can get some very fast set-based code to run in Oracle. Personally, I never raced set-based against cursors in Oracle, so I can't speak to the performance differences.

My advice is to give it a try and test, test, test. A lot has changed in Oracle since 7.3 and 8i, so there may very well be faster ways of doing things by now.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

PL\SQL is not a procedural language. It's used a lot that way but still seriously benefits from set-based coding.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jeff Moden - Tuesday, September 12, 2017 12:17 PM
PL\SQL is not a procedural language. It's used a lot that way but still seriously benefits from set-based coding.

I have to agree even though I only worked with it for one year.

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

I see on this page: https://en.wikipedia.org/wiki/PL/SQL

PL/SQL (Procedural Language/Structured Query Language

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

sqlfriends - Tuesday, September 12, 2017 1:28 PM
I see on this page: https://en.wikipedia.org/wiki/PL/SQL

PL/SQL (Procedural Language/Structured Query Language

Still, set based will run better.

sqlfriend SSC Guru Points: 52781 More actions · Answer 11

I agree that, just wonder why Jeff said it is not a procedure language. but the website said it is :

PL/SQL (Procedural Language/Structured Query Language)

Sue_H SSC Guru Points: 90860 More actions · Answer 12

Correct, the PL does stand for Procedural Language.
Oracle has SQL and then extend that with PL/SQL
SQL Server has SQL and then extends that with T-SQL.
Both extensions have procedural components.

Part of the difficulties in understanding all of that is Oracle essentially uses a different processing method with PL/SQL, which goes back to some of what you were originally asking about. My apologies - I'll try to do a little better in explaining it than I did before.

So when you write something in PL/SQL, it is different than regular SQL but you move to that if just standard SQL doesn't meet your needs. Just as you would use more T-SQL extensions if using ANSI/ISO only SQL in SQL server doesn't meet your needs.

In either case, you don't move away from standard SQL when you don't need to.
Years ago, Oracle's standard SQL was a bit on the weak side and not all that ansi compliant. From there a lot of people got too dependent on using PL/SQL. When you combine that with programmers who don't think in sets and are generally comfortable with more procedural things, you get a mix of what is used and sometimes a mess.

For what you saw It could be that the stored procedures could be implemented with standard SQL - you'd have to look at whether anything in those stored procedures needs to be using or doing something that is not available in standard SQL.
And getting to the other part you asked about with context switches - yup, that happens when you use PL/SQL and is something to be aware of as it can become an issue although is not always an issue. So again, you want to go back to asking if using just standard SQL will meet your needs.
When you use PL/SQL, a cursor will be created whether it is explicit or implicit. But it's not really the same as a SQL Server cursor. In Oracle, it's a pointer to a work area in memory that parses and executes (and I think does some tuning at some level) the SQL statement. So really very different from a cursor in SQL Server. The overhead is different for explicit vs implicit cursors so you are better off using implicit whenever possible, avoid explicit. They have changed some operations so that things like bulk collections operate in bulk instead of row by row. But the context switching has been asked about more in recent years and I rarely dabble into Oracle anymore (it was my primary for about 5 years and then sporadic support/use for awhile). But you may want to search that and see what you can come up with. Search with PL/SQL included as that is what it applies to.

Any of the relational database platforms operate better with standard SQL set based operations. The problem has always been that the standard SQL doesn't always meet the needs and each platform ended up with it's own extensions. So that's basically an attempt to answer your why in your post.

Sue

Edited: typo

sqlfriend SSC Guru Points: 52781 More actions · Answer 13

Thanks Sue, that makes a lot of sense.
The stored procedure I saw in the database can mostly be replaced by set based standard queries, other than some log and error handling that needs to log/specify particular record usually identified by IDs,

And later oracle invented the bulk collection and forAll statement that handles records in batch, but in more complicated code and longer code. I just don't understand why they simply use standard SQL.

So basically I think unless you cannot do without standard SQl, then you can use PL_SQL with cursors.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 14

sqlfriends - Tuesday, September 12, 2017 3:01 PM
Thanks Sue, that makes a lot of sense.
The stored procedure I saw in the database can mostly be replaced by set based standard queries, other than some log and error handling that needs to log/specify particular record usually identified by IDs,
And later oracle invented the bulk collection and forAll statement that handles records in batch, but in more complicated code and longer code. I just don't understand why they simply use standard SQL.
So basically I think unless you cannot do without standard SQl, then you can use PL_SQL with cursors.
Thanks

Yup, pretty much. Keep in mind, PL/SQL is a lot more than the cursors. I think Ed mentioned earlier not using them often - I didn't either. But I did use packages and such.
I was just looking for articles on the context switching for you and it's funny because I found an article basically saying just what you said above - it has a section Stop Using PL/SQL when you can use SQL:
PL/SQL: Stop Making the Same Performance Mistakes

But here one of the articles on context switching and addresses some of your other points:
On Cursors, Context Switches, and Mistakes

Sue