9 Replies Latest reply: Apr 5, 2009 1:37 PM by jschellSomeoneStoleMyAlias RSS

    Java code vs database query performance  ?????

    807588
      Hi to all forum Members,

      I am quite new in this forum, if i am at wrong place to post the query, please direct me to right place.

      Question background : I want to have date in dd.MM.yyyy format in my excel sheets. Therefore, i have two possibilities
      1- either to parse date from timestamp ( loaded column values from database ) in java
      2 - or write a query in such a way which cast timestamp as date e.g.

      select *, DATE_FORMAT(cast( inserted_timestamp as date), '%d.%m.%Y') as inserted_date from link where id > 290002;

      Question: i like to know which method is better in terms of performance ? Because , in my first internship , i have been always desired to get the most of job done from database instead of one's application code.

      Thanks in advance.

      Regards,
      Romi
        • 1. Re: Java code vs database query performance  ?????
          807588
          2 - or write a query in such a way which cast timestamp as date e.g.
          This one. If you RTFM, you will see that java.sql.TimeStamp is a subclass of Date
          Question: i like to know which method is better in terms of performance ? Because , in my first internship , i have been always desired to get the most of job done from database instead of one's application code.
          This is not a good approach. Doing as much as you can in java / application layer makes scaling + testing SO MUCH EASIER
          • 2. Re: Java code vs database query performance  ?????
            jschellSomeoneStoleMyAlias
            tjacobs01 wrote:
            This is not a good approach. Doing as much as you can in java / application layer makes scaling + testing SO MUCH EASIER
            Not at all. Attempting to process large amounts of data in java, requiring it to be moved from the database to client machines and back again, is a very good way to make a system that doesn't scale. Databases have been around for much longer than java and are very good at doing very efficient processing with the contained data.
            • 3. Re: Java code vs database query performance  ?????
              jschellSomeoneStoleMyAlias
              romi_ch wrote:
              Question: i like to know which method is better in terms of performance ? Because , in my first internship , i have been always desired to get the most of job done from database instead of one's application code.
              I suggest you focus first on getting the code to work. The optimization you are talking about is a micro-optimization and as such it is something easily determined after the code works.
              • 4. Re: Java code vs database query performance  ?????
                807588
                Hi Mr / Miss tjacobs01,

                Thanks for reply. I had alread knowledge that timestamp is subclass of date. Firstly, I wanted to know how far my x-colleague's statement is authentic.

                I am completely agree with your statement

                " Doing as much as you can in java / application layer makes scaling + testing SO MUCH EASIER"

                So far i am performing convertion related manipulation in java classes. But i am facing some kind of java heap problem, as long as i insert data into xls sheet coz my fetched dataset contains more than 50K rows. Therefore i thought perhaps i can spare lil bit heap space. And i dont think i should let the my own java class handle larger dataset. What i want to say perhaps i can not as a single developer can achieve, with little experience, as much performance as i can achieve from database which is designed by many profis. that is why i am in favor of database query instead of cracking and parsing timestamp into date. If i am getting a ready made solution which is optimized then why should i try to reinvent the wheel again at application level.



                I think now, reply to first question truly force me to ask heap related problem. But i am thinking its not good idea to ask related question in this post.

                By the way : second question is : how can i insert dataset more than 50K rows into xls sheet. So far i know two methods

                1 - extend java heap size on both JRE and Tomcat
                2- use streams to continously transfer the data.

                About 2nd method, i never used and dont know whether it is a better choice.

                If you think you do not like to answer 2nd question in this post then please click on following link.

                Kind Regards,
                Romi
                • 5. Re: Java code vs database query performance  ?????
                  807588
                  Mr. Jschell,

                  Code works fine. I can easily generate xls sheets with dataset about 40K rows. That is not a big deal. Only problem i face with large dataset ... you may find my second question at following link

                  http://forums.sun.com/thread.jspa?threadID=5377237

                  Thanks
                  romi
                  • 6. Re: Java code vs database query performance  ?????
                    807588
                    Ideally, the presentation layer should format the code based on the end-user's locale (google java locale) and not in the database layer. For example: Some countries use dd/mm/yyyy, other countries use mm/dd/yyyy.

                    As far as running out of heap space, I question why you are dealing with 40000 rows. The end-user can't handle that many records on their display. If you really need, to have more heap space, you can google 'java -xmx' which shows how to increase the heap space.
                    • 7. Re: Java code vs database query performance  ?????
                      807588
                      Hi Mr. njb7ty,

                      At the begning, I have written date function query for explaination purposes and my question still is the same whether should i submit many tasks to database or should i handle each task at presentation or middle layer.

                      concern to 40K rows, i am inserting that data into excel sheet and let the user to download it through FTP. I am not showing it to client.

                      As far as, java heap concerns "Java -xmx" i have already posted pros and cons, related to my problem, of java heap extnetion in my second question ..Neverthless Thanks for replying.
                      http://forums.sun.com/thread.jspa?threadID=5377242

                      I think, i some how agree with Mr. jschell ' post
                      Regards,
                      Romi
                      • 8. Re: Java code vs database query performance  ?????
                        807588
                        a

                        Edited by: romi_ch on Apr 5, 2009 11:11 AM
                        • 9. Re: Java code vs database query performance  ?????
                          jschellSomeoneStoleMyAlias
                          romi_ch wrote:
                          concern to 40K rows, i am inserting that data into excel sheet and let the user to download it through FTP. I am not showing it to client.
                          However that format is still a "user" format. So myself I would question the requirement.

                          Users can make demands without thinking about the consequences. And not knowing that alternatives exist. For example someone might need the ability to verify individual accounts for a period of days. Thus a report that allowed that versus the user finding it spanning serveral days of large excel files would be better all around.