SQL Server Performance

Actual Execution Plan

Discussion in 'SQL Server 2005 General DBA Questions' started by SQL2000DBA, Nov 20, 2006.

  1. SQL2000DBA New Member

    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 getting
    FoodInvoice
    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
  2. xiebo2010cx Member

    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
    ------------------
  3. satya Moderator

    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.
  4. joechang New Member

    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
  5. SQL2000DBA New Member

    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



  6. satya Moderator

    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.

Share This Page