SQL Server VS. Oracle
-
Joseph said:
One point I have not seen, is that how well Oracle and SLQ Server work depend largely on the people involved.
You have to have people that know what equipement to buy, how to configure it for the DB and App. Then it is up to the DBA's to configure and setup the database correctly and tune it to the hardware and app. Bad setup in either one will lead to disappointing performance if the application is going to push the hard ware performance capabilities. Then of course you typically get the most improvement in application performance by tweaking/tuning the application and the database statements themselves. The knowlege of the people working on the project have a far greater impact than which database you put on a piece of hardware.
My 2 cents:
I have seen this over the years and many times was glad I spent the time to learn the hardware engineering side while learning software programming.
Another point is the vital role or "teaming" with your DBA to tune, setup indexes, triggers and custom scripts before saying a project is done.
All important factors in reliability as well as ultimately what clients view as "performance".
Rob
-
I see what your saying. And your correct. And I realize such things are outside the control of the dba at times. The thing about it though, is that it's also exactly what I was saying about bad design. When a primary key is changed, I do not consider that an update, but a delete of the old entity, and an add of the new entity, and by definition, there is no old and new values from an update. It's a shortcut to give the new row some identical values to the old. So there should not be matching rows. Which of course, would be correct when the identity values didn't match. For your boss who wants to see the old and new, I would explain that the row was deleted and a new row was added and it just happened to have the same values in some of the fields. Maybe I'm just nitpicking, but in all reality, an update of an identity field is not an update at all, but a delete and insert of a new row.
I think that by oracle allowing the concept of an update in this case, it's presenting the function in an incorrect manner, and allowing the continuation of bad practices.Oh, and I also think that you do, in fact, have an excellent idea of what your talking about. You arguments and concise and explicit, and you obviously have a very good idea of the issues you describe. You present your facts in an easily understand way. So, I don't think you are wrong, as your points are valid, I just disagree with the idea of the triggers handling bad practice.
Edited by - scorpion_66 on 04/02/2003 09:29:39 AM
-
quote:
...it's also exactly what I was saying about bad design. When a primary key is changed, I do not consider that an update, but a delete of the old entity, and an add of the new entity, and by definition, there is no old and new values from an update.I agree, but in my experience, if the SQL Parser will allow it, the developers will do it eventually. It would suit me just fine if SQL Server began requiring 1) all tables (at least all permanent ones) to have explicit primary keys, and 2) no updates on primary key columns. But such is not yet the case.
quote:
It's a shortcut to give the new row some identical values to the old. So there should not be matching rows.... Maybe I'm just nitpicking, but in all reality, an update of an identity field is not an update at all, but a delete and insert of a new row.I've seen such code used for things such as departmental transfers, where in some sense it really is the same entity, only the row's location changes, and location is in this case part of the key.
I want to make it clear that I'm not advocating such design, just observing that it's out there, somewhere. I've gotten into trouble in these discussions before, because I tend to be less of a purist than most DBAs... but it's not that I wouldn't like things to be purer, it's just that they seldom are. One of my favorite aphorisms: "School teaches you how things should be, but not necessarily how they are; experience teaches you how things are, but not necessarily how they should be." I've had a lot more practice than schooling -- fortunately or unfortunately, I don't know which.
quote:
For your boss who wants to see the old and new, I would explain that the row was deleted and a new row was added and it just happened to have the same values in some of the fields.I haven't actually yet seen any instance where the primary key is updated. It's just that I couldn't rule that out, a priori, as a possibility.
quote:
I think that by oracle allowing the concept of an update in this case, it's presenting the function in an incorrect manner, and allowing the continuation of bad practices.Well, SQL Server allows the update, too. What Oracle provides is a cleaner way of dealing with it, or actually with any type of trigger issues. I forget the exact syntax (it's been two years, sorry) but as I recall, in Oracle, you can address individual before and after column values using 'old' or 'new' aliases, and, as I recall, these values come pre-joined, so to speak, without making you hunt down the primary key and roll your own join between 'INSERTED' and 'DELETED'.
Something else you can do with an Oracle trigger is you can facilitate easy communication between multiple triggers firing off from the same update. You can instantiate a 'package' in Oracle that allows you to cache values from one trigger and retrieve them in one of the later firing triggers. I guess you could do that same thing in a klugier way using tables or global cursors in SQL Server, but the Oracle method is a lot more ergonomic: think of the package declaration as global memory.
quote:
Oh, and I also think that you do, in fact, have an excellent idea of what your talking about....Thanks! Like a broken clock, I'm right about twice a day, even if no one tells me when. 🙂
Edited by - Lee Dise on 04/02/2003 10:30:45 AM
-
My comments to cascading updates particular in a master-detail data model:
In SS the order of deleted and inserted rows (after statement) can get out of sync if the primary key is clustered. Microsoft does not guarantee the order at all - clustered or non-clustered. To implement a multi-row cascading PK update one has to use:
(1) artificial primary keys (ie Identity)
(2) move to SS2000 and use referential cascade update ?? does this work ??
(3) rollback multi-row update transaction where a primary key field is affected and inform the unfortunate user.
In a details table, simple multi-row updates of part of the primary key can pass under these conditions:
Assume table has 3 fields:
FK_to_master,
virtual_col_name,
value
PK is:
FK_to_master & virtual_col_name
One set of virtual_col_name is changed:
eg. set virtual_col_name='Field_4' where virtual_col_name='Field_5'
In Ora there is no problem, every :new corresponds to an :old in a row-level trigger. I am not sure what happens if a ps/sql table holding all updated rows (equiv to SS deleted) in the ProgramGlobalArea runs out of memory? Is it a programmers' problem to be caught as an exception? I have seen memory use increase by approx 20 MB for 120,000 records written to a pl/sql table.
In SS, deleted (and inserted) is a black box handled by SS
It would be nice if Microsoft could implement inserted and deleted as a sequence of records a la ordered pl/sql table. In this case a cursor could be used for cascading PK updates. Unfortunately this is not as fast as join, but in the short term better than trying to fix an old data model.
Win
-
Due to ANSI standards the order of records returned to you is not guaranteed by SQL Server. This is true. Using an order by clause is the proper way to handle this. With that in mind, you have your solution for using a cursor for cascading primary key updates a la ordered table. This solution will not work for situations where you have several of the fields being updated, not just the primary key, but works fine for just the primary key updates, if you have values to sort by that give a proper relationship (Ie. no duplicates)
Again, however, you are trying to apply procedural logic to a set based system. Databases are not meant to be procedural. Even Oracle one's. And again, by definition, when a primary key changes, it is a new identity, hence, a new entity. Not an update of an old one. The proper way to handle it procedurally, is to create the new identity, duplicate the values from the old one, and then delete the old one. If there are cascading updates needed, they should be handled by the same transaction. NOT update the primary key. So while what you say is in fact a problem, when approaching the issue from that perspective, it's something that should not be done to start with, and just because you can do something wrong in oracle or SQL Server, doesn't mean you should. Basing an opinion on SQL or Oracle for not allowing you to perform this function that you shouldn't be doing or allowing in the first place is a little silly in my opinion. Let me give you an example of what I mean. Let's say that I have two wrench's (used to turn bolts.) I decide to drive a nail with them (using them as a hammer). Debating whether the flatter one works better than the more curved one in driving the nail would get you some really wierd looks from a carpenter. His answer would be to use the proper tool, a hammer. If you insisted in using the wrench anyway, for whatever reason, wouldn't change his opinion in the least.
-
quote:
Again, however, you are trying to apply procedural logic to a set based system. Databases are not meant to be procedural. Even Oracle one's.I'm not so sure that this statement applies as well to Oracle as it does to SQL Server. Oracle's PL/SQL seems equally ergonomic in dealing with rows as it does with sets. The FOR..LOOP structures in PL/SQL are designed to walk through cursors, updating as they go.
To facilitate this, Oracle returns a cursor with every row set, and not just when asked. I've seen Sybase ad propaganda that snidely pings Oracle for doing this, and they may have a point, but to some degree Sybase may also be projecting their own shortcomings. Just because Oracle returns a cursor doesn't mean it has to be implemented as inefficiently as Sybase (and SQL Server) cursors tend to be.
(It's not like Sybase or SQL Server are perfect. If they're going to ask why Oracle always returns a cursor, it's also fair to ask why Sybase and SQL Server log transactions in 'tempdb', isn't it?)
quote:
...just because you can do something wrong in oracle or SQL Server, doesn't mean you should.The flip side of which is, just because something shouldn't be done, doesn't mean it isn't done. Things that are done must be dealt with.
quote:
Basing an opinion on SQL or Oracle for not allowing you to perform this function that you shouldn't be doing or allowing in the first place is a little silly....It's a question of perspective. I tend to appreciate tools that allow us to effectively work with something that is, but shouldn't be. I'd see it more your way if SQL Server were to enforce the "should" and make it a "must", but they don't.
quote:
Let's say that I have two wrench's.... Debating whether the flatter one works better than the more curved one in driving the nail would get you some really wierd looks from a carpenter. His answer would be to use the proper tool, a hammer...I'm not sure I can come up with a prettier analogy than that to illustrate my position, but I'll try: Most of my fishing buddies agree that small front-wheel-drive cars are poor choices for towing a boat trailer. Most manufacturers of small front-drivers issue all sorts of warnings against towing. But what if they were to install a trailer hitch on the cars as standard equipment? A mixed message, no? Would their verbal admonishments mean anything, or do you think that, some day, someone who bought a little front-driver with a trailer hitch might try towing a heavy boat, with disastrous consequences for the suspension and drive train?
Now, along comes a front-driver manufacturer who also puts trailer hitches on his cars, but says, "Look! We have a beefier suspension, a better-cooled transmission, and extra engine torque, just in case you do something stupid like pull a trailer with the trailer hitch we're giving you."
Ideally, if the car makers didn't want you towing trailers, they wouldn't provide hitches, and might even consider making it impossible to install them. Likewise, if Oracle or SQL Server didn't want anyone updating primary key columns, they should instruct their parsers to disallow it. But they don't, hence the dilemma.
-
LMAO....excellent points.....and I can't argue what IS in the world.
I guess I'm just too much a stickler on what should be, and best practice.
-
quote:
Again, however, you are trying to apply procedural logic to a set based system. Databases are not meant to be procedural. Even Oracle one's.The issue here is not row-based vs set-based thinking. The issue is that SS does not provide a safe means of matching before and after images of data sets if all or part the PK was modified. To see what I mean, please run enclosed script. The cursor is only used for presentation purposes.
Win
--------------------------------------------
print '### Start of Win''s test ###'
print ''
set nocount on
CREATE TABLE [dbo].[aaatab] (
[f1] [varchar] (6) NOT NULL ,
[f2] [varchar] (6) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[aaatab] WITH NOCHECK ADD
CONSTRAINT [PK_aaatab] PRIMARY KEY NONCLUSTERED
(
[f1],
[f2]
) ON [PRIMARY]
GO
CREATE TRIGGER aaatrig ON aaatab FOR UPDATE AS
declare @f1_del varchar(6)
declare @f2_del varchar(6)
declare @f1_ins varchar(6)
declare @f2_ins varchar(6)
declare @msg varchar(100)
declare DelRows cursor for select f1,f2 from deleted
declare InsRows cursor for select f1,f2 from inserted
open DelRows
open InsRows
fetch DelRows into @f1_del,@f2_del
fetch InsRows into @f1_ins,@f2_ins
while @@fetch_status=0
begin
set @msg='Before: ' + @f1_del + ' '+@f2_del+' After: ' + @f1_ins + ' '+@f2_ins
print @msg
fetch DelRows into @f1_del,@f2_del
fetch InsRows into @f1_ins,@f2_ins
end
close DelRows
close InsRows
deallocate DelRows
deallocate InsRows
GO
insert into aaatab values('A','2')
insert into aaatab values('B','1')
GO
print 'Initial content of table aaatab'
select * from aaatab
GO
print 'Transaction: update aaatab set f1=B'
print ''
print '*** Using non-clustered PK ***'
update aaatab set f1='B'
GO
truncate table aaatab
ALTER TABLE dbo.aaatab
DROP CONSTRAINT PK_aaatab
GO
ALTER TABLE dbo.aaatab ADD CONSTRAINT
PK_aaatab PRIMARY KEY CLUSTERED
(f1,f2)
GO
insert into aaatab values('A','2')
insert into aaatab values('B','1')
GO
print ''
print '*** Using clustered PK ***'
update aaatab set f1='B'
GO
drop table aaatab
GO
set nocount off
print ''
print '### End of Win''s test ###'
-
Let me go about explaining this in a diffeent manner, as I think you are missing my point.
A primary key uniquely identifies a row in a table by definition. In changing the value of it's unique identifier, you have created a new row which differs from the origional by it's unique identifier. This means that it is no longer the same row. It is a new row. When you insert new rows into your table, do you expect them to match ANY previous or deleted rows? I would hope not. You are getting hung up on the word update, and forgetting the fact that when you change a unique identifier, it is no longer the origional identity. Just because you can shortcut the insert of a new identity, the changing of the other values to match an old value, and deletion of the old one, does not mean you should view it that way. You should try to keep perspective, and realize the actuality of what is happening from a database standpoint. This is in all reality, NOT an update.
By the way, your script demonstrates this very well.....
-
A primary key uniquely identifies a row in a table by definition. In changing the value of it's unique identifier, you have created a new row which differs from the origional by it's unique identifier.
Nobody disputes that updating a primary key is conceptually equivalent to inserting a unique new row followed by deleting the replaced row. Also, nobody can dispute that SS has a serious Achilles heel by allowing trigger-based cascading updates on primary keys. There is no label on the SS software box saying: “Warning, cascading updates of primary keys is hazardous, it can scramble your database”.
The purist approach is not without danger. Let’s assume a purist client application (PCA) translates all updates into inserts/deletes. There are 3-4 dependent tables hanging off the primary key of the master table to be modified. The “update” of n rows in the master table can cause a total of physical “updates” in the order of 10^6 in the dependent tables.
This translates into twice as many physical row-level activities for inserts/deletes for the PCA. Depending on the system such a process could run from minutes to hours.
While rows are being inserted from the PCA into one of the “updated” tables a second user (outside the PCA) may add new rows belonging to the same “updated” keys in another table. At this stage the RDBMS cannot guess that all this inserting is will be followed by a delete as part of a PCA “update”.
During the final PCA transaction, a cascade delete from the master table, the new rows, inserted by the second user will be wiped as well. To avoid such unplanned deletes, all tables involved in the PCA “update” must be locked from the first insert initiated by the PCA.
A trigger must be created on the master table to rollback multi-row real updates if all or part of the PK is affected, to stop users from bypassing the somewhat sluggish PCA.
Microsoft introduced cascade deletes and updates in SS2000, possibly to alleviate this problem, leaving SS7 sites with one choice only – to upgrade. Oracle, to my knowledge does not provide a cascade update to this day. I suspect Oracle will argue, that they don’t need it.
Win
-
As far as i know Oracle supports only read committed and serializable for isolation levels ( read uncommitted also). SQL server supports all 4. Does this have anything to do with the fact the Oracle provides a better read consistent view to users ?
-srini
-
I've used SQL Server, Sybase and Oracle.
As far as OS reliability/security goes, Lately it seems that my Linux system has more security patches than my Windows 2000 Server. Also you can disable access to the OS by anyone other than SA if you want to, Usally a good idea. Unix is becomming more "Home Friendly" therefore I believe the security issues will increase as time goes by.
I prefer SQL Server for a GP DBMS. I have ran two large financial applications with no locking issues as mentioned above. If I did have a deadlock, it was usally an application issue. I think Oracle may be better for banks where you want to run more reports against live data that may cause more locking issues. My servers usally have 500 or fewer users. When you get into the 1000's it may be a different story.
Here's something that I did notice. My company was building a data warehouse for it's shipping system based on a Mainframe application. Oracle came in with a 4 way Sun enterprise server configured with 6 mirrored drives running Oracle 9i. I had a compaq Proliant 6400 2 way Xeon running SQL Server Standard edition with a raid 5 array(I know RAID 5 is slower on writes but with data warehouses, you only write at night during off-peak hours. Both servers had the same database loaded with the same indexes created. The Oracle DBA spread the fact tables, indexes, and detail tables across 4 of the 6 mirrored drives to optimnize performance.
We ran the same queries on both servers. I was expecting the Oracle box to be faster but to my suprise, the Compaq/SQL server beat the Oracle/SUN box by a huge margin. The oracle guys said they needed to tune the DB more but he made my point for me. I spent a day trying to tune that Oracle database with him but it still was slower than my out of the box SQL Server.
Don't get me wrong. I do like a lot of the stuff Oracle has put into 9i. But do we really need 600 MB for a DBA's desktop install to get the GUI console?
-
I forgot to mention my thoughts on triggers and cursors.
In my experience, these are the easiest ways to slow down your database. I even saw a few developers who nested a cursor inside a trigger. It made a 2 minute query turn into a 5 hour query.
-
quote:
HiYou can use hints, like you can in SQLServer to alter the plan, thats about it though. I would highly recommend against hints unless you have a good reason to use them, why??
a) maintaintence is a right pain
b) does the hint work fine with lost of data or very little?
c) does the validity of using the hint "grow" with the tables growth?
Plan manipulation is not a big plus for going to oracle. I would say
a) superior replication (sqlserver is a damn site easier though and is catching up nicely)
b) partitioned tables
c) bitmap indexes
e) superior clustering (oracle RAC)
f) ability to alter block sizes for each tablespace
even still, without DTS, its tough getting data in and out.... ive heard of a few sites that purchased sqlserver just for DTS between their DB2 and oracle apps.
Cheers
Chris
Chris:
Thanks for the post. Can you tell me how Oracle replication is superior to SQLServer 2000 replication ? We have some complicated replication scenarios(2-way trans with immediate updating).
tia
-srini
-
Great series of posts....
I never work on oracle but it was really great to know all this....
Though I tried to consolidates all that discusstion but due to confrontations of opinions not able to finalize what's the correct.....
May I request Moderators to consolidates and put it up as an article.....
Prakash Heda
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
I think that by oracle allowing the concept of an update in this case, it's presenting the function in an incorrect manner, and allowing the continuation of bad practices.
Viewing 15 posts - 76 through 90 (of 96 total)
You must be logged in to reply to this topic. Login to reply