#### esProc Simplifies SQL-style Computations - Tr...

*Machine*

*on 23 December 14*

MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.

I'm not curious

I'm not curious

SKIP>>

We built MyTechLogy for you

Help us to help you.

Share your expectations and experience to improve it.

Please enter your feedback.

Click here to continue..

Thank you for your Feedback

Your feedback would help us in sending you the most relevant job opportunities

Published on 08 July 14

1470

1

To say the least, even if there is top, it only makes it easy to get the preceding part lightly. If we change the problem into getting the 6th place to the 10th place, or seeking the salesman whose sales amount is 10% more than that of the next one, the difficulty is still there.

The reason causing this phenomenon lies in**the third important disadvantage of SQL: Lack the support of ordered set**.

SQL inherits the unordered set in mathematics, which directly causes the fact that the computations relating to sequence are rather difficult. And it can be imagined how common the computations relating to sequence (such as over the preceding month, over the same period last year, the first 20%, and rankings) will be.

The newly added window functions in SQL-2003 standard provides some computation capabilities relating to sequence, which makes it possible to solve some problems in a relatively simple method and alleviate the problem of SQL to a certain extent. But the use of window functions is often accompanied by sub-query, and it cannot enable user to directly use the sequence number to access set member, so there are still many ordered computations that are difficult to solve.

Now we want to pay attention to the gender proportion of the good salespersons that are computed out, that is, how many males and females there are respectively. Generally, the gender information is recorded in the employee table but not in the performance table, and it is simplified as follows:

**employee** Employees table

**name ** Names of employees, suppose there is no repeated name.

**gender** Genders of employees.

We have already computed out the name list of good salespersons, and the relatively natural idea is to seek out their genders from the employee table using name list, and count the number. But in SQL, it is necessary to use join operation to get information across tables . In this way, following the initial result, SQL will be written as:

*select employee.gender,count(*)*

from employee,

( ( select top 10 sales from sales_amount where product='AC' order by amount desc )

intersect

( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A

where A.sales=employee.name

group by employee.gender

With only an associated table more, it is made so over-elaborate and in reality there are rather more cross-table storages and they are often multi-layered. For example, for salespersons, there are departments where there are managers, and now we want to know by which managers these good salespersons are managed. Then there are three table joins, and it is indeed no easy job to write clear** where **and **group** in this computation.

This is just**the fourth important disadvantage of SQL as we want to say: Lack of object reference,** in relational algebra, the relations between objects completely depends on foreign key. This not only makes the efficiency very low in looking for relation, but also makes it impossible to directly treat the record pointed by foreign key as the attribute of primary record . Try thinking, can the above statement be written as this:

*select sales.gender,count(*)*

from (â¦) // â¦is the SQL computing the good salespersons above

group by sales.gender

Evidently, this statement is not only clearer, and at the same time, the computation will also be more efficient (without join computation).

We have analyzed, through a simple example, the four important difficulties of SQL. We believe this is just the main reason why SQL fails to reach the original intention of its invention. The process of solving business problem based on a kind of computation system is in fact the process of**translating business problems into formalized computation syntax**(similar to the case in which a pupil solves application problem, translates the problem into formalized four arithmetic operations). Before overcoming these difficulties, SQL model system rather does not comply with peopleâs natural thinking habit, causing great barriers in translating problems, making it very difficult for SQL to be applied, on a large scale, in data computation for business problems.

For still another example which is easily understood by programmer, use SQL as data computation, which is similar to the case in which assembly language is used to complete four arithmetic operations. We very easily write out the calculation expression such as**3+5*7**, but to use assembly language (take X86 as the example), it needs to be written as

**mov ax,3**

mov bx,5

mul bx,7

add ax,bx

In either writing or reading, such code is far inferior to**3+5*7** (it will be more troublesome if we come across decimal). Though it cannot be regarded as a big problem to a skilled programmer, to most people, however, this kind of writing is too hard to understand. In this sense, FORTRAN is really a great invention.

**Related:**

Thinking of Set in esProc

Referencing Thoughts in esProc

The reason causing this phenomenon lies in

SQL inherits the unordered set in mathematics, which directly causes the fact that the computations relating to sequence are rather difficult. And it can be imagined how common the computations relating to sequence (such as over the preceding month, over the same period last year, the first 20%, and rankings) will be.

The newly added window functions in SQL-2003 standard provides some computation capabilities relating to sequence, which makes it possible to solve some problems in a relatively simple method and alleviate the problem of SQL to a certain extent. But the use of window functions is often accompanied by sub-query, and it cannot enable user to directly use the sequence number to access set member, so there are still many ordered computations that are difficult to solve.

Now we want to pay attention to the gender proportion of the good salespersons that are computed out, that is, how many males and females there are respectively. Generally, the gender information is recorded in the employee table but not in the performance table, and it is simplified as follows:

We have already computed out the name list of good salespersons, and the relatively natural idea is to seek out their genders from the employee table using name list, and count the number. But in SQL, it is necessary to use join operation to get information across tables . In this way, following the initial result, SQL will be written as:

from employee,

( ( select top 10 sales from sales_amount where product='AC' order by amount desc )

intersect

( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A

where A.sales=employee.name

group by employee.gender

With only an associated table more, it is made so over-elaborate and in reality there are rather more cross-table storages and they are often multi-layered. For example, for salespersons, there are departments where there are managers, and now we want to know by which managers these good salespersons are managed. Then there are three table joins, and it is indeed no easy job to write clear

This is just

from (â¦) // â¦is the SQL computing the good salespersons above

group by sales.gender

Evidently, this statement is not only clearer, and at the same time, the computation will also be more efficient (without join computation).

We have analyzed, through a simple example, the four important difficulties of SQL. We believe this is just the main reason why SQL fails to reach the original intention of its invention. The process of solving business problem based on a kind of computation system is in fact the process of

For still another example which is easily understood by programmer, use SQL as data computation, which is similar to the case in which assembly language is used to complete four arithmetic operations. We very easily write out the calculation expression such as

mov bx,5

mul bx,7

add ax,bx

In either writing or reading, such code is far inferior to

Thinking of Set in esProc

Referencing Thoughts in esProc

Related Posts:

10 July 14For goodness sake - is this just an article to get some publicity ?

The complaint and ridiculous example are easily solved with a modicum of planning, and the stated 'disadvantages' are hardly a problem.

If this was a regular report, there is NO way you would query the current Sales table "Select top 10 sales from sales.....". That would already be a computed dataset that gets also used to calculate bonuses/performance reviews etc. It would also be segregated by sales period, product category and a dozen other required attributes.

If this report was a one off thingf big deal that you have an extra line and a two second poorer performance.

If anything, this article demonstrates the amazing power of the SQL 'language.'