Viewing How Much Data was Processed by a Query
You can execute a query and determine how much data was processed by each node, and which nodes took the most time in the query execution plan.
Query plans in SQL that call procedures in FROM clauses show the details of what the procedures do (in the PROCEDURE nodes) when you direct the tool to execute and show the statistics.
If Keep Result is selected, the results of the query execution are available in a new Result tab. The data in the tab is available for as long as you keep the resource open in Studio. The query execution ID appears both at the top of the Execution Plan nodes in the left pane and in the Result tab name to help you match statistics and query information to results.
To view how much data was processed by a node
1. Generate an execution plan.
2. In the Execution Plan panel, select a node in the left pane.
3. Optionally, select Keep Result in the lower right corner of the Execution Plan to view the results of this query in a separate tab.
4. Click Execute and Show Statistics.
Statistics are added to the nodes in the left pane of the Execution Plan.
Each node name is followed by values in parentheses like (n) or (n, m%), where n is the number of rows produced by that node, and m is the percentage of elapsed time that the node used to process the data. If m is not shown, it is 0.
For example, if the elapsed time was 60 seconds and m is 20, the node accounted for 12 seconds of the elapsed time (20 x 60). If m is 0, processing at the node did not contribute to elapsed time—for example, if the node was processed by a background thread and its processing was completed before the rows were needed by the parent node. The m percentages help determine which nodes to focus on to improve performance.
5. Optionally, set a refresh rate for the execution plan.
6. Optionally, click Refresh Now during the execution of the query.
This updates the statistics being gathered on data sources and tables, which can take a long time for long-running queries.
For Example
Name:
SELECT
Rows Returned:
220
Estimated Rows Returned:
Unknown
Total execute time which include children time and wait time.:
121.4 msecs
Foreground Node Processing Time:
2.5 msecs
Peak memory reserved:
4000000
Projection:
orderdetails0.orderid OrderID, orderdetails0.productid ProductID, orderdetails0.discount Discount, orders.orderdate OrderDate, customers.companyname CompanyName, customers.contactfirstname CustomerContactFirstName, customers.contactlastname CustomerContactLastName, customers.phonenumber CustomerContactPhone, productCatalog_xform.ProductName ProductName, inventorytransactions.transactionid TransactionID, purchaseorders.daterequired DateRequired, purchaseorders.datepromised DatePromised, purchaseorders.shipdate ShipDate, suppliers.supplierid SupplierID, suppliers.suppliername SupplierName, suppliers.contactname SupplierContactName, suppliers.phonenumber SupplierPhoneNumber
Data Ship Notes:
Data Ship Query is not possible. No suitable source scan or ship-to target found.