March 5, 2009 at 6:43 am
Hi Sql server experts,
I need to db tuning advice on sql server.
I need to insert 200000 records into a table .
When I insert 200000 records at a time it is taking 13 seconds to insert.
The same 200000 records if I am inserting one at a time in a loop its taking 4 hrs.
Actually I need to insert so many records one by one after setting parameters through java.
Can any body advice any parameter setting or any work around for this.
Please advice me.
Thanks in advance.
Mujeeb
March 5, 2009 at 6:47 am
musheik (3/5/2009)
I need to insert 200000 records into a table .When I insert 200000 records at a time it is taking 13 seconds to insert.
The same 200000 records if I am inserting one at a time in a loop its taking 4 hrs.
Sounds about right. SQL's optimised for bulk-processing, not one row at a time.
Can you change your java program or do the insert in a different way?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 6:52 am
I checked with java developers, its difficult for them to modify the code in short time. as we are at the verge of release.
Any thing need to be done at db level only.
March 5, 2009 at 7:07 am
musheik (3/5/2009)
I checked with java developers, its difficult for them to modify the code in short time. as we are at the verge of release.Any thing need to be done at db level only.
Then you're probably stuck with the 4 hour process.
Drop all indexes, it might help a bit. Disable any foreign keys and reenable them WITH CHECK afterwards. No way that inserting the rows 1 by 1 is going to come close to the 13 sec though.
I hope this is a once-off process. It is, isn't it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 7:49 am
yes, this is one time process. I will try your advice and get back to you
March 5, 2009 at 7:53 am
I have been working with enterprise level SQL servers large enough to be considered super computers some even paralell processing for about ten years.
I have yet to see the hardware, OS, and RDBMS combination that could create and destroy 200,000 connections in 13 seconds, few could do it in 4 hours.
However, there should be no reason any system that is only working with your transactions can not support a single connection object making all those updates in less than 4 hours (or one hour), unless the real time lag is added by the rest of the Java code on each Loop.
If you want to prove this with a test, ask them to append the output of each loop to a local text file instead of creating a DB record. Then time the full execution of the code as it loops 200,000 times.
Worst case scenario is there code still takes forever, and you get something you can Bulk Insert into SQL in Thirteen seconds.
Remember always have development write code that will work for you.
Good Database does not resolve poorly written code, EVER.
Good Luck,
JRP
March 5, 2009 at 8:07 am
I am sorry for not putting my expectations.
We are doing same process of inserting in oracle in 1 hour 20 min.
we need to get in same time or reach nearer to the time.
March 5, 2009 at 8:07 am
jparker2 (3/5/2009)
If you want to prove this with a test, ask them to append the output of each loop to a local text file instead of creating a DB record. Then time the full execution of the code as it loops 200,000 times.Worst case scenario is there code still takes forever, and you get something you can Bulk Insert into SQL in Thirteen seconds.
That's a very good alternative, if it's possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 4:41 pm
musheik (3/5/2009)
yes, this is one time process. I will try your advice and get back to you
Heh... that's what the all say... it'll come back.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2009 at 1:37 am
Thanks you all, for your valuable suggestions. i am concluding that with sql server we will be slower than Oracle,And we cant do much modifications with sql server settings.
I request you to guid me ,if there any possible setting to enhance to db, such as reduce time to parsing query etc.
like we have in oracle ( cursor sharing= Force ). or any other settings to enhance. I am not well versed with sql server db settings.
March 8, 2009 at 1:48 am
musheik (3/8/2009)
I request you to guid me ,if there any possible setting to enhance to db, such as reduce time to parsing query etc.like we have in oracle ( cursor sharing= Force ). or any other settings to enhance. I am not well versed with sql server db settings.
Oracle works well with cursors, SQL Server does not. It's a fundamental architectural difference between the two. There is no setting that's going to make row-by-row processing fast in SQL Server.
If you're going to use SQL, you need to work in a way that it supports well. Same with Oracle, that's one of the reasons why writing software to use both is so hard, they have different ways of working and different 'best' practices
To get stuff working fast in SQL, work with sets of data not rows of data. That means bulk-insert, multi-row insert and no cursors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2009 at 8:43 am
musheik (3/8/2009)
Thanks you all, for your valuable suggestions. i am concluding that with sql server we will be slower than Oracle,And we cant do much modifications with sql server settings.I request you to guid me ,if there any possible setting to enhance to db, such as reduce time to parsing query etc.
like we have in oracle ( cursor sharing= Force ). or any other settings to enhance. I am not well versed with sql server db settings.
Heh... you are SO wrong. The only time SQL Server runs slow is when someone tries to write Oracle-like code against it. Like Gail said, they're two different animals. You can't make it run fast if you don't know how to shift gears on it.
And there is no "run SQL Server faster" button just like there's no "run Oracle faster" button... performance is in the code. Good code will run fast even on a poorly setup server and bad code can absolutely cripple even the most modern of servers. And, it doesn't matter if it's SQL Server or Oracle... it happens to both.
The key to writing good code in SQL Server is to stop thinking about what you want to do to a row... start thinking about what you want to do to a column. In other words, stop treating it like GUI code. Once you adopt that paradigm, even Oracle code will run faster despite what they say about cursors in Oracle.
Don't think for even a minute that all RDBMS's operate the same way for the same commands. They simply do not. They have different engines, optimizers, and functionality. For example, Oracle cannot do high speed pseudo-cursors... SQL Server can. Oracle can do Connect By... SQL Server can't. The date functions are all different and so is how triggers work. If you want to write fast code in either, then you have to learn how to write fast code. Stop looking for the mythical hardware setting that will run bad code fast in either.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2009 at 11:29 am
Jeff Moden (3/8/2009)
And there is no "run SQL Server faster" button
aww shuks, i been lookin for that one LOL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 8, 2009 at 12:41 pm
musheik (3/8/2009)
Thanks you all, for your valuable suggestions. i am concluding that with sql server we will be slower than Oracle,And we cant do much modifications with sql server settings.I request you to guid me ,if there any possible setting to enhance to db, such as reduce time to parsing query etc.
like we have in oracle ( cursor sharing= Force ). or any other settings to enhance. I am not well versed with sql server db settings.
Your fundamental problem is poor code design: even if you can get acceptable performance out of Oracle, it is only because Oracle was built in the old days to penalize bad code less. DB2, likewise, penalizes bad code less than SQL Server, but in all three RDBMSs set-relational code will run faster, often far, far faster, than row-by-row code.
The correct solution to your problem is not to beat SQL Server into tortured subservience, it is to rewrite the bad code into good code.
March 8, 2009 at 1:06 pm
Dean Cochrane (3/8/2009)
The correct solution to your problem is not to beat SQL Server into tortured subservience, it is to rewrite the bad code into good code.
😎 May I steal that line for future use?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply