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 ««123»»

Question regarding the difference between two different ways to populate a new table in TSQL Expand / Collapse
Author
Message
Posted Saturday, August 23, 2014 4:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 2,388, Visits: 6,590
Jeff Moden (8/23/2014)
DAs don't actually need to know how to write a lick of code, never mind high performance code, to do their jobs correctly.


Hmm???


BTW +100 for the soapbox speach
Post #1606806
Posted Sunday, August 24, 2014 9:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 7,847, Visits: 9,596
Jeff Moden (8/23/2014)
There's nothing wrong with challenging a DA when it comes to performance

up to there I agree with you 100%

because, and with absolutely no malice in my heart, DAs don't actually need to know how to write a lick of code, never mind high performance code, to do their jobs correctly.

And I don't agree with that at all; a DA had better be able to produce a schema design which enables high-performance code to be written, and if the DA knows nothing about code and in particular nothing about high performance code that DA is not going to be able to do that.


Tom
Post #1606881
Posted Sunday, August 24, 2014 2:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,390, Visits: 36,823
Jeff Moden (8/23/2014)
Cathy DePaolo (8/22/2014)
I checked the execution plans of each, and they are the same. I am of the opinion that the subquery is not going to improve performance of the query, and the execution plans seem to prove me correct. I prefer the straight "select into" version because it is less code and, in my opinion, is a cleaner version.

I was wondering if anyone in the community knows why I would be told, by a database architect, that the subquery version of this query would be better performing. I was forced to change all of my queries to use the subquery version, rather than a straight select into. I was not given a reason that satisfied me, so I am hoping someone in this community can tell me why I would be directed in this manner.

Thank you for your time.


Gosh, be careful now. In this case, it works out, but the Execution plans never prove anything having to do with performance. Not even the ACTUAL execution plans.


Sorry, but I'm going to disagree with you there.

If two queries have identical execution plans, then they will (in the absence of blocking or waits) perform identically.
I don't mean same costs (costs are always estimated), I mean identical plans. If the operators are the same and the row counts are the same and the number of executions for each operator is the same, then the two queries will be executed the same way because the plan is the 'recipe' given to the execution engine.

It's a good way to tell if two different ways of writing a query are equivalent. If they produce identical plans, then they have to be logically equivalent.

I enjoy doing this to people who insist on 'subqueries in the from clause are BAD' or 'subqueries in the from clause are good' (I've seen both recently), as I can just write the queries with and without the subquery (logically equivalent ways) and then show that the execution plans are absolutely, 100%, completely identical in every way and hence there's no difference between the two queries by the time they reach the query execution engine.




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 #1606926
Posted Sunday, August 24, 2014 2:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 2,388, Visits: 6,590
TomThomson (8/24/2014)
Jeff Moden (8/23/2014)
There's nothing wrong with challenging a DA when it comes to performance

up to there I agree with you 100%

because, and with absolutely no malice in my heart, DAs don't actually need to know how to write a lick of code, never mind high performance code, to do their jobs correctly.

And I don't agree with that at all; a DA had better be able to produce a schema design which enables high-performance code to be written, and if the DA knows nothing about code and in particular nothing about high performance code that DA is not going to be able to do that.


We wouldn't want to leave the design of the internals of a building to the builders would we?
Post #1606927
Posted Sunday, August 24, 2014 10:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 13,182, Visits: 12,672
Jeff Moden (8/23/2014)
Sean Lange (8/22/2014)
There is no valid reason that one is preferred over the other.


Actually, there is ... the first code is both shorter to write and easier to read, not to mention that the original code didn't cost extra to have a change put in that didn't matter.


The "valid" reasons here are subject to personal preference (other then fewer keystrokes). I would not have changed the original because I too think that is easier to read. I certainly would never force somebody to change their code because I don't like the style in which they wrote it. Now if performance was measurably an issue that would be another story.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1606961
Posted Sunday, August 24, 2014 10:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 35,531, Visits: 32,114
TomThomson (8/24/2014)
Jeff Moden (8/23/2014)
There's nothing wrong with challenging a DA when it comes to performance

up to there I agree with you 100%

because, and with absolutely no malice in my heart, DAs don't actually need to know how to write a lick of code, never mind high performance code, to do their jobs correctly.

And I don't agree with that at all; a DA had better be able to produce a schema design which enables high-performance code to be written, and if the DA knows nothing about code and in particular nothing about high performance code that DA is not going to be able to do that.


I absolutely agree that they'd better be able to pull the proverbial rabbit out of the hat on schema design to enable high performance code to be written but, with the tools they use to do such a thing and the design patterns they follow, they don't actually need to know a thing about how to write the code.

I went through that about a year ago. A company wanted me to come in to help them write some code. They introduced me to the DA and he spent about an hour going over the schema, the table keys, and the relationships for a huge database. It was a work of art bordering on genius. I asked him why with such a great design that they needed me to write code. His answer was (and I literally nearly fell backwards in my chair) "I only know how to design the databases... I don't actually know how to write code".

As another example, I went through that at a previous company that I worked for. They built a whole new OSS in Oracle. Again, the design of the database was outstanding in every way I could think of. I'd been assigned a task that I was having problems writing code for (it was early in my experience with Oracle) so I went to the DA that designed it and starting drawing code on his white-board. He asked me to stop because he couldn't help. When I asked "Why not?", his comment was "This is a gibberish to me... I don't know how to do a 3 table join never mind what you've written so far". When I asked him how he designed the database, his reply was "The tools make it easy. I just need to fill in the right blanks and draw the right lines. It even builds the correct indexes for me".


--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 #1606963
Posted Monday, August 25, 2014 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 7,847, Visits: 9,596
Eirikur Eiriksson (8/24/2014)
TomThomson (8/24/2014)
Jeff Moden (8/23/2014)
There's nothing wrong with challenging a DA when it comes to performance

up to there I agree with you 100%

because, and with absolutely no malice in my heart, DAs don't actually need to know how to write a lick of code, never mind high performance code, to do their jobs correctly.

And I don't agree with that at all; a DA had better be able to produce a schema design which enables high-performance code to be written, and if the DA knows nothing about code and in particular nothing about high performance code that DA is not going to be able to do that.


We wouldn't want to leave the design of the internals of a building to the builders would we?

No, and neither would I want to use a coder (as opposed to a developer) to design code. But I wouldn't want to use an architect who didn't know about about joins and unions and projections and restrictions and filters and intersections and symmetric differences and so on to design a database - and if someone knows about all those things they can presumably write relational expressions in some notation, even if that notation isn't SQL or T-SQL.

edit; typos


Tom
Post #1607020
Posted Monday, August 25, 2014 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 7,847, Visits: 9,596
Jeff Moden (8/24/2014)
I absolutely agree that they'd better be able to pull the proverbial rabbit out of the hat on schema design to enable high performance code to be written but, with the tools they use to do such a thing and the design patterns they follow, they don't actually need to know a thing about how to write the code.

I went through that about a year ago. A company wanted me to come in to help them write some code. They introduced me to the DA and he spent about an hour going over the schema, the table keys, and the relationships for a huge database. It was a work of art bordering on genius. I asked him why with such a great design that they needed me to write code. His answer was (and I literally nearly fell backwards in my chair) "I only know how to design the databases... I don't actually know how to write code".

As another example, I went through that at a previous company that I worked for. They built a whole new OSS in Oracle. Again, the design of the database was outstanding in every way I could think of. I'd been assigned a task that I was having problems writing code for (it was early in my experience with Oracle) so I went to the DA that designed it and starting drawing code on his white-board. He asked me to stop because he couldn't help. When I asked "Why not?", his comment was "This is a gibberish to me... I don't know how to do a 3 table join never mind what you've written so far". When I asked him how he designed the database, his reply was "The tools make it easy. I just need to fill in the right blanks and draw the right lines. It even builds the correct indexes for me".

That sounds as if you've met some amazing data analysts - perhaps people who are using tools based on the research by people like Bernstein and Zaniolo and Beeri and Fagin and Raiha and Melkanoff and so on which I thought had been ignored by the industry (except IBM, who published some of it, but as far as I know didn't actually use it) and indeed by most of academia. I wish I'd met some of those DAs and got my hands on some of their tools.

