Results 1 to 15 of 15
  1. #1
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default Resultset to Json Performance

    Which is faster?
    or
    How do I perform the pulling of data out of my resultset for the best performance.

    I have about 100 columns in my resultset, If resultset is over 500 rows, than my result set fails. (Meaning my browser times out)

    String LVL_SUM_ORDERED_QTY = rs.getDouble(1);

    String LVL_SUM_ORDERED_QTY = rs.getDouble("LVL_SUM_ORDERED_QTY");
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    765
    Rep Power
    6

    Default

    The above lines are not sufficient to identify your problem.
    Might be query is taking time.

    You can do 2 things
    1.Check the query by running directly into database and check query execution time.
    2.For java,you can go for jprofiler.
    Ramya:cool:

  3. #3
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    I have, and using firebug I have established that it is this operation along with eclipse's debug mode which allows me to resume at specific locations and where I pull out of the result set, it takes 7 or 8 seconds.

    I am exactly pulling 146 columns, and 1,600 + rows.

    it takes roughly 3 seconds to execute in oracle.

    my sql is an select statement engine.

    I guess what I am asking is, is there any way to accelerate the result set?

    or which is more efficient, or is there another way.

    String LVL_SUM_ORDERED_QTY = rs.getDouble(1);

    String LVL_SUM_ORDERED_QTY = rs.getDouble("LVL_SUM_ORDERED_QTY");
    Last edited by Sno; 07-29-2010 at 03:29 PM.
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  4. #4
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    765
    Rep Power
    6

    Default

    Hi,
    Still you are not showing the glimpse of your code.
    Go thru this below article.
    Get disconnected with CachedRowSet - JavaWorld
    Ramya:cool:

  5. #5
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    Ramya
    I'm sorry,
    I feel like it would be absolutely pointless to show my code.

    I feel like your trying to solve my problem than answer my efficiency question.

    on which is faster....

    String LVL_SUM_ORDERED_QTY = rs.getDouble(1);

    String LVL_SUM_ORDERED_QTY = rs.getDouble("LVL_SUM_ORDERED_QTY");

    or are they equivalent?

    How would caching the rows make anything different?


    Java Code:
                    SLRSQLEngineSummaryGRP SQLEngine = new SLRSQLEngineSummaryGRP();
    		String SQL = SQLEngine.buildSQL(Group, ChainGrp, WHGrp, GLGrp, BuyerGrp, VendorGrp, APVGrp, NeilsenGrp, Report, Date, Chain, PL, Seasonal_Holiday, CP, ChainDiv, GL, WH, Buyer, Vendor, APV, Nielsen, Detail);
    	
    		
    		System.out.println(SQL);
    		
    		Statement stmt = con.createStatement();
    		
    	
    		rs = stmt.executeQuery(SQL);
    
    		JSONObject store = new JSONObject();
    		JSONArray json = new JSONArray();
    		
    	while(rs.next()){	
    		JSONObject obj = new JSONObject();
    
    		obj.put("CATEGORY", 			        rs.getString("CATEGORY"));
    		obj.put("LVL_SUM_ORDERED_QTY", 				rs.getDouble("LVL_SUM_ORDERED_QTY"));
    		obj.put("LVL_SUM_TURN_QTY", 				rs.getDouble("LVL_SUM_TURN_QTY"));
    		obj.put("LVL_SUM_AD_QTY", 					rs.getDouble("LVL_SUM_AD_QTY"));
    		
    		obj.put("LVL_SUM_SHIPPED_QTY", 				rs.getDouble("LVL_SUM_SHIPPED_QTY"));
    		obj.put("LVL_SUM_SHIPPED_TRN_QTY", 			rs.getDouble("LVL_SUM_SHIPPED_TRN_QTY"));
    		obj.put("LVL_SUM_SHIPPED_AD_QTY", 			rs.getDouble("LVL_SUM_SHIPPED_AD_QTY"));
    		
    		obj.put("LVL_SUM_MFGOUT_QTY", 				rs.getDouble("LVL_SUM_MFGOUT_QTY"));
    		obj.put("LVL_SUM_MFGOUT_TRN_QTY", 			rs.getDouble("LVL_SUM_MFGOUT_TRN_QTY"));
    		obj.put("LVL_SUM_MFGOUT_AD_QTY", 			rs.getDouble("LVL_SUM_MFGOUT_AD_QTY"));
    		
    		obj.put("LVL_SUM_ALLOCATED_QTY", 			rs.getDouble("LVL_SUM_ALLOCATED_QTY"));
    		obj.put("LVL_SUM_ALLOCATED_TRN_QTY", 		rs.getDouble("LVL_SUM_ALLOCATED_TRN_QTY"));
    
    ... CONTINUES for 146 lines...
    
                    obj.put("TTL_TOTAL_PCT", 					rs.getDouble("TTL_TOTAL_PCT"));
    		obj.put("SUM_TTL_TURN_QTY", 				rs.getDouble("SUM_TTL_TURN_QTY"));
    		obj.put("TTL_TURN_TOTAL_ORD_PCT", 			rs.getDouble("TTL_TURN_TOTAL_ORD_PCT"));
    		obj.put("TTL_TURN_TOTAL_TRN_PCT", 			rs.getDouble("TTL_TURN_TOTAL_TRN_PCT"));
    		obj.put("SUM_TTL_AD_QTY", 					rs.getDouble("SUM_TTL_AD_QTY"));
    		obj.put("TTL_AD_TOTAL_ORD_PCT", 			rs.getDouble("TTL_AD_TOTAL_ORD_PCT"));
    		obj.put("TTL_AD_TOTAL_AD_PCT", 				rs.getDouble("TTL_AD_TOTAL_AD_PCT"));
    
    		json.put(obj);
    	}
    		
    		store.put("results",json);
    		
    		// stream JSON Object
        	 out.print(store);
       		 out.flush();
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    Quote Originally Posted by Sno View Post
    Ramya
    I'm sorry,
    I feel like it would be absolutely pointless to show my code.

    I feel like your trying to solve my problem than answer my efficiency question.

    on which is faster....

    String LVL_SUM_ORDERED_QTY = rs.getDouble(1);

    String LVL_SUM_ORDERED_QTY = rs.getDouble("LVL_SUM_ORDERED_QTY");
    If you're down to such a level of speed fix then I would say this is not your problem area. The former is faster than the latter, but not by anything near enough for it to make a huge difference to your timings.

    For your timing, did you put a start time just prior to the while loop, and and end time just after it?

    Cacheing the rows is only worth it if you use them alot...and not knowing what you're doing we can't really say.

  7. #7
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    Gotcha, sorry for the blabber than,

    I've determined that it is
    this statement
    Java Code:
    out.print(store);
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    So it's the transmission of the json object that's the problem?
    Not much you can do about that...how much data is it?

  9. #9
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    I'm pulling a lot about 1600 records, but a lot of it are 0's, so I threw if statements around some of the objects to try and reduce the string that gets passed through the network.

    ie
    Java Code:
    String Total = rs.getDouble("TTL_TOTAL_PCT");
    if(Total == "0"){
    obj.put("TTL_TOTAL_PCT", 			        "");
    obj.put("SUM_TTL_TURN_QTY", 				"");
    ..
    ..
    ..
    }else{
    obj.put("TTL_TOTAL_PCT", 					rs.getDouble("TTL_TOTAL_PCT"));
    obj.put("SUM_TTL_TURN_QTY", 				rs.getDouble("SUM_TTL_TURN_QTY"));
    obj.put("TTL_TURN_TOTAL_ORD_PCT", 			rs.getDouble("TTL_TURN_TOTAL_ORD_PCT"));
    obj.put("TTL_TURN_TOTAL_TRN_PCT", 			rs.getDouble("TTL_TURN_TOTAL_TRN_PCT"));
    obj.put("SUM_TTL_AD_QTY", 					rs.getDouble("SUM_TTL_AD_QTY"));
    obj.put("TTL_AD_TOTAL_ORD_PCT", 			rs.getDouble("TTL_AD_TOTAL_ORD_PCT"));
    obj.put("TTL_AD_TOTAL_AD_PCT", 				rs.getDouble("TTL_AD_TOTAL_AD_PCT"));
    }
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  10. #10
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,015
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by Sno View Post
    I'm pulling a lot about 1600 records, but a lot of it are 0's, so I threw if statements around some of the objects to try and reduce the string that gets passed through the network.

    ie
    Java Code:
    String Total = rs.getDouble("TTL_TOTAL_PCT");
    if(Total == "0"){
       [ ... ]
    As you could (and should) have known you can't compare Strings for equality like that; use the equals( ... ) method for that purpose.

    kind regards,

    Jos

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    So, assuming each column only had the single character (and I missed you'd said how many rows and columns you were dealign with earlier)...that would be 233600 characters, without the json overhead which would be the attribute name (plus quotes), a colon, and the attribute value (plus quotes) and a comma.

    Just taking the shortest attribute name there as a "standard" (13 characters for TTL_TOTAL_PCT), and adding in quotes and commas, I get 233600 * (13 (name) + 2 (quotes) + 1 (data) + 2 (data quotes) + 1 (comma)) = just shy of 4.5Mb, and that assumes it's 8 bit ascii.

    In other words, you have a lot of data travelling there.

    ETA: And just looked at that code, and you will save a grand total of not very much doing that. At best (assuming each row and column had a zero for its data) you would save less than 1/4 of a meg...you'd still be transmitting over 4 meg. And that's with (frankly) no data in there.
    Last edited by Tolls; 07-30-2010 at 01:57 PM.

  12. #12
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    @ Josah,

    Ha yes you are correct!

    I had written that by hand while I was waiting for my eclipse to start up.

    if you notice
    String Total = rs.getDouble("TTL_TOTAL_PCT");
    is not a valid operation
    it should of been double total.

    so it should be. Double.compareTo(Total, 0);
    ?

    @ Tolls,

    Looks like im SOL...
    Last edited by Sno; 07-30-2010 at 02:31 PM.
    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    This is when you look at what you're sending and try and figure out if you need all of it.

    I'm assuming because you are sending 1600 rows that this is for some massive report?
    Can it be paginated?

  14. #14
    JosAH's Avatar
    JosAH is online now Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,015
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by Sno View Post
    @ Josah,

    Ha yes you are correct!

    I had written that by hand while I was waiting for my eclipse to start up.

    if you notice
    String Total = rs.getDouble("TTL_TOTAL_PCT");
    is not a valid operation
    it should of been double total.

    so it should be. Double.compareTo(Total, 0);
    ?
    Nope, you're retrieving a double value from that row, not a Double; primitives (such as double values) should be compared with the == operator. so:

    Java Code:
    double total= rs.getDouble("TTL_TOTAL_PCT);
    if (total == 0) ...
    kind regards,

    Jos

  15. #15
    Sno's Avatar
    Sno
    Sno is offline Senior Member
    Join Date
    Apr 2010
    Posts
    196
    Rep Power
    4

    Default

    Yes, it is a Service Level Report Software.

    I am building a web based version of a ms access one.

    They are tired of using ms access and running out of licenses.

    heres a link to a screen shot to give you guys an idea.

    :rolleyes: ~ Sno ~ :rolleyes:
    '-~ B.S. Computer Science ~-'

Similar Threads

  1. hashmap to json
    By prakashkadakol in forum New To Java
    Replies: 5
    Last Post: 07-30-2011, 01:32 AM
  2. Json
    By mac in forum New To Java
    Replies: 10
    Last Post: 06-08-2010, 11:15 PM
  3. JSON Lib: json-lib-1.0-jdk13.jar
    By Raghuraman K in forum Advanced Java
    Replies: 3
    Last Post: 05-13-2010, 10:00 AM
  4. DB Values to JSON
    By prakashkadakol in forum New To Java
    Replies: 1
    Last Post: 01-05-2010, 06:19 PM

Posting Permissions

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