Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 56,251 through 56,265 (of 66,712 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Brandie Tarvin (10/13/2016)
GilaMonster (10/13/2016)
Grant Fritchey (10/13/2016)
Sean Lange (10/13/2016)
WOW...http://www.sql-datatools.com/2016/05/sql-queries-tuning-and-optimization.html
This is supposed to be a list of things to do to optimize queries. While some of these are valid most of them lack context or explanation. And some of them are just plain awful. :sick:
What?
"Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship."
I can kinda see where that one comes from (though it's badly articulated). Consider:
SELECT DISTINCT CustomerName FROM Customers INNER JOIN Orders ON ... WHERE OrderStatus = 'A'
vs
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE <join> and OrderStatus = 'A')
The latter is likely to be more efficient than the former.
Okay, so it's time to learn something new here... Can you point me to a reference (because I don't want to bog down The Thread on technicalities) so I can read up on why EXISTS might be more efficient than the JOIN?

Gail's published tons of work comparing IN/EXISTS etc. subqueries on her blog.

^{“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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Brandie Tarvin (10/13/2016)
GilaMonster (10/13/2016)
Grant Fritchey (10/13/2016)
Sean Lange (10/13/2016)
WOW...http://www.sql-datatools.com/2016/05/sql-queries-tuning-and-optimization.html
This is supposed to be a list of things to do to optimize queries. While some of these are valid most of them lack context or explanation. And some of them are just plain awful. :sick:
What?
"Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship."
I can kinda see where that one comes from (though it's badly articulated). Consider:
SELECT DISTINCT CustomerName FROM Customers INNER JOIN Orders ON ... WHERE OrderStatus = 'A'
vs
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE <join> and OrderStatus = 'A')
The latter is likely to be more efficient than the former.
Okay, so it's time to learn something new here... Can you point me to a reference (because I don't want to bog down The Thread on technicalities) so I can read up on why EXISTS might be more efficient than the JOIN?

With the DISTINCT ... JOIN, you will first join each customer to potentially thousands of orders, and then the DISTINCT will dedpulicate those thousands of joined rows to just the original customer.

With EXISTS, the optimizer will do a semi join, which simply stops fetching rows after the first hit. And no need for depuplication.

Assuming that CustomerName is actually unique in the Customers table, the two queries will return the same results. If the column is not unique, then they are different and a perf comparison is moot.

(Though I never tested it, I assume that in the case of a simple query such as this the optimizer might be able to make the transformation. But I rather not rely on it, and EXISTS makes it easier for future me to understand what the query is doing and why)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

Hugo Kornelis (10/13/2016)
Brandie Tarvin (10/13/2016)
GilaMonster (10/13/2016)
Grant Fritchey (10/13/2016)
Sean Lange (10/13/2016)
WOW...http://www.sql-datatools.com/2016/05/sql-queries-tuning-and-optimization.html
This is supposed to be a list of things to do to optimize queries. While some of these are valid most of them lack context or explanation. And some of them are just plain awful. :sick:
What?
"Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship."
I can kinda see where that one comes from (though it's badly articulated). Consider:
SELECT DISTINCT CustomerName FROM Customers INNER JOIN Orders ON ... WHERE OrderStatus = 'A'
vs
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE <join> and OrderStatus = 'A')
The latter is likely to be more efficient than the former.
Okay, so it's time to learn something new here... Can you point me to a reference (because I don't want to bog down The Thread on technicalities) so I can read up on why EXISTS might be more efficient than the JOIN?
With the DISTINCT ... JOIN, you will first join each customer to potentially thousands of orders, and then the DISTINCT will dedpulicate those thousands of joined rows to just the original customer.
With EXISTS, the optimizer will do a semi join, which simply stops fetching rows after the first hit. And no need for depuplication.
Assuming that CustomerName is actually unique in the Customers table, the two queries will return the same results. If the column is not unique, then they are different and a perf comparison is moot.
(Though I never tested it, I assume that in the case of a simple query such as this the optimizer might be able to make the transformation. But I rather not rely on it, and EXISTS makes it easier for future me to understand what the query is doing and why)

