Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««4,4124,4134,4144,4154,416»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Friday, June 6, 2014 8:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
Koen Verbeeck (6/6/2014)
I think I have finally met the "exception on the rule" for SELECT *.

Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.

I am not kidding.

I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.


I have seen the same behavior in very wide denormalized tables in SQL Server. I wrote a blog post about it.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1578343
Posted Friday, June 6, 2014 3:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 590, Visits: 6,754
Impromptu announcement-of-not-too-much-note time, I suppose!

Decided to switch my posting name from one of my usual online aliases to my own name. Read Brad McGehee's book on becoming an exceptional DBA, and online branding was a pretty healthy focus.

I figured it couldn't hurt, and I registered for the site under my pseudonym since I figured I wouldn't get too involved with it. Well, that changed a decent bit .

So, err, I guess this is part where I should make the remark that I'm Andrew, and I'm a SQLHolic .

Well, back to the regularly scheduled activity of The Thread




-
Post #1578525
Posted Friday, June 6, 2014 3:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,269, Visits: 31,762
Andrew Kernodle (6/6/2014)
So, err, I guess this is part where I should make the remark that I'm Andrew, and I'm a SQLHolic .


HI ANDREW!!!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1578533
Posted Friday, June 6, 2014 3:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 3,636, Visits: 8,153
Hi Andrew!
Could you remind me your previous username? My ADHD won't let me remember.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1578537
Posted Friday, June 6, 2014 4:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 590, Visits: 6,754
Oh great, now I look at my previous post and I notice I didn't even mention that part . I'm the poster previously known as hisakimatama! Clearly my faulty memory is a result of me ingesting too much SQLhol in the last hour



-
Post #1578541
Posted Sunday, June 8, 2014 7:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
Okay, I really need to step away from this person:

Welsh Corgi (6/8/2014)
Lynn Pettis (6/7/2014)
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

Thanks a bunch!


Divide twice by 1024.0.


Syntax please?


< RANT >

Really, you need someone to actually spell this out for you?

Joined: 2/18/2005 10:38:00 AM (4,213 visits since) and still can't do simple queries??

You have been working with SQL Server for over 9 years, this should be second nature by now.

< /RANT >



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1578650
Posted Sunday, June 8, 2014 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 13,046, Visits: 10,812
Lynn Pettis (6/8/2014)
Okay, I really need to step away from this person:

Welsh Corgi (6/8/2014)
Lynn Pettis (6/7/2014)
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

Thanks a bunch!


Divide twice by 1024.0.


Syntax please?


< RANT >

Really, you need someone to actually spell this out for you?

Joined: 2/18/2005 10:38:00 AM (4,213 visits since) and still can't do simple queries??

You have been working with SQL Server for over 9 years, this should be second nature by now.

< /RANT >


Wut? This seems inbelievable.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1578683
Posted Monday, June 9, 2014 2:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 39,984, Visits: 36,352
Koen Verbeeck (6/8/2014)
Wut? This seems inbelievable.


Not the first time I've seen that kind of thing from that person.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1578723
Posted Monday, June 9, 2014 5:31 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 4,056, Visits: 3,493
I saw that exact post over the weekend and had to look at it twice to make sure I wasn't missing something. I too was in disbelief.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1578768
Posted Monday, June 9, 2014 5:48 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:39 PM
Points: 670, Visits: 6,720
Koen Verbeeck (6/8/2014)
Lynn Pettis (6/8/2014)
Okay, I really need to step away from this person:

Welsh Corgi (6/8/2014)
Lynn Pettis (6/7/2014)
Welsh Corgi (6/7/2014)
That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

Thanks a bunch!


Divide twice by 1024.0.


Syntax please?


< RANT >

Really, you need someone to actually spell this out for you?

Joined: 2/18/2005 10:38:00 AM (4,213 visits since) and still can't do simple queries??

You have been working with SQL Server for over 9 years, this should be second nature by now.

< /RANT >


Wut? This seems inbelievable.


Which one - Lynn staying away, or the asking for clarification?
Both are a bit surprising, although not exactly shocking.
Sometimes a quick glance and quick reply leads to some interesting online exchanges.

Post #1578774
« Prev Topic | Next Topic »

Add to briefcase «««4,4124,4134,4144,4154,416»»»

Permissions Expand / Collapse