June 16, 2010 at 9:57 am
Trey Staker (6/16/2010)
Dave Ballantyne (6/16/2010)
Damn Damn Damn, ive mislaid my sql magic wand.http://www.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
June 16, 2010 at 10:49 am
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
June 16, 2010 at 11:54 am
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
June 16, 2010 at 12:55 pm
[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
June 16, 2010 at 1:16 pm
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
June 16, 2010 at 1:18 pm
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
June 16, 2010 at 2:57 pm
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
June 16, 2010 at 3:08 pm
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
June 16, 2010 at 3:41 pm
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
June 17, 2010 at 10:16 pm
Testing...
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
June 17, 2010 at 11:12 pm
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....
June 17, 2010 at 11:14 pm
Checked out a thread where one of my blogs was referenced, feels good when it helps someone solve a problem. 🙂
June 18, 2010 at 2:50 am
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
June 18, 2010 at 2:57 am
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
June 18, 2010 at 5:54 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 15,841 through 15,855 (of 66,688 total)
You must be logged in to reply to this topic. Login to reply