Inserted 15000 records

Question

Inserted 15000 records

Arsenal

SSC Veteran

Points: 218
More actions
May 30, 2005 at 4:21 pm

#86882

Hello all,
I posted this question on the XML section but I do not get any reply, so I will try it here.
I created a stored procedure to import data from an xml file to a database. The following is my code in vb.net that execute the stored precedure and write to the database:
Dim SQLDataset As New DataSet("root")
Dim DBCommand As SqlCommand = New SqlCommand
Dim tbl As DataTable = SQLDataset.Tables(databaseA)
Dim sb As StringBuilder = New StringBuilder
Dim sw As StringWriter = New StringWriter(sb)
DBConnection.Open()
Dim DBAdapter As New SqlDataAdapter(Select * from databaseB, DBConnection)
DBAdapter.Fill(SQLDataset)
SQLDataset.WriteXml(sw, XmlWriteMode.WriteSchema)

DBCommand.Connection = DBConnection
DBCommand.CommandType = CommandType.StoredProcedure
DBCommand.CommandText = My_stored_procedure
DBCommand.Parameters.Add(New SqlParameter("@XMLFile", System.Data.SqlDbType.NText))
DBCommand.Parameters(0).Value = sb.ToString()
DBCommand.ExecuteNonQuery()
DBConnection.Close()
Catch ex As Exception
Console.Write(ex)
DBConnection.Close()
End Try
The problem is, the above work perfectly if I specify a selection criterion with a 'where' clause in my SQL statement. If I want to import all 15000+ data from databaseB to the xml file then from xml file to databaseA, I got a timeout expired error. Any suggestion on how to improve this? Thanks a bunch!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

gurusql Mr or Mrs. 500 Points: 508 More actions · Answer 1

I cannot answer your question directly, but I think that the standard time-out that VB.NET will wait is 30 seconds. Have you tried starting wth setting your WHERE clause to import 10-100 records and then find out the amount of time that this takes?

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

It seems like you actually want to move data from 1 db to another ... (I might be wrong)

You can try to use :

BCP to export/import data

Linked Servers

Kindest Regards,

Vasc

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 3

Or if you use the same server but different DBs try using full names

INSERT INTO DB1Name.dbo.MyTable1

SELECT * FROM DB2Name.dbo.MyTable2

WHERE ....

Kindest Regards,

Vasc

Brian Filppu Old Hand Points: 322 More actions · Answer 4

I'm assuming you are using a OpenXML statement in your proc.... You have to be careful with that, if you try moving up the nodes with a (at least I think it's this) ../ then you performance can go to you know what. It's a known issue, I've had queries go from over 20 minutes down to 45 seconds with using temp tables or table variables. Point being, don't move up nodes when using a OpenXML Statement.

Brian

Arsenal SSC Veteran Points: 218 More actions · Answer 5

Thanks very much for all the replies.

Brian, I am new to SQL Server, can you tell me what do you mean by moving up the nodes and what is the known issue of doing it? As well do you mind explain a little bit about your method with temp tables/table variables? I appreciate your help! thanx!!

Arsenal SSC Veteran Points: 218 More actions · Answer 6

Actually, am I doing the right thing by importing the large chuck of data from the XML to the database? Any other way to do this?