This discussion is archived
9 Replies Latest reply: Oct 2, 2012 8:48 AM by 955495 RSS

SQL Developer responding slowly

955495 Newbie
Currently Being Moderated
Hi,

I am connecting to Oracle DB from SQL Developer using remote connection through LDAP. I work with sql server management studio regularly but through sql developer this is my first time.

I find SQl developer slow compared to sql server management studio- IS this true or is it because of the remote connection.?

What steps can I take to improve its speed . Are there any tools or settings that I need to change?

I have a simple insert query into a temporary table which ran for 5 hours.The application does not stop executing after that and I need to end it from task manager. I think this is to do with the buffer size, so I need to commit data in batches to process it more speedily.Are there any settings to do to run data in batches?


Thanks for your time and help
  • 1. Re: SQL Developer responding slowly
    Jim Smith Expert
    Currently Being Moderated
    Any performance difference is more likely to be down to the DBMS differences rather than the client tools.

    I assume you are using SQL Developer against an Oracle database?
    What is your script attempting to do?

    If the database is remote then network delays will be made worse by inefficient coding resulting in too many round trips. You need to consider that Oracle and MSSQL work in different ways and what constitutes good practice in one won't necessarily work well in the other.
  • 2. Re: SQL Developer responding slowly
    955495 Newbie
    Currently Being Moderated
    Hi Jim,

    Thanks for the reply. My Query is pulling from two different tables using a nested query.

    when I tried running a select * from employee table which took a rough 40 seconds to bring results whereas when I did it in SQL Server management studio, it took around 1-2 seconds to pull the similar amount of data.So I was curious if this was because of the remote connection!?(SQl server is running on my network).

    Also Can you tell me how to run the query in batches,like inserting and comitting 5000 records once and then inserting next 5000 records and so on! Is there a way doing it from any settings or do we nee do modify the query?

    Thanks for your time and help.
  • 3. Re: SQL Developer responding slowly
    Jim Smith Expert
    Currently Being Moderated
    user13555152 wrote:
    Hi Jim,

    Thanks for the reply. My Query is pulling from two different tables using a nested query.

    when I tried running a select * from employee table which took a rough 40 seconds to bring results whereas when I did it in SQL Server management studio, it took around 1-2 seconds to pull the similar amount of data.So I was curious if this was because of the remote connection!?(SQl server is running on my network).
    That certainly looks like the network is the issue. You may be able to improve query performance by increasing the SQL Array Fetch Size (Tools|Preferences|Database|Advance). However, your insert statement shouldn't be affected by the query performance.

    You should be doing something like
     insert into target (x,y) 
     select a, b 
    from source1 inner join source2
    ...
    I suspect you are doing something like (pseudocode)
     for s1 in ( select * from source1 )
    loop
      fetch (select * from source2 where source2.key=source1.key
      insert into ( target ) values (source1.a,source2.b)
    end loop
    This is hugely inefficient.
    Also Can you tell me how to run the query in batches,like inserting and comitting 5000 records once and then inserting next 5000 records and so on! Is there a way doing it from any settings or do we nee do modify the query?
    Note that commiting like this is considered bad practice in oracle. It does not improve performance and can make it worse. Bulk inserts (without commits) can improve performance but the best improvement would be from recoding as a straight insert..select as suggested above. If you can't recode as a straight query you need to look into the use of 'bulk collect' and 'forall' in conjunction with PL/SQL collections.

    Please post your code and I'll make suggestions. (Although this isn't really a SQL Developer problem)
  • 4. Re: SQL Developer responding slowly
    955495 Newbie
    Currently Being Moderated
    INSERT INTO Temp
    SELECT
    VisitNo,
    EmployeeNo,
    (SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp,
    ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum
    FROM
    ClinicVisit clin
    ORDER BY
    VisitNo,
    CaseNo;

    This is how my query is
  • 5. Re: SQL Developer responding slowly
    955495 Newbie
    Currently Being Moderated
    INSERT INTO Temp
    SELECT
    VisitNo,
    EmployeeNo,
    (SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp,
    ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum
    FROM
    ClinicVisit clin
    ORDER BY
    VisitNo,
    CaseNo;

    is the above query same as:

    INSERT INTO Temp
    SELECT
    clin.VisitNo,
    clin.EmployeeNo,
    e.employeeno emp,
    ROW_NUMBER() OVER (ORDER BY clin.VisitNo, clin.CaseNo) rnum
    FROM
    ClinicVisit clin left join Employee e WHERE e.CaseNo = clin.CaseNo
    ORDER BY
    clin.VisitNo,
    clin.CaseNo;
  • 6. Re: SQL Developer responding slowly
    Jim Smith Expert
    Currently Being Moderated
    user13555152 wrote:
    INSERT INTO Temp
    SELECT
    VisitNo,
    EmployeeNo,
    (SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp,
    ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum
    FROM
    ClinicVisit clin
    ORDER BY
    VisitNo,
    CaseNo;

    is the above query same as:

    INSERT INTO Temp
    SELECT
    clin.VisitNo,
    clin.EmployeeNo,
    e.employeeno emp,
    ROW_NUMBER() OVER (ORDER BY clin.VisitNo, clin.CaseNo) rnum
    FROM
    ClinicVisit clin left join Employee e WHERE e.CaseNo = clin.CaseNo
    ORDER BY
    clin.VisitNo,
    clin.CaseNo;
    I think they are equivalent.
    You don't need the final order by - you are inserting into a table so order is irrelevant. Depending on the number of rows a sort could be expensive.

    Why do you need rnum? If the tables aren't indexed appropriately the sort required for rownumber could be expensive.

    Are the tables' statistics up to date?
    Are the tables indexed appropriately?
    Try running explain plan (F10 in SQL Developer) on both versions of the query and look for high cost elements.
  • 7. Re: SQL Developer responding slowly
    rp0428 Guru
    Currently Being Moderated
    >
    I find SQl developer slow compared to sql server management studio- IS this true or is it because of the remote connection.?

    What steps can I take to improve its speed . Are there any tools or settings that I need to change?

    I have a simple insert query into a temporary table which ran for 5 hours.The application does not stop executing after that and I need to end it from task manager. I think this is to do with the buffer size, so I need to commit data in batches to process it more speedily.Are there any settings to do to run data in batches?
    >
    Based on the code you posted in a later reply this does NOT appear to be a sql developer issues at all and should be posted in the SQL and PL/SQL forum.
    SQL and PL/SQL

    Before you post there please read the thread 'How to post a tuning request' located at
    HOW TO: Post a SQL statement tuning request - template posting

    That thread discusses the information that you need to provide in order for volunteers to be able to make meaningful suggestions. That information includes

    1. the DDL for the tables and indexes involved
    2. the row counts for the tables
    3. the row counts for the query predicates
    4. the execution plan for the query
    5. the expected row count of the result set

    Based of this code that you posted
    INSERT INTO Temp 
    SELECT
    VisitNo,
    EmployeeNo,
    (SELECT EmployeeNo FROM Employee e WHERE e.CaseNo = clin.CaseNo) emp,
    ROW_NUMBER() OVER (ORDER BY VisitNo, CaseNo) rnum
    FROM
    ClinicVisit clin
    ORDER BY
    VisitNo,
    CaseNo;
    the query will simply be sent by sql developer to Oracle for processing. Sql developer will play no further role until the query is actually completed. So based on this example code there is no basis at all for saying:

    1. sql developer is slow
    2. the application does not stop executing - please clarify what you mean by 'application' and by 'does not stop'
    3. I think this is to do with the buffer size
    4. I need to commit data in batches to process it more speedily
    5. the remote connection is in any way involved

    The code (insert into temp) suggests that you are using the typical sql server practice of populating a temporary table whose data will then, typically, be used for further processing and population of the target table.

    In Oracle you rarely need to use temporary tables and when you do use them you use them differently than the way they are used in sql server.

    The SQL and PL/SQL forum is the place to gets answers about tuning queries but you will get better help if you provide additional information about what you are really trying to do.
  • 8. Re: SQL Developer responding slowly
    955495 Newbie
    Currently Being Moderated
    Hi Thanks for the replies.
  • 9. Re: SQL Developer responding slowly
    955495 Newbie
    Currently Being Moderated
    @Jim:

    Thanks for your help.

    If both queries are equivalent I can use the second query with left join as it is returning me results much much faster compared to other

    @rp0428:

    Thanks for your comments.

    When I said the application does not stop executing,I meant that after running Sql Developer for a while with the above query I posted it runs a script runner task and it runs continously without displaying results.After a while when I try to stop the query and quit SQL Developer it does not allow me to instead it gives a warning message connection is currently busy and SQL developer does not close.I need to end it using windows task manager.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points