SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PL_SQL compares with T_SQL


PL_SQL compares with T_SQL

Author
Message
sqlfriends
sqlfriends
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34215 Visits: 4595

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,


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)

Group: General Forum Members
Points: 271611 Visits: 41183
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sue_H
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494

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




sqlfriends
sqlfriends
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34215 Visits: 4595
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
Sue_H
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41464 Visits: 11494
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
Jo Pattyn
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20889 Visits: 10505
SQL will likely be faster as PL/SQL causes cpu-context-switches.
Perhaps they made cursors for the error-handling?
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)SSC Guru (190K reputation)

Group: General Forum Members
Points: 190003 Visits: 11898
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
Jeff Moden
SSC Guru
SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)SSC Guru (615K reputation)

Group: General Forum Members
Points: 615140 Visits: 45183
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)SSC Guru (271K reputation)

Group: General Forum Members
Points: 271611 Visits: 41183
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sqlfriends
sqlfriends
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34215 Visits: 4595
I see on this page: https://en.wikipedia.org/wiki/PL/SQL

PL/SQL (Procedural Language/Structured Query Language
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search