troubleshooting Question

How to tune the following function to be faster?

Avatar of Peter Kwan
Peter KwanFlag for Hong Kong asked on
Microsoft SQL ServerJavaJava EESQL
8 Comments1 Solution145 ViewsLast Modified:
Hi,  I have a J2EE application in which one function is to retrieve all author names in the database and their corresponding number of strokes in  Chinese.  The application is to be run on JBoss application server 7.1.

I have designed the following SQL tables:

BIOGRAPHY (Storing real name and display name (which equals either real name or one of the pen name of the author) in Traditional Chinese)
AUTHOR_ID, REAL_NAME, DISPLAY_NAME, SHOW_REAL_NAME, IS_DELETED, <other fields>

BIO_PENNAME (Storing pen names of the author in Traditional Chinese)
AUTHOR_ID, PEN_NAME, DISPLAY_NAME, DISPLAY_IN_AUTHOR_LIST

UNICODE_CHAR_STROKE_MAP (Storing all the unicode characters and their corresponding number of strokes)
UNI_CHAR, STROKE_NO

Below please find our program that performs the retrieval of author name list:
	public List<AuthorName> getAuthorNameList(String lang) throws SQLException {
		List<AuthorName> nameList = new ArrayList<AuthorName>();
		
		PreparedStatement stmt = connection.prepareStatement("SELECT AUTHOR_ID, REAL_NAME AS NAME, DISPLAY_NAME, SHOW_REAL_NAME AS SHOW_NAME FROM PUBLISH_BIOGRAPHY WHERE IS_DELETED = 0 UNION "
				+ "SELECT N.AUTHOR_ID, N.PENNAME AS NAME, B.DISPLAY_NAME, N.DISPLAY_IN_AUTHOR_LIST AS SHOW_NAME FROM PUBLISH_BIO_PENNAME N, PUBLISH_BIOGRAPHY B "
				+ "WHERE N.AUTHOR_ID = B.AUTHOR_ID AND B.IS_DELETED = 0");
		ResultSet rs = stmt.executeQuery();
		
		while (rs.next()) {
			AuthorName authorName = new AuthorName();
			authorName.setAuthorID(rs.getString("AUTHOR_ID"));
			
			String name = rs.getString("NAME");
			String displayName = rs.getString("DISPLAY_NAME");
			boolean isShowName = rs.getBoolean("SHOW_NAME");
			int strokeNo = -1;
			
			if (AppConstants.LANG_SIMPLIFIED_CHINESE.equals(lang)) {
				name = StringUtil.convertToSimplified(name);
				displayName = StringUtil.convertToSimplified(displayName);
			}

/****  Getting Stroke number ******/
			PreparedStatement stmt2 = connection.prepareStatement("SELECT STROKE_NO FROM UNICODE_CHAR_STROKE_MAP WHERE UNI_CHAR = ?");
			stmt2.setString(1, name.substring(0, 1));
			ResultSet rs2 = stmt2.executeQuery();
			if (rs2.next())
				strokeNo = rs2.getInt("STROKE_NO");
			rs2.close();
			stmt2.close(); 
/******* End getting stroke number ******/

			authorName.setName(name);
			authorName.setDisplayName(displayName);
			authorName.setShowName(isShowName);
			authorName.setNoStroke(strokeNo);
			nameList.add(authorName);
		}
		rs.close();
		stmt.close();
		
		final Map<Integer, Integer> chineseStrokeMap = getUnicodeCharMap();
		
		Collections.sort(nameList, new Comparator<AuthorName>() {

			@Override
			public int compare(AuthorName n1, AuthorName n2) {
				// TODO Auto-generated method stub
				return StringUtil.compareString(n1.getName(), n2.getName(), chineseStrokeMap);
			}
			
		});
		
		return nameList;
	}

Open in new window

Since our application will need to display either Traditional Chinese or Simplified Chinese whenever the public user wants to click on  a button to change the language. If the user selects "Simplified Chinese", then the author names will be converted into Simplified Chinese using Java library and their corresponding stroke number will be returned.

I have tried to check the code and found that the bottleneck is the above code.  It takes around 4 second to return (around 3000 records) which is not acceptable.  However, if I commented out the section "Getting stroke number", results returns within 300ms, but it does not contain the stroke number information which is crucial.

May I know how can I modify this code to make the retrieval to return results within 1 second? Please note that we need the stroke number of the author names that are in corresponding language (Simplified / Traditional Chinese), which means that some characters may have different stroke number before and after converting the author names in Traditional Chinese to Simplified Chinese.

Our database is SQL Server 2008 R2 and using Java 6 (JBoss AS 7.1.1).

Grateful if someone can help.  Thank you.
ASKER CERTIFIED SOLUTION
Chris Luttrell
Senior Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004