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())

        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.CommandText = queryName
                Dim dataAdapter As New SqlCeDataAdapter(cmd)
    End Using

    'Through Reader:-

    Using cmd = m_conn.CreateCommand
    Using dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)


                End Using
    End Using

    'Through ResultSet

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

    Please send me any solution as soon as possible.

    Thanks & Regards
    Vipin Kumar

    • 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.



    “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.





    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