Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I am Confused about SQL Server performance


I am Confused about SQL Server performance

Author
Message
Coderx7
Coderx7
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 32
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
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5681 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Coderx7
Coderx7
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 32
Evil Kraig F (7/24/2014)
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.


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 ?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18081
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/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Coderx7
Coderx7
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 32
Thanks alot Wink
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37929
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44886 Visits: 39856
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
crmitchell
crmitchell
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 1702
Coderx7 (7/24/2014)
[quote]Evil Kraig F (7/24/2014)
[quote]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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37929
crmitchell (7/25/2014)
Coderx7 (7/24/2014)
[quote]Evil Kraig F (7/24/2014)
[quote]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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search