SQL Server Performance

Using * in a query and the query optimiser

Discussion in 'T-SQL Performance Tuning for Developers' started by trifunk, Oct 8, 2003.

  1. trifunk New Member

    I've been working on some queries and have found that when queries have the shamefull * operator in them to retrieve all columns instead of the favoured column list, the estimated execution plan that the query optimiser puts out is different.

    eg :
    Q1 : select * from myTable

    and

    Q2 : select col1, col2, col3 from myTable

    Now, there are 219 rows in the table but the query optimiser shows an estimated rowcount of 1 for the first query and the correct estimated rowcount of 219 for the second query, the cpu and io cost also favour the first query but this must be because it's assuming it returns 1 row.

    I updated statistics and tried this again but to no avail.
    I'm running winXP professional, SqlServer 2k Standard edition (on my developer machine).

    There may be some explanation on the site but I haven't come accross it, any suggestions or pearls of wisdom would be appreciated.

    Cheers
    Shaun

    World Domination Through Superior Software
  2. satya Moderator

    On the T-Sql (tips) page this refers
    In your queries, don't return column data you don't need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column. In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance. [6.5, 7.0, 2000] Updated 2-8-2002
    ...


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. bambola New Member

    My tests showed same rowcount for the different queries. Any chance you set rowcount 1 on the connection from where you run select * and forgot? try set rowcount 0 and execute query again...

    Bambola.
  4. trifunk New Member

    Thanks Satya, but I still don't understand why the rowcount is different as there is only 1 clustered index on the table and both queries result in a clustered index seek so although the query can't benefit from covered indexes it wouldn't need to in this case anyway.

    Bambola, I tried setting rowcount to 0 but still got the same result with the one query only displaying a rowcount of 1 in the query optimiser page.

    I'm a bit stumped, I reckon I'll try it on some other machines against other databases just to make sure my machine and database aren't messed up!

    Thanks
    Shaun

    World Domination Through Superior Software

Share This Page