Dynamically Query a 100 Million Row Table-Efficiently

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

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

  • Dwain Camps

    SSC Guru

    Points: 86883

    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

  • ChrisM@Work

    SSC Guru

    Points: 186078

    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.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Dwain Camps

    SSC Guru

    Points: 86883

    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

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    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 πŸ™

  • Dwain Camps

    SSC Guru

    Points: 86883

    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

  • h.tobisch

    SSCommitted

    Points: 1671

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

  • adam.everett

    SSC Veteran

    Points: 265

    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!

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    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.

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    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

    SSCommitted

    Points: 1800

    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. πŸ˜€

  • ChrisM@Work

    SSC Guru

    Points: 186078

    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.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Alan Burstein

    SSC Guru

    Points: 61074

    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

  • PHYData DBA

    SSCertifiable

    Points: 7541

    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? πŸ˜‰

  • azhuravel3343

    SSC Enthusiast

    Points: 179

    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 37 total)

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