locked
SQL ServerCe Very Slow RRS feed

  • Question

  • I am facing a problem regarding fill datatable.It's very slow when i am execute through .Net.But when i open the SqlCe file in my sqlserver and executes the query in sqleditor it's very fast.My code is as following:-

    dim m_conn = New SqlCeConnection(GetConnectionString())
            m_conn.Open()

        Private Function GetConnectionString() As String
            Return String.Format("DataSource=""{0}""; Password='{1}' ; SSCE:Max Database Size=2048 ;", FileName, Password)
        End Function

    'Throught DataAdapter:-

    Using cmd = m_conn.CreateCommand
                cmd.Parameters.AddRange(m_parameters.ToArray())
                cmd.CommandText = queryName
                Dim dataAdapter As New SqlCeDataAdapter(cmd)
                dataAdapter.Fill(table)
                m_parameters.Clear()
    End Using


    'Through Reader:-

    Using cmd = m_conn.CreateCommand
                cmd.Parameters.AddRange(m_parameters.ToArray())
    Using dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

                    table.Clear()
                    table.Load(dr)

                End Using
                m_parameters.Clear()
    End Using


    'Through ResultSet

    Using rs = cmd.ExecuteResultSet(ResultSetOptions.None)
                        Dim schema = rs.GetSchemaTable()
                        table.Clear()
                        For Each row As DataRow In schema.Rows
                            table.Columns.Add(row!ColumnName, row!DataType)
                        Next
                        While rs.Read
                            Dim values(rs.FieldCount - 1) As Object
                            rs.GetValues(values)
                            table.Rows.Add(values)
                        End While
                    End Using


    Please send me any solution as soon as possible.

    Thanks & Regards
    Vipin Kumar

    Vipin
    • Moved by Bob Beauchemin Friday, September 2, 2011 4:51 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Friday, September 2, 2011 4:34 AM

All replies

  • I beleive it is the fact that you use a DataTable that makes the process slow, not the reading of data. But it is hard to tell unless you share more information, like the number of columns and rows selected, and your select  statement.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Wednesday, September 7, 2011 6:45 AM
  • Vipin,

    Can you provide more information as suggested by Eric.

    Thanks

    Imran


    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Thursday, September 8, 2011 5:52 PM
  • Hello Vipin,

     

    You may want to profile the app to measure the timings for fill, ExecuteReader and other methods.  If this is on a device, check to see if it has enough memory.  What is the size of database and the select statement?  Can you try using SqlCeResultSet object?  If you are able to extract the Select statement, you may run it in SQL Management Studio and review the Query Plan.

     

    Thanks,

     

     

    Swetha G | Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 9, 2011 2:23 PM