SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question regarding the difference between two different ways to populate a new table in TSQL


Question regarding the difference between two different ways to populate a new table in TSQL

Author
Message
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39493 Visits: 19437
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???
Cool

BTW +100 for the soapbox speach
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25525 Visits: 12494
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217619 Visits: 46278
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, 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


Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39493 Visits: 19437
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?
Cool
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60775 Visits: 17954
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 :-D ... 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 Modens 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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209085 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25525 Visits: 12494
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?
Cool

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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25525 Visits: 12494
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

dbDiva
dbDiva
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 365
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209085 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search