9 Replies Latest reply: Oct 2, 2012 8:48 AM by 955495 RSS

    SQL Developer responding slowly

    955495
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      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
                        Hi Thanks for the replies.
                        • 9. Re: SQL Developer responding slowly
                          955495
                          @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.