Oh oh oh oh oh...

I have a query to try that on. If it only joins the first hit, wow. That could save a LOT of time...

YAY. Time to experiment!

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 4

ChrisM@Work (10/13/2016)
Brandie Tarvin (10/13/2016)
GilaMonster (10/13/2016)
Grant Fritchey (10/13/2016)
Sean Lange (10/13/2016)
WOW...http://www.sql-datatools.com/2016/05/sql-queries-tuning-and-optimization.html
This is supposed to be a list of things to do to optimize queries. While some of these are valid most of them lack context or explanation. And some of them are just plain awful. :sick:
What?
"Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship."
I can kinda see where that one comes from (though it's badly articulated). Consider:
SELECT DISTINCT CustomerName FROM Customers INNER JOIN Orders ON ... WHERE OrderStatus = 'A'
vs
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE <join> and OrderStatus = 'A')
The latter is likely to be more efficient than the former.
Okay, so it's time to learn something new here... Can you point me to a reference (because I don't want to bog down The Thread on technicalities) so I can read up on why EXISTS might be more efficient than the JOIN?
Gail's published tons of work comparing IN/EXISTS etc. subqueries on her blog.

Thanks, Chris. I'll go there next.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Brandie Tarvin (10/13/2016)
I have a query to try that on. If it only joins the first hit, wow. That could save a LOT of time...

The actual processing depends on the physical join operator used. For Nested Loops, the lower (inner) input will stop processing after the first hit. For Merge Join and Hash Match, both inputs (usually) still have to be read completely, but there are some internal shortcuts. Best of all is not producing extra rows that have to be removed later (in the case of more complex queries where the optimizer overlooks the possible simplification). The others are more obscure and less significant.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Brandie Tarvin (10/13/2016)
ChrisM@Work (10/13/2016)
Brandie Tarvin (10/13/2016)
GilaMonster (10/13/2016)
Grant Fritchey (10/13/2016)
Sean Lange (10/13/2016)
WOW...http://www.sql-datatools.com/2016/05/sql-queries-tuning-and-optimization.html
This is supposed to be a list of things to do to optimize queries. While some of these are valid most of them lack context or explanation. And some of them are just plain awful. :sick:
What?
"Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship."
I can kinda see where that one comes from (though it's badly articulated). Consider:
SELECT DISTINCT CustomerName FROM Customers INNER JOIN Orders ON ... WHERE OrderStatus = 'A'
vs
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE <join> and OrderStatus = 'A')
The latter is likely to be more efficient than the former.
Okay, so it's time to learn something new here... Can you point me to a reference (because I don't want to bog down The Thread on technicalities) so I can read up on why EXISTS might be more efficient than the JOIN?
Gail's published tons of work comparing IN/EXISTS etc. subqueries on her blog.
Thanks, Chris. I'll go there next.

Here is the specific article I think he is referring to. http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jasona.work SSC Guru Points: 50117 More actions · Answer 7

So, thinking about going down to the riverfront this weekend, they're supposed to be giving tours before the 22nd...

http://www.ussdetroitlcs7.com/[/url]

I think it'd be cool to poke around (as much as they'll let the public...)

Probably crack my head on a hatch coaming, though...

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

BLOB_EATER (10/13/2016)
Hugo Kornelis (10/13/2016)
BrainDonor (10/13/2016)
jasona.work (10/12/2016)
even 20 years ago
Even 20 years ago! Even? I was 13 years into my IT career 20 years ago - it wasn't that far back!
Last week some staff in this office were talking about the new Westworld series - "Based on some really old movie". I watched that movie in the cinema when it first came out. It was in colour too - with sound. They even gave the organist the day off.
Back to my comfy chair in the corner. I hope they've mashed my food properly this time.
Oh, cut the crap. You're not that old.
For me, "20 yeas ago" I had been in my IT career for more than half of my life already. "13 yeas" ... that's nothing, you whippersnapper!
(And now get off my lawn)
20 years I was 10....primary school, the good days!

Twenty years ago I had been working with SQL Server for 5 years.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

jasona.work (10/13/2016)
So, thinking about going down to the riverfront this weekend, they're supposed to be giving tours before the 22nd...
http://www.ussdetroitlcs7.com/[/url]
I think it'd be cool to poke around (as much as they'll let the public...)
Probably crack my head on a hatch coaming, though...

Cool. I'd love to see the engineering spaces. Touring WWII ships is exactly like being on my submarine. I'm hoping modern stuff is better.

"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

Chad Crawford SSChampion Points: 11610 More actions · Answer 10

I usually take a technical book to the Summit and have the people I meet there sign the inside cover or title page. It's my souvenir for the trip, sparks memories of the good times I had, and when Grant is rich and famous (wait...he already is!), they'll be worth something.

So... This year I don't have anything particularly in mind so I'm looking for suggestions. Anything SQL Server related you've read lately that is worth picking up?

Thanks,

Chad

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Lynn Pettis (10/13/2016)
Ed Wagner (10/13/2016)
Grant Fritchey (10/13/2016)
Hugo Kornelis (10/13/2016)
I may need to redo my reviewing work of your exec plans book after reading that page.. :w00t:
BWA-HA-HA-HA-HA!
<quiet voice>
don't you dare
</quiet voice>
My favorite:
28. Include a clustered index on the columns in the GROUP BY clause.
So if I need to run queries that group the data in two different ways, I guess my options are:
1. Drop and rebuild the clustered index before I run my query.
2. Figure out how to have two clustered indexes on one table, causing the data to be stored in two different orders in the same table.
3. Duplicate the data into enough tables to have one table for each query I need to run.
What could possibly go wrong?
Covering indexes?

Absolutely, but they're nonclustered. The tip says to include a clustered index, which you can only have one of per table. The data in the table can only be sorted in one order.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Ed Wagner (10/13/2016)
Lynn Pettis (10/13/2016)
Ed Wagner (10/13/2016)
Grant Fritchey (10/13/2016)
Hugo Kornelis (10/13/2016)
I may need to redo my reviewing work of your exec plans book after reading that page.. :w00t:
BWA-HA-HA-HA-HA!
<quiet voice>
don't you dare
</quiet voice>
My favorite:
28. Include a clustered index on the columns in the GROUP BY clause.
So if I need to run queries that group the data in two different ways, I guess my options are:
1. Drop and rebuild the clustered index before I run my query.
2. Figure out how to have two clustered indexes on one table, causing the data to be stored in two different orders in the same table.
3. Duplicate the data into enough tables to have one table for each query I need to run.
What could possibly go wrong?
Covering indexes?
Absolutely, but they're nonclustered. The tip says to include a clustered index, which you can only have one of per table. The data in the table can only be sorted in one order.

Just saying you could consider a properly configured nonclustered index a pseudo-clustered index.

jasona.work SSC Guru Points: 50117 More actions · Answer 13

Grant Fritchey (10/13/2016)
jasona.work (10/13/2016)
So, thinking about going down to the riverfront this weekend, they're supposed to be giving tours before the 22nd...
http://www.ussdetroitlcs7.com/[/url]
I think it'd be cool to poke around (as much as they'll let the public...)
Probably crack my head on a hatch coaming, though...
Cool. I'd love to see the engineering spaces. Touring WWII ships is exactly like being on my submarine. I'm hoping modern stuff is better.

Well, much as I'd love to take the tour, turns out the tours are only on Wednesday and only from 1pm to 5pm (possibly even earlier, depending on what the crew need to get done for the commissioning)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

I am really getting tired of Mr. Celko. And obviously he is losing it as he can't seem to type English sentences without stammering.

Sean Lange SSC Guru Points: 286590 More actions · Answer 15

Lynn Pettis (10/13/2016)
I am really getting tired of Mr. Celko. And obviously he is losing it as he can't seem to type English sentences without stammering.

No kidding. I get the gist of his post but the actual words written are rubbish. They sentences are so completely mangled as to make his whole post uesless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/