want to get a result at any step of query execution


  • Hi

    Here is my query

    select *
    from employee as a
    where 1=(select count(distinct(b.salary))
    from employee as b
    where a.salary<b.salary)

    I want to know that how many times my outer query and inner query runs. Is there any method to handle a counter inside the query ? As in visual studio, we can watch the value of any variable at each iteration of loop, similarly can we see the result of outer and inner query at any step ? Is there any tool that is used for this purpose ? Any method ?

    Thanks in Advance.

    Saturday, June 29, 2013 1:53 PM


All replies

  • Have a look at this to get the executions count from execution plan.

    But, your query might be executed one. Because the sub-query is executed once and the output from it is compared with 1 in outer query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, June 29, 2013 3:17 PM
  • Thanks Sarat.

    Thanks for reply but this post is not giving me answer. For example I have two tables. A & B. If for each row of table A, B is fully scanned. I just want to know that on the given condition which values are compared and which row is returned on each step. In short I want a complete picture just as the whole operation is being done in front of me in real life and I am watching this whole operation with my eyes. Thanks

    Saturday, June 29, 2013 5:06 PM
  • And to answer a question you didn't ask, there is a more efficient way to get the result of that query (that query returns all the employees who have the second highest salary).  A more efficient way to do that is

    ;With cte As
    (Select <list of columns from employee table>,
       Dense_Rank() Over(Order By salary Desc) As rk
    From employee)
    Select <list of columns>
    From cte
    Where rk = 2;

    Saturday, June 29, 2013 5:29 PM
  • >>>I want to know that how many times my outer query and inner query runs

    set statistics profile on

    --run the query

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 30, 2013 6:42 AM
  • Congratulations on being able to read the original query's function, just reading it mystified me!


    Sunday, June 30, 2013 7:24 PM
  • You missed the point of a declarative language. Iteration? Loop? That was procedural programming! The optimizer is free to do this any way it wishes. If the indexing changes, or the version of SQL gets updated, the entire execution plan and performance can change. If the SQL engine has high parallelism, it can be totally different. 

    You improperly named this table "Employee" because your mindset is still in magnetic tape files where one record (one employee) is processed in a loop. An SQL programmer would name it "Personnel"; an abstract set that is processed all at once. 

    If this was a geography class, you would be the "flat earth kid" who has missed fundamental concepts. You want an old fashioned step-by-step debugger 

    set statistics profile on and read the result; It will change with table size, indexing and the next update of SQL. But it will give you a hint as to any changes that might help. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, June 30, 2013 10:32 PM