July 13, 2010 at 8:15 pm
Grant Fritchey (7/13/2010)
CirquedeSQLeil (7/13/2010)
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?It's not 1000 rows, it's a 1000 pages and, it's a made up number[/url].
I knew about the pages and the defrag of said pages. I didn't think that it applied to creating an index as well and the number of rows. The same doc I referenced earlier has this myth in there (no indexes on tables with less than 1000 rows because the index causes more overhead blah blah blah).
But the pages reference works for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 8:19 pm
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
CirquedeSQLeil (7/13/2010)
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?It's not 1000 rows, it's a 1000 pages and, it's a made up number[/url].
I knew about the pages and the defrag of said pages. I didn't think that it applied to creating an index as well and the number of rows. The same doc I referenced earlier has this myth in there (no indexes on tables with less than 1000 rows because the index causes more overhead blah blah blah).
But the pages reference works for me.
This document sounds like it's a doozy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 8:56 pm
Grant Fritchey (7/13/2010)
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
CirquedeSQLeil (7/13/2010)
Anybody have a good source concerning the "don't index tables with less than 1000 rows" optimization technique?It's not 1000 rows, it's a 1000 pages and, it's a made up number[/url].
I knew about the pages and the defrag of said pages. I didn't think that it applied to creating an index as well and the number of rows. The same doc I referenced earlier has this myth in there (no indexes on tables with less than 1000 rows because the index causes more overhead blah blah blah).
But the pages reference works for me.
This document sounds like it's a doozy.
That's an understatement.
On the plus side, it is providing me with good fodder for a blog post.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 10:27 pm
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
This document sounds like it's a doozy.That's an understatement.
On the plus side, it is providing me with good fodder for a blog post.
I love reading those sorts of documents. You can't stop reading because you just want to know how they can top the last silly rule!
The only thing I enjoy reading more is the little signs in every workplace kitchen that tells people to clean their own dishes.
July 13, 2010 at 10:34 pm
Jim McLeod (7/13/2010)
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
This document sounds like it's a doozy.That's an understatement.
On the plus side, it is providing me with good fodder for a blog post.
I love reading those sorts of documents. You can't stop reading because you just want to know how they can top the last silly rule!
The only thing I enjoy reading more is the little signs in every workplace kitchen that tells people to clean their own dishes.
HAHA - those are funny but necessary because too many people leave their dishes for the next person to clean.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 14, 2010 at 5:34 am
CirquedeSQLeil (7/13/2010)
Jim McLeod (7/13/2010)
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
This document sounds like it's a doozy.That's an understatement.
On the plus side, it is providing me with good fodder for a blog post.
I love reading those sorts of documents. You can't stop reading because you just want to know how they can top the last silly rule!
The only thing I enjoy reading more is the little signs in every workplace kitchen that tells people to clean their own dishes.
HAHA - those are funny but necessary because too many people leave their dishes for the next person to clean.
Whoa! Hang on a second. You mean you guys aren't cleaning my dishes?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 6:22 am
Something tells me that I may have just answered a homework question :Whistling:
July 14, 2010 at 6:37 am
Gianluca Sartori (7/12/2010)
This is a very tricky requirement and I did my best to solve it, with no luck.http://www.sqlservercentral.com/Forums/FindPost950057.aspx
Can somebody suggest something better? I'm totally stuck.
Sorted 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 6:44 am
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
Just like the tired old "more than three tables in a join leads to performance problems." I think I have an involuntary spitting problem when someone rolls that one out.I actually have something similar to that in this doc. I already removed it. The premise being that the query is too complex if it has multiple joins - yak yak.
If people think that three joins is nuts, they should evaluate some of what MS CRM does in its views and procs - the yakking and convulsing will last for days.
I've always wondered about that "hard and fast" non-rule. Where I am working, we have to look up claim information, so almost always have to join at least three tables (claim/member/provider) for the simplest of queries. If you want any useful info, you're up to at least four, five or more. I wondered for a while if I was doing something wrong, but you can't get the data out if you don't join to them.
Luckily I hung out here and read enough to figure out I wasn't doing anything wrong.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 14, 2010 at 6:46 am
Grant Fritchey (7/14/2010)
CirquedeSQLeil (7/13/2010)
Jim McLeod (7/13/2010)
CirquedeSQLeil (7/13/2010)
Grant Fritchey (7/13/2010)
This document sounds like it's a doozy.That's an understatement.
On the plus side, it is providing me with good fodder for a blog post.
I love reading those sorts of documents. You can't stop reading because you just want to know how they can top the last silly rule!
The only thing I enjoy reading more is the little signs in every workplace kitchen that tells people to clean their own dishes.
HAHA - those are funny but necessary because too many people leave their dishes for the next person to clean.
Whoa! Hang on a second. You mean you guys aren't cleaning my dishes?
Ours starts "Out of respect for your fellow employees..." <-- lost me right there...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 14, 2010 at 6:47 am
Paul White NZ (7/14/2010)
Gianluca Sartori (7/12/2010)
This is a very tricky requirement and I did my best to solve it, with no luck.http://www.sqlservercentral.com/Forums/FindPost950057.aspx
Can somebody suggest something better? I'm totally stuck.
Sorted 🙂
That's a great solution, Paul!
I'm happy you took the time to sort it out. I'm sure it's going to be useful to many people.
-- Gianluca Sartori
July 14, 2010 at 6:50 am
skcadavre (7/14/2010)
Something tells me that I may have just answered a homework question :Whistling:
Not sure. It's not typical quoting object names with square brackets in homework...
I could be dead wrong, anyway.
-- Gianluca Sartori
July 14, 2010 at 6:50 am
Gianluca Sartori (7/14/2010)
I'm happy you took the time to sort it out.
It was a bit more effort that I would usually put in - but you did remember my birthday! 😀
One of those cases where there's no real T-SQL solution, at least not one that will perform anything like acceptably on large data sets.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 7:39 am
Gianluca Sartori (7/14/2010)
skcadavre (7/14/2010)
Something tells me that I may have just answered a homework question :Whistling:Not sure. It's not typical quoting object names with square brackets in homework...
I could be dead wrong, anyway.
Seems strange though, not to know how to modify a query from using a table variable to your actual table.
July 14, 2010 at 7:59 am
Paul White NZ (7/14/2010)
Gianluca Sartori (7/14/2010)
I'm happy you took the time to sort it out.It was a bit more effort that I would usually put in - but you did remember my birthday! 😀
One of those cases where there's no real T-SQL solution, at least not one that will perform anything like acceptably on large data sets.
Ah! I'll make sure to remember next year and maybe have a thread of mine waiting for an answer... 😀
-- Gianluca Sartori
Viewing 15 posts - 16,351 through 16,365 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply