• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

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


                  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 (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)
                              account = account.substring(0,i)+"@"+account.substring(i);
                        String urlbase
                              = "https://"
//                              + ECommLoader.getAppConfigValue(Constants.APCFG_DMS_ADDR)
                              + "dms.rapidsolutionsgroup.com"
                              //+ ""
                              + "/xpedio/groups/public/@"
                              + account
                              + "/documents/"
                              + docType
                              + "/"
                              + docName;
                        String itemURL
                              = urlbase
                              + "."
                              + webExt;
                        String previewURL
                              = urlbase
                              + "@t~"+rev+".jpg";
                        ItemSearchOutputBean bean = (ItemSearchOutputBean)skutable.get(docName.toUpperCase());
  • 2
  • 2
2 Solutions
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:


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

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

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

Dave FordSoftware Developer / Database AdministratorCommented:

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.

Dave FordSoftware Developer / Database AdministratorCommented:

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
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?

Forced accept.

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now