I had to review a stored procedure to figure out why it was causing the web application to timeout when being passed certain parameters. The web application was using this one stored procedure to not only show a search results page (with 9 columns) but also to show the detail for a particular row (showing all 39 columns in a separate window). The stored procedure was using a CTE to handle/enable paging. Reviewing the code showed at the end of the CTE, the code had the following:
SELECT * FROM CTE
This version of the code would take up to 4 minutes to display a result based upon the parameters used.
The fix was to change
SELECT *
to
SELECT ColumnName
and this improved the performance dramatically. Where the original code took up to 4 minutes to display a result, this change had the results display instantly (well, under a second).
The lesson, only use
SELECT * FROM OBJECT
when absolutely necessary (and when is that?).