SQL Server ?trick?

Discussion in 'OT Technology' started by michelin man, Nov 19, 2003.

  1. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    Our dba has set limits on our sql server that is going to cost me many of an hour of redesigning my quires.

    Is there any way to "trick" the server into thinking/seeing the queries as smaller than they are?



    Here is the message I get when I try and run a query

    The query has been canceled because the estimated cost of this query (983) exceeds the configured threshold
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah - you have to run several small queries before you run yours which may be a few minutes, to a few hours of reprogramming. You can also try indexing the linked fields if you haven't already. The threshold is based on one query being run, not the total runtime of all of the queries required to run before you run your final one. What's the threshold at anyway? It could be only a few off.
     
  3. 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!
    stored procs or inline sql?
     
  4. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Don't know if you have or not, but use SQL Enterprise Manager to view your query's performance. It might yeild some clues to where the bottleneck is at and might help to tweak your query into something more efficient. This is basically performance tuning your query 5Gen touched on this when referring to indexing and such.

    If you've already done that, then I like the idea of the stored procedure approach SLED mentioned. You toss some SQL code on the SQL server and you shouldn't have to modify your code much.

    Another thought I had was to break the query down into a view. I don't know what your query is doing so this may or may not work. If you're doing a lot of grouping, subselects, and/or ordering, this might help - issue your monster query but without the groupings and store the results in a view - then group/order the view and server the results.
     
  5. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Oh, I might mention this: take your SQL query to the DBA and explain the problem.

    From an initial inspection, they may be able to spot a bottleneck. Otherwise, they should know how to performance tune it (via stored procedure, indexing, etc).

    Plus you may score browny points with the DBA for taking the query to them. I know from my dad he gets REALLY ticked if app developers run wild with his departments SQL servers without consulting them. Then when it breaks, he lets them hang themselves - "Well, you knew what you were doing when you built the query/table/database and we don't have any documentation - we figure you would know how to solve [said problem]."

    Or are you already in part of the "hanging" processes?
     
  6. michelin man

    michelin man IDB Construction

    Joined:
    Jul 22, 2001
    Messages:
    11,658
    Likes Received:
    0
    Location:
    Austin
    I will try and run the stored proc again and see if I get the same issue.
    -----
    Side note*
    My team has its own sql server that is hitting up against a warehouse that the dba resides over. In time this is going to turn into a real mess I can already see it.
     

Share This Page