SQL performance issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL performance issue

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    I am having a performance problem with some sql scripts.

    I have two Windows 2000 machines, same release + service pack. Both have 2 gig of Ram, both have the same CPU type.

    One machine is running Oracle 8.1.6 and the other SQL Server 2000.

    We created a database on each machine. We created the same tables on each machine (Table A, B and C). We then loaded each table with 2.1 million rows of data. We then created a view of all three tables -> Select all columns from A, union all Select all columns from B union all Select all columns from C. All the tables have an index (columns 1 & 2).

    The issue that has come up is why does it take Oracle a combined total of 36 seconds to extract one record from the three tables when it only takes SQL Server 5 seconds? Here is the select statement:

    Select col3 from table A where column 5 = xyz;
    Select col3 from table B where column 5 = xyz;
    Select col3 from table C where column 5 = xyz;

    Please note that column 5 is not part of the index. This was done purposely.

    Is there anything that I can do (change, reset, add, delete, etc) so that Oracle will perform close to what SQL Server does?

    This really annoys me that SQL Server can beat Oracle.

    Thanks.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Set your optimizer to COST. Indexed those colums. Analyze your table and indexes and then have a the

    SQL> set autotrace traceonly
    SQL> run your query

    and see what exaclty oracle does behind the query.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Aug 2001
    Posts
    111
    Could be doing lots of sorting. Increasing the SORT_AREA_SIZE may help it out.
    As above need to see the execution plan for both databases.

    Cheers

    Paul
    Performance... Push the envelope!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width