Logo SQLinForm

 

[solved] Performance issue

[solved] Performance issue
July 16, 2015 05:01PM
We are using sqlinformpro-4.8.49.jar to format SQL statements here at Guidewire. I believe we bought a lifetime license. When we have a lot of statements to format (10-20,000) in a batch job, the job runs a long time, and when I take a thread dump, it is in SQLForm.formatSQL(). Is there a newer version available with better performance? Is there any technique for performance we can use to speed this up?



Edited 1 time(s). Last edit at 06/13/2016 11:15PM by guidomarcel.
Re: Performance issue
July 16, 2015 10:44PM
Hi,
the performance will significantly increase when you uncheck the "display colored SQL".

Using one of my 20,000 line SQL, it gets formatted in color in 7 secinds and without coloring in 3 seconds.

If this does not help, could you please send me the big SQL to <<>> so that I can monitor the performance. MAybe it is related to one specific parsing rule

Regards
Guido

PS: Did you receive my newsletter regarding the new release 5.0?
Re: Performance issue
July 16, 2015 11:29PM
I just signed up for the newsletter today. We don't have a large SQL statements (20-50 lines each formatted), but between 10,000 and 20,000 statements to be formatted, so we want the formatting to be as efficient as possible. This is happening within a batch job. Should I try the latest version? We are using version sqlinformpro-4.8.49.jar. I think the basic way we are using it is through

public String printText(String sql, boolean html) {
SQLForm sqlForm = createSQLForm(sql);
String text;
try {
if (html) {
StyledDocument styledDocument = createCustomStyledDocument();
sqlForm.getColoredAndFormattedSQLinDocument(styledDocument);
text = sqlForm.getHtmlText(styledDocument);
} else {
text = sqlForm.getFormattedSQLinString();
}
} catch (Exception e) {
e.printStackTrace();
return sql;
}
return text;
}
Re: Performance issue
July 17, 2015 12:27AM
How do I get the latest version? As I said, we currently have sqlinformpro-4.8.49.jar. You should be able to verify Guidewire Software has a corporate license. I think you dealt with Tim Lockwood back when.
Guido
Re: Performance issue
July 17, 2015 01:05AM
Sorry for the typos
Will send you a mail tomorrow with more info
Regards
Guido
Re: Performance issue
July 21, 2015 12:32AM
I received the instructions for downloading the latest version. Thanks. I am having trouble using your suggestion to avoid color coding:

niceSQL = new SQLForm() ;
niceSQL.formatSQL(“Select a from b where c = d”);
String formattedSQL = niceSQL.getFormattedSQLinString();

I modified out code by commenting out lines relating to color:

public class SQLInFormPrettyPrinter implements SQLPrettyPrinter{
private SQLForm createSQLForm(String sql) {
final SQLForm sqlForm = new SQLForm();
sqlForm.setSpacesPerLevelDynamic(true);
sqlForm.setIndention(2, false);

// Prettify
// sqlForm.setMasterKeywordColor(Color.RED.getRGB());
// sqlForm.setLineCommentColor(Color.GRAY.getRGB());
// sqlForm.setBlockCommentColor(Color.GRAY.getRGB());
// sqlForm.setCASEColor(Color.MAGENTA.getRGB());
// sqlForm.setLiteralColor(Color.DARK_GRAY.getRGB());
// sqlForm.setReservedWordColor(Color.BLUE.getRGB());

sqlForm.formatSQL(sql);
return sqlForm;
}

@Override
public String printText(String sql, boolean html) {
SQLForm sqlForm = createSQLForm(sql);
String text;
try {
if (html) {
// StyledDocument styledDocument = createCustomStyledDocument();
// sqlForm.getColoredAndFormattedSQLinDocument(styledDocument);
text = sqlForm.getHtmlText(null);
} else {
text = sqlForm.getFormattedSQLinString();
}
} catch (Exception e) {
e.printStackTrace();
return sql;
}
return text;
}

We are calling printText(sqlString, true), since we always format for an HTML document. Suggestions?
Re: Performance issue
July 23, 2015 11:11PM
The new version 5.0.1 works beautifully; it really cuts back on the time. Thanks again.
Author:

Your Email (Optional):


Subject:


Spam prevention:
Please, solve the mathematical question and enter the answer in the input field below. This is for blocking bots that try to post this form automatically.
Question: how much is 22 plus 12?
Message: