views:

123

answers:

1

Hi, i have a performance problem while inserting data to sqlce.I'm reading string and making inserts to My tables.In LU_MAM table,i insert 1000 records withing 8 seconds.After Mam tables i make some inserts but my largest table is CR_MUS.When i want to insert record into CR_MUS,it takes too much time.CR_MUS has 2000 records and insert takes 35 seconds.What can be reason?I use same logic in my insert functions.Do u have any idea?I use VS 2008 sp1.

Dim reader As StringReader
reader = New StringReader(data)

cn = New SqlCeConnection(General.ConnString)
cn.Open()

If myTransfer.ClearTables(cn, cmd) = True Then
    progress = 0
     '------------------------------------------
cmd = New SqlServerCe.SqlCeCommand
Dim rs As SqlCeResultSet
cmd.Connection = cn
cmd.CommandType = CommandType.TableDirect
Dim rec As SqlCeUpdatableRecord

' name of table 

While reader.Peek > -1
    If strerr_col = "" Then

        satir = reader.ReadLine()
        ayrac = Split(satir, "|")

        If ayrac(0).ToString() = "LC" Then
            prgsbar.Maximum = Convert.ToInt32(ayrac(1))

        ElseIf ayrac(0).ToString = "PPAR" Then
            .
            If ayrac(2).ToString <> General.PMVer Then
                ShowWaitCursor(False)
                txtDurum.Text = "Wrong Version"

                Exit Sub
            End If
            If p_POCKET_PARAMETERS = True Then
                cmd.CommandText = "POCKET_PARAMETERS"
                txtDurum.Text = "POCKET_PARAMETERS"
                rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
                rec = rs.CreateRecord()
                p_POCKET_PARAMETERS = False
            End If
            strerr_col = myVERI_AL.POCKET_PARAMETERS_I(ayrac, cmd, rs, rec)
            prgsbar.Value += 1

        ElseIf ayrac(0).ToString() = "MAM" Then
            If p_LU_MAM = True Then
                txtDurum.Text = "LU_MAM "
                cmd.CommandText = "LU_MAM"
                rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
                rec = rs.CreateRecord()
                p_LU_MAM = False
            End If
            strerr_col = myVERI_AL.LU_MAM_I(ayrac, cmd, rs, rec)
             prgsbar.Value += 1
                  ElseIf ayrac(0).ToString = "KMUS" Then
            If p_CR_MUS = True Then
                cmd.CommandText = "CR_MUS"
                txtDurum.Text = "CR_MUS"
                rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
                rec = rs.CreateRecord()
                p_TR_KAMPANYA_MALZEME = False
            End If
            strerr_col = myVERI_AL.CR_MUS_I(ayrac, cmd, rs, rec)
            prgsbar.Value += 1

end while

Public Function CR_KAMPANYA_MUSTERI_I(ByVal f_Line() As String, ByRef myComm As SqlCeCommand, ByRef rs As SqlCeResultSet, ByRef rec As SqlCeUpdatableRecord) As String

    Try
        rec.SetValue(0, If(f_Line(1) = String.Empty, DirectCast(DBNull.Value, Object), f_Line(1)))
        rec.SetValue(1, If(f_Line(2) = String.Empty, DirectCast(DBNull.Value, Object), f_Line(2)))
        rec.SetValue(2, If(f_Line(3) = String.Empty, DirectCast(DBNull.Value, Object), f_Line(3)))
        rec.SetValue(3, If(f_Line(5) = String.Empty, DirectCast(DBNull.Value, Object), f_Line(5)))
        rec.SetValue(4, If(f_Line(6) = String.Empty, DirectCast(DBNull.Value, Object), f_Line(6)))
        rs.Insert(rec)
    Catch ex As Exception
        strerr_col = ex.Message
    End Try
    Return strerr_col

End Function
A: 

ok,i found problem.I am wrong in CR_MUS.I am craeating cr_mus everytime because i dont make p_CR_MUS=false.And Also increasing progressbar value decrease performance very much too.So now i dont have problem.

Alexander