none
SQL Connection behaviour... RRS feed

  • Question

  • Hi,

    I'm writing an application for the windows mobile environment, I added sdf file to my project in order to make the C# environment build the TypedDataSet.

    My question is this procedure will give me some benefits through developing, but what about the run time, how the sql connection behaves?


     
    When the application is running, the sql connection opened once or it will be opened then closed for each task?

    in my application I'm using the following scenario, I fill the dataset with the required table whenever I need the data from that table, after that I check if this table was filled previously then I will not fill it again.

    But still the application is slow, and I'm trying to figure out the reasons for that..


    Thank you.
    Monday, May 25, 2009 11:29 AM

Answers

  • Hey,

    That amount of data that is being loaded into memory seems pretty large for 100M. I'm not exactly convinced that this is definitely a memory problem because if this happens you will get out of memory exceptions, are you getting any of these?

    So it sounds like you fill all the tables and such and use DataTable.Select to get the set of rows that is required. Is this correct?

    There are two options I'm thinking, the first option is aimed at reducing the overall set of things loaded into memory.
        Option 1)
          Perhaps you could create a set of select queries so that instead of filling all the data at load, it fills the data only as required. For instance in the first dialog it shows all the categories, then based on the category selected you will select all the companies. So maybe with the visual query designers you can make a query for selecting all companies based on a specified category. Then this can be filled into a datatable which is bounded to the control to fill it with the list and so on. I'm doub

       Option 2)
          I'm not convinced this would necessaily be better but perhaps instead of using select method on DataTable, perhaps you can just create a DataView of the DataTable to begin with and then just specify in the RowFilter property of the view which rows to include or exclude and let the DataView manage the Databinding.

    When you use the select method can you show me a code snippet of how you are using this and how this information is being bound to the listviews?

    You indicate that you are unsure which is taking longer the select statement or loading the list. Perhaps can you just measure both of these incremental operations. Just do something simple like use DateTime.Now before and after the select statement is called and the same thing where you are loading the list

    I would first try to measure what the problem is before attempting any solutions though. I'm still not clear how you are databinding the selected items to the listview, if you could clear this up i might be able to come up with better options

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 3:38 PM

All replies

  • Application  will slow due to interaction of different layer of architecture.
    If you want to faster then use sp with DAL(Data layer) code which is  to be write.
    PK
    Wednesday, May 27, 2009 5:35 AM
  • Hi puruk,

    Sorry but I didn't get your point , would you please explain further ?

    Thanks for your cooperation.


    Wednesday, May 27, 2009 8:06 AM
  • Hi,

    How big are your tables? How much data are you caching into memory?

    I am unsure of which version of VS you are using but perhaps you can profile your code.
    http://msdn.microsoft.com/en-us/magazine/cc337887.aspx

    The link goes over how one that is built into VS works.

    If you don't have this version perhaps you can do something simple like putting in code to track how much time particular operations cost. Then you can narrow down where the specific performance problem is.

    Here are a couple of follow-up questions I have

    1)How much data are you pulling back per table?
    2)What types of tasks are you doing?
    3)Are you caching all the tables on load of the application, can you delay this?
    4)What type of mobile device is this running on? How much memory does it have?

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 27, 2009 4:35 PM
  • use sqlconnection ADO.Net for connection and use stored procedure to execute the query and its return that will better.
    PK
    Wednesday, May 27, 2009 9:47 PM
  • Hi Chris,

    Thank you for your reply,

    Actually I'm using VS2008, I just include the database file inside my project and As you know the VS2008 will define a typed dataset for me, I'm just using this dataset and I'm filling the tables inside the dataset whenevr I need to do that.

    The Scenario like this:

    1 . First dialog, shows the categories.
    2 . When the user select a certain category-> next dialog will select all companies that works under this category .
    3 . When one of these companies is selected-> All products under this company are shown to the user.
    4.
    When the user selects a product, then the specifications and the features of this product is shown.
    5 . As you see it's just a select commands, and actually I'm using the select function of the data table.

    when I move from one step to another step, it takes a long time to fill the list view of the next dialog with the selected data, Actually I don't know if loading the list view is the problem or the select statement, further I have two tables of 15000 record, the memory size of the device is 100 M, it's samsung SGH-i900, the size of the database file (.sdf) is 64 M.

    I'm waiting your analysis chris.

    Thank you very much.
    Thursday, May 28, 2009 5:09 AM
  • In this scenario data loading will be slower than other method I preferred. These all have to interact with different layers of framework to load the data. Event If we need to write some code for displaying the data to use html code inside the stored procedure to load faster. so that it should not take much time to render.
    PK
    Thursday, May 28, 2009 7:06 AM
  • Hey,

    That amount of data that is being loaded into memory seems pretty large for 100M. I'm not exactly convinced that this is definitely a memory problem because if this happens you will get out of memory exceptions, are you getting any of these?

    So it sounds like you fill all the tables and such and use DataTable.Select to get the set of rows that is required. Is this correct?

    There are two options I'm thinking, the first option is aimed at reducing the overall set of things loaded into memory.
        Option 1)
          Perhaps you could create a set of select queries so that instead of filling all the data at load, it fills the data only as required. For instance in the first dialog it shows all the categories, then based on the category selected you will select all the companies. So maybe with the visual query designers you can make a query for selecting all companies based on a specified category. Then this can be filled into a datatable which is bounded to the control to fill it with the list and so on. I'm doub

       Option 2)
          I'm not convinced this would necessaily be better but perhaps instead of using select method on DataTable, perhaps you can just create a DataView of the DataTable to begin with and then just specify in the RowFilter property of the view which rows to include or exclude and let the DataView manage the Databinding.

    When you use the select method can you show me a code snippet of how you are using this and how this information is being bound to the listviews?

    You indicate that you are unsure which is taking longer the select statement or loading the list. Perhaps can you just measure both of these incremental operations. Just do something simple like use DateTime.Now before and after the select statement is called and the same thing where you are loading the list

    I would first try to measure what the problem is before attempting any solutions though. I'm still not clear how you are databinding the selected items to the listview, if you could clear this up i might be able to come up with better options

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 3:38 PM