| Author |
Topic |
|
decapolo
Starting Member
15 Posts |
 Posted - 2005-07-13 : 00:48:34
|
| Hi ...i am trying to make a some 20,000 insertions in SQL Server using XML tags ... and the performance is highly critical .... is there a way so that the insertions are not recorded into the transaction log? This will really save a huge amount of time!! looking fwd to ur replies ...regardz,SAm |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-07-13 : 01:05:48
|
| No. The transaction log isn't just an event log, it's actually part of your data.Secondly...... if performance is highly critical, why the hell are you using XML ?Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
 |
|
|
decapolo
Starting Member
15 Posts |
Posted - 2005-07-13 : 01:10:55
|
| well ... there are multiple types of user-defined data to be inserted ... isn't there any possibility that this might just work?? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-07-13 : 02:06:11
|
| Which bit do you think might just work ?You can't turn off the transaction log for your inserts, it doesn't work like you think it works.andXML isn't fast.You need to understand SQL Server achitecture if you want to improve the performance of your application.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
decapolo
Starting Member
15 Posts |
Posted - 2005-07-13 : 05:33:33
|
| in any case wat do u suggest as to what shud i do given the current situation??regardz,sAm |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-07-13 : 06:17:16
|
| I don't know enough about your current situation to suggest an alternative.Buy a faster machine ?Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
decapolo
Starting Member
15 Posts |
Posted - 2005-07-13 : 06:21:40
|
| yeah ... right!!!regardz,sAm |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-07-13 : 07:41:20
|
| The fastest insert performance you can get from SQL Server utilizes BULK INSERT and bcp, look both of these up in Books Online. They do NOT work with XML as neither one can parse XML data, but for plain old text data they are very fast indeed. Also, they can insert data with absolutely minimal logging, i.e. they will not log each row inserted as a normal insert would. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-13 : 09:17:47
|
What was the format of the data before it was XML-itized ?Do You need the XML format ?Sometimes XML is used unnecessarily as an intermediate transport format.The fastest way to move data, is to get it out in csv,(flat file)then get it in using some of the techniques Rob described.If You stick with it, well there is no really fast way to process it...quote:
well ... there are multiple types of user-defined data to be inserted ... isn't there any possibility that this might just work??
A staging table with the raw data, and set based processing on this would probably be the fastest.(or several files could also work)rockmoose |
|
|
|
decapolo
Starting Member
15 Posts |
Posted - 2005-07-14 : 00:46:14
|
| thanx a millions guys ... am working right now to see if it works ...regardz,sAm |
|
|
|
|
"Transaction log" in SQL Server 2000