Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Query View slower when specifying column list vs *

Author  Topic  jackmang Starting Member 1 Post Posted - 2008-07-31 : 14:21:32 I have a view in a sql2000 database. It isn't especially complex, but does have a subquery and references other views. I can supply it if that is relevant. My problem that I discovered yesterday is that if I specify a column select list the query runs > 20X slower than if I select *. Example: SELECT Column1, Column2, Column3, Column4, Column5, Column6 FROM View_MyView WHERE OtherColumn = 'XYZ123' -- The above takes 10+ seconds to run SELECT * FROM View_MyView WHERE OtherColumn = 'XYZ123' -- This one takes < 1 second The Query plans look VERY similar, but the slower one has more references to "Parallelism/Repartition Streams" and most of the little icons have a little yellow circle in the lower right hand corner with 3 arrows. It is against everything I've ever been taught to do to use the * to select columns so I have to ask what would cause this to happen? Is this a bug in the query optimizer? Any thoughts?