Very Computer

by **KPK** » Mon, 13 May 2002 01:31:39

Hello all:

We are building support into our Powerbuilder 7.0 application to allow our
customers to utilize their Oracle databases (we are testing against 8i).

Right now our application has support for Microsoft SQL Server 7.0 and 2000
as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7. Oracle
(later DB/2 as well) support is being added now.

In our application, we make use of temporary tables quite a bit. Consider
the following example:

Set SQLCA.AutoCommit = TRUE

Create Table #TempVols(ObjID int, StartOil float, EndOil float)

Insert Into #TempVols(ObjID, StartOil, EndOil)
Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
Sum(EndingOilVol)
From CompletionDaily, TankDaily
Where CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
'02-02-2002'
and UserID = 400

Update TankCompletionDaily
Set BeginningOil = StartOil,
EndingOil = EndOil
From #TempVols
Where ObjID = TankCompID and RecordDate = '02-02-2002' //or whatever date we
want

Just ignore the "set sqlca.autocommit = true" line because that is unique to
Powerbuilder.

I don't want to use a nested SELECT for each column being updated because
it's too slow. The #Temp Table method is much faster as I have already
tested it.

What's nice about this, is that I can create it in a stored procedure or
within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction Object.
The stored procedure is a bit faster than doing it in PB, however.

How can I accomplish this same task within 8i using a temp table? The
reason for the temp table is that both Sybase and SQL Server handle the temp
table a bit differently from a normal table.

In what ways you ask?

Well first, when the temp table is created, both databases generate a unique
value and append it to the temp table so that if any other users run this
code simultaneously, they too will have their own temp table with different
values assigned to their current transaction/connection session therefore
eliminating the possibility of table name conflicts. Once the code above is
complete, both databases (after the commit) will delete the temp table.
Another reason for using a temp table is that we have some stored procedures
that are quite long. We create the temp table at the beginning of the
stored procedure and populate it with ONLY those records that meet our
selection criteria and the rest of the stored procedure's queries and sql
transactions can make use and even join against that temp table.

Any suggestions?

by **Sybrand Bakke** » Mon, 13 May 2002 00:12:27

Quote:> Hello all:

> We are building support into our Powerbuilder 7.0 application to allow our
> customers to utilize their Oracle databases (we are testing against 8i).

> Right now our application has support for Microsoft SQL Server 7.0 and
2000
> as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7. Oracle
> (later DB/2 as well) support is being added now.

> In our application, we make use of temporary tables quite a bit. Consider
> the following example:

> Set SQLCA.AutoCommit = TRUE

> Create Table #TempVols(ObjID int, StartOil float, EndOil float)

> Insert Into #TempVols(ObjID, StartOil, EndOil)
> Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
> Sum(EndingOilVol)
> From CompletionDaily, TankDaily
> Where CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
> '02-02-2002'
> and UserID = 400

> Update TankCompletionDaily
> Set BeginningOil = StartOil,
> EndingOil = EndOil
> From #TempVols
> Where ObjID = TankCompID and RecordDate = '02-02-2002' //or whatever date
we
> want

> Just ignore the "set sqlca.autocommit = true" line because that is unique
to
> Powerbuilder.

> I don't want to use a nested SELECT for each column being updated because
> it's too slow. The #Temp Table method is much faster as I have already
> tested it.

> What's nice about this, is that I can create it in a stored procedure or
> within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction
Object.
> The stored procedure is a bit faster than doing it in PB, however.

> How can I accomplish this same task within 8i using a temp table? The
> reason for the temp table is that both Sybase and SQL Server handle the
temp
> table a bit differently from a normal table.

> In what ways you ask?

> Well first, when the temp table is created, both databases generate a
unique
> value and append it to the temp table so that if any other users run this
> code simultaneously, they too will have their own temp table with
different
> values assigned to their current transaction/connection session therefore
> eliminating the possibility of table name conflicts. Once the code above
is
> complete, both databases (after the commit) will delete the temp table.
> Another reason for using a temp table is that we have some stored
procedures
> that are quite long. We create the temp table at the beginning of the
> stored procedure and populate it with ONLY those records that meet our
> selection criteria and the rest of the stored procedure's queries and sql
> transactions can make use and even join against that temp table.

> Any suggestions?

