SQL Performance When Calling Queries

Discussion in 'OT Technology' started by Fase, Jul 6, 2009.

  1. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    What is the performance cost of doing SQL with lots of small queries vs 1 large query.

    Ex

    Code:
    for (int i = 0; i <= 50; i++)
    {
        // Run SELECT query
    }
    
    VS

    Code:
    BEGIN
    
    DECLARE @A INT
    DECLARE @B INT
    ...
    
    SELECT @A = ...
    SELECT @B = ...
    ...
    
    SELECT @A, @B, ...
    END
    GO
    
    Is there standard practice?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    the standard is to test it in your environment and see what works best for your situation.

    1 query is pretty much always better than 100's or even 1000's for the same job, but there are some cases where you just can't avoid it
     
  3. Fase

    Fase Your Face, In A Pickle Jar.

    Joined:
    Apr 6, 2004
    Messages:
    29,540
    Likes Received:
    0
    Location:
    Windsor, Ont, Canada.
    Right now I'm dealing with around 10-15 queries that are basic. Just wasn't sure if there's a general rule of thumb to always try to build 1 query and run it or just run multiple queries.
     
  4. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Do whatever is easiest unless you have reason to think the speed will be a problem. In time you'll develop intuition in this matter. But yes, you almost always want a single query if you're grabbing hundreds or thousands of rows. The speedup can be a 100 or 1000x performance improvement for the query. I once redid a reporting app that did many single selects and manually aggregated them, to use GROUP BY and it got about a 100 times performance improvement. Just to give you an example.
     
    Last edited: Jul 6, 2009
  5. ge0

    ge0 New Member

    Joined:
    Oct 31, 2005
    Messages:
    8,398
    Likes Received:
    0
    Location:
    JERSEY
    Depends if those 100 little queries are being cached by some mechanism ( hibernate, terrcotta, etc... )

    Its good practice to usually in a standard enviroment to use ONE.
     

Share This Page