Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: Time required for executing a query

    Hello,

    select source, channel, count(*) totalDups from DUP_PAYMENT.DETAILS as details, DUP_PAYMENT.DUPLICATE as duplicate where details.sys_id = duplicate.sys_id and details.sys_entered_date between '2012-10-10 00:00:00.0' AND '2012-10-19 00:00:00.0' and duplicate.sys_dup = 'Y' and details.SYS_DELETED IS NULL group by source, channel with UR.

    Database have 7,50,000 Rows.
    Index - 2 used for SYS_ID,sys_entered_date,SYS_DUP column.
    Tables - 2 tables
    Above query is taking 18-20 sec for executing.
    Can you please tell me why its taking so much time and how can i optimize above query for reducing time required for execution.?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you looked at the explain plan to see if the indexes are being used ?

    Where did you get the info about the execution time ? On the database server ?
    If you have done on a client, you should also see if there is a bottleneck in rendering the data (if using a GUI tool) or even network.

    Your query will be doing sorting - do you know how good the clustering is on source and channel columns.

    Do you have table stats up to date ?

    BTW, you cannot tune a by posting on an internet forum , unless you have a pretty good grasp of query tuning already and are using the forum for more ideas.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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