I still reckon a good DA can probably write relational expressions in some notation though, although it may not be SQL so some people won't recognise it as code.


Tom
Post #1607040
Posted Tuesday, August 26, 2014 8:04 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:54 PM
Points: 65, Visits: 354
Thank you everyone for your responses. I obviously need to work on my confidence level and challenge our DAs when necessary. I am new to the organization and haven't felt confident enough to push back when I am directed to do something that does not make sense to me. Sounds like in this case in particular, I would have been better off pushing back a bit and requiring some supporting evidence before changing all of my code.
Post #1607642
Posted Wednesday, September 3, 2014 4:46 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 35,531, Visits: 32,114
TomThomson (8/25/2014)
Jeff Moden (8/24/2014)
I absolutely agree that they'd better be able to pull the proverbial rabbit out of the hat on schema design to enable high performance code to be written but, with the tools they use to do such a thing and the design patterns they follow, they don't actually need to know a thing about how to write the code.

I went through that about a year ago. A company wanted me to come in to help them write some code. They introduced me to the DA and he spent about an hour going over the schema, the table keys, and the relationships for a huge database. It was a work of art bordering on genius. I asked him why with such a great design that they needed me to write code. His answer was (and I literally nearly fell backwards in my chair) "I only know how to design the databases... I don't actually know how to write code".

As another example, I went through that at a previous company that I worked for. They built a whole new OSS in Oracle. Again, the design of the database was outstanding in every way I could think of. I'd been assigned a task that I was having problems writing code for (it was early in my experience with Oracle) so I went to the DA that designed it and starting drawing code on his white-board. He asked me to stop because he couldn't help. When I asked "Why not?", his comment was "This is a gibberish to me... I don't know how to do a 3 table join never mind what you've written so far". When I asked him how he designed the database, his reply was "The tools make it easy. I just need to fill in the right blanks and draw the right lines. It even builds the correct indexes for me".

That sounds as if you've met some amazing data analysts - perhaps people who are using tools based on the research by people like Bernstein and Zaniolo and Beeri and Fagin and Raiha and Melkanoff and so on which I thought had been ignored by the industry (except IBM, who published some of it, but as far as I know didn't actually use it) and indeed by most of academia. I wish I'd met some of those DAs and got my hands on some of their tools.

I still reckon a good DA can probably write relational expressions in some notation though, although it may not be SQL so some people won't recognise it as code.


Indeed, the were amazing, IMHO. Still, it would have been nice if they understood the T-SQL language a bit more but it was a happy sacrifice to have a really well built database for a change.


--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 #1610316
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse