I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
|||That's where I was going. Glad you found it.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <smckillop@.NOSPAM.ns.sympatico.ca.REMOVECAPS> wrote in message news:%23PKm2RwMEHA.1348@.TK2MSFTNGP12.phx.gbl...
Thanks for your response! I think I may have stumbled upon something that will help... I was finding mixed documents on the internet regarding the Bulk Load functionality... But I think the trick is to use Version 3.0SP2 with identities to get this to work...
I'm going to start with some easier samples and hopefully fluke my way through it. If I have a more precise and meaningful question in my experiments, I'll holler!
Thanks Again!
"Irwin Dolobowsky [MS]" <irwind@.mail.microsoft.com> wrote in message news:%23BIJuAwMEHA.3208@.TK2MSFTNGP10.phx.gbl...
Do golfers have ID's or anything? If not, can you just add an ID column that will auto-generate and id for each row? You could then relate them using that.
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in message news:ubT1rwsMEHA.3420@.TK2MSFTNGP11.phx.gbl...
I have an application that exports an XML file in a strange format. I would like to be able to upload this file into an SQL 2000 database using Bulk Load. I have been able to complete some of the various examples and have a handle on what I need to do in the Bulk Load.
The problem I have is understanding how to load the data (in the form the application exports) into a relational format. My data does not contain any indexes and has multiple tables that will need to be created. A snippet of the XML is copied below:
================================================== ==============================
<Round Cn="Amherst Golf Course" Cc="Amherst" Cs="NS" Cy="CA" St="-1030918208" Et="-1030902963" Sf="0" Pt="31" Gm="3" Op="0">
<Rp N="Steve Peddle" S="STEVE" Hv="25" Te="1">
<Sc h1="7" h2="6" h3="6" h4="3" h5="7" h6="6" h7="6" h8="5" h9="5" h10="5" h11="6" h12="5" h13="3" h14="6" h15="4" h16="6" h17="5" h18="6"/>
<Pt h1="1" h2="2" h3="2" h4="1" h5="2" h6="1" h7="2" h8="1" h9="2" h10="2" h11="2" h12="2" h13="1" h14="2" h15="2" h16="2" h17="2" h18="1"/>
</Rp>
<Rp N="Chris White" S="Chris" Hv="18" Te="1">
<Sc h1="7" h2="5" h3="5" h4="3" h5="8" h6="4" h7="5" h8="6" h9="4" h10="5" h11="6" h12="5" h13="4" h14="6" h15="4" h16="5" h17="5" h18="7"/>
<Pt h1="2" h2="2" h3="1" h4="1" h5="3" h6="1" h7="2" h8="3" h9="2" h10="2" h11="2" h12="2" h13="2" h14="2" h15="2" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Scott McKillop" S="Scott" Hv="14" Te="1">
<Sc h1="7" h2="5" h3="4" h4="3" h5="7" h6="5" h7="5" h8="6" h9="4" h10="5" h11="5" h12="5" h13="4" h14="4" h15="4" h16="4" h17="5" h18="7"/>
<Pt h1="1" h2="1" h3="2" h4="2" h5="3" h6="2" h7="1" h8="2" h9="1" h10="2" h11="2" h12="3" h13="2" h14="1" h15="3" h16="2" h17="2" h18="2"/>
</Rp>
<Rp N="Steve Rayworth" S="SteveR" Hv="13" Te="1">
<Sc h1="6" h2="5" h3="5" h4="3" h5="5" h6="6" h7="3" h8="5" h9="3" h10="6" h11="4" h12="5" h13="3" h14="5" h15="3" h16="4" h17="4" h18="7"/>
<Pt h1="2" h2="2" h3="2" h4="2" h5="2" h6="2" h7="1" h8="2" h9="2" h10="3" h11="1" h12="2" h13="1" h14="2" h15="2" h16="1" h17="2" h18="2"/>
<Ev h1="0" h2="0" h3="0" h4="0" h5="0" h6="0" h7="0" h8="0" h9="4" h10="0" h11="0" h12="0" h13="0" h14="0" h15="0" h16="0" h17="0" h18="0"/>
</Rp>
<Games>
<Gm I="0" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="17" M="0" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="0" H2="0" H3="0" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
<Gm I="11" M="8" D="0" C="25" S="0" E="17" P1="1" P2="1" P3="1" P4="1" P5="0" H1="12" H2="5" H3="1" H4="0" H5="0" Hd1="0" Hd2="0" Hd3="0" Hd4="0" Hd5="0" Hd6="0" Hd7="0" Hd8="0" Hd9="0" Hd10="0" Hd11="0" Hd12="0" Hd13="0" Hd14="0" Hd15="0" Hd16="0" Hd17="0" Hd18="0" O1="0" O2="0" O3="0" O4="0"/>
</Games>
</Round>
================================================== ================================================== =============================
The application is actually used to track golf rounds.
a.. Each round can have many players;
a.. Each Player wil have 1 Score (<sc>)
b.. Each Player will have 1 Putt (<Pt>)
c.. Each Player will have 1 Ev? (<Ev>)
b.. Each Round can have 1 Games
a.. Each Games can have multiple Gm's (<Gm>)
The problem with this structure is that there are no indexes in the file, and from what I understand about the Bulk Load and relational databases, is that it will be next to impossible to link the data properly after importing.
Is there any way that this can be imported into SQL 2000? Will I have to write an application using VB to manually create the indexes and then import? The solution that I come up with will have to be repeatable as the data will need to be updated with a new XML file often.
If the entire XML file will help with understanding the scope of the issue, I can attach it.
Thanks in advance
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Tuesday, March 27, 2012
Bulk insertion
Hi,
I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.
You can make use of SQL BulkCopy feature of ADO.NET. Load your XML into a DataSet/DataTable and run SQL Bulk copy into your table. Very few lines of code.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
foreach (string tableName in tableNames)
{
SqlBulkCopy(dataSet.Tables[tableName], tableName, bulkCopy);
}
}
private static void SqlBulkCopy(DataTable dataTable, string tableName, SqlBulkCopy bulkCopy)
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);
}
Thanks for the reply Raghu. I don't need all the data elements in the XML files, only a selected few. This is an example XML file:
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='c0' rs:name='AFR Finalization' rs:number='1' rs:write='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='18' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='Processed' rs:number='2' rs:write='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='Rate' rs:number='3' rs:write='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='7' rs:precision='0' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row c0='CIF '/>
<z:row c0=' Input ' Processed='12028'/>
<z:row c0=' Finalized ' Processed='5444' Rate=' 45.26%'/>
<z:row c0='RTS '/>
<z:row c0=' Input ' Processed='9802'/>
<z:row c0=' Finalized ' Processed='5504' Rate=' 56.15%'/>
<z:row c0='Interception '/>
<z:row c0=' Input ' Processed='12639'/>
<z:row c0=' Finalized ' Processed='8220' Rate=' 65.04%'/>
</rs:insert>
</rs:data>
</xml>
I only need CIF/Input and RTS/INPUT from this particular file. There will be one of this file for each day of the month and I will have to process a month's worth of xml files. There are tens of different xml files but they all follow the same schema.
Thanks.
bulk inserting uniqueidentifier column
Hi at all,
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
Latin1_General_CI_AS
Latin1_General_CI_AS
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)
>
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
I use in bulk insert a format file like this :
9.0
3
1SQLNCHAR00"|\0"1IDLatin1_General_CI_AS
2SQLNCHAR00"|\0"2Field1Latin1_General_CI_AS
3SQLNCHAR00"|\0"3Field2Latin1_General_CI_AS
and I use this script
BULK INSERT [dbo].[KWTA2] FROM 'd:\WTA2.txt'
WITH (FORMATFILE = 'd:\wta2Format.FMT')
It doesn't work, it prints out
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
Please can you help me?
Why sql converts alphanumerical GUID into only numbers ID?
How can I bulk insert GUID? (I didn't find anything googling around :
\ )
Thanks!
Bob(bob.speaking@.gmail.com) writes:
Quote:
Originally Posted by
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
>
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
>
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
>
I use in bulk insert a format file like this :
>
9.0
3
1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
2 SQLNCHAR 0 0 "|\0" 2 Field1
Latin1_General_CI_AS
Quote:
Originally Posted by
3 SQLNCHAR 0 0 "|\0" 3 Field2
Latin1_General_CI_AS
Does the file really consist of one single line?
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>
Quote:
Originally Posted by
Does the file really consist of one single line?
>
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)
Quote:
Originally Posted by
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
>
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.
I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?
Thanks for the prompt reply :)
bobsql
Bulk inserting into Sql Server
Hello Everyone,
I am currently struggling with a problem bulk inserting data into sql server.
The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).
What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.
1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.
2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).
3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).
Does anyone have any idea's on a cpu friendly approach to this problem ??
Thanks in advance..
Gary.You can actually use BCP
Sunday, March 25, 2012
bulk insert vs CLR stored proc doing transactioned insert
I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.
easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>
|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.
easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>
|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
bulk insert vs CLR stored proc doing transactioned insert
I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
bulk insert vs CLR stored proc doing transactioned insert
I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/te...l
t.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/te...l
t.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Tuesday, March 20, 2012
BULK INSERT Query
Hey i am planning to use the BULK INSERT to copy the data into my Server. Th
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
Monday, March 19, 2012
Bulk Insert into SQL Server Table With XML
Hey There,
Here, is the example of Bulk Insert into SQL Server Table.
From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.
Example SP.
CREATE PROCEDURE StoredProcName
(
@.strXML varchar(8000)
)
AS
Declare @.intPointer int
exec sp_xml_preparedocument @.intPointer output, @.strXML
INSERT into tbl_plnd_insertion
SELECT Column1, Column2, Column3, Column4, Column5
FROM OpenXml(@.intPointer,'/root/tbl_plnd_insertion',2)
WITH (Column1 varchar(20) '@.Column1' , Column2 varchar(20) '@.Column2', Column3 varchar(20) '@.Column3' , Column4 varchar(50) '@.Column4', Column5 varchar(50) '@.Column5')
exec sp_xml_removedocument @.intPointer
Thanks !!!!!
In SQL Server 2000, Text data type is reasonable to use for huge data,
@.strXML Text--varchar(8000)
This is not a bulk insert, it is reading the data from the XML string into a table. sp_xml_preparedocument is expensive, it will use lot of memory & resource when the HUGE data parsed.
In SQL Server 2005, the best option is using XML datatype. You don’t need expensive sp_xml_preparedocuemnt to parse your XML string into selectable values, no need OpenXML also.
Sample,
Code Snippet
Declare @.xml as XML
set @.xml = '<root><student name="Stud1" sex="m"/><student name="Stud2" sex="f"/></root>'
Select
[Table].[Column].value('./@.name', 'varchar(100)') as [Name],
[Table].[Column].value('./@.sex', 'char') as [Sex]
from
@.xml.nodes('/root/student') as [Table]([Column])
|||Hey
I am really appreciating your input… J
I did not describe all details in my code.
Thanks
Wednesday, March 7, 2012
bulk insert and trigger
Some application insert record to MyTable using the bulk insert statement
without the clause "With fire_triggers".In MyTable i need that the triggers
fire.
I can't modify the bulk insert statement. How i can fire the triggers on my
table?
thx
In this case, you need to run sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
without the clause "With fire_triggers".In MyTable i need that the triggers
fire.
I can't modify the bulk insert statement. How i can fire the triggers on my
table?
thx
In this case, you need to run sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
BULK INSERT and Application role
On a connection I use an application role via command:
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?The bulkadmin role is a server-level role. Application roles are at the
database level. If you are using SQL Server 2005, you can create a stored
proc where the BULK INSERT is done with elevated privileges and then grant
EXEC to the app role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Max" <Max@.discussions.microsoft.com> wrote in message
news:7167E83B-538F-4D72-BF0D-70F757FC289F@.microsoft.com...
On a connection I use an application role via command:
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?The bulkadmin role is a server-level role. Application roles are at the
database level. If you are using SQL Server 2005, you can create a stored
proc where the BULK INSERT is done with elevated privileges and then grant
EXEC to the app role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Max" <Max@.discussions.microsoft.com> wrote in message
news:7167E83B-538F-4D72-BF0D-70F757FC289F@.microsoft.com...
On a connection I use an application role via command:
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?
Saturday, February 25, 2012
Bulk insert - to log or not to log
Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
Bulk insert - to log or not to log
Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
Bulk insert - to log or not to log
Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
Friday, February 24, 2012
Bulk import images to binary object in SQL 2000
I have a web application that I am rebuilding. I have many picture files that want to take off the file system and move into SQL as a blob. I will create an index of uids against the file names but need a good way to bulk add the files to the database... any hints on code or tools would be a great help.
Thanks
Bill
You can use a utility 'textcopy' in 'Command Prompt' under the SQL instance directory:
C:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn
FYI, you can use it as following, make sure the command in a single line:
====Copy data from file to table====
textcopy /S labsh96223\iori2000 /I /F c:\xml\iori.bmp /D northwind /T testImage /C img /U sa /P /W "where idx=2"
====Copy data from table to file====
textcopy /S labsh96223\iori2000 /O /F c:\xml\iori.jpg /D northwind /T testImage /C img /U sa /P /W "where idx=2"
You can also use some programming code (written in VC++,C# etc.) to manipulte the binary data of BLOBs between files and database.
Sunday, February 19, 2012
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
bulk copy(batch insert) is failed when the mssql backup the database
Hi All,
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie
bulk copy(batch insert) is failed when the mssql backup the database
Hi All
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie
Subscribe to:
Posts (Atom)