Reply to: Bob update

[suityou01]

The identity statement was one I use for SQL server. I assume that there would be similar for Oracle. In any case, if SY is using cursors he would still need the @DinnerId to insert into Dinner_Item table. Therefore substitute my identity statement to whatever method SY is currently using.

The debate here is for the use of cursors in a stored procedure and I have stated that for the problem that SY is facing, cursors are not required and a simple SQL statement would suffice.

Nope. This was covered in post 39.

The stored procedure debate trundles on, but we were forced into using stored procs as Oracle doesn't have an identity field, so we need to use sequences so this is all wrapped up in the insert stored proc, and the new id value returned as an output variable.

But you keep on going flower.

[suityou01]

It is supposed to be a mighty enterprise level database so I would assume it would support standard SQL queries.

All I would say is lets wait and see what our BPM architect has to say about my solution. He has already commented that I should stick to bog cleaning which I assume was a hasty retort.

Oh no, I still see you in a white coat mopping out trap 3.

[suityou01]

Does it matter which database is being used ? My solution was a general "SQL" solution which should work on any database.

Or are you saying the Insert into SELECT from query is not supported by Oracle ?

Keep digging pal.

Although in fairness couple your solution with Doodabs syntaxy thingy and you two have a solution that does indeed avoid cursors. Which as I understand from all wailing and gnashing of teeth on here is a good thing. So hats off.

[suityou01]

On a serious note you do realise you can insert multiple rows using a sequence with something like

insert into dinner_items select seq.nextval, data from dinner_items_metadata where dinner_type = 'lardarse deluxe combo'

don't you?

Nope I didn't as I'm not an Oracle guy. This looks gorgeous though.

Love you, don't forget to invoice.

:

[fullyautomatix]

Define "standard SQL queries"

Your code wouldn't run on Oracle because "SELECT @DinnerId = @@Identity" is meaningless to Oracle.

The approach doesn't look bad, but the suggested implementation won't do it.

The identity statement was one I use for SQL server. I assume that there would be similar for Oracle. In any case, if SY is using cursors he would still need the @DinnerId to insert into Dinner_Item table. Therefore substitute my identity statement to whatever method SY is currently using.

The debate here is for the use of cursors in a stored procedure and I have stated that for the problem that SY is facing, cursors are not required and a simple SQL statement would suffice.

[TheFaQQer]

It is supposed to be a mighty enterprise level database so I would assume it would support standard SQL queries.

All I would say is lets wait and see what our BPM architect has to say about my solution. He has already commented that I should stick to bog cleaning which I assume was a hasty retort.

Define "standard SQL queries"

Your code wouldn't run on Oracle because "SELECT @DinnerId = @@Identity" is meaningless to Oracle.

The approach doesn't look bad, but the suggested implementation won't do it.

[fullyautomatix]

Did not look at it closely, but I think your solution is more or less what I what I would have thought.

SQL was invented as the simplest and most elegant way to specify any database operation, if Oracle can't handle this solution, it's Oracle not the solution that's a failure.

In short, if Oracle can't do absolutely everything without cursors, its not a proper SQL DBMS.

If it can do everything, but sometimes much slower that it would for a procedural approach, then it's a poor SQL DBMS.

There is nothing in the definition of pure SQL that constrains execution to be slower than a hand-coded procedural solution. SQL specifies what is to be done, not how it is to be done, so if a particular procedural algorithm is optimal, it is the optimisers job to find it. If Oracle is slower with pure SQL than it is with hand-crafted procedural code, it means the DBMS has a poor optimiser.

(I realise this is a bit of an Ivory Tower response. In fairness, I don't know for certain that a SQL DBMS with a perfect optimiser actually exists. I have a good impression of SQL Server, though no large system experience, and believe that the views of my mainframe colleagues are outdated and wrong. They regard all SQL DBMS's, including Oracle, as toys, unfit for any serious purpose.)

It is supposed to be a mighty enterprise level database so I would assume it would support standard SQL queries.

All I would say is lets wait and see what our BPM architect has to say about my solution. He has already commented that I should stick to bog cleaning which I assume was a hasty retort.

[IR35 Avoider]

Does it matter which database is being used ? My solution was a general "SQL" solution which should work on any database.

Or are you saying the Insert into SELECT from query is not supported by Oracle ?

Did not look at it closely, but I think your solution is more or less what I what I would have thought.

SQL was invented as the simplest and most elegant way to specify any database operation, if Oracle can't handle this solution, it's Oracle not the solution that's a failure.

In short, if Oracle can't do absolutely everything without cursors, its not a proper SQL DBMS.

If it can do everything, but sometimes much slower that it would for a procedural approach, then it's a poor SQL DBMS.

There is nothing in the definition of pure SQL that constrains execution to be slower than a hand-coded procedural solution. SQL specifies what is to be done, not how it is to be done, so if a particular procedural algorithm is optimal, it is the optimisers job to find it. If Oracle is slower with pure SQL than it is with hand-crafted procedural code, it means the DBMS has a poor optimiser.

(I realise this is a bit of an Ivory Tower response. In fairness, I don't know for certain that a SQL DBMS with a perfect optimiser actually exists. I have a good impression of SQL Server, though no large system experience, and believe that the views of my mainframe colleagues are outdated and wrong. They regard all SQL DBMS's, including Oracle, as toys, unfit for any serious purpose.)

[d000hg]

Does it matter which database is being used ? My solution was a general "SQL" solution which should work on any database.

Or are you saying the Insert into SELECT from query is not supported by Oracle ?

For a plain DB holding data, for a small-medium app, you are right. The moment you want stored procedures or anything fancy, nope.

[TheFaQQer]

Oracle is nowhere in the TPC-E table for performance or price/performance

TPC-E - Homepage

MS SQL Server holds every position.

TPC-E is a much more modern and relevant benchmark.




HTH BIDI

Ah, but on a 30000GB database, the TPC-H benchmark has only Oracle on it

[fullyautomatix]

Because it's Oracle dumbo.

To insert a row you need sequences, implicit cursors, ref cursor for the ID etc.

Jeez, ever wondered why an Oracle licence costs like £5bn per server?

Does it matter which database is being used ? My solution was a general "SQL" solution which should work on any database.

Or are you saying the Insert into SELECT from query is not supported by Oracle ?

[DimPrawn]

Oracle comes top of the TPC-C benchmarks, both in terms of performance and price/performance

Oracle is nowhere in the TPC-E table for performance or price/performance

TPC-E - Homepage

MS SQL Server holds every position.

TPC-E is a much more modern and relevant benchmark.

TPC Benchmark™ E (TPC-E) is a new On-Line Transaction Processing (OLTP) workload developed by the TPC. The TPC-E benchmark uses a database to model a brokerage firm with customers who generate transactions related to trades, account inquiries, and market research. The brokerage firm in turn interacts with financial markets to execute orders on behalf of the customers and updates relevant account information.
The benchmark is “scalable,” meaning that the number of customers defined for the brokerage firm can be varied to represent the workloads of different-size businesses. The benchmark defines the required mix of transactions the benchmark must maintain. The TPC-E metric is given in transactions per second (tps). It specifically refers to the number of Trade-Result transactions the server can sustain over a period of time.

Although the underlying business model of TPC-E is a brokerage firm, the database schema, data population, transactions, and implementation rules have been designed to be broadly representative of modern OLTP systems.

HTH BIDI

[TheFaQQer]

Probably because it's no faster than the competition, no more reliable than the competition, no more productive than the competion, no more feature rich than the competition, but costs a LOT more than the competition.

A bit like paying Mercedes money for a Kia.

Oracle comes top of the TPC-C benchmarks, both in terms of performance and price/performance

[RobertRice]

Welcome sir

[Hill Station Murthy]

It is a dull thread (apart from Hill Station's rather amusing posts).

Welcome sir