|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT * vs SELECT col1, col2... colN*All other things being equal*... just wondering if there is any performance
difference [really, if SQL Server has to do less work] between: SELECT * and SELECT col1, col2, col3.... I'm wondering because I have a SELECT statement in a sp that returns all but one of the columns from a single table. The table has about 30 columns, and the consuming application does *not* care about the order in which columns are returned. Thanks! I believe SQL Server still has to read the entire page if it's one column or
100. However, if it's a large number of rows, then consider memory and bandwidth. Show quote "Verde" <VFuen***@BeansAndTacos.net> wrote in message news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... > *All other things being equal*... just wondering if there is any > performance difference [really, if SQL Server has to do less work] > between: > SELECT * > and > SELECT col1, col2, col3.... > > > I'm wondering because I have a SELECT statement in a sp that returns all > but one of the columns from a single table. The table has about 30 > columns, and the consuming application does *not* care about the order in > which columns are returned. > > Thanks! > There is a performance difference because more data will be sent to the
client. Even if that were the case, you should never use SELECT * in production code. NEVER!!! There are many reasons, not only the order of the columns. Suffice it to say that using it WILL bite you in the backside. Show quote "Verde" <VFuen***@BeansAndTacos.net> wrote in message news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... > *All other things being equal*... just wondering if there is any performance > difference [really, if SQL Server has to do less work] between: > SELECT * > and > SELECT col1, col2, col3.... > > > I'm wondering because I have a SELECT statement in a sp that returns all but > one of the columns from a single table. The table has about 30 columns, and > the consuming application does *not* care about the order in which columns > are returned. > > Thanks! > > poppycock!
Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:uGwZ3MuuFHA.2504@tk2msftngp13.phx.gbl... > There is a performance difference because more data will be sent to the > client. Even if that were the case, you should never use SELECT * in > production code. NEVER!!! There are many reasons, not only the order of > the columns. Suffice it to say that using it WILL bite you in the > backside. > > "Verde" <VFuen***@BeansAndTacos.net> wrote in message > news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... >> *All other things being equal*... just wondering if there is any > performance >> difference [really, if SQL Server has to do less work] between: >> SELECT * >> and >> SELECT col1, col2, col3.... >> >> >> I'm wondering because I have a SELECT statement in a sp that returns all > but >> one of the columns from a single table. The table has about 30 columns, > and >> the consuming application does *not* care about the order in which >> columns >> are returned. >> >> Thanks! >> >> > > "JT" <some***@microsoft.com> wrote in message Don't forget Fiddle Faddle!news:Oqw2rEvuFHA.1032@TK2MSFTNGP12.phx.gbl... > poppycock! > http://www.lincolnsnacks.com/about.asp Seriously though, using SELECT * in production code is a practice that is more trouble than it is worth. Some developers will use this in their View definitions, think that if they change the underlying table schema, the view will "automagically" show the changed layout. The do not realise that the column list is set for the view when the view is created (or altered), then they cannot figure out why their new columns are not showing up in the view. This can also create problems in procedures. For example: ------------------------------- CREATE TABLE TestColumnAdd (RowID int PRIMARY KEY, RowText varchar(20)); INSERT INTO TestColumnAdd (RowID, RowText) VALUES (1, 'This is a test.'); INSERT INTO TestColumnAdd (RowID, RowText) VALUES (2, 'This is only a test.'); INSERT INTO TestColumnAdd (RowID, RowText) VALUES (3, 'Testing.'); GO CREATE PROCEDURE TestColumnAddProc AS DECLARE @TestTable TABLE (RowID int, RowText varchar(20)) INSERT INTO @TestTable SELECT * FROM TestColumnAdd --Add other procedure logic GO EXEC TestColumnAddProc --Works fine... ALTER TABLE TestColumnAdd ADD RowValue int; EXEC TestColumnAddProc --Doesn't work now... ------------------------------- The second call to the procedure (after changing the base table schema) fails, of course, because the schema for the table variable is different from the schema of the table being selected from. This may be the desired effect, but it is also possible that the added column really has no bearing on the functionality of the procedure, and changing the table schema should not require the procedure to be modified. "SELECT *" does not even offer a measurable benefit for coding productivity. If it is just a matter of saving a few keystrokes once in a while, simply use a tool like Query Analyzer to drag the column list from the Object Browser to the text area - you can drag all the column names to the work area in one motion! In fact, I cannot think of a single instance where using "SELECT *" offers an advantage over explicitly listing the columns out. However, using "SELECT *" definitely has disadvantages in a number of cases. Show quote > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:uGwZ3MuuFHA.2504@tk2msftngp13.phx.gbl... >> There is a performance difference because more data will be sent to the >> client. Even if that were the case, you should never use SELECT * in >> production code. NEVER!!! There are many reasons, not only the order of >> the columns. Suffice it to say that using it WILL bite you in the >> backside. >> >> "Verde" <VFuen***@BeansAndTacos.net> wrote in message >> news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... >>> *All other things being equal*... just wondering if there is any >> performance >>> difference [really, if SQL Server has to do less work] between: >>> SELECT * >>> and >>> SELECT col1, col2, col3.... >>> >>> >>> I'm wondering because I have a SELECT statement in a sp that returns all >> but >>> one of the columns from a single table. The table has about 30 columns, >> and >>> the consuming application does *not* care about the order in which >>> columns >>> are returned. >>> >>> Thanks! >>> >>> >> >> > > Here are a few additional problems you can encounter: SELECT * can cause
problems if you use column-level security. Transact-SQL cursors defined with SELECT * will fail because there aren't enough variables to fetch into. ROWGUID columns may need to be added for replication or to enable set-based updates within triggers in the absence of an immutable primary key, and any schema change will reduce scalability by increasing the memory and network bandwidth required to satisfy queries containing SELECT *. Also, if an index exists that covers the columns that are actually required to satisfy the business need, the query analyzer must choose between using a scan and sort--ignoring the covering index--or adding a bookmark lookup to obtain all of the unnecessary columns, and both of these will dramatically decrease performance. Show quote "Jeremy Williams" <jeremydw***@netscape.net> wrote in message news:OkDQjyvuFHA.664@tk2msftngp13.phx.gbl... > "JT" <some***@microsoft.com> wrote in message > news:Oqw2rEvuFHA.1032@TK2MSFTNGP12.phx.gbl... > > poppycock! > > > > Don't forget Fiddle Faddle! > http://www.lincolnsnacks.com/about.asp > > Seriously though, using SELECT * in production code is a practice that is > more trouble than it is worth. > > Some developers will use this in their View definitions, think that if they > change the underlying table schema, the view will "automagically" show the > changed layout. The do not realise that the column list is set for the view > when the view is created (or altered), then they cannot figure out why their > new columns are not showing up in the view. > > This can also create problems in procedures. For example: > ------------------------------- > CREATE TABLE TestColumnAdd (RowID int PRIMARY KEY, RowText varchar(20)); > > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (1, 'This is a test.'); > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (2, 'This is only a > test.'); > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (3, 'Testing.'); > > GO > > CREATE PROCEDURE TestColumnAddProc AS > > DECLARE @TestTable TABLE (RowID int, RowText varchar(20)) > > INSERT INTO @TestTable > SELECT * FROM TestColumnAdd > > --Add other procedure logic > > GO > > EXEC TestColumnAddProc --Works fine... > > ALTER TABLE TestColumnAdd ADD RowValue int; > > EXEC TestColumnAddProc --Doesn't work now... > > ------------------------------- > The second call to the procedure (after changing the base table schema) > fails, of course, because the schema for the table variable is different > from the schema of the table being selected from. This may be the desired > effect, but it is also possible that the added column really has no bearing > on the functionality of the procedure, and changing the table schema should > not require the procedure to be modified. > > "SELECT *" does not even offer a measurable benefit for coding productivity. > If it is just a matter of saving a few keystrokes once in a while, simply > use a tool like Query Analyzer to drag the column list from the Object > Browser to the text area - you can drag all the column names to the work > area in one motion! > > In fact, I cannot think of a single instance where using "SELECT *" offers > an advantage over explicitly listing the columns out. However, using "SELECT > *" definitely has disadvantages in a number of cases. > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > > news:uGwZ3MuuFHA.2504@tk2msftngp13.phx.gbl... > >> There is a performance difference because more data will be sent to the > >> client. Even if that were the case, you should never use SELECT * in > >> production code. NEVER!!! There are many reasons, not only the order of > >> the columns. Suffice it to say that using it WILL bite you in the > >> backside. > >> > >> "Verde" <VFuen***@BeansAndTacos.net> wrote in message > >> news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... > >>> *All other things being equal*... just wondering if there is any > >> performance > >>> difference [really, if SQL Server has to do less work] between: > >>> SELECT * > >>> and > >>> SELECT col1, col2, col3.... > >>> > >>> > >>> I'm wondering because I have a SELECT statement in a sp that returns all > >> but > >>> one of the columns from a single table. The table has about 30 columns, > >> and > >>> the consuming application does *not* care about the order in which > >>> columns > >>> are returned. > >>> > >>> Thanks! > >>> > >>> > >> > >> > > > > > > OOPS, I meant query optimizer, not analyzer.
Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:#Dnf6GwuFHA.728@TK2MSFTNGP10.phx.gbl... > Here are a few additional problems you can encounter: SELECT * can cause > problems if you use column-level security. Transact-SQL cursors defined > with SELECT * will fail because there aren't enough variables to fetch into. > ROWGUID columns may need to be added for replication or to enable set-based > updates within triggers in the absence of an immutable primary key, and any > schema change will reduce scalability by increasing the memory and network > bandwidth required to satisfy queries containing SELECT *. Also, if an > index exists that covers the columns that are actually required to satisfy > the business need, the query analyzer must choose between using a scan and > sort--ignoring the covering index--or adding a bookmark lookup to obtain all > of the unnecessary columns, and both of these will dramatically decrease > performance. > > "Jeremy Williams" <jeremydw***@netscape.net> wrote in message > news:OkDQjyvuFHA.664@tk2msftngp13.phx.gbl... > > "JT" <some***@microsoft.com> wrote in message > > news:Oqw2rEvuFHA.1032@TK2MSFTNGP12.phx.gbl... > > > poppycock! > > > > > > > Don't forget Fiddle Faddle! > > http://www.lincolnsnacks.com/about.asp > > > > Seriously though, using SELECT * in production code is a practice that is > > more trouble than it is worth. > > > > Some developers will use this in their View definitions, think that if > they > > change the underlying table schema, the view will "automagically" show the > > changed layout. The do not realise that the column list is set for the > view > > when the view is created (or altered), then they cannot figure out why > their > > new columns are not showing up in the view. > > > > This can also create problems in procedures. For example: > > ------------------------------- > > CREATE TABLE TestColumnAdd (RowID int PRIMARY KEY, RowText varchar(20)); > > > > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (1, 'This is a test.'); > > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (2, 'This is only a > > test.'); > > INSERT INTO TestColumnAdd (RowID, RowText) VALUES (3, 'Testing.'); > > > > GO > > > > CREATE PROCEDURE TestColumnAddProc AS > > > > DECLARE @TestTable TABLE (RowID int, RowText varchar(20)) > > > > INSERT INTO @TestTable > > SELECT * FROM TestColumnAdd > > > > --Add other procedure logic > > > > GO > > > > EXEC TestColumnAddProc --Works fine... > > > > ALTER TABLE TestColumnAdd ADD RowValue int; > > > > EXEC TestColumnAddProc --Doesn't work now... > > > > ------------------------------- > > The second call to the procedure (after changing the base table schema) > > fails, of course, because the schema for the table variable is different > > from the schema of the table being selected from. This may be the desired > > effect, but it is also possible that the added column really has no > bearing > > on the functionality of the procedure, and changing the table schema > should > > not require the procedure to be modified. > > > > "SELECT *" does not even offer a measurable benefit for coding > productivity. > > If it is just a matter of saving a few keystrokes once in a while, simply > > use a tool like Query Analyzer to drag the column list from the Object > > Browser to the text area - you can drag all the column names to the work > > area in one motion! > > > > In fact, I cannot think of a single instance where using "SELECT *" offers > > an advantage over explicitly listing the columns out. However, using > "SELECT > > *" definitely has disadvantages in a number of cases. > > > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > > > news:uGwZ3MuuFHA.2504@tk2msftngp13.phx.gbl... > > >> There is a performance difference because more data will be sent to the > > >> client. Even if that were the case, you should never use SELECT * in > > >> production code. NEVER!!! There are many reasons, not only the order > of > > >> the columns. Suffice it to say that using it WILL bite you in the > > >> backside. > > >> > > >> "Verde" <VFuen***@BeansAndTacos.net> wrote in message > > >> news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... > > >>> *All other things being equal*... just wondering if there is any > > >> performance > > >>> difference [really, if SQL Server has to do less work] between: > > >>> SELECT * > > >>> and > > >>> SELECT col1, col2, col3.... > > >>> > > >>> > > >>> I'm wondering because I have a SELECT statement in a sp that returns > all > > >> but > > >>> one of the columns from a single table. The table has about 30 > columns, > > >> and > > >>> the consuming application does *not* care about the order in which > > >>> columns > > >>> are returned. > > >>> > > >>> Thanks! > > >>> > > >>> > > >> > > >> > > > > > > > > > > > > Is that the best you can come up with?
Show quote "JT" <some***@microsoft.com> wrote in message news:Oqw2rEvuFHA.1032@TK2MSFTNGP12.phx.gbl... > poppycock! > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:uGwZ3MuuFHA.2504@tk2msftngp13.phx.gbl... > > There is a performance difference because more data will be sent to the > > client. Even if that were the case, you should never use SELECT * in > > production code. NEVER!!! There are many reasons, not only the order of > > the columns. Suffice it to say that using it WILL bite you in the > > backside. > > > > "Verde" <VFuen***@BeansAndTacos.net> wrote in message > > news:OCZoNEuuFHA.2064@TK2MSFTNGP09.phx.gbl... > >> *All other things being equal*... just wondering if there is any > > performance > >> difference [really, if SQL Server has to do less work] between: > >> SELECT * > >> and > >> SELECT col1, col2, col3.... > >> > >> > >> I'm wondering because I have a SELECT statement in a sp that returns all > > but > >> one of the columns from a single table. The table has about 30 columns, > > and > >> the consuming application does *not* care about the order in which > >> columns > >> are returned. > >> > >> Thanks! > >> > >> > > > > > > Thanks - you're the only one who answered the actual question I asked...
This was a question specifically about column inclusion - NOT row inclusion or exclusion -V Show quote "Anith Sen" <an***@bizdatasolutions.com> wrote in message news:%231Rj7OuuFHA.3548@tk2msftngp13.phx.gbl... > Aaron has it capsuled here: www.aspfaq.com/2096 > > -- > Anith > Verde wrote:
> *All other things being equal*... just wondering if there is any Using "SELECT *" is considered bad practice for production systems. It's > performance difference [really, if SQL Server has to do less work] > between: SELECT * > and > SELECT col1, col2, col3.... > > > I'm wondering because I have a SELECT statement in a sp that returns > all but one of the columns from a single table. The table has about > 30 columns, and the consuming application does *not* care about the > order in which columns are returned. > > Thanks! always better to spell out the columns that are required for a given query. I suppose if you were using embedded SQL, and generally required all columns, you could make the claim that using "SELECT *" sends less data over the network, but I would still argue against its use - you should be using stored procedures anyway. There are a number of reasons to not use SELECT *: 1- Keeps the data collected to a minimum, which reduces network traffic 2- Keeps the data collected to a minimum, which can speed query execution by reducing bookmark lookups in some cases and just not reading all columns in others (covering indexes) 3- Keeps the data collected to a minimum, which reduces overall execution time and blocking problems 4- Keeps developers from using SELECT * for all queries (the lazy factor) 5- Makes queries more readable Here are some stats: SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM [Northwind].[dbo].[Orders] GO SELECT * FROM [Northwind].[dbo].[Orders] GO - both generate the same execution plan (not surprising given the lack of a WHERE clause) - Duration is about 25% faster on the SELECT with columns (avg of 46ms vs 36ms) probably due to internal system table access to get the list of columns. So you could make the claim that you'll see about a 10ms increase in duration for each query if no columns are specified. Just a very simple example. Other queries could see much improved performance.  
Other interesting topics
|
|||||||||||||||||||||||
