SQL Server Performance Forum – Threads Archive
Actual Execution Plan
Hi, I need small inputs on below scenario. I am having two approach to measure the performance of table while retrieving records from a table. Approach1: I am creating one table as Invoice(InvoiceNumber,Invoice Qty, InvoiceType) with InvoiceNumber as Primary Key. In this table Invoice Type is to differentiate whether invoice is for Food or NonFood. i am having around 23000 records. To retrieve 23000 rows, time taken is just 3 second and Actual Execution Plan, i am getting I/O cost: 0.320903 and CPU cost 0.026557. Approach2:In this approach i am creating two tables as FoodInvoice(InvoiceNumber,Invoice Qty) and NonFoodInvocie((InvoiceNumber,Invoice Qty) with invoicenumber as Primary Key in both the tables. I am having 11500 records in each table. To retrieve records from both the tables i am doing UNION and retrieving 23000 records. Here also time taken to retrieve 23000 rows is 3 second and actual execution plan, i am gettingFoodInvoice
I/O cost: 0.0134954 and CPU cost 0.00345726557
NonFoodInvoice
I/O cost: 0.0712731 and CPU cost 0.0013357 i am surprise with above result because as per me as i am doing UNION it is more resource consuming operation but here results are saying something else.
I wanted to get your inputs whether i should consider approach1 or approach2 to create tables to get best performance while retrieving records. Thanks and Regards
Ravi K
Note: In my example i have given few field we are having around 20 fields
1. Before the query, please update the statistics, query processor uses statistics to make estimates. 2. If you want more accurate cost information, please do a sQL profiler trace, from there, you can get the number of disk-reads, number of disk-writes, and CPU cost and the duration of the queries. ——————
Bug explorer/finder/seeker/locator
——————
First of all you need to confirm whether it is a SQL 2000 or 2005 installation.
As suggested make sure to update the statistics on all the tables that are involved in this query, also in query analyzer you can lookup the estimated execution plan to get more information. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
use SET STATISTICS TIME ON or Profiler to determine CPU,
I suspect the 3 sec you are seeing the time to display 23K rows, not for the database to fetch 23K rows
Thanks for your reply. I am using SQL Server 2005. Here i am having difference in Time Without Union Query SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 3660 ms. Union Query SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 3539 ms. Here it is displayed difference between both the query. So i am sure that Without Union Query takes less time than Union Query. Thanks and Regards
Ravi K
Have you tried with UPDATE STATS? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>