locked
Cascading deletes and dataset designer with Azure RRS feed

  • Question

  • Hi All

    I'm migrating an VB.Net 3.5 windows forms application to utilize a SQL Azure DB.  I've run into a problem with cascading deletes in a child table with a large number of related entries and would like some advice on where the problem might be occurring.   When I delete an entry in the parent table my application locks up.  This hasn't been a problem with a remote SQL server or local SQL db.  I had a look with a local DB and SQL profiler and there are a lot of commands for the deletes from the child table which I am assuming might be the cause of the problem with Azure.  I'm a bit of a SQL novice so there are a couple of questions I would like answered.

    Firstly a lot of my code is generated using the dataset designer in Visual Studio.  This is also where I have created the relationships for the datatables.

    1.  Is the dataset designer grossly inefficient when it comes to Azure so that applications will be costly in the number of transactions, data and speed in comparison with manually created code or SQL stored procedures?

    2.  Does it make a difference to the number of transactions such as cascading deletes if relationships are created in the SQL database or the dataset designer?

    3.  Is there something different in SQL Azure that might be causing a problem with my cascading deletes? 

    4.  Does anyone have any recommendations for application/DB design for SQL Azure DBs to improve efficiency?

    Thanks for any assistance. 

    ilr

     

     

     

    Friday, September 3, 2010 1:12 AM

Answers

  • After further research I don't think this is necessarily a SQL Azure issue.  It seems that the problem is that I have a couple of columns in my datatable that are only used to display calculated values and after these calculations are made all rows in the datatable are marked as changed.  When the tableadapter is updated after a delete or update of even a single row it is updating every row in that datatable.  Because there is a large number of rows in that table SQL Azure doesn't process it.  This hasn't been an issue with a local DB or remote DB.  

    I have posted to the ADO.net forum to see if I can get assistance on this but if anyone in this forum has any ideas to handle this I would appreciate it.

    Regards

    ILR 

    • Marked as answer by Yi-Lun Luo Thursday, September 23, 2010 9:34 AM
    Monday, September 6, 2010 5:23 AM