SQL ExecuteTable gets slower when optimized compiled

Discussion in 'Questions (Windows Mobile)' started by corwin42, May 28, 2009.

  1. corwin42

    corwin42 Expert Licensed User

    Hi,

    I'm just looking for some speed optimizations for the application I am currently working on. Currently I store my data in an invisible table control. Now I'm trying to use an "in memory" SQLite Database to store my data. For this I wrote a small test program that reads recursively all files from a directory and saves the filename and last modification date in an SQL table. To display the data I use the ExecuteTable() to load the data from the SQL table into a table control.

    There is a strange effect in performance if I (optimize) compile the test program that I didn't understand.

    The appended test program has 3 stages between each a Msgbox() is displayed. 1st stage is generating the file list which is really fast in device IDE and compiled application. I use a test directory with 5000 files which is a normal amount for the later application. Generating the file list takes only a few seconds (both in device IDE and compiled for Device).

    2nd stage is a loop that gets the last modification date for alle the files and stores this data in an SQL table. This takes one minute in the device IDE and 30 seconds with the compiled Exe. That is Ok for me.

    3rd stage is just loading all the data from the SQL table into a table control with ExecuteTable(). The strange thing is that if run from the device IDE this process takes 8 seconds and if run with the compiled exe it takes 25 seconds.

    Erel, do you have any idea why the compiled exe is slower here than the uncompiled version on the device IDE?

    By the way: Later in my application I don't really need to load the SQL data into a table control. I will work with the SQL table directly. Though I thought that it would be a nice step to convert my application because the slowest part is to add data to a large table (the table control gets slower adding new data if there are many thousand rows in it). Because the ExecuteTable() is quite fast I thought I would add my data to an SQL table and then load this into the table control so the rest of the application could work with the table control as before.

    If you want to try the test program be sure to change the path to one which contains a few thousand files.

    Greetings,
    Markus
     

    Attached Files:

  2. Erel

    Erel Administrator Staff Member Licensed User

    The reason is that the underlying .Net control (DataGrid) was "improved" in .Net CF 2.0 with many features that made it quite slower than the .Net CF 1.0 DataGrid.

    It is rarely useful to load a table with many rows. You should work with the SQL table instead and only load the rows which you want to display.
     
  3. corwin42

    corwin42 Expert Licensed User

    Thanks for the info. I will change my application then to fully use the SQL table.

    As I said before I don't want to display the table normally. I used it as a data store for now but found out that it is not very well suited for large amount of data. Hope to get a real speed gain if I use an SQL table.

    Greetings,
    Markus
     
  4. berndgoedecke

    berndgoedecke Active Member Licensed User

    SQL speed

    Hello corwin42,
    do you use an index for the column that represents your selection criteria ?

    Best regards

    berndgoedecke
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice