Getting rid of SELECT *

Question

Getting rid of SELECT *

Emil B

SSCertifiable

Points: 5568
More actions
September 29, 2015 at 10:55 am

#311921

Hi,
I've started not long ago in a company where I've notice that SELECT * is a common practice used by developers in stored procedures. From what i understand They are using it to be able to reuse resultset from a stored procedure in many places, Lets say on 1 site they are using 4 columns out of 30, other 5 out of 30 etc. I have a very good understanding of what impact it have on the performance, index usage (or not usage), missing index analysis... so on... Wouldn't it be better to have at least separate stored procedure to use in all those different places but only returning columns that they actually need or maybe replace it with dynamic SQL? Give me some bullets here so i can present to management and "make a change".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

I did some work for a company that used SELECT *.

Their telesales department used the browser base APP extensively and the same DB was used by their end users across the web. Lots of concurrent users.

The telesales people asked for a "Notes" column on several tables, so that they could do some sort of workflow within telesales.

DEVs added varchar(MAX) to several tables. Telesales thought this was "Brill" and used it extensively 😉

Within a few weeks performance had slowed to a crawl, internet customers leaving in droves, huge number of end user complains wasting Support's time, revenue down ...

When I explained that SELECT * was the problem and they shouldn't do that they made some tests (which cured the performance problem) and then set about a months work fixing all the SELECT * statements throughout the APP.

So if you don't have a performance problem yet then you could easily in the future, at which time the fix would be very expensive to retro fit. But I expect that you do already have a performance issue (in terms of elapsed network time to transmit the data) particularly on any tables that have large text columns in them.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Wouldn't it be better to have at least separate stored procedure to use in all those different places but only returning columns that they actually need or maybe replace it with dynamic SQL?

Yep.

Give me some bullets here so i can present to management and "make a change".

Here's a good thread on this topic: you'll find plenty of amo (literally:-P).

http://www.sqlservercentral.com/Forums/Topic1685114-3412-1.aspx

Going back to your idea about separate stored procs. Do it, check the results, if it's faster bring it to your bosses. An effective bullet point is, "Doing it this way the query is able to use this nonclustered index and the query is now 3 times faster. See..."

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Emil B SSCertifiable Points: 5568 More actions · Answer 3

Alan.B (9/29/2015)
Wouldn't it be better to have at least separate stored procedure to use in all those different places but only returning columns that they actually need or maybe replace it with dynamic SQL?
Yep.
Give me some bullets here so i can present to management and "make a change".
Here's a good thread on this topic: you'll find plenty of amo (literally:-P).
http://www.sqlservercentral.com/Forums/Topic1685114-3412-1.aspx
Going back to your idea about separate stored procs. Do it, check the results, if it's faster bring it to your bosses. An effective bullet point is, "Doing it this way the query is able to use this nonclustered index and the query is now 3 times faster. See..."

Hah! Thanks Alan, i knew i saw that thread somewhere but i couldn't find it, that's the kind of ammunition i needed!!

Emil B SSCertifiable Points: 5568 More actions · Answer 4

Kristen-173977 (9/29/2015)
...
So if you don't have a performance problem yet then you could easily in the future, at which time the fix would be very expensive to retro fit. But I expect that you do already have a performance issue (in terms of elapsed network time to transmit the data) particularly on any tables that have large text columns in them...

There is no very noticeable performance impact but you can notice the effects of * when you do index usage and unused index analysis. I'm aware that it's only a matter of time, that's why i want to act straight away and prevent some firefighting in the future.

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 5

just dont use Select * is my advice.. its lazy and causes issues in multiple ways