importFromResultSet with convertStringToNumber is extremely slow

[samuel.reed]

I’ve been spending some time trying to track down a performance issue in the latest version of our software, to find that it can be isolated down to this line:

rows = currentCells.importResultSet(rs,currentRow,0,true, “yyyy-mm-dd”, true);

With this line in place, exporting about 280k rows by 40 columns takes over six minutes.

But if I change convertStringToNumber to false, as below:

rows = currentCells.importResultSet(rs,currentRow,0,true, “yyyy-mm-dd”, false);

The export takes about 40 seconds.

What does this parameter do and why is it so slow?

From the API for reference:

public int importResultSet(java.sql.ResultSet rs,

int rowIndex,

int columnIndex,

boolean isFieldNameShown,

java.lang.String customDateFormatString,

boolean convertStringToNumber)

throws java.sql.SQLException

Imports data in a ResultSet object to the worksheet.

Parameters:

rs - the ResultSet object to import from.

rowIndex - start row index in the worksheet.

columnIndex - start column index in the worksheet.

isFieldNameShown - Indicates whether the field name of the resultset will be imported to the first row.

customDateFormatString - Date format string for cells which contain date value.

convertStringToNumber - Indicates if this method will try to convert string to number.

[Amjad_Sahi]

Hi,

Well, convertStringToNumber parameter in the method might not be compulsory for your situation, so you may set it to false to skip the processing. If your data is too large and you set this parameter to true, it will check each value in the fields (of your base table(s)), if any of the value that is stored as string data type (in the source table) but yet a numeric value, it will change its type to numeric (if possible). For example, you have a field named XXXX, its data type is “String”, but some of the values stored in that field are numbers e.g “123”, “234” etc. So, when you import that dataset to Excel worksheet, these would be converted as numeric values. Remember if a value in that field is “123abc”, it cannot be converted to numeric values even you set the parameter to true.

In short, if your values are already organized in your source tables with proper data types, you don’t need this parameter to set to true.

Thank you.

[samuel.reed]

Thanks, I appreciate the explanation. We have properly formatted data so we do not need the feature.

I would like to bring the performance problem to your attention though. I’m seeing about a 10x performance differential when you switch this feature on. I understand that checking if a field can be formatted as a number is an extra step that takes some time, but should it be the difference between taking 40 seconds and taking 6 minutes?

[Amjad_Sahi]

<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–>

Hi,

To parse string to number, we need to check some special formats of MS Excel, such as, currency symbol, percentage, datetime format etc., and so on. So the inner logic of this method is more complicated than checking mere numeric characters/values. so, it will take certain amount of time.

Thank you.