March 13, 2010 at 4:38 am
Thanks again Steve ... i did something a little different but it helped all the same ... I was pretty beat up by this until your reply ...
Now ... this is slightly off topic with this current discussion but ....
I have this running right now by hand as a query that I run from Management Studio. I will be calling it from my vb ap that's doing all the other background file work.
Right now I have about 30 records per xml file and it takes 58 sec to read, parse, and load into my sql table. When this gets automated it will be receiving a new file about every 60sec. I really need to get this time down. Getting it to the 10 sec range would be where I am in need of if possible.
any thoughts???
March 13, 2010 at 4:41 am
I don't know where my head is .... lol ...
here is the query i am using to do this
declare @data xml
SELECT @data = BulkColumn
/* select * */
from openrowset
(
bulk 'c:\testout\stream.xml',SINGLE_BLOB
)T
insert into [MiddletownData].[dbo].[RectifierData]
select T.Circuit.value('../@Name', 'nvarchar(10)') as CircuitName,
T.Circuit.value('../@ProcID', 'nchar(10)') as ProcID,
T.Circuit.value('../@PrgStp', 'int') as PrgStp,
T.Circuit.value('../@Mode', 'int') as Mode,
T.Circuit.value('../@A', 'float') as Amps,
T.Circuit.value('../@StpAH', 'float') as StpAH,
T.Circuit.value('../@V', 'float') as Volts,
T.Circuit.value('../@StpT', 'float') as StpT,
T.Circuit.value('../@RunTime', 'int') as RunTime,
T.Circuit.value('../@Tick', 'nchar(4)') as Tick,
T.Circuit.value('../@Pwr', 'char(5)') as Pwr,
T.Circuit.value('../@TLeft', 'float') as TLeft,
T.Circuit.value('../@AccumAH', 'float') as AccumAH,
T.Circuit.value('../@Alarm', 'nchar(2)') as Alarm,
T.Circuit.value('../@ProcStart', 'Datetime') as ProcStart,
T.Circuit.value('../@ProcEnd', 'Datetime') as ProcEnd,
T.Circuit.value('../@ProcFree', 'Datetime') as ProcFree,
T.Circuit.value('@Name', 'nvarchar(15)') as PrgName
from @data.nodes('Circuits/Circuit/Prg') as T(Circuit);
using the same xml file I posted with my first msg ...
March 13, 2010 at 10:31 am
The reason for the bad performance is you're 'climbing up' one level each time you select one of the values from the Circuit node. Try to use CROSS APPLY instead.
I'd guess it's at least 10 times faster with your data than your current code...
insert into [MiddletownData].[dbo].[RectifierData]
select
U.Circuits.value('@Name', 'nvarchar(10)') as CircuitName,
U.Circuits.value('@ProcID', 'nchar(10)') as ProcID,
U.Circuits.value('@PrgStp', 'int') as PrgStp,
U.Circuits.value('@Mode', 'int') as Mode,
U.Circuits.value('@A', 'float') as Amps,
U.Circuits.value('@StpAH', 'float') as StpAH,
U.Circuits.value('@V', 'float') as Volts,
U.Circuits.value('@StpT', 'float') as StpT,
U.Circuits.value('@RunTime', 'int') as RunTime,
U.Circuits.value('@Tick', 'nchar(4)') as Tick,
U.Circuits.value('@Pwr', 'char(5)') as Pwr,
U.Circuits.value('@TLeft', 'float') as TLeft,
U.Circuits.value('@AccumAH', 'float') as AccumAH,
U.Circuits.value('@Alarm', 'nchar(2)') as Alarm,
U.Circuits.value('@ProcStart', 'Datetime') as ProcStart,
U.Circuits.value('@ProcEnd', 'Datetime') as ProcEnd,
U.Circuits.value('@ProcFree', 'Datetime') as ProcFree,
T.Circuit.value('@Name', 'nvarchar(15)') as PrgName
from @data.nodes('Circuits/Circuit') as U(Circuits)
cross apply Circuits.nodes('Prg') T(Circuit)
March 14, 2010 at 11:33 pm
OMG .... unbelievable .... went from 59sec for 300 records to < 1sec ... wow ... thanks a million ...
March 15, 2010 at 11:11 am
Glad I could help 😀
And sorry for underestimating the performance improvement 😉
March 15, 2010 at 7:27 pm
another issue just came up ....
i have come across some xml files that have the following:
-
-<Circuit Name="13B-10" Adrs="710" G="13B" Ofst="1" Micro="0" SvrID="0" ProcID="727842327" PrgIndx="641" PrgStp="2" Mode="7" A="37.91" StpAH="96.6" V="18.7" Tmp="32.0" StpT="152" RunTime="184" Comm="0" Tick="4095" Pwr="True" MainC="" ES="100" TLeft="392.0582" AccumAH="113.616" -Alarm="0" ProcStart="2/16/2010 9:17:34 PM" ProcEnd="12:00:00 AM" ProcInfo="" -ProcFree="12:00:00 AM" -TIA="0" TIV="0" TIT="0" Cyc="0" AT="0" CR="0" TCR="0">
-<Prg Name="269PMPG" Indx="641" AcummAH="False">
-<Stp ID="1" Cod="7" N2="34.1" T12="17"></Stp>
-<Stp ID="2" Cod="7" N2="40" T12="221"></Stp>
-<Stp ID="3" Cod="7" N2="34.1" T12="40.9"></Stp>
-<Stp ID="4" Cod="7" N2="25.5" T12="52.6"></Stp>
-<Ext>
-
-</Ext>
-</Prg>
-<UData></UData>
-</Circuit>
-</Circuits><Stp ID="2" Cod="7" N2="40" T12="221"></Stp>
-<Stp ID="3" Cod="7" N2="34.1" T12="40.9"></Stp>
-<Stp ID="4" Cod="7" N2="25.5" T12="52.6"></Stp>
-<Ext>
-
-</Ext>
-</Prg>
-<UData></UData>
-</Circuit>
-</Circuits>1" Cod="7" N2="40" T12="527.3"></Stp>
-<Ext>
-
-</Ext>
-</Prg>
-<UData></UData>
-</Circuit>
-</Circuits>ircuits>ts>s>ts>cuits>"></Stp>
-<Ext>
-
-</Ext>
-</Prg>
-<UData></UData>
-</Circuit>
-</Circuits>
don't mind the "-" as this what i did to post the text in the forum. The first element of -<Circuit> is fine as you can see but below you can see where the file has gotten corrupted and there is a bunch of -</Circuits> ... some are even incomplete within the < identifiers. I would like to truance the bulk load at the end of the good data. This is giving me a sql server first exception error.
June 22, 2010 at 5:25 am
How to save xml file data into sql server database by using vb.net
August 6, 2010 at 5:44 am
Hi there,
I tried using this article but i get an object required error on the line
Set wsh = WScript.CreateObject("WScript.Shell") ?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply