build sql script: inserts

Showing posts with label inserts. Show all posts

Tuesday, March 27, 2012

Bulk load doesn't preserve element order?

Hi,
I have an interesing issue with sqlxml bulk load. It's about the order in which bulk load inserts rows in the database. I assumed that the rows should be inserted in the same order as the elements appear in the xml file, but looks like it is not always the case.
We use SQL server 2000 and SQLXML 3.0 SP 3. The files are large(2GB) and look something like this:
<Betalingskrav>
<Krav>
<BetalingsKravStatus><![CDATA[1]]></BetalingsKravStatus>
<FakturaDato><![CDATA[04.02.2006]]></FakturaDato>
<CustomerAgreementReference>
<![CDATA[12345689]]></CustomerAgreementReference>
....
<Samtaler>
<Samtale_Gruppeheader>
<SamtaleType><![CDATA[Mobilsamtaler]]></SamtaleType>
<Benevning><![CDATA[Abonnentnr]]></Benevning>
<Telefonnr><![CDATA[12 34 56 78]]></Telefonnr>
<Periode><![CDATA[Periode03.01.06-02.02.06]]></Periode>
<Etikett_Dest><![CDATA[Destinasjon/Operator]]></Etikett_Dest>
<Etikett_Oppr_nr><![CDATA[Oppringt nummer]]></Etikett_Oppr_nr>
<Etikett_SamtDato><![CDATA[Dato]]></Etikett_SamtDato>
<Etikett_Samtstart><![CDATA[Start]]></Etikett_Samtstart>
<Etikett_lngd><![CDATA[Samtalelengde]]></Etikett_lngd>
<Etikett_Kost><![CDATA[Kostnad]]></Etikett_Kost>
</Samtale_Gruppeheader>
<Samtale_Data>
<Dta_Dest><![CDATA[Tele2 GSM]]></Dta_Dest>
<Dta_Oppr_nr><![CDATA[12345678]]></Dta_Oppr_nr>
<Dta_SamtDato><![CDATA[02.01.06]]></Dta_SamtDato>
<Dta_Samtsstart><![CDATA[11:13]]></Dta_Samtsstart>
<Dta_lngd><![CDATA[ 0:00:37]]></Dta_lngd>
<Dta_Kost><![CDATA[0.81]]></Dta_Kost>
</Samtale_Data>
<Samtale_Data>
....
</Samtale_Data>
...
</Samtaler>
</Krav>
...
</Betalingskrav>

The schema (i list a shortened version):
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Krav_T2KravLinje"
parent="Betalingskrav"
parent-key="BetalingskravID"
child="T2BetalingskravLinje"
child-key="BetalingskravID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="KravType">
<xsd:all>
<xsd:element name="BetalerID" default="9999" />
<xsd:element name="FakturaTypeID" default="0" />
<xsd:element name="MalID" default="0" />
<xsd:element name="BuntID" default="0" />
<xsd:element name="UtstederID" default="28" />
...
<xsd:element name="Samtaler" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Samtale_Gruppeheader" type="Samtale_GruppeheaderType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje" />
<xsd:element name="Samtale_Data" type="Samtale_DataType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MVATekst1" type="xsd:string" />
<xsd:element name="MVATekst2" type="xsd:string" />
...
</xsd:all>
</xsd:complexType>
<xsd:complexType name="Samtale_GruppeheaderType">
<xsd:sequence>
<xsd:element name="SamtaleType" type="xsd:string" />
<xsd:element name="Benevning" type="xsd:string" />
<xsd:element name="Telefonnr" type="xsd:string" />
<xsd:element name="Periode" type="xsd:string" />
<xsd:element name="Etikett_Dest" type="xsd:string" />
<xsd:element name="Etikett_Oppr_nr" type="xsd:string" />
<xsd:element name="Etikett_Samtant" type="xsd:string" />
<xsd:element name="Etikett_SamtDato" type="xsd:string" />
<xsd:element name="Etikett_Samtstart" type="xsd:string" />
<xsd:element name="Etikett_lngd" type="xsd:string" />
<xsd:element name="Etikett_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Samtale_DataType">
<xsd:sequence>
<xsd:element name="Dta_Dest" type="xsd:string" />
<xsd:element name="Dta_Oppr_nr" type="xsd:string" />
<xsd:element name="Dta_Samtant" type="xsd:string" />
<xsd:element name="Dta_SamtDato" type="xsd:string" />
<xsd:element name="Dta_Samtsstart" type="xsd:string" />
<xsd:element name="Dta_lngd" type="xsd:string" />
<xsd:element name="Dta_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="Betalingskrav" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Krav" type="KravType" sql:relation="Betalingskrav" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML data represents telephone bills with a listing of all calls, sms's, data services and so on. Samtale_Gruppheader elements define headers, and Samtale_Data elements contain the items that go below the headings. The point is that the sequence of elements inside <Samtaler> must be preserved in the database via identity column to provide correct formatting of bills. Not a good approach, I understand, but we are dealing with a legacy system that is not easy to modify...
The shema above maps xml data to two tables in the database that have primary-foreign relationship. The foreign-key table(T2BetalingskravLinje) contains both contents of Samtale_Gruppeheader and Samtale_Data elements. When the bills are rendered, rows in this table are shown in the sequence given by the identity column, in other words in the order they were inserted into the table.
KeepIdentity property is set to false on the bulk load component, the SQL server generates identity keys itself. This works fine, foreign keys are propagated correctly. But the order of rows is not the same as the order of elements in <Samtaler>! Not all the times, but in about 5 to 10% of the cases and I don't see any pattern here. I extracted a <krav> element that had this problem from a real file, and tried to bulk load the resulting short file. The effect disappered, rows were in correct order...
So the question is: Is this behaiviour normal for bulk load component and the order of row insertion into foreign-key tables is not guaranteed to be the same as the order of elements in the xml file? Is it a bug or a feature? :-) Is there any way to enable order preservation?
Thanks in advance for any ideas or suggestions!

The bulkloading component on which the SQLXML bulkloader is built does not guarantee order. So the behaviour is normal.

Best regards

Michael

sql

Bulk inserts to Data Warehouse - Best Practices?

Hello all,

I just started a new job this week and they complain about the length of
time it takes to load data into their data warehouse,
which they do once a month.

From what I can gather, they rebuild the indexes before the insert with an
80% Fillfactor, then insert the data (with the
indexes enabled), then rebuild the indexes with a 100% Fillfactor.

Most of my RDBMS experience is with a different product. We would have
disabled the indexes and Foreign Keys, loaded the data, then
re-enabled them, moving any records that violated the constraints into an
appropriate audit table to be checked after.

Can someone share with me what the accepted "best practices" are for loading
data efficiently into a data warehouse?

Any thoughts would be deeply appreciated.

SteveIn article <kZI4d.27460$pA.1692759@.news20.bellglobal.com>,
steveee_ca@.yahoo.com says...
> Hello all,
> I just started a new job this week and they complain about the length of
> time it takes to load data into their data warehouse,
> which they do once a month.
> From what I can gather, they rebuild the indexes before the insert with an
> 80% Fillfactor, then insert the data (with the
> indexes enabled), then rebuild the indexes with a 100% Fillfactor.
> Most of my RDBMS experience is with a different product. We would have
> disabled the indexes and Foreign Keys, loaded the data, then
> re-enabled them, moving any records that violated the constraints into an
> appropriate audit table to be checked after.
> Can someone share with me what the accepted "best practices" are for loading
> data efficiently into a data warehouse?
> Any thoughts would be deeply appreciated.

Your method, dropping indexes, unless part of a constraint, would be
proper. You could BCP the data into a flat table and then process it
from the flat table to the destination table after you clean it in the
flat table.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Here's a link to a DTS/BI best practices doc:
http://msdn.microsoft.com/library/d...ntbpwithdts.asp

