Post reply

Adhoc Query Question

  • October 17, 2018 at 7:09 am

    #408985

    Why would I get an adhoc query plan and prepared plan at same time for one query?

    query:
    select id, name from customers where email = 'blah@blah.com'

    I get 2 plans in cache, 1 plan is adhoc and 1 is prepared
    When I run the same query again adhoc count increments by 1

    If i change the email i get another adhoc query but prepared plan increments by 1
    bucketid    refcounts    usecounts    size_in_bytes    memory_object_address    cacheobjtype    objtype    plan_handle
    9492    2    1    16384    0x000000EC0E9D4060    Compiled Plan    Adhoc    0x06000C0071351F19305693C1EB00000001000000000000000000000000000000000000000000000000000000
    25166    2    1    16384    0x000000FADCBB0060    Compiled Plan    Adhoc    0x06000C00CC0EEE28304793C1EB00000001000000000000000000000000000000000000000000000000000000
    5446    4    2    73728    0x000000ED858CC060    Compiled Plan    Prepared    0x06000C00D8E4F302904D93C1EB00000001000000000000000000000000000000000000000000000000000000

  • October 17, 2018 at 7:55 am

    #2010055

    Just adding a single space to a query will cause a new plan to be generated.  It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 17, 2018 at 8:03 am

    #2010061

    Jeff Moden - Wednesday, October 17, 2018 7:55 AM

    Just adding a single space to a query will cause a new plan to be generated.  It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.

    The SQL is parameterized using PDO params are binded etc.
    Could it be a driver issue PHP to MSSQL?

    Unsure why one execution creates a prepared plan and also an adhoc one, from one call.

  • October 17, 2018 at 11:36 pm

    #2010173

    bugg - Wednesday, October 17, 2018 8:03 AM

    Jeff Moden - Wednesday, October 17, 2018 7:55 AM

    Just adding a single space to a query will cause a new plan to be generated.  It's one of the reasons why it's so very important to parameterize code, especially dynamic SQL.

    The SQL is parameterized using PDO params are binded etc.
    Could it be a driver issue PHP to MSSQL?

    Unsure why one execution creates a prepared plan and also an adhoc one, from one call.

    I wouldn't think it to be a driver issue but I don't know for sure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 18, 2018 at 1:41 am

    #2010184

    On further inspection it doesn't look like one query that goes via PDO has a prepared plan in cache everything is adhoc!

Viewing 5 posts - 1 through 4 (of 4 total)

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