July 24, 2014 at 1:08 am
Hello Everyone,
About two months ago me and two of my friends tried to see for ourselves whether Oracle is faster than SQL Server or vice versa, and or if they are on par most of the time,
We didn't have a clue how to go about it, so we decided to create a a stored procedure to insert some rows in a loop ( around 1000,000 ).
In oracle (11g i guess ) it took 1:57 minutes and sqlserver 2012 took ~3 minutes.
we had an auto incrementing Id, and a field representing name for example.
I cant believe SQL Server is this slow compared to oracle.What are we missing here?
This is not all, I have read in StackOverflow (i guess) that SQL Server is transaction based internally as well, so if in the middle of something we cancel our operation, the inserted rows or updated columns must be deleted or get reverted back.
To test this, we actually tried to run the stored procedures mentioned above, and cancelling its execution after couple of seconds
Oracle indeed reverted back the changes, but SQL Server 2012 didn't do that at all, it just cancelled the loop half way and the data that were inserted in to the table, remained there, weren't erased !!
So Why is SQLServer acting this weird? what are we doing wrong? Apparently SQLServer is being used on highly data intensive servers world wide as well , But such a low performance compared to oracle according to our simplistic sample test is not just right!
I would be thankful if anyone could help me get what the problem is here.
Thanks in advance
July 24, 2014 at 2:07 am
Coderx7 (7/24/2014)
We didn't have a clue how to go about it
You lack research, young padawan. Let us show you where you errored.
so we decided to create a a stored procedure to insert some rows in a loop ( around 1000,000 ).
Oracle is built around the concept of developers building loops. It's primary access technique is using Cursors. In SQL Server, Cursors are (usually) one of the worst performing things you can do. Remove the thought of 'transferrable SQL' between systems. You have to build towards the specific system. Insert 1,000 rows *at once* as a test, not a loop.
This is not all, I have read in StackOverflow (i guess) that SQL Server is transaction based internally as well, so if in the middle of something we cancel our operation, the inserted rows or updated columns must be deleted or get reverted back.
To test this, we actually tried to run the stored procedures mentioned above, and cancelling its execution after couple of seconds
Oracle indeed reverted back the changes, but SQL Server 2012 didn't do that at all, it just cancelled the loop half way and the data that were inserted in to the table, remained there, weren't erased !!
This is the difference between implicit and explicit transactions, and how the two systems consider them. I'm not an Oracle expert, but your loop, because of how Oracle likes it's code, sounds like it contains each looping insert as part of an overall implicit transaction.
In SQL Server, each of your INSERTS in the loop is its own implicit transaction. Without an explicitly declared transaction, it keeps what's performed to that point. Also, you need to have it do a ROLLBACK when it fails like that, or the transactions can stick around in a ghost like state (ie: READ UNCOMMITTED can find them) until the SPID fails and the transaction rolls back via internal mechanisms.
But such a low performance compared to oracle according to our simplistic sample test is not just right!
I would be thankful if anyone could help me get what the problem is here.
Thanks in advance
This is where the whole idea of 'standard transferable SQL' goes out the window. Each engine, be it MS SQL, Oracle, MySQL, Mongo... has their own internal engines for optimization. Some are better than others for particular tasks, but in general they perform reasonably similar as long as you code to the engine you're working against.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 24, 2014 at 4:31 am
Evil Kraig F (7/24/2014)
Coderx7 (7/24/2014)
We didn't have a clue how to go about itYou lack research, young padawan. Let us show you where you errored.
so we decided to create a a stored procedure to insert some rows in a loop ( around 1000,000 ).
Oracle is built around the concept of developers building loops. It's primary access technique is using Cursors. In SQL Server, Cursors are (usually) one of the worst performing things you can do. Remove the thought of 'transferrable SQL' between systems. You have to build towards the specific system. Insert 1,000 rows *at once* as a test, not a loop.
This is not all, I have read in StackOverflow (i guess) that SQL Server is transaction based internally as well, so if in the middle of something we cancel our operation, the inserted rows or updated columns must be deleted or get reverted back.
To test this, we actually tried to run the stored procedures mentioned above, and cancelling its execution after couple of seconds
Oracle indeed reverted back the changes, but SQL Server 2012 didn't do that at all, it just cancelled the loop half way and the data that were inserted in to the table, remained there, weren't erased !!
This is the difference between implicit and explicit transactions, and how the two systems consider them. I'm not an Oracle expert, but your loop, because of how Oracle likes it's code, sounds like it contains each looping insert as part of an overall implicit transaction.
In SQL Server, each of your INSERTS in the loop is its own implicit transaction. Without an explicitly declared transaction, it keeps what's performed to that point. Also, you need to have it do a ROLLBACK when it fails like that, or the transactions can stick around in a ghost like state (ie: READ UNCOMMITTED can find them) until the SPID fails and the transaction rolls back via internal mechanisms.
But such a low performance compared to oracle according to our simplistic sample test is not just right!
I would be thankful if anyone could help me get what the problem is here.
Thanks in advance
This is where the whole idea of 'standard transferable SQL' goes out the window. Each engine, be it MS SQL, Oracle, MySQL, Mongo... has their own internal engines for optimization. Some are better than others for particular tasks, but in general they perform reasonably similar as long as you code to the engine you're working against.
Thanks alot, very informative indeed, i appreciate it.
You know, the reason we tried that loop, was actually to simulate a busy server accepting lots of transactions, such as a website like Facebook which users post lots of status updates or other things alike. we though to ourselves that, so in that situations we are facing lots of inserts! so lets create a loop to provide such a burden on each of these servers and see how the handle it.
I really cant get the part to insert 1000 rows at once! How is that even possible? is it mot dangerous ? since it something happens you lose 1000 rows! not just 1, two or 100 rows, 1000 rows!
I am not an oracle expert either, i am just a developer that has been using SQL Sever for the last 10 years! So i would be greatful if you or any one else can explain more. suppose you want to benchmark two db servers, how would you do that ?
July 24, 2014 at 8:01 am
One of the reasons that you're facing this is because SQL Server auto commits every transaction and Oracle doesn't. You need to manually commit any DML actions. If you use an explicit transaction around the loop in SQL Server, you'll find an improvement on its performance or commit every row insert on Oracle and the performance should decrease.
To try the insert of 1000 at once, read about tally tables and how do they replace a loop. Here's a good place to start: http://www.sqlservercentral.com/articles/T-SQL/62867/
July 24, 2014 at 9:15 am
Thanks alot 😉
July 24, 2014 at 1:23 pm
One thing to remember about Oracle and SQL Server, they are not the same. The internals are different, and there for how the operate is different.
For example, the following will work fine in SQL Server but will fail in Oracle:
select current_timestamp
To get that SQL to run in Oracle it needs to be written as this:
select current_timestamp from dual
I'll leave it to you to find out what dual is in Oracle.
July 24, 2014 at 4:13 pm
Lynn Pettis (7/24/2014)
One thing to remember about Oracle and SQL Server, they are not the same. The internals are different, and there for how the operate is different.For example, the following will work fine in SQL Server but will fail in Oracle:
select current_timestamp
To get that SQL to run in Oracle it needs to be written as this:
select current_timestamp from dual
I'll leave it to you to find out what dual is in Oracle.
Actually in Oracle it must be written as:
select current_timestamp from dual;
Oracle absolutely requires the ending ; before it will run a query. As to "dual", Oracle's parser requires a FROM clause after a SELECT, so Oracle create a dummy (pseudo-)table to use when the SELECT doesn't pull data from an actual table.
I'm sure not what only 1,000 rows is going to tell you, but, as noted by others, to be fair to SQL Server, explicitly start a transaction before any insert, then explicitly commit it afterward. The 1000 separate transactions are huge overhead vs. a single, larger trans.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 24, 2014 at 6:20 pm
Coderx7 (7/24/2014)
Hello Everyone,About two months ago me and two of my friends tried to see for ourselves whether Oracle is faster than SQL Server or vice versa, and or if they are on par most of the time,
We didn't have a clue how to go about it, so we decided to create a a stored procedure to insert some rows in a loop ( around 1000,000 ).
In oracle (11g i guess ) it took 1:57 minutes and sqlserver 2012 took ~3 minutes.
we had an auto incrementing Id, and a field representing name for example.
I cant believe SQL Server is this slow compared to oracle.What are we missing here?
This is not all, I have read in StackOverflow (i guess) that SQL Server is transaction based internally as well, so if in the middle of something we cancel our operation, the inserted rows or updated columns must be deleted or get reverted back.
To test this, we actually tried to run the stored procedures mentioned above, and cancelling its execution after couple of seconds
Oracle indeed reverted back the changes, but SQL Server 2012 didn't do that at all, it just cancelled the loop half way and the data that were inserted in to the table, remained there, weren't erased !!
So Why is SQLServer acting this weird? what are we doing wrong? Apparently SQLServer is being used on highly data intensive servers world wide as well , But such a low performance compared to oracle according to our simplistic sample test is not just right!
I would be thankful if anyone could help me get what the problem is here.
Thanks in advance
Post both snippets of code and let's have a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2014 at 7:39 am
Coderx7 (7/24/2014)
Evil Kraig F (7/24/2014)
Coderx7 (7/24/2014)
I really cant get the part to insert 1000 rows at once! How is that even possible? is it mot dangerous ? since it something happens you lose 1000 rows! not just 1, two or 100 rows, 1000 rows!
That's the point - if something fails it rolls the whole thing back so you know what state the system is in and can just resubmit the same task. In the other case you have rolled back just the single insert and not the whole transaction. If you were looking to insert 1000 records you now need to work out where it failed and change your insert task to only insert those records after it failed and manual intervention is the surest way to have bugs creep in.
July 25, 2014 at 8:04 am
crmitchell (7/25/2014)
Coderx7 (7/24/2014)
Evil Kraig F (7/24/2014)
Coderx7 (7/24/2014)
I really cant get the part to insert 1000 rows at once! How is that even possible? is it mot dangerous ? since it something happens you lose 1000 rows! not just 1, two or 100 rows, 1000 rows!
That's the point - if something fails it rolls the whole thing back so you know what state the system is in and can just resubmit the same task. In the other case you have rolled back just the single insert and not the whole transaction. If you were looking to insert 1000 records you now need to work out where it failed and change your insert task to only insert those records after it failed and manual intervention is the surest way to have bugs creep in.
Difference between SQL Server and Oracle.
If you want to treat the loop inserting 1000 rows of data as a single transaction in SQL Server you need to explicitly define the transaction.
BEGIN TRANSACTION
your 1000 iteration loop
COMMIT TRANSACTION
Now, if you kill the process running this while still in the look, all rows entered up to the point you kill the process will be rolled back.
July 25, 2014 at 8:20 am
Lynn Pettis (7/25/2014)
crmitchell (7/25/2014)
Coderx7 (7/24/2014)
Difference between SQL Server and Oracle.
If you want to treat the loop inserting 1000 rows of data as a single transaction in SQL Server you need to explicitly define the transaction.
BEGIN TRANSACTION
your 1000 iteration loop
COMMIT TRANSACTION
Now, if you kill the process running this while still in the look, all rows entered up to the point you kill the process will be rolled back.
except that my namesake's suggestion was to replace
your 1000 iteration loop
with
single 1000 record insert using tally table
which would be rolled back in its entirety by the implicit transaction failing
July 25, 2014 at 8:28 am
crmitchell (7/25/2014)
Lynn Pettis (7/25/2014)
crmitchell (7/25/2014)
Coderx7 (7/24/2014)
Difference between SQL Server and Oracle.
If you want to treat the loop inserting 1000 rows of data as a single transaction in SQL Server you need to explicitly define the transaction.
BEGIN TRANSACTION
your 1000 iteration loop
COMMIT TRANSACTION
Now, if you kill the process running this while still in the look, all rows entered up to the point you kill the process will be rolled back.
except that my namesake's suggestion was to replace
your 1000 iteration loop
with
single 1000 record insert using tally table
which would be rolled back in its entirety by the implicit transaction failing
Yes. If that single 1000 row insert was terminated while being insert the entire transaction would be rolled back.
July 25, 2014 at 11:57 am
Sorry I fell off this discussion a bit.
How does adding 1,000,000 rows at once in a single insert differ from adding them 1,000,000 times in a loop if you want them all to fail out simultaneously anyway? You described that SQL Server didn't rollback all the records like Oracle did, and didn't understand why. Equivalent danger, no?
I'd forgotten that Oracle required explicit commits, so that helps explain that. Needless to say I don't work much with that software.
However, I currently run MANY processes that have million+ data row deliveries in a single transaction, and it's definitely pass/fail. I don't want to hunt down a portion of the data that's missing. I want to fix the entire thing, at once, to clean up the problem and not have to spend hours de-corrupting the data, or heaven forbid have to go to backup.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 25, 2014 at 12:42 pm
Of course, there's always the old but effective thought of not putting anything in your mouth until you look at it and smell it first. 😉
Rollbacks are incredibly expensive especially when they're for transactions containing millions of rows. It's far better to spend a little time pre-validating the data before attempting such large transactions. It will also allow one to mark bad rows so that they can be easily found and reworked.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply