May 11, 2011 at 7:23 am
One of the big optimization tips when working with an Access database backend is to explicity state the field names to be returned in a query, as opposed to using the asterick.
Is this also true when working with SQLServer? Is it more efficient to use the astrick or to state the column names?
I'm asking the question withing the context of working with views where the columns are limited to begin with.
May 11, 2011 at 7:26 am
The real tuning is to NOT returned columns you don't need.
Select * or select 100 column names won't change much on sql server. If there's a difference it's measured in nanoseconds.
May 11, 2011 at 7:28 am
Right because for ever column you return that you don't need SQLServer is doing unneccessary work.
May 11, 2011 at 7:32 am
david.holley (5/11/2011)
Right because for ever column you return that you don't need SQLServer is doing unneccessary work.
And potentially ignoring indexes it could otherwise have used.
The other reason for not using select * is future-proofing. If, in a month time, someone adds a varbinary(max) column containing an image to the table (or view), and you're using select *, suddenly your app is getting massive amounts of data is doesn't need.
With a view, someone alters the view and changes column order. With select * suddenly you're getting columns in a different order to what you expected. Maybe bad, maybe not, depends on how the app is coded. Use column names and it's not even a concern.
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
May 11, 2011 at 7:41 am
The habit of using column (field) names is very ingrained into me. Using * honestly feels bad at this point. I was just curious not being entirely familary with SQLServer optimization.
I'm developing an ASP.NET app so its a SELECT statements on top of views for now. I'll probably migrate to using views specific to the asp pages as in vw_CustomerRecords_OpenOrders.ASPX to tie the view to the page that it serves.
May 11, 2011 at 7:44 am
david.holley (5/11/2011)
The habit of using column (field) names is very ingrained into me. Using * honestly feels bad at this point. I was just curious not being entirely familary with SQLServer optimization.I'm developing an ASP.NET app so its a SELECT statements on top of views for now. I'll probably migrate to using views specific to the asp pages as in vw_CustomerRecords_OpenOrders.ASPX to tie the view to the page that it serves.
Like Gail said, the real problem is the query execution itself. The server will always have to do a table scan (well, too often anyways). And you won't have any chance to do further index optimization.
Figuring out the 10 columns instead of * is an insignificant hit.... if there's one.
May 11, 2011 at 8:07 am
Is there any significant cost to having two views with more or less the same columns with just a few differences between the two?
May 11, 2011 at 8:11 am
No. views are just saved select statements.
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
May 11, 2011 at 8:11 am
The size of the code of the view in bytes in the system table of the db.
So NO :-).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply