Dynamically Query a 100 Million Row Table-Efficiently

  • Comments posted to this topic are about the item Dynamically Query a 100 Million Row Table-Efficiently

  • Nice article Gary.

    I was wondering if there would be any benefit to making your TVFs schema-bound?

    I haven't tried to see if it could be done, just wondering if you've tried and/or seen any difference.


    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

  • Very good article Gary, I gave it five stars because you covered the ground with some excellent explanations. Two omissions stood out however - the cost of recompiling, and why oh why didn't you mention the keywords "parameter sniffing"?

    I'll ask Adam to remind you of this next time you meet up. He sits next to me.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (2/24/2015)


    I'll ask Adam to remind you of this next time you meet up. He sits next to me.

    Sounds like a small world.


    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

  • Morning all,

    And thanks for the feedback.

    A big concern I had with writing the article was it length. The original article was 30 pages long and I knew I had to reduce the length otherwise people just wouldn't find the time to read it. So I tried to keep the focus on the dynamic querying and how we can can influence the query plan.

    The section on the query cache got chopped πŸ™

  • gary.strange-sqlconsumer (2/24/2015)


    Morning all,

    And thanks for the feedback.

    A big concern I had with writing the article was it length. The original article was 30 pages long and I knew I had to reduce the length otherwise people just wouldn't find the time to read it. So I tried to keep the focus on the dynamic querying and how we can can influence the query plan.

    The section on the query cache got chopped πŸ™

    I smell a part two coming!


    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

  • so, in one or two sentences: what do you suggest ?

  • Fantastic article!

    I have used with recompile on sp's before to solve exactly this but my question is does the TVF have any performance value?

    If you add the with recompile to an SP declaration, and move the select from the TVF into the body of the sp what performance difference is there?

    Is the TVF an unnecessary step? I will be checking back on the posts as this great real world!

  • I include the encapsulation of the query in a TVF in my demo code, because that's how I would use it in the real world.

    Not because a TVF with the recompile hint will out perform a sproc created with the recompile hint.

    Having the query encapsulated as a TVF offers so much more flexibility, not performance.

    Hope that helps.

  • dwain.c (2/24/2015)


    ChrisM@Work (2/24/2015)


    I'll ask Adam to remind you of this next time you meet up. He sits next to me.

    Sounds like a small world.

    Adam is my brother, an awsome System Admin.

    and Chris must be his colleague. Took me a little while to figure that one out. πŸ˜€

  • dwain.c (2/24/2015)


    ChrisM@Work (2/24/2015)


    I'll ask Adam to remind you of this next time you meet up. He sits next to me.

    Sounds like a small world.

    Adam is my brother, an awsome System Admin.

    and Chris must be his colleague. Took me a little while to figure that one out. πŸ˜€

  • gary.strange-sqlconsumer (2/24/2015)


    Adam is my brother, an awsome System Admin.

    and Chris must be his colleague. Took me a little while to figure that one out. πŸ˜€

    He is awesome, but don't tell him that! GP and Adam are probably the best system guys I've ever worked with.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Absolutely Brilliant article Gary, 5 stars! I really enjoyed the discussion about TVFs. Very good work sir!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • To start with. I did not carefully read the entire article.

    I stopped at the 5th time I saw "Select *" used in the code for an article about Query performance.

    At that point I rolled through and it seems all the SQL uses "Select *" instead of selecting from column names.

    You want to remove the Schema Scan that needs a shared lock to complete and will occur on every execution of your Performance tuned select.

    This was an article about performance tuning right? πŸ˜‰

  • Mr Strange

    I appreciate the information but it also would be helpful to list title/ISBN of Itzik Ben Gan book that you are referring to.

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

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