2 Replies Latest reply on Feb 25, 2015 1:19 AM by rp0428

    query result is taking long time

    2839685

      Hi,

      I am ok when SQL result fetched into SQL DEVELOPER result set.

      But I need to save the same set in an excel sheet. Saving in excel file using file.xml format is taking a time to the extent which is not acceptable.

       

      What I am doing is SQL Editor / Data Grid / Right Click / Export / 2003 xml format.

       

      Here is the query.

       

      SELECT SIEBEL.S_ORDER.ORDER_NUM,

             SIEBEL.S_ORDER.ORDER_DT,

             SIEBEL.S_ORDER.DLVRY_STATUS_CD,

             SIEBEL.S_ORDER.X_AMT_PAYING,

             SIEBEL.S_ORDER.X_ETC_ACCOUNT_ID,

             SIEBEL.S_ORDER.X_VIOL_STATUS,

             SIEBEL.S_ORDER.X_VIOL_FEE_PAYING,

             SIEBEL.S_ORDER.X_VIOL_TOLL_PAYING,

             SIEBEL.S_ORDER.X_TOR_FLG,

             SIEBEL.S_ORDER.X_PLATE_NUMBER,

             SIEBEL.S_ORDER.X_PLATE_STATE,

             SIEBEL.S_ORDER.X_COURT_STATUS

        FROM SIEBEL.S_ORDER

      WHERE     SIEBEL.S_ORDER.ORDER_DT >= '01-JUL-13'

             AND SIEBEL.S_ORDER.X_VIOL_STATUS IN ('OPEN', 'PAIDPART')

             AND SIEBEL.S_ORDER.X_TOR_FLG = 'N'

             AND SIEBEL.S_ORDER.X_PLATE_STATE = 'IL';

      Data fetched in the SQL editor set fast but when I am saving in excel sheet is taking very very long time and it it is not acceptable.

      @

      I do not think that it has  any thing to do with the query but I am just sharing.

       

      Thanks in advance.

      Sarwar

      OCP DBA

      Xerox

        • 1. Re: query result is taking long time
          Gary Graham-Oracle

          Hi Sarwar,

           

          You do not state which release of SQL Developer you use.  A complete answer depends on that, but standard advice for xls formats is...

          1. Export to csv format instead.

          2. Increase the Java VM heap size (via the -Xmx parameter).

           

          A old discussion that covers this is Re: Export to Excel Hangs

           

          Starting with the 4.0.x releases, the -Xmx parameter may be found in a product.conf file under your user settings folder.  For prior releases, it lives in sqldeveloper.conf under your SQL Developer install folder.

           

          The reason for the slow down is the API used keeps all the data in memory.  Recent releases of SQL Developer use a newer API for the xlsx format that does not have this problem.

           

          Regards,

          Gary

          SQL Developer Team

          • 2. Re: query result is taking long time

            Data fetched in the SQL editor set fast but when I am saving in excel sheet is taking very very long time and it it is not acceptable.

            You may think that 'Data fetched in the SQL editor set fast' but you can only see a SMALL SET of data.

             

            The issue is directly related to the amount of data you are retrieving and saving. That amount depends on the average size of each row and the number of rows.

             

            The amount of data is then expanded SIGNIFICANTLY when you use XML format because the begin/end tags take a significant amount of space.

             

            Excel can read delimited data so you can always reduce the amount of data by saving to a delimited file instead of XML. If, as Gary suspects you are using an older version of Sql Developer then you should download and use the latest released version instead.