Every situation is different, with a lot depending on how much data is being
loaded and what (if any) indexes need to be in place to support the ETL
process. It seems a bit odd to rebuild indexes twice, though. Normally,
one drops all but the required indexes beforehand and recreates the other
(reporting) indexes afterward.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Steve_CA" <steveee_ca@.yahoo.com> wrote in message
news:kZI4d.27460$pA.1692759@.news20.bellglobal.com. ..
> Hello all,
> I just started a new job this week and they complain about the length of
> time it takes to load data into their data warehouse,
> which they do once a month.
> From what I can gather, they rebuild the indexes before the insert with an
> 80% Fillfactor, then insert the data (with the
> indexes enabled), then rebuild the indexes with a 100% Fillfactor.
> Most of my RDBMS experience is with a different product. We would have
> disabled the indexes and Foreign Keys, loaded the data, then
> re-enabled them, moving any records that violated the constraints into an
> appropriate audit table to be checked after.
> Can someone share with me what the accepted "best practices" are for
> loading
> data efficiently into a data warehouse?
> Any thoughts would be deeply appreciated.
> Steve

Bulk inserts between two different DBMS

Hi folks,
I have a table located in DB2 nd I need to have a mirror image of this table on a SQL2000 database to avoid some server downtime problems.
Right now I have a solution using ADO.NET with Windows Services.

This windows service invokes itself everyday morning and pulls all the records from this table in DB2 to a dataset. Then I loop through the dataset and insert every record into SQL 2000 Table. This method is working fine ( It take approximately 2 minutes to insert 5000 records). I am just wondering whether there is any way to acheive bulk insertion in this case. Considering future growth of table I am not thinking the existing solution is neither elegant nor efficient.

Please let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

Thanks,
SaiPlease let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

You probably want to set up a DTS package in SQL Server that migrates the data. This does support using another DBMS as a source.

The alternative is you dump from DB2 to a flat file and then BULK INSERT that flat file into SQL Server.

Both approaches will be much faster than a procedural row by row transfer on large data sets.sql

Thursday, March 22, 2012

Bulk Insert SQL Server in C#

I am searching for an efficient way to do
bulk inserts via C#. It seems there are three
ways of doing that:

1.) Copy values to file and call BCP or BULK INSERT in TSQL
2.) Call the IRowFastLoad Interface of the SQL OLEDB Provider
3.) bcp interface of the ODBC driver

I think 2.) and 3.) are the only viable solutions and I wanted
to ask if anybody out there has already experiences
with these interfaces and the way of invocating them in C#.

Thank you for your help.

Bernhard
------------------
Solution Architect
Management Factory
Vienna/AUSTRIA/EUROPE
http://www.mf.ag
Bernhard.Walter@.mf.agYou may want to look at using SQL-DMO with C#. I've not coded in C# however I have used SQL-DMO with VB, VBScript (in an ASP page) and Perl.|||Thanks for the help.

Using SQL-DMO seems to be quite the same than inserting into a
file and then calling BCP, right ?
What I am looking for is a more efficient approach (memory to
SQL Server), because my routine has to work for 1 record to
millions of records in a fast way.

Bernhard

Tuesday, March 20, 2012

Bulk Insert performance

I have a situation where I need to do multiple inserts into the sql mobile db at one time. I am wondering what would be the most efficient method to do this. Right now I am just doing many inserts, but the performance is lacking. I tried to wrap all the inserts into 1 sql command and process it like that, but it does not seem to want to execute. Any help would be appreciated.

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Darren,

What is the best method to import a large amount of data into SQL Mobile? Are you saying that Batching isn't supported? Also, SQL Server will not be installed on our servers because of licensing costs. So this takes out the replication and RDA methods for imports.

In a C# application, I'm reading ASCII files and need to add to three seperate tables. It takes hours to process 100,000+ records. BTW - I need to support over 2 million records. The total file size will be around 1 gig. Since SQL CE supports files up to 4 gig, I'm not expecting this to be a problem. I'm I right here?

I'm creating an Item Lookup function that will run stand alone in our retail chain. The data (SDF file) The data will be refreshed nightly on the server and will be copied down to the device daily. If you have any suggestions you can email me directly @. jhoran@.fheg.follett.com

Here is an example of my code to add to the vendor table. I'm only using 2 fields in this example.

Why is it so slow?

.......

string p1, p2;