1 Try to learn Oracle, or better still try to learn SQL.
Sucessful one on one ports from Sqlserver to Oracle don't exist, because
Oracle is not sqlserver sold by a different vendor. If I have to investigate
performance problems on a database which is used for an application which
has been ported from Sqlserver to Oracle, that is ALWAYS the root cause: the
developers involved simply didn't know anything about Oracle. Read the
initial chapters of Tom Kyte's book and you will see this demonstrated into
more detail

2 If you *really* think you can't do without temp tables (and the above
example shows you resort to temp tables for simply *everything*)
, check out the Oracle manuals on GLOBAL TEMP tables.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

by **KPK** » Mon, 13 May 2002 13:21:16

Thanks for the reply.

I'll have you know that I've been writing SQL now since the WatCOM SQL days
(late 80s-early 90s). I've been writing software since 1981 and started out
with DBase and moved into Sybase on Unix and then SQL Server and Sybase on
Windows. I've written scalable sybase and sql server-based applications
that are being used by 3 of the top 10 independent Oil and Gas producers in
the United States. I have also designed and written SQL Server and Sybase
applications used in plant Safety Systems that are now responsible for
saving the lives of many oilfield and hydrocarbon plant workers in the Gulf
of Mexico region. These apps are not only scalable, but are very fast (at
the database level) in those areas where timing is critical for setting
Alarm conditions. Our intentions for this application is not to merely
*TACK ON* support for Oracle, but investigate those areas where Oracle and
(later DB/2) provide the best performance. It just so happens that our
tests have shown that both Sybase and SQL Server perform well utilizing the
*temp table* technique I illustrated. My intentions are not to try and
stabb at Oracle, but to acquire valuable technical information from folks (I
figured) knew more about the product than I currently do. Over the past
several months, I've been periodically visiting a few of these Oracle
newsgroups and it seems that everytime a developer who previously utilized a
competing database asks a simple (How To using Oracle) question, they are
branded as SQL idiots! As if PL/SQL is the ONLY SQL there is. As I recall
Transact SQL has been around for quite sometime as well and is also very
successful and well accepted. I happen to like PL/SQL as I've done some ADA
programming back in the day. We are merely trying to better understand the
syntax and the best techniques to use within the Oracle database. That's
why at the end of my posting, I asked "Any Suggestions?" If indeed the temp
table method is not the best approach, then suggest something that is or
don't. This application is our company's livelihood and we only hurt our
company by not optimizing performance on all of the major DB platforms in
the industry which we serve. Pardon me and all of us non-Oracle developers
trying to better understand and embrace Oracle for using newsgroups in the
manner which they were meant to be used. I do indeed apologize and I do
intend to purchase one of the many great Oracle PL/SQL titles out there. I
just thought I could reach out to my fellow developers without being
chastized for not first reading some book. By providing support for Oracle
in our application only helps those smaller independents who have not yet
standardized or even utilize any SQL database consider using Oracle within
their organization. Larry Ellison would be proud. Up until about 3 years
ago, there was virtually NO presence of Oracle in the offshore environment.
Now that companies like ours and oilfield consultant firms like the last one
I worked for suggest Oracle, it is beginning to replace Sybase and SQL
Server out there.

With regard to your comment:

Quote:> 2 If you *really* think you can't do without temp tables (and the above
> example shows you resort to temp tables for simply *everything*)
> , check out the Oracle manuals on GLOBAL TEMP tables.

I only cited one example of where the temp table (for those platforms)
offers the greatest benefit and talked about how we've used them in stored
procedures (for those platforms). Don't have a cow!
I have written more than a thousand stored procedures and triggers and made
use of temp tables in perhaps 10% to 12% of them. I don't know where you
get these *assumptions*!

> > Hello all:

> > We are building support into our Powerbuilder 7.0 application to allow
our
> > customers to utilize their Oracle databases (we are testing against 8i).

> > Right now our application has support for Microsoft SQL Server 7.0 and
> 2000
> > as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7. Oracle
> > (later DB/2 as well) support is being added now.

> > In our application, we make use of temporary tables quite a bit.
Consider
> > the following example:

> > Set SQLCA.AutoCommit = TRUE

> > Create Table #TempVols(ObjID int, StartOil float, EndOil float)

> > Insert Into #TempVols(ObjID, StartOil, EndOil)
> > Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
> > Sum(EndingOilVol)
> > From CompletionDaily, TankDaily
> > Where CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
> > '02-02-2002'
> > and UserID = 400

> > Update TankCompletionDaily
> > Set BeginningOil = StartOil,
> > EndingOil = EndOil
> > From #TempVols
> > Where ObjID = TankCompID and RecordDate = '02-02-2002' //or whatever
date
> we
> > want

