Dynamic thread creation and for Procdure performance RRS feed

  • General discussion

  • Hi ,

    I've a procedure which retrieves hierarchical data from SQL Server when an array of IDs is passed(Comma separated values). As thats taking a lot of time I'm trying to work around with threading by passing each Id in separate thread so that i can synchronize the whole data later., Bu t still i'm facing some amount of latency when the threads are runnings. The total l time taken is still some what high say to 1 min when each ID is executed in 14-15 sec on SQL Server side . Can anyone suggest me a better solution such on sql side or c# side as we need the performance very much.
    foreach (string s in strBreakParts) 
      Thread t = new Thread(new ParameterizedThreadStart(StartProcedure)); 

    • Changed type Figo Fei Thursday, August 28, 2008 9:06 AM to comment
    Monday, August 25, 2008 4:10 PM

All replies

  • Try to find a way to send all your Ids to the database at once.
    I would suggest generating a dynamic query within SQL, by creating a varchar type variable in your SQL stored proc, concatenating out your query, and using the IN statement in T-SQL instead of equals in your query.  This way, you can send the entire list of comma separated IDs, and you can get back all of your data at once, and do some construction in .NET using Linq or some other method. 

    Trying to reduce the number of connections to your sql server will greatly improve your performance here. 

    I once inherited an application similar to the one you're describing.  The original inherited code would take approximately 4-5 minutes to fetch all the data it needed to construct my hierarchical data.  After reducing the number of queries made to the database down to about 5, I managed to speed the process up significantly, to about 30 seconds instead. 
    David Morton - http://blog.davemorton.net/
    Monday, August 25, 2008 4:19 PM
  • Hi David,

    Thank you for the reply, Unfortuanately i cannot query the data using in as the hierarchy i was dealing with is not a kinda org charts but it involves children begin linked to the many roots. Imagine building blocks being part of differnet products and i want to know the children of each product at time passing the productid as the concatenated string. i want a csv of parent for each child and all the values are in one table only in context and maping format i'm using a recursive query for each parent and getting the below result later using distinct and coalesce. I'm expecting the data to be so huge that each parent may have upto 500-1000 childern in hierarchy and children are shared between parents.

    Child            Parent
    100              201,202
    101              201,202,203
    102              201,203

    any Idea on how can i speed up the process on the server side. Currently we are able to achive a speed of 14 sec(6.5 sec query running on server) for 3 products with 500 distinct childern in total.

    Monday, August 25, 2008 6:13 PM
  • Hmya, 14 seconds is ridiculously slow for modern computer technology, unless you're trying to compute the weather forecast for Timbuktu for the next 20 years (it will be hot).  A well designed database scheme should blow raspberries at a join like that.  I have fond memories of playing with SQL Server 2000's query optimizer.  You'd type in the query and it would grind for a while, eventually telling you exactly what kind of columns you'd need to index.  Coming up with the untrivial answers like making compound indexes.  I forget the exact term.  It always worked out.

    Recommended.  And off topic here, find a database programming related forum or newsgroup if you're still stuck.

    Hans Passant.
    Tuesday, August 26, 2008 12:06 AM
  • ThreadSafeBindingList<T> is just a wrapper that contains the BindingList<T> and locks its internal method. ThreadSafeBindingList<T> implementation prevents occupation of synchronizing object at the time when notification is fired. It is especially important to avoid potential deadlock problems when two synchronizing objects are taken by the opposite threads dapfor. com
    Monday, November 12, 2012 4:44 AM
  • Your stored procedure doesn't sound like it does a very efficient job or you are missing a pretty obvious index on a large table.  Either way the solution is to fix your database schema and/or your sql, you need a database/sql forum.

    This is nothing to do with c# and/or threads.

    Paul Linton

    • Edited by PaulLinton Monday, November 12, 2012 6:32 AM
    Monday, November 12, 2012 6:31 AM