// loop through Vendors

int Len = 0,i=0;

try

{

using (StreamReader sr = new StreamReader("VENDOR.EXP"))

{

string line;

Status = "Exporting Vendors";

while ((line = sr.ReadLine()) != null)

{

Len = line.Length;

p1 = line.Substring(0, 9);

p2 = line.Substring(9, (Len - 9));
i = i++;

this.vendorTableAdapter.Insert(p1,p2);

}

catch (System.Exception ex)

{

MessageBox.Show(ex + " Vendor File could not be read");

}

Darren Shaffer wrote:

You are correct, you can only submit one INSERT statement at a time with SQL CE and SQL Mobile (or any SQL command for that matter - no support for batching is included).

to improve INSERT performance, here are some tips:

1. use a paramaterized INSERT statement. prepare the command, set the param values in a loop and reuse the same command for each subsequent INSERT

2. don't apply indexes until after your are done with your INSERTs

3. have a look at using the SqlCeResultSet to insert the data into your table

Darren

|||

Let me see if I can answer your questions:

1. SQL Mobile/Everywhere/Compact Edition databases have a 4GB limit. If you plan to grow one of these databases larger than 128MB (the default maximum), then you need to set the max database size in your connection string to something larger.

2. In terms of your insert statement - using the TableAdapter is equivalent to using one insert statement after the next - you would be able to realize faster insert time (30-50%) with a parameterized insert statement (be sure to call Prepare() on the command one time and then change the parameter values and reuse the command).

3. If you are loading up this large database with vendor lookup data, you might ask yourself if you could perform this load on the desktop versus on device and then deploy the resulting sdf file to device. For databases as large as yours, you will get the sdf file loaded much much faster on the desktop given the CPU and disk speeds available there.

4. With a database of this size on device, be aware that you should have free storage memory in an amount at least equal to the database size to realize an effective tempdb and hence decent performance.

-Darren

|||This was convered a couple of says ago :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923795&SiteID=1

Using the resultset makes no difference to the insert performance either|||

Thanks Darren,

Pardon my ignorance, but I'm new to C#, VS2005 and SQL, but I've been writing code for decades.

2. Can you send me an example or point me to one, of how to avoid using the tableadapter? I was thinking that it was passing parameters.

3. The largest table will be the item table, but I'm using the same technique on it. We have less than 1000 vendors, but we could have 2.8 million items.

I agree about creating the file on the server and sending it to the device. That is in the design. Is activesync the best way for this or would we be better off to "roll our own"?

4. we are planning on at least 2 gig of memory.

p.s. I liked your Webinar on Mobile device development. It gave me a good jump start on this project. I've been working with mobile computing using C++ v1.52 for a long time, and don't expect that it will take all that long to get up to speed with this.

Thanks again for your help!

|||

Jack,

Instead of using the table adapter, try a parameterized insert. Here is the "pseudo code" to do this:

SqlCeCommand cmd = null
SqlCeParameter param = null

try

cmd = _mysqlceconnection.CreateCommand()
cmd.CommandText = "INSERT INTO tablename VALUES (@.Param1, @.Param2, @.Param3)"

param = New SqlCeParameter("@.Param1", SqlDbType.UniqueIdentifier) // set each parameter's type to match the column in the database
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param2", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)
param = New SqlCeParameter("@.Param3", SqlDbType.UniqueIdentifier)
cmd.Parameters.Add(param)

cmd.Prepare() // this allows SQL CE to predetermine an execution plan for the insert and cache it

// note you do this one time and it is outside the insertion loop below

While ' loop through your input file line by line, one row per line

cmd.Parameters("@.Param1").Value = firstColumnValueInYourInputFile

cmd.Parameters("@.Param2").Value = secondColumnValueInYourInputFile

cmd.Parameters("@.Param3").Value = thirdColumnValueInYourInputFIle

etc until you have set all the params in the INSERT statement

cmd.ExecuteNonQuery()

End While

I recommend that anytime you are inserting or updating data in SQL Server you use parameterized SQL - the first time your data contains an apostrophe, a comma, an ampersand, etc, you'll see why.