Are the posted questions getting worse?

Question

Post reply

Are the posted questions getting worse?

Viewing 15 posts - 15,841 through 15,855 (of 66,000 total)

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

Trey Staker (6/16/2010)

Dave Ballantyne (6/16/2010)

Damn Damn Damn, ive mislaid my sql magic wand.

http://qa.sqlservercentral.com/Forums/FindPost937995.aspx

Perhaps if i shouted at the server .....

skcadavre's comment on that thread cracked me up to tears.

It's quite funny, but it made me think.

Last week I attended an Oracle administration course at "Oracle University" in Rome. The BI department is rolling out a DWH on a relational Oracle database (no OLAP cubes) and they have performance issues, so they sent me to the course to raise my knowledge and hopefully help them tune the database.

The front-end tool is a query generator that, reading the DWH metadata, produces huge SQL statements to aggregate the data. So, basically, no way to modify the statements.

Long story short, I asked the teacher how to start tuning in this scenario, knowing that in general 80% of performance problems are due to poor SQL code and I can't modify the SQL itself.

I was a bit shocked by his reply: Oracle recommends tuning the database and the server hardware, not the SQL statements. They say that the Oracle optimizer is so smart that it can produce the best query plan from ANY equivalent SQL syntax.

Obviously it isn't so. These two statements produce the same results, but very different query plans. Even in Oracle.



SELECT MAX(SomeColumn)

FROM SomeTable

SELECT SomeColumn

FROM SomeTable AS A

WHERE NOT EXISTS (

	SELECT 1

	FROM SomeTable AS B

	WHERE B.SomeColumn > A.SomeColumn

)

So, is that scenario so atypical?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

To start with, because they are not equivalent queries.

CREATE TABLE #SomeTable (



	SomeColumn INT

)

INSERT INTO #SomeTable (SomeColumn)

VALUES  (1), (2), (2)

GO

SELECT MAX(SomeColumn)

FROM #SomeTable

SELECT SomeColumn

FROM #SomeTable AS A

WHERE NOT EXISTS (

        SELECT 1

        FROM #SomeTable AS B

        WHERE B.SomeColumn > A.SomeColumn

)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95405 More actions · Answer 3

GabyYYZ (6/16/2010)

Can you imagine the quality and amount of SQL Training I could have for $1 billion? 😀

Sorry Gaby, but I just have to ask... would that be enough? :w00t::-P;-)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 4

[The front-end tool is a query generator that, reading the DWH metadata, produces huge SQL statements to aggregate the data. So, basically, no way to modify the statements.

Long story short, I asked the teacher how to start tuning in this scenario, knowing that in general 80% of performance problems are due to poor SQL code and I can't modify the SQL itself.

I was a bit shocked by his reply: Oracle recommends tuning the database and the server hardware, not the SQL statements. They say that the Oracle optimizer is so smart that it can produce the best query plan from ANY equivalent SQL syntax.

Hardware, and the ability to move massive volumes of data, can produce incredible performance.

Although I've never worked in an environment where $$ didn't come into play.

Cube technology fits in our budget.

Greg E

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

WayneS (6/16/2010)

GabyYYZ (6/16/2010)

Can you imagine the quality and amount of SQL Training I could have for $1 billion? 😀

Sorry Gaby, but I just have to ask... would that be enough? :w00t::-P;-)

I can build a house out of that kind of money.

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

Greg Edwards-268690 (6/16/2010)

Hardware, and the ability to move massive volumes of data, can produce incredible performance.

Yes it can. I am finishing up a SQL blitz right now. Moved 6 clusters with a few more to go. Our warehouse used to take 8 hours to backup. Now it is done in 30 minutes. ETL processes run about 15 times faster as well. A world of difference these days.

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 7

GilaMonster (6/16/2010)

To start with, because they are not equivalent queries.

-- some code here

Oh, don't be too picky, Gail! You know what I mean.

BTW, they can be equivalent. Just change your table and set SomeColumn as primary key.

So, if buying new hardware is the future for DBAs, next time I'll ask for a course on how to plug the chord to the power supply. 😀

-- Gianluca Sartori

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 8

CirquedeSQLeil (6/16/2010)

Greg Edwards-268690 (6/16/2010)

Hardware, and the ability to move massive volumes of data, can produce incredible performance.

Yes it can. I am finishing up a SQL blitz right now. Moved 6 clusters with a few more to go. Our warehouse used to take 8 hours to backup. Now it is done in 30 minutes. ETL processes run about 15 times faster as well. A world of difference these days.

About how many GB, if I might ask?

It will be likely several years before I a SAN dedicated to our SQL server.

So for now, I just dream.

Greg E

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Gianluca Sartori (6/16/2010)

Oh, don't be too picky, Gail! You know what I mean

Sorry, habit from too many inane questions posted around here.

The optimiser can't identify all equivalent query patterns. Also bear in mind that in general planning is a hard problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 10

The Dixie Flatline

SSC Guru

Points: 53353

June 17, 2010 at 10:16 pm

#1181902

Testing...

Is this Thread on?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 11

Lynn Pettis

SSC Guru

Points: 442447

June 17, 2010 at 11:12 pm

#1181915

Don't you love it when some one PM's you for help? Just deciding if I should help, post it for others as well, or just ignore it. Decisions, decisions, decisions....

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

Lynn Pettis

SSC Guru

Points: 442447

June 17, 2010 at 11:14 pm

#1181916

Checked out a thread where one of my blogs was referenced, feels good when it helps someone solve a problem. 🙂

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

Lynn Pettis (6/17/2010)

Don't you love it when some one PM's you for help? Just deciding if I should help, post it for others as well, or just ignore it. Decisions, decisions, decisions....

My standard reply to that is 'please post in the forums'. Two problems I have with helping via PM. No one else can help out. No one else can benefit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105712 More actions · Answer 14

GilaMonster (6/18/2010)

Lynn Pettis (6/17/2010)

Don't you love it when some one PM's you for help? Just deciding if I should help, post it for others as well, or just ignore it. Decisions, decisions, decisions....

My standard reply to that is 'please post in the forums'. Two problems I have with helping via PM. No one else can help out. No one else can benefit.

Ditto. I usually start a new thread quoting the PM and send the link to the OP.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 15

GilaMonster (6/18/2010)

Lynn Pettis (6/17/2010)

Don't you love it when some one PM's you for help? Just deciding if I should help, post it for others as well, or just ignore it. Decisions, decisions, decisions....

My standard reply to that is 'please post in the forums'. Two problems I have with helping via PM. No one else can help out. No one else can benefit.

Me too. My one exception is when they're asking about something I wrote.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software