March 8, 2009 at 1:33 pm
GilaMonster (3/8/2009)
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?
Certainly, feel free.
March 9, 2009 at 7:42 am
Have fun in your new ventures,
JR
March 9, 2009 at 8:07 am
Jeff Moden (3/8/2009)
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.
Words of wisdom right here.
March 9, 2009 at 2:22 pm
Hi Musheik,
another option to consider could be the use of XML format to transfer the data. Maybe your JAVA folks will look into this easier than extract into a a flat file..
I wouldn't go that far and suggest to put all 200k sets/elements in one xml file since this would require some knowledge on how many attributes per element you have (= how many columns per row). I'd probably split it up in xml blocks of a "reasonable size"...
JAVA developers (at least most of the ones I know) don't like to extract set based flat files and they don't really care about what effect 200k single insert statements will have on a database...
But as soon as I mention the magic three letters "X", "M", "L" they get a glance in their eyes and they're more than willing to provide the data. I don't know what the benefit of all these begin and end tags is, but they hopefully do...
Let the JAVA folks transfer the XML data directly in a table with a XML column (ending up with just a few insert statement) and by using OPENXML SQL2K5 will transfer the data pretty fast into the relational world - not as fast as a bulk insert would do but still a lot faster that 200000 separate insert statements.
Oh, don't forget to drop the import-xml-table to keep your database a relational one 😉
March 9, 2009 at 3:45 pm
I have seen this issue with any code, and any variation of Oracle Versus SQL ever since the first Java interface for ODBC drivers was released.
It can be done with a loop that makes no database updates like this:
1.Write a Java code class method that includes a loop has the ODBC connection statement inside the start of the Loop, not outside the loop before it begins.
2. Connection string is pointed to an Oracle Database Server. Execute code.
3. Update the connection string to point at any other SQL database server. Execute code.
This was a neat trick I was taught once so we could stay on the Oracle platform.
Use ODBC connectivity and open you're Database connection at the begging of a code loop. All you change in the code is the server/database it connects to. So most people that do not understand client server database drivers will see an issue with the Server Platform, and not how the code is written. After all, the only thing that changed is server and database name.
The Oracle client drivers will automagicly connection pool this code loop.
Most RDBMS clinet drivers do not, and will create a new connection every time the loop cycles.
All you have to do is make ceratain that you make all settings to and then open your database connection outside of a loop, right before it begins. Then explicitly close this connection after the loop has closed. If you are opening a connection specific Transaction, do that outside you're loops also.
FYI: Most developers would consider this second method "Better Code", and the first one "Differant Code". It is more object oriented to make your code loops self contained, but less Modern to only include in the loop what changes inside the loop. Modern way is to set and call anything that does not need to change in the loop outside of it, prior to its start. If this does not work, then another loop or class object may be needed.
March 9, 2009 at 4:41 pm
jparker2 (3/9/2009)
I have seen this issue with any code, and any variation of Oracle Versus SQL ever since the first Java interface for ODBC drivers was released.It can be done with a loop that makes no database updates like this:
1.Write a Java code class method that includes a loop has the ODBC connection statement inside the start of the Loop, not outside the loop before it begins.
2. Connection string is pointed to an Oracle Database Server. Execute code.
3. Update the connection string to point at any other SQL database server. Execute code.
This was a neat trick I was taught once so we could stay on the Oracle platform.
Use ODBC connectivity and open you're Database connection at the begging of a code loop. All you change in the code is the server/database it connects to. So most people that do not understand client server database drivers will see an issue with the Server Platform, and not how the code is written. After all, the only thing that changed is server and database name.
The Oracle client drivers will automagicly connection pool this code loop.
Most RDBMS clinet drivers do not, and will create a new connection every time the loop cycles.
All you have to do is make ceratain that you make all settings to and then open your database connection outside of a loop, right before it begins. Then explicitly close this connection after the loop has closed. If you are opening a connection specific Transaction, do that outside you're loops also.
FYI: Most developers would consider this second method "Better Code", and the first one "Differant Code". It is more object oriented to make your code loops self contained, but less Modern to only include in the loop what changes inside the loop. Modern way is to set and call anything that does not need to change in the loop outside of it, prior to its start. If this does not work, then another loop or class object may be needed.
Cool.... how long does that take to run on 200,000 rows like the OP wants?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2009 at 1:25 am
Hi Lutz,
your advice seems to be more suitable for my scenario.
Thank you.
Mujeeb
March 12, 2009 at 1:10 pm
Hi Mujeeb,
you're welcome.
One correction though:
instead of using OPENXML you should consider XQuery, since
hardly anyone uses OPENXML anymore.
(quote from post http://www.sqlservercentral.com/Forums/FindPost672137.aspx"> http://www.sqlservercentral.com/Forums/FindPost672137.aspx )
I had to make that correction in another post, too...
March 13, 2009 at 8:10 am
just a curious to know. is your table contains any indexes clustered or nonclustered?
there is an article in SQL 2005 BOL...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2625ba79-2966-4df8-9cf8-185beead5c83.htm..
I am not sure how to use it becuase I have never run it...i guess this is the way it works (but not sure)...
INSERT INTO destination_table
SELECT from destination_table
ORDER BY
March 13, 2009 at 10:51 pm
Yeah... I'm still curious, too. Someone posted a suggestion to use Java on this and, as I asked in a previous post on this thread, I'd really like to know what the performance is for a 200,000 row problem like what the OP posted is. 😉 I do know that a "simple" import from a text file using Java takes over 16 minutes for a lousy 1.2 million rows. Same thing using Bulk Insert with full data validation and a wad of data mapping only took 51 seconds to do the same thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2009 at 3:18 am
Hi all ,
There are two solutions found for my problem,
1) Take the data as xml input and inserting at a time.
25 lacs of data is getting inserted within 30 minutes including required updates. quite fast
and met the requirements.
2) There is a patch for sql server 2005
SQL Server 2005 SP2 with Cumulative Update 3 pack from Microsoft on 10.77.116.151 Server.
With this the 1 lac inserts in a loop as mentioned earlier are within 40 seconds quite equal to Oracle.
This patch may be of help for others too.
Thanks all for your suggestions.
Cheers
Mujeeb
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply