Improve performance for Data input

[noccy]

Hello!

 

I am currently using this function to input data in a SQL-server database:

 

Public Function ImportTxt(ByVal strDato As String, ByVal strKlokkeslett As String, ByVal strFraNummer As String, ByVal strFraBruker As String, ByVal strTilNummer As String, ByVal strTilBruker As String, ByVal strType As String, ByVal strVarighet As String, ByVal strRetning As String, ByVal strLand As String, ByVal strCelle As String, ByVal strBasestasjon As String, ByVal strIMEI As String, ByVal strKildefil As String, ByVal strDataType As String) As Integer

Dim cnn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=PC60380\VSDOTNET; Initial Catalog=**********;user id=****;password=*******")

Dim cmd As New SqlClient.SqlCommand()

cnn.Open()


cmd.Connection = cnn

cmd.CommandText = "INSERT INTO tblData "

cmd.CommandText = cmd.CommandText & "(Dato, Klokkeslett, FraNummer, FraBruker, TilNummer, TilBruker, AnropType, Varighet, Retning, Land, Celle, Basestasjon, IMEI, Kildefil, DataType)"

cmd.CommandText = cmd.CommandText & " VALUES(@Dato, @Klokkeslett, @FraNummer, @FraBruker, @TilNummer, @TilBruker, @AnropType, @Varighet, @Retning, @Land, @Celle, @Basestasjon, @IMEI, @Kildefil, @DataType)"

cmd.Parameters.Add("@Dato", SqlDbType.DateTime)

cmd.Parameters.Add("@Klokkeslett", SqlDbType.DateTime)

cmd.Parameters.Add("@FraNummer", SqlDbType.VarChar, 25)

cmd.Parameters.Add("@FraBruker", SqlDbType.VarChar, 200)

cmd.Parameters.Add("@TilNummer", SqlDbType.VarChar, 25)

cmd.Parameters.Add("@TilBruker", SqlDbType.VarChar, 200)

cmd.Parameters.Add("@AnropType", SqlDbType.VarChar, 60)

cmd.Parameters.Add("@Varighet", SqlDbType.VarChar, 10)

cmd.Parameters.Add("@Retning", SqlDbType.VarChar, 10)

cmd.Parameters.Add("@Land", SqlDbType.VarChar, 5)

cmd.Parameters.Add("@Celle", SqlDbType.VarChar, 6)

cmd.Parameters.Add("@Basestasjon", SqlDbType.VarChar, 100)

cmd.Parameters.Add("@IMEI", SqlDbType.VarChar, 20)

cmd.Parameters.Add("@Kildefil", SqlDbType.VarChar, 50)

cmd.Parameters.Add("@DataType", SqlDbType.VarChar, 20)





cmd.Parameters("@Dato").Value = strDato

cmd.Parameters("@Klokkeslett").Value = strKlokkeslett

cmd.Parameters("@FraNummer").Value = strFraNummer

cmd.Parameters("@FraBruker").Value = strFraBruker

cmd.Parameters("@TilNummer").Value = strTilNummer

cmd.Parameters("@TilBruker").Value = strTilBruker

cmd.Parameters("@AnropType").Value = strType

cmd.Parameters("@Varighet").Value = strVarighet

cmd.Parameters("@Retning").Value = strRetning

cmd.Parameters("@Land").Value = strLand

cmd.Parameters("@Celle").Value = strCelle

cmd.Parameters("@Basestasjon").Value = strBasestasjon

cmd.Parameters("@IMEI").Value = strIMEI

cmd.Parameters("@Kildefil").Value = strKildefil

cmd.Parameters("@DataType").Value = strDatatype



cmd.ExecuteNonQuery()

cnn.Close()


End Function

 

I run this function while looping through a text file, so the function might run 100k times...

 

For small amounts of data the function performes well, but when the number og records to be inserted increases the performance is poor.

 

I am thinking that inserting into a dataset, and then update the db with the dataset would be a faster method, but I have not done that before, and really don't know how to do it...

 

Can anyone tell med how to do this, or if there are other prefered metods?

 

Any help is appreciated

 

 

noccy

[PlausiblyDamp]

Using a dataset may help but one thing you could do is move the creation of the connection, stored proc and all the parameters outside of that function - currently everytime you execute the function you are opening a new DB connection, creating the command, creating each parameter, then doing the insert and throwing all the objects away.

[noccy]

Hello!

 

Could you help me on how to acheive this?

 

noccy