Understanding “Set based” and “Procedural” approaches in SQL

Destruction of bugs: preparation of the apartment for disinfestation and its processing, how are these procedures

like SELECT @N =min(Number) from @Elements

Thanks and Regards
Narendra Singh Sign In· View Thread

Re: Remove hardcoding from SET @N = 1

ReginaJensen 4-Feb-15 8:30
Excellent recommendation!
Sign In· View Thread
My vote of 5 Аslam Iqbal 28-Apr-13 7:48
good article
Sign In· View Thread
This article is very interesting munna bhakta 2-Oct-12 19:55
I have taken much advantage by this tips.This article is very help in advance level where performance take first level .
Sign In· View Thread
Typo at the top of the article

Tim Schmelter 30-Aug-12 21:51
You have written: «why is Procedural approach better?».

But you wanted to say: «Why are Set based approaches better?»

Sign In· View Thread
Correction.

Jeff Moden 21-May-12 10:35
The author wrote.
«Moreover, data insertion on a table variable is a lot faster than on a temporary table as no disk I/O and inter DB communication takes place.»

I’ve not read the rest of this article (yet) but the above is incorrect. Table Variables and Temp Tables both start out in memory and they both have I/O because they both ultimately live in TempDB.

Table Variables are NOT memory only structures. Temp Tables are NOT disk only structures.

Sign In· View Thread
My vote of 5

Manoj Kumar Choubey 2-Apr-12 1:12
Nice
Sign In· View Thread
My vote of 5 rezaru2000 19-Sep-11 16:39
Nice very simple
Sign In· View Thread
Some things I don’t agree with on the post

manub22 29-Dec-10 2:00
#1 I don’t agree with you that table variable are memory only structures and they move to tempdb if grow.
Plz check this:

#2
The activities you mentioned with temp-tables are also with table-variables.
— The table variable has to be created
— Data has to be inserted on the table vairalbe
— Often, table-variable has to be joined with a physical table to obtain a result
— A lock has to be established on the temporary table while updating data on it, yes there is less locking with table-var
— Temporary table has to be dropped, yes agree, but sql engine also drops table-var from tempdb when out of scope.

#3
Also in what terms you say that table-variables can be used instead of CURSORS? Did you try with other CURSOR options except default options. Cursors with FORWARD_ONLY, STATIC, READ_ONLY gives you best performance & beats temp-tables & table-variables. It is a misconception among people without evaluating.
Check this link on MSDN, which is tested: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e67105a6-0f4a-4a12-85b9-e7e9855279e7/[^]

Sign In· View Thread
Set Logic vs Set Design

rreukema 14-Sep-10 5:56
Set Logic, as you have defined it, is incorrect. What you are describing is what is actually called Declarative Programming In all declarative programming, you describe what you want done, but do not get down to what is now called Imperative Programming, or as you have correctly described as procedural programming.

In Declarative Programming, you can still approach a problem procedurally — just have a look at most Oracle stored procedures, and the incorporation of procedural programming within them. IMHO (religious war may occur here), it’s the main difference between Oracle and MS SQL, and hence makes migration of applications between the two platforms very difficult (not to mention the differences between the languages).

Your descriptions of what to do to make stored procedures more effecient is absolutely correct. In nearly 20 years of development, I cannot recall ever using a loop within a stored procedure — that is not a Set Logic construct. However, I feel you should not approach the code, and change what you use, you should be aware of the differences when you approach the problem. This is where developers fall short today, they are so conditioned to learn procedural constructs, they simply do not have the experience with «Set design».

I’ll try a quick example. Let’s say you need to find all the houses in a city with red doors, and if you find a house with a red door you then have to determine the number of people living in the house in order to split $100 dollars between how many people are living there. (weird but it’s early and I can’t come up with anything else)

This all depends on the structure and attributes in the DB — but lets say the DB has addresses in it, with each address their is a building type (house, apartment, office tower, etc.). Only houses have additional attributes, one of which describes the color of the door (not likely but roll with it). Another table (one to many) contains the name and some personnel information of who is living in the home.

A procedural programmer (worse case) may: 1) extract all houses from the DB, 2) loop through each house looking for a red door, and when finding one, 3) retrieve the names of those people in the home — or — (smarter) ask the DB to count the number of people in the home and return the number, 4) make sure there is one or more people living in the home, and 5) divide the $100 by the number of people in the home.

Set base approach: 1) determine what is the most unique attribute for the information you are looking for (this is always the most important consideration as you want to limit the number of rows in your result set), 2) review indexes and/or key structure to leverage that information before going after the actual data (indexes are actually tables in the DB, but are more efficient because each row is smaller — it only contains the info in the index and a pointer — and is more ‘dense’ interms of how much information you really need fits in one physical «read» of a «page» as data comes off the disk).

In this example the red door — is likely the most unique piece of information — and if it has an index — would be the ideal candidate to minimimize the amount of data retrieved from the DB. Also, because the color of a door is only related to houses, we have killed two birds with one pass of the data — it also dramatically reduces the work requried to count how many people are in homes — as we only have to do that for those homes with a red door. Hence this should be the «outer query», and the «inner query» should then count the number of people with the door (note that through the inner/outer query definition we are introducing some procedural logic — do this before you do that.

At this point, some developers would then return the information, to do the division of individuals against the $100. However, if you pass the $100 dollars in as a parameter, we could create an inner most query to the actual division.

Did I miss something?

What if nobody lives in the house? In the summation query, we simply need to add a condition to exclude all homes with red doors that have one or more people living in the house. This would then remove the conditional logic when we divide as the set would only contain homes with red doors within more than one person in the house. If the set contains all the information we need (remember we passed in the $100), then the entire problem has been solved using «Set» logic.

The point here is that you have to THINK about how to reduce the Set in the DB, and then do procedure logic in imperative languages as opposed to declarative languages.

Sign In· View Thread
My vote of 2 KiwiPiet 6-Sep-10 21:38
I don’t agree with your ‘set based’ solutions here as they are not set based at all.
Sign In· View Thread
My vote of 3 Eric Xue (brokensnow) 3-Sep-10 22:48
Instead of a cursor, use a ‘while’ loop to process the result set??
Sign In· View Thread
good post Shahriar Iqbal Chowdhury/Galib 18-Apr-10 9:46
thanks for sharing
Sign In· View Thread
Good insights

Donsw 28-Apr-09 8:11
well written. good job.

Sign In· View Thread
Re: Good insights Al-Farooque Shubho 28-Apr-09 17:08
Thanks
Sign In· View Thread
Great article

professordavos 30-Mar-09 17:17
Thanks for explaining this. I suppose I have learned the correct way as I never make use of cursors or UDF’s.

In the example about the employer and manager, wouldn’t this return the same name twice? as you are essentially selecting name, name from employee.

Perhaps this table has a Manager_Name column that you intended to refer to? so the resulting query would return name, manager_name from employee.

Its inconsequential and doesn’t detract from the very useful info.

Or did I totally miss something there and get it wrong?

Sign In· View Thread
Re: Great article

Al-Farooque Shubho 30-Mar-09 17:52

I guess, you got it wrong.

The Employee(ID, Name,MgrID) table is a «Self referential» table which contains a MgrID column. This MgrID column values actually point to the ID column of the same table (Hence, self referential).

For example, an employee «John», has MgrID = 2. That means, John’s manager is someone who has ID 2 (Who is employee «Tom» in the same table).

So, in this case, the mentioned queries would return the following result for «John»

Employee Name—Manager Name
John——————Tom

Sign In· View Thread
Re: Great article

professordavos 30-Mar-09 18:00
Ah thanks for explaining that one.
Sign In· View Thread
Re: Great article Paul Horstink 27-Apr-09 20:11

However, this should be done much simpler, with a ‘self-refential’ join:

SELECT E.Name AS [Employee Name], M.Name AS [Manager Name]
FROM Employee E JOIN Employee M ON M.ID = E.MgrID

Sign In· View Thread
Re: Great article

Al-Farooque Shubho 27-Apr-09 20:32
Correct. «Self referential join» is a good way in terms of «simplicity»
Sign In· View Thread
. i.j.russell 23-Mar-09 3:09
The example of set-based that you provide is not set-based but procedural because you are still looping through the data item by item not as a set.
Sign In· View Thread
Re: . Al-Farooque Shubho 23-Mar-09 5:14

You are right. Actually, looping through the item is still a procedural approach.

I think I tried to point out how to avoid the «traditional» procedural approach of writing data processing logic using Cursors and UDF’s. Hence, using a Table variable is recommended in place of a Cursor.

But, still, I am going to edit the article for correctness. Many thanks for pointing this out.

Sign In· View Thread
Last Visit: 24-Apr-20 20:29 Last Update: 24-Apr-20 20:29 Refresh 1 2 Next »

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

www.codeproject.com

Share:
No comments

Добавить комментарий

Your e-mail will not be published. All fields are required.