Response time for queries from Plan Viewer

[Former Member]

Hi

Recently, I saw that the same query may take much more time to execute when it is run via Plan Viewer ("Detailed Node and Statistics" mode) as compared to Interactive SQL. Note that some of the queries in question return only a few rows, so client fetching is not an issue here.

Also, some queries doesn't return at all when run via Plan Viewer, or at least I didn't enough patience to wait. I see that the query surely does something using OS utilities, but it just doesn't return. The same query returns in a couple of minutes when run via Interactive SQL. Particularly, I saw this behavior from queries with a number of queries concatenated with UNION ALL in the FROM clause.

The question: is it something expected or I'm hitting a bug? Is it a limitation of the Plan Viewer? I'm working with SQLA 12.0.1.

[Former Member · Accepted solution]

Executing a query while collecting detailed statistics about the query's execution is an excellent way to determine not only the characteristics of the execution strategy chosen by the optimizer, but to get actual (versus estimated) query cost parameters in the process.

However, the CPU and elapsed time cost of this data collection is far from free. Saving detailed statistics for an individual DFO operator roughly doubles (I do not have an actual number at my fingertips, unfortunately) the per-row execution cost, in terms of CPU time. So if a DFO operator processes 1 million rows and takes 7 CPU seconds, the plan with statistics will roughly double that to 14 seconds. The more complex the query (and the more filtering that takes place), the greater the amount of metadata collected and, consequently, the higher the overhead.

[VolkerBarth · Accepted solution]

Glenn, what's the meaning of a DFO operator?

[Former Member · Accepted solution]

DFO is an acronym for Data Flow Operator. Each physical plan operator in an access plan (sequential scan, hash group by, sort, union, etc) is a DFO. It is the tree of DFO operators that constitute the access plan as viewed in the ISQL plan viewer.

[Former Member · Accepted solution]

Thank you very much for explanation, it is very useful. But, this behavior is somewhat surprising to me. In other database products I'm working with, generating of detailed query plans, including I/O statistics etc. doesn't have so great overhead. I only wish it would stated more clearly in the documentation.

[Former Member · Accepted solution]

I am agree with you, something wrong with query plans in SA. Work too slow, impossible to get compound statements plan 😞

[Former Member · Accepted solution]

I cannot answer for other products. The overhead of detailed statistics is considerable; that's why on graphical plans there are three options: estimates only, detailed statistics, and detailed and node statistics, in increasing order of overhead.