> > Just ignore the "set sqlca.autocommit = true" line because that is
unique
> to
> > Powerbuilder.

> > I don't want to use a nested SELECT for each column being updated
because
> > it's too slow. The #Temp Table method is much faster as I have already
> > tested it.

> > What's nice about this, is that I can create it in a stored procedure or
> > within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction
> Object.
> > The stored procedure is a bit faster than doing it in PB, however.

> > How can I accomplish this same task within 8i using a temp table? The
> > reason for the temp table is that both Sybase and SQL Server handle the
> temp
> > table a bit differently from a normal table.

> > In what ways you ask?

> > Well first, when the temp table is created, both databases generate a
> unique
> > value and append it to the temp table so that if any other users run
this
> > code simultaneously, they too will have their own temp table with
> different
> > values assigned to their current transaction/connection session
therefore
> > eliminating the possibility of table name conflicts. Once the code
above
> is
> > complete, both databases (after the commit) will delete the temp table.
> > Another reason for using a temp table is that we have some stored
> procedures
> > that are quite long. We create the temp table at the beginning of the
> > stored procedure and populate it with ONLY those records that meet our
> > selection criteria and the rest of the stored procedure's queries and
sql
> > transactions can make use and even join against that temp table.

> > Any suggestions?

> 1 Try to learn Oracle, or better still try to learn SQL.
> Sucessful one on one ports from Sqlserver to Oracle don't exist, because
> Oracle is not sqlserver sold by a different vendor. If I have to
investigate
> performance problems on a database which is used for an application which
> has been ported from Sqlserver to Oracle, that is ALWAYS the root cause:
the
> developers involved simply didn't know anything about Oracle. Read the
> initial chapters of Tom Kyte's book and you will see this demonstrated
into
> more detail

> 2 If you *really* think you can't do without temp tables (and the above
> example shows you resort to temp tables for simply *everything*)
> , check out the Oracle manuals on GLOBAL TEMP tables.

> Regards

> --
> Sybrand Bakker
> Senior Oracle DBA

> to reply remove '-verwijderdit' from my e-mail address

by **Jim Kenned** » Mon, 13 May 2002 14:15:26

Unlike Sqlserver and Sybase you don't need (or very very rarely) need temp
tables in Oracle. Yes, in Sybase and Sqlserver you need them and I am sure
you are correct that using them in those environments gives you the fastest
performance in those environments - no arguement. In Oracle however, you
don't have to go through all those gyrations. (Good approuch possibly for
SQLServer and Sybase - not a good approuch in Oracle. I am not saying you
are stupid; I am saying things work differently.) You can use SQL (not
pl/sql or transact, just sql). If you still feel you just gotta have temp
tables use global temporary tables otherwise performance will suffer
greatly. In Oracle readers don't block writers and writers don't block
readers. It will save you a lot of pain and anguish to try some simple
experiements in Oracle and see that the differences are great and use those
differences to your advantage. Again, this has nothing to do with your
intelligence; it is just the products are different and assumptions in one
are not valid in the other.

Tom Kyte's book is excellent and well worth the money. His site is at:
http://asktom.oracle.com/pls/ask/f?p=4950:1:

Some useful links:
http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:48812348054,%7Bsybase%7D%20and%20%7Btemp%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1423204060137,%7Bsybase%7D%20and%20%7Btemp%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1164655862293,%7Bsybase%7D%20and%20%7Btemp%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:734225532321,%7Bsybase%7D%20and%20%7Btemp%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1886476148373,%7Bsybase%7D%20and%20%7Boracle%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1502605216980,%7Bsybase%7D%20and%20%7Boracle%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1136552219291,%7Bsybase%7D%20and%20%7Boracle%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:61012348055,%7Bsybase%7D%20and%20%7Boracle%7D

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISP...
4950_P8_CRITERIA:1390203651798,%7Bsybase%7D%20and%20%7Boracle%7D

That should help.

Jim

"KPK" <kpkel...@alltel.net> wrote in message

news:abkjht$e0b$1@iac5.navix.net...

> Thanks for the reply.

> I'll have you know that I've been writing SQL now since the WatCOM SQL
days
> (late 80s-early 90s). I've been writing software since 1981 and started
out
> with DBase and moved into Sybase on Unix and then SQL Server and Sybase on
> Windows. I've written scalable sybase and sql server-based applications
> that are being used by 3 of the top 10 independent Oil and Gas producers
in
> the United States. I have also designed and written SQL Server and Sybase
> applications used in plant Safety Systems that are now responsible for
> saving the lives of many oilfield and hydrocarbon plant workers in the
Gulf
> of Mexico region. These apps are not only scalable, but are very fast (at
> the database level) in those areas where timing is critical for setting
> Alarm conditions. Our intentions for this application is not to merely
> *TACK ON* support for Oracle, but investigate those areas where Oracle and
> (later DB/2) provide the best performance. It just so happens that our
> tests have shown that both Sybase and SQL Server perform well utilizing
the
> *temp table* technique I illustrated. My intentions are not to try and
> stabb at Oracle, but to acquire valuable technical information from folks
(I
> figured) knew more about the product than I currently do. Over the past
> several months, I've been periodically visiting a few of these Oracle
> newsgroups and it seems that everytime a developer who previously utilized
a
> competing database asks a simple (How To using Oracle) question, they are
> branded as SQL idiots! As if PL/SQL is the ONLY SQL there is. As I
recall
> Transact SQL has been around for quite sometime as well and is also very
> successful and well accepted. I happen to like PL/SQL as I've done some
ADA
> programming back in the day. We are merely trying to better understand
the
> syntax and the best techniques to use within the Oracle database. That's
> why at the end of my posting, I asked "Any Suggestions?" If indeed the
temp
> table method is not the best approach, then suggest something that is or
> don't. This application is our company's livelihood and we only hurt our
> company by not optimizing performance on all of the major DB platforms in
> the industry which we serve. Pardon me and all of us non-Oracle
developers
> trying to better understand and embrace Oracle for using newsgroups in the
> manner which they were meant to be used. I do indeed apologize and I do
> intend to purchase one of the many great Oracle PL/SQL titles out there.
I
> just thought I could reach out to my fellow developers without being
> chastized for not first reading some book. By providing support for
Oracle
> in our application only helps those smaller independents who have not yet
> standardized or even utilize any SQL database consider using Oracle within
> their organization. Larry Ellison would be proud. Up until about 3 years
> ago, there was virtually NO presence of Oracle in the offshore
environment.
> Now that companies like ours and oilfield consultant firms like the last
one
> I worked for suggest Oracle, it is beginning to replace Sybase and SQL
> Server out there.

> With regard to your comment:
> > 2 If you *really* think you can't do without temp tables (and the above
> > example shows you resort to temp tables for simply *everything*)
> > , check out the Oracle manuals on GLOBAL TEMP tables.

> I only cited one example of where the temp table (for those platforms)
> offers the greatest benefit and talked about how we've used them in stored
> procedures (for those platforms). Don't have a cow!
> I have written more than a thousand stored procedures and triggers and
made
> use of temp tables in perhaps 10% to 12% of them. I don't know where you
> get these *assumptions*!

> "Sybrand Bakker" <post...@sybrandb.demon.nl> wrote in message
> news:udqdq1p88dk5dd@corp.supernews.com...

> > "KPK" <kpkel...@alltel.net> wrote in message
> > news:abj9vb$lqd$1@iac5.navix.net...
> > > Hello all:

> > > We are building support into our Powerbuilder 7.0 application to allow
> our
> > > customers to utilize their Oracle databases (we are testing against
8i).

> > > Right now our application has support for Microsoft SQL Server 7.0 and
> > 2000
> > > as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7.
Oracle
> > > (later DB/2 as well) support is being added now.

> > > In our application, we make use of temporary tables quite a bit.
> Consider
> > > the following example:

> > > Set SQLCA.AutoCommit = TRUE

> > > Create Table #TempVols(ObjID int, StartOil float, EndOil float)

> > > Insert Into #TempVols(ObjID, StartOil, EndOil)
> > > Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
> > > Sum(EndingOilVol)
> > > From CompletionDaily, TankDaily
> > > Where CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
> > > '02-02-2002'
> > > and UserID = 400

> > > Update TankCompletionDaily
> > > Set BeginningOil = StartOil,
> > > EndingOil = EndOil
> > > From #TempVols
> > > Where ObjID = TankCompID and RecordDate = '02-02-2002' //or whatever
> date
> > we
> > > want

> > > Just ignore the "set sqlca.autocommit = true" line because that is
> unique
> > to
> > > Powerbuilder.

> > > I don't want to use a nested SELECT for each column being updated
> because
> > > it's too slow. The #Temp Table method is much faster as I have
already
> > > tested it.

> > > What's nice about this, is that I can create it in a stored procedure
or
> > > within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction
> > Object.
> > > The stored procedure is a bit faster than doing it in PB, however.

> > > How can I accomplish this same task within 8i using a temp table? The
> > > reason for the temp table is that both Sybase and SQL Server handle
the
> > temp
> > > table a bit differently from a normal table.

> > > In what ways you ask?

> > > Well first, when the temp table is created, both databases generate a
> > unique
> > > value and append it to the temp table so that if any other users run
> this
> > > code simultaneously, they too will have their own temp table with
> > different
> > > values assigned to their current transaction/connection session
> therefore
> > > eliminating the possibility of table name conflicts. Once the code
> above
> > is
> > > complete, both databases (after the commit) will delete the temp
table.
> > > Another reason for using a temp table is that we have some stored
> > procedures
> > > that are quite long. We create the temp table at the beginning of the
> > > stored procedure and populate it with ONLY those records that meet our
> > > selection criteria and the rest of the stored procedure's queries and
> sql
> > > transactions can make use and even join against that temp table.

> > > Any suggestions?

> > 1 Try to learn Oracle, or better still try to learn SQL.
> > Sucessful one on one ports from Sqlserver to Oracle don't exist, because
> > Oracle is not sqlserver sold by a different vendor. If I have to
> investigate
> > performance problems on a database which is used for an application
which
> > has been ported from Sqlserver to Oracle, that is ALWAYS the root cause:
> the
> > developers involved simply didn't know anything about Oracle. Read the
> > initial

...

read more »

by **KPK** » Mon, 13 May 2002 22:56:05

And indeed this is the type of response I was looking for. An educated one.
Yes. These products are different and Yes we want to use the method(s) that
provide the best performance on each. I am not a temp table advocate for if
there were a better way of doing this rather than using temp tables in
sybase and sql server, then we would have pursued them. The dev team and I
do plan on doing some experimentation, but we are under a time constraint on
the project for one of our customers and really need to get a compiled beta
version out to them for testing. Once they are busy testing, they will be
off our backs for a few weeks giving us enough time to further explore what
Oracle best practices are. We must to the same for DB/2 as well.

Regarding "If you still feel you just gotta have temp

> tables use global temporary tables otherwise performance will suffer
> greatly", I "feel" that we have to use what works best in each respective

environment. It is the functionality of our application that customers want
and it made sense to us to provide good and fast Oracle support in there as
well.

I just don't feel that the Personal Attacks on my software development
skills were warranted. We are all developers and/or DBA folks. Our jobs
are already somewhat thankless. The last thing we need is to attack one
another (not saying you did). Bottom line (and apparently Mr. Bakker fails
to understand this) is that we "developers" typically don't get the time to
do things the correct way because upper management unfortunately and often
times make the decisions.

Although they (mostly) have no experience in Software Engineering or
development processes, they look at "time" and "cost". That's it. How much
time will it take to get "something" out to the customer and how much will
it cost them before they go out and look for another vendor that can do it
for less. When things bogg down and bugs begin to surface (usually do to
the lack of adequate testing time), then it's our (developers) fault. I
just don't need or want the extra headache from folks like Mr. Bakker.

Thank You Jim for your suggestion.

"Jim Kennedy" <kennedy-fam...@attbi.com> wrote in message

news:OTmD8.19791$Po6.8045@rwcrnsc52.ops.asp.att.net...

> Unlike Sqlserver and Sybase you don't need (or very very rarely) need temp
> tables in Oracle. Yes, in Sybase and Sqlserver you need them and I am
sure
> you are correct that using them in those environments gives you the
fastest
> performance in those environments - no arguement. In Oracle however, you
> don't have to go through all those gyrations. (Good approuch possibly for
> SQLServer and Sybase - not a good approuch in Oracle. I am not saying you
> are stupid; I am saying things work differently.) You can use SQL (not
> pl/sql or transact, just sql). If you still feel you just gotta have temp
> tables use global temporary tables otherwise performance will suffer
> greatly. In Oracle readers don't block writers and writers don't block
> readers. It will save you a lot of pain and anguish to try some simple
> experiements in Oracle and see that the differences are great and use
those
> differences to your advantage. Again, this has nothing to do with your
> intelligence; it is just the products are different and assumptions in one
> are not valid in the other.

> Tom Kyte's book is excellent and well worth the money. His site is at:
> http://asktom.oracle.com/pls/ask/f?p=4950:1:

> Some useful links: