Multiple DB accesses overhead

greggogreggo Member Posts: 6
edited 2009-06-09 in SQL Performance
Hello everyone,

I am trying to optimize an existing system which is currently using LINQ. I have a section where LINQ makes 4-7 database queries for different sections of data based on a primary key.

Each query is obviously very fast (1-10ms on SQL Server Profiler), but I am concerned that there may be overhead in making each separate query, and am considering writing a single larger query as a stored procedure to get all the information used for the section. I am hesitant to do so as some of the logic to rewrite this will be complex

Could anyone suggest a way of determining what, if any, overhead is involved in making a LINQ query, so that I can better determine if rewriting this section will be helpful?

Thanks in advance,
Greg

Edit: From start to finish, the set of queries takes about 100ms, but I am not sure if that is due to overhead in making each query, or if it is time spent in other places in the code.

Comments

  • ara3nara3n Member Posts: 9,255
    Have you thought about maybe run your queries in parallel?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • greggogreggo Member Posts: 6
    No, I wasn't aware I could perform this functionality in LINQ. Could you please point me in the direction of some resources on this topic.

    Thank you.
  • ara3nara3n Member Posts: 9,255
    you can use google and search for plinq.

    here is one link.
    http://msdn.microsoft.com/en-us/library ... 00%29.aspx
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • greggogreggo Member Posts: 6
    Thank you, however this does not seem to address my problem.

    Each individual query is very fast, so I do not think there will be any performance benefit to running them in parallel. However, I have to access multiple tables, and therefore end up running between 4 and 7 queries. It seems that running this set of queries takes much longer than the sum of running each query on its own. In the trace, I see that each query takes only 1 ms, but it is 150ms from the start of the first one until the end of the last.

    Is it possible to determine if this extra time is due to overhead associated with making a query, or if it is taking place in the code logic? Is it possible to automatically have linq send all the queries in a single session, or does it already do this automatically?

    I am trying to determine if anything could be gained by writing a single stored procedure to return all the results at once. As this would require a lot of change in the program logic, I am hesitant to jump into it without some idea of why this slowdown is occurring.

    Thanks again,
    Greg
  • ara3nara3n Member Posts: 9,255
    I think you need to ask this in another forum related to csharp and linq.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.