Post reply

Sql query optimization

  • October 19, 2011 at 11:47 pm

    #261755

    Hello expert,

    I have one sql query. Its taking too much time to execute.

    The query is as,

    select distinct c.content_id,c.content_code,ct.content_type_id,cftm.file_type_id, cd.content_title, fl.file_path,

    ct.content_type_name, cftm.raw_sub_type_id, cf.file_id,cft.content_file_type_title,

    fs.file_sub_type_title raw_file_type,fl.added_on

    from tbl_contents c, tbl_content_files cf, tbl_content_file_type_map cftm ,

    tbl_file_locations fl, tbl_content_details cd, tbl_content_types ct, tbl_content_file_types cft,

    tbl_file_sub_types fs

    where cf.content_id=c.content_id

    and cf.file_sub_type_id=cftm.raw_sub_type_id

    and cf.content_file_type_id=cftm.file_type_id

    and cf.file_id=fl.file_id and fl.file_location='S3'

    and c.content_id = cd.content_id

    and ct.content_type_id = c.content_type_id

    and cf.content_file_type_id=cft.content_file_type_id

    and fs.file_sub_type_id = cftm.raw_sub_type_id

    and cftm.file_sub_type_id not in (select file_sub_type_id from tbl_sqs_requests sr where sr.raw_file_id=cf.file_id)

    also i have applier varios filter criteria on this.

    -- table tbl_file_location, tbl_contents have more than 3 lakhs records.

    I have created indexing on every table.

    Pls suggest me to optimize this query.

    Thanks in advance.

  • October 20, 2011 at 12:00 am

    #1397054

    Please see this article on how to post performance problems, thanks.

    http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/


    Clear Sky SQL
    My Blog[/url]

  • October 20, 2011 at 12:11 am

    #1397057

    Thank you sir for reply.

    Plz find attached query execution plan.

    the database is in oracle.

    Thanks.

  • October 20, 2011 at 12:23 am

    #1397058

    niteen2009 (10/20/2011)

    Thank you sir for reply.

    Plz find attached query execution plan.

    the database is in oracle.

    Thanks.

    Well this is SQL Server Central, isn't it?

  • October 20, 2011 at 12:23 am

    #1397059

    niteen2009 (10/20/2011)

    Thank you sir for reply.

    Plz find attached query execution plan.

    the database is in oracle.

    Thanks.

    Then im afraid, im not going to be able to help you much here.

    The 'big' table is tbl_content_files and it looks like it is scanning that rather than seeking, but i dont know enough about how to influence oracles optimizer.


    Clear Sky SQL
    My Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply