How to improve performance od a sql query

Posted on 2007-10-17
Last Modified: 2008-02-21
Hi Guys

I have this code(pasted below) where I get the image paths of different images to show in a report. Now I need to optimize this code to impove the performnce. Would you guys have any suggestions on what I can I can do which can improve performance(Hibernate, stored procedure etc) and which would be better. I would really appreciate any help regarding this


                  String sql
                        = "SELECT distinct r.dDocAccount, r.dDocType, r.dDocName, r.dRevLabel, r.dWebExtension, len(r.dRevLabel) as ggg "
                        + "FROM Documents d INNER JOIN Revisions r ON d.dID = r.dID "
                        + "WHERE dDocName in "
                        + skulist
                        + " order by ggg, r.dRevLabel, r.dDocName";

                  con = initializeConnection();
                  stmt = con.createStatement();
                  result = stmt.executeQuery(sql);
                  while (
//                        String skuid = result.getString("dOriginalName").toLowerCase();
                        String account = result.getString("dDocAccount").toLowerCase();
                        String docType = result.getString("dDocType").toLowerCase();
                        String docName = result.getString("dDocName").toLowerCase();
                        String webExt = result.getString("dWebExtension".toLowerCase());
                        String rev = result.getString("dRevLabel");
                        i = 0;
                        while ((i = account.indexOf('/',i)) > -1)
                              account = account.substring(0,i)+"@"+account.substring(i);
                        String urlbase
                              = "https://"
//                              + ECommLoader.getAppConfigValue(Constants.APCFG_DMS_ADDR)
                              + ""
                              //+ ""
                              + "/xpedio/groups/public/@"
                              + account
                              + "/documents/"
                              + docType
                              + "/"
                              + docName;
                        String itemURL
                              = urlbase
                              + "."
                              + webExt;
                        String previewURL
                              = urlbase
                              + "@t~"+rev+".jpg";
                        ItemSearchOutputBean bean = (ItemSearchOutputBean)skutable.get(docName.toUpperCase());
Question by:huzefaq
    LVL 58

    Expert Comment

    I'm not sure what you want to optimize, but your query looks bad.

    SELECT DISTINCT should not be used on that many fields. Are you sure you need it? If you do, try to replace it with a GROUP BY clause.

    For example, given:

    FROM X

    If you know that A,B are enough to extract the distinct values, use this:

    SELECT A,B,First(C),First(D)
    FROM X

    If you have an index on A,B, it will be much faster.

    LVL 18

    Accepted Solution


    Your SELECT statement is pretty simple, so there's not much to improve there. Start by ensuring you have the proper indices (and create them if you don't).

    For instance, you'll definitely want one on Revisions.dID. Then, if I were you, I'd create an index over (Revisions.dRevLabel, Revisions.dDocName). Heck, I'd even create one over Revisions.dDocName alone, too.

    Then, after all the indices are in place, run the query through the Visual Explain tool to see how it's going to evaluate the query. Based on that, you might create more indices (that it recommends).

    Depending on which flavor of DB2 you're using (iSeries, zSeries, etc) , there might be more performance suggestions.

    LVL 18

    Expert Comment


    Ah, yes. I didn't notice the DISTINCT in my first pass over the query.

    In that case, try an index over Revisions.dDocAccount, Revisions.dDocType, Revisions.dDocName, Revisions.dRevLabel, Revisions.dWebExtension.

    (In most DB2's, the performance of DISTINCT is about equivalent to the performance of GROUP BY for properly indexed tables.)

    -- DaveSlash
    LVL 58

    Assisted Solution

    If the reason behind DISTINCT it to get distinct document names, why add indexes to label, extension, and account...

    In this case, we even have a DISTINCT over an expression: len(r.dRevLabel) which is really terrible in terms of performance. As dRevLabel is already distinct, why have the engine ensure that the length is as well?

    A moot point here, as ggg is also used in the ORDER BY clause (which will necessarily be slow, no index will help there...)

    As a side note: is there a reason to link in the table Documents? At present, it filters for Revisions also found in Documents. Is that necessary?

    LVL 1

    Expert Comment

    Forced accept.

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    The viewer will learn how to implement Singleton Design Pattern in Java.
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now