Obviously, 'select *' is never a good idea.

HOWEVER, I have taken a job with an org that allowed this cancer to spread.

They have huge queries, using select * as SUB-QUERIES, when the coder only needed 3 or 4 columns.

 select t.field1, t.field2, t.field3, x.field4 ...
   from table t
   left join (select * from table where .... ) x
     on x.field5 = t.field5 
   left join (select * from table where .... ) y
     on y.field6 = t.field6
   left join (select * from table where .... ) z
     on z.field7 = t.field7 

Performance on this beast is a dog.

The databases we pull from we don't own, so I don't have rights to get an estimated or actual execution plan.

Before I start rewriting these queries, is the query optimizer on the M$ SQL Server smart enough to translate the splat into just the needed columns? Or do I start targeting one query a day at lunch?

Thank you for your time and consideration.

  • 2
    \$\begingroup\$ Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Perhaps dba.stackexchange.com is more suited for your question. Unless you can provide us with the required context. \$\endgroup\$ – dfhwze Jun 14 '19 at 20:31
  • \$\begingroup\$ Harsh dude. Harsh. \$\endgroup\$ – ARLibertarian Jun 14 '19 at 20:58
  • \$\begingroup\$ Well, I did spare you a bit. I didn't downvote :) \$\endgroup\$ – dfhwze Jun 14 '19 at 20:59
  • \$\begingroup\$ Hmmm...DBAs I know don't write code. Not sure what they do.... Probably nothing. \$\endgroup\$ – ARLibertarian Jun 14 '19 at 21:13
  • \$\begingroup\$ Harsh dude. Harsh. \$\endgroup\$ – dfhwze Jun 14 '19 at 21:15

Browse other questions tagged or ask your own question.