asked on

How to improve performance od a sql query

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

Thanks

--------------------------------------------------------------------------------------------------------------------------
try
            {
                  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";

                  System.out.println(sql);

                  con = initializeConnection();
                  stmt = con.createStatement();
                  result = stmt.executeQuery(sql);

                  while (result.next())
                  {
//                        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)
                        {
                              i++;
                              account = account.substring(0,i)+"@"+account.substring(i);
                        }

                        String urlbase
                              = "https://"
//                              + ECommLoader.getAppConfigValue(Constants.APCFG_DMS_ADDR)
                              + "dms.rapidsolutionsgroup.com"
                              //+ "69.48.206.6"
                              + "/xpedio/groups/public/@"
                              + account
                              + "/documents/"
                              + docType
                              + "/"
                              + docName;

                        String itemURL
                              = urlbase
                              + "."
                              + webExt;

                        String previewURL
                              = urlbase
                              + "@t~"+rev+".jpg";

                        ItemSearchOutputBean bean = (ItemSearchOutputBean)skutable.get(docName.toUpperCase());
                        bean.setItemURL(itemURL);
                        bean.setItemPreviewURL(previewURL);
                  }
            }

Markus Fischer

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:

SELECT DISTINCT A,B,C,D
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
GROUP BY A,B

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

(°v°)

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_2484401

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

SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Computer101

Forced accept.

Computer101
Community Support Moderator