Post reply

SELECT COUNT(*) on a view runs many times longer than SELECT * on same view

  • January 21, 2009 at 9:45 am

    #133719

    Hello,

    I have a view which includes joins to a single table view and a single table subquery, and UDF is called in the column list. The query runs under 1 minute under normal circumstances (using SELECT *). However, if I perform a SELECT COUNT(*) on the same query, it's running multiple minutes. I've added non-clustered indexes and nolock hints to mitigate the risk of blocking, but no luck. Do COUNT(*) type queries have specific issues with UDFs, subqueries, etc.? In the execution plan, what should I be looking for that might be the cause of the hangup?

    Thank you!

    Matt

  • January 21, 2009 at 10:52 am

    #930541

    Matthew Zuberko (1/21/2009)

    Hello,

    I have a view which includes joins to a single table view and a single table subquery, and UDF is called in the column list. The query runs under 1 minute under normal circumstances (using SELECT *). However, if I perform a SELECT COUNT(*) on the same query, it's running multiple minutes. I've added non-clustered indexes and nolock hints to mitigate the risk of blocking, but no luck. Do COUNT(*) type queries have specific issues with UDFs, subqueries, etc.? In the execution plan, what should I be looking for that might be the cause of the hangup?

    Thank you!

    Matt

    COUNT(*) is an aggregation, so, yes, it does behave differently with all types of code. If you post both execution plans, it might be possible to identify the bottleneck. In general, I'd do two things. Look for the costliest operations in the execution plan. But, since cost in execution plans is estimated you also need to look at the query IO and execution time to see where most of the physical work is being done.

    I don't recommend nolock hints as a performance tuning mechanism, especially not as a first pass mechanism. Also, you added indexes, but did you add them based on clear needs within the query based on execution plans?

    You might want to post the code & structures too, if you can.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply