2

I have a weird situation here. I have a complex query which should take approximately 10 seconds to retrieve about 6000 rows from the database.

Query version 1:

SELECT * from .....

In above version, it takes about 10 seconds to get 6000 rows from the database (I am talking about running the query in SQL management studio)

Query version 2:

SELECT [FieldName1], [FieldName2] from .....

In the above version (2), it takes more than an hour or even more to get the results (6000 rows) from the database (I am talking about running the query in SQL management studio)

Both the queries have same joins and where conditions.

Can anyone shed some light on this please?

Thanks.

Regards, Nayan

Dhwani
  • 7,484
  • 17
  • 78
  • 139
Nayan Ambaliya
  • 31
  • 3
  • I don't know sql-server but I assume you can inspect the plan for the queries somehow. Is there a difference in the plan for these two? – Lennart - Slava Ukraini May 29 '14 at 04:08
  • You may want to take a look at the accepted answer to this question: http://stackoverflow.com/questions/3180375/select-vs-select-column – shree.pat18 May 29 '14 at 04:09
  • Yeah. Start with doing baseline work and give us ESTIMATED and EXECUTED query plans for both scenarios. – TomTom May 29 '14 at 04:09
  • I observed this as well (many releases of the SQLServer engine back). – hardmath May 29 '14 at 04:11
  • @shree.pat18: the answer on that question does not answers why `select *` is faster than `select [FieldName1], [FieldName2] from`. – cha May 29 '14 at 04:17
  • Could that be a case of SQL Server caching. I.e. if you run the second query several time will you get better performance? – cha May 29 '14 at 04:18
  • I am not sure by the answer but I think because sql need the column name so it should fetch the Sys views first. – Meysam Tolouee May 29 '14 at 04:47
  • 2
    I would think that for "select *" SQL uses full table scan somewhere in query plan, for "select few fields" it attempts to use indexes and screws query plan up somehow; slow disk subsystem may "help". Sometimes (full) statistics update for related tables will help. Like many others said, take a look at query plans. – Arvo May 29 '14 at 06:22
  • Be very careful using the time it takes results to appear in SMSS as a performance benchmark. There can be significant network impedence returning the rows from the server to the client & presenting them in the tool. I've seen very variable times submitting the same query consecutively. One work-around is to wrap your query, say in a stored procedure, such that one or zero rows is actually communicated to the client workstation. – Michael Green May 29 '14 at 06:26
  • Thank you guys for your insight..!! I will look into it and will provide updates.. Thanks very much – Nayan Ambaliya May 29 '14 at 06:46
  • To add to what @MichaelGreen posted, try using `SET STATISTICS IO ON` and/or `SET STATISTICS TIME ON` in your query window from SSMS (you only need to do this once). Then run query one and start looking at the info returned in the messages output tab. Run query two and do the same. http://msdn.microsoft.com/en-us/library/ms184361.aspx http://msdn.microsoft.com/en-us/library/ms190287.aspx – Dave Mason Jun 08 '14 at 08:11

1 Answers1

0

I suppose you may be retrieving data from a view rather than a table. In that case it's conceivable that the plan in use may be less efficient.

However, in "normal" use in which you're SELECTing from tables (rather than views or functions)

You should never expect a significant performance drop from using column names rather than * and you should always prefer explicit column names rather than * for non-predicate SELECTs

Matthew
  • 10,244
  • 5
  • 49
  • 104