Dynamically Query a 100 Million Row Table-Efficiently

Question

Post reply

Dynamically Query a 100 Million Row Table-Efficiently

gary.strange-sqlconsumer

SSCommitted

Points: 1816
More actions
February 23, 2015 at 10:18 pm

#315671

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

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

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 1

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: 186127 More actions · Answer 2

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

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: 1816 More actions · Answer 4

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: 86978 More actions · Answer 5

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 More actions · Answer 6

h.tobisch

SSCommitted

Points: 1671

February 24, 2015 at 2:16 am

#1779508

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

adam.everett SSC Veteran Points: 265 More actions · Answer 7

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: 1816 More actions · Answer 8

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: 1816 More actions · Answer 9

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: 1816 More actions · Answer 10

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: 186127 More actions · Answer 11

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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.