locked
DB Call 10K times RRS feed

  • Question

  • I  have a C# component that is Quering Database inside for each loop.

    for each loop have around 10K records. each record fetch some data from database.

    this sequencial call is taking too much time .

    I have also tried Parallel.For but then i got connection polling Exception .

    ANY BEST PRACTICE FOR THIS.

    Wednesday, October 31, 2012 8:41 AM

Answers

  • I would consider to call DB once and get all data in some in memory cache. Then read the data from this cache.
    So it would be one layer on top of db to do this.

    The best practice is a minimisation of DB calls.


    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions

    Wednesday, November 14, 2012 5:40 AM

All replies

  • Without knowing too much about your requirements or your data, I would try to re-work the code so that you don't need to make 1 query for each record in the loop. If you know you're going to need additional data for each record, maybe you can make 1 query  for all of the records before entering, or after completing, the loop?

    One possible solution is to use a feature called Table Valued Parameters (TVP). You didn't mention what DB you are using, but assuming it is SQL Server version 2005 or higher, look into TVP. A TVP is an array like structure allowing you to pass multiple values as a single parameter to a stored procedure. It is possible to build a TVP from your loop and then make 1 query call to the db, passing in the TVP as a parameter, and returning the results for all of the records in the loop.


    Paul Delcogliano

    Wednesday, November 7, 2012 1:43 PM
  • I would consider to call DB once and get all data in some in memory cache. Then read the data from this cache.
    So it would be one layer on top of db to do this.

    The best practice is a minimisation of DB calls.


    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions

    Wednesday, November 14, 2012 5:40 AM