Adding a Query Governor to Dynamic-Search Stored Procedures

  • Dwain Camps

    SSC Guru

    Points: 86893

    Comments posted to this topic are about the item Adding a Query Governor to Dynamic-Search Stored Procedures


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • fregatepllada

    SSCommitted

    Points: 1648

    In advanced search situation instead of creating acrobatic code on back end I just added a simple validation on front end that at least one input parameter has to be defined 😉 this work well with query results paging :-D:-D:-D

  • Dwain Camps

    SSC Guru

    Points: 86893

    fregatepllada (12/15/2014)


    In advanced search situation instead of creating acrobatic code on back end I just added a simple validation on front end that at least one input parameter has to be defined 😉 this work well with query results paging :-D:-D:-D

    I have an overall preference to handle that kind of logic in the back end.

    https://www.simple-talk.com/sql/t-sql-programming/using-stored-procedures-to-provide-an-application%e2%80%99s-business-logic-layer/

    As they say though, different strokes for different folks. I recognize that some prefer to hide their business logic in the application layer. I do not so that when business logic changes it can be changed without re-deploying the application.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • fregatepllada

    SSCommitted

    Points: 1648

    Both validation check and paging had been implemented in stored procedure - you are preaching to a church choir 😉

    I had a wonderful conversation with BAs - they were unable to spell out

    Filtering rules to avoid getting full set of data - all filtering parameters had been OPTIONAL but they agree with my suggestion that at least one of 25+ Search criteria must be specified.

  • Manie Verster

    SSCertifiable

    Points: 7022

    fregatepllada (12/15/2014)


    In advanced search situation instead of creating acrobatic code on back end I just added a simple validation on front end that at least one input parameter has to be defined 😉 this work well with query results paging :-D:-D:-D

    I agree to add validation in the front end but I am don't like dumb or lizard brained users because if that is what you think of your users then you can't expect them to respect you. My users are my barometer that shows me how well I do my job. If they smile then I am good and I want to be comfortable in their presence and they in mine. You should never be above your users but equal. Just my two cents.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Dwain Camps

    SSC Guru

    Points: 86893

    I never said I was above my users. Heck, most of them get paid more than me anyway!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Manie Verster

    SSCertifiable

    Points: 7022

    dwain.c (12/15/2014)


    I never said I was above my users. Heck, most of them get paid more than me anyway!

    But talking about dumb and lizard brain makes you look like you feel superior to them.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • fregatepallada

    SSC-Addicted

    Points: 407

    Manie, we all had our "CLIENT IS ALWAYS RIGHT" moments, even when client was 100% absolutely wrong.

    How about user request to implement 5 different data interfaces for 5 different entities (that had only ID and Description in common) in a single file (!). We settled in the end on ONE file - Excel spreadsheet with 5 worksheets 🙂

  • Dwain Camps

    SSC Guru

    Points: 86893

    dwain.c (12/15/2014)


    I never said I was above my users. Heck, most of them get paid more than me anyway!

    Actually I have the utmost respect for them. After all, they're the ones that need to suffer through all of the buggy and poor performing code software developers produce.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86893

    fregatepallada (12/15/2014)


    Manie, we all had our "CLIENT IS ALWAYS RIGHT" moments, even when client was 100% absolutely wrong.

    How about user request to implement 5 different data interfaces for 5 different entities (that had only ID and Description in common) in a single file (!). We settled in the end on ONE file - Excel spreadsheet with 5 worksheets 🙂

    The client is always right. Even more so when they are 100% wrong.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • fregatepallada

    SSC-Addicted

    Points: 407

    Clients are ALWAYS right - as long as they are paying!

  • John N Hick

    Ten Centuries

    Points: 1315

    I like the concept, but I don't like extraneous function calls:

    "WHERE a.Last_Status_DT >= DATEADD(day, -@LookbackDays, @CurrentDT)"

    My preference would be to place the DATEADD clause in yet another variable before calling the SELECT.

    Just a performance preference of mine.

    Great article!

  • Dwain Camps

    SSC Guru

    Points: 86893

    John Hick-456673 (12/16/2014)


    I like the concept, but I don't like extraneous function calls:

    "WHERE a.Last_Status_DT >= DATEADD(day, -@LookbackDays, @CurrentDT)"

    My preference would be to place the DATEADD clause in yet another variable before calling the SELECT.

    Just a performance preference of mine.

    Great article!

    If you are worried about the optimizer running that DATEADD more than once (as in once per row examined), I believe that it is smart enough to not do that. Of course there is nothing wrong with your method either.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • GPO

    SSCarpal Tunnel

    Points: 4556

    "The customer is always right." is a great principle but not an absolute. Especially when the customer comes to you with a problem and then proceeds to tell you what the solution is. Especially when they're unaware of all the possible options to solve the problem. The customer is not always right, but the customer IS always the customer and deserves that courtesy.

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • fregatepllada

    SSCommitted

    Points: 1648

    GPO (4/5/2015)


    "The customer is always right." is a great principle but not an absolute. Especially when the customer comes to you with a problem and then proceeds to tell you what the solution is. Especially when they're unaware of all the possible options to solve the problem. The customer is not always right, but the customer IS always the customer and deserves that courtesy.

    Our slogan - "For your money - any feature"

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply