Extracting data from database to CSV

Question

I have a feature for users to be able to export the database information. But if the user choose all options to export it takes 1 minute or more to download the .csv file. I'm only including 1 part of the if statement, where I'm pulling in all the data.

here it is:

 function exportTheData() {
 //get the data for data array
 if(exportVolumeData == 1) {
 for(j=0; j<plantData1.length; j++) {
 i = plantData["MergeKey_lvl00"].indexOf(plantData1["MergeKey_lvl00"][j]);
 data.push(plantData["PlantName"][i]);
 if(statesExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 stateid = counties["StateId"][countyindex];
 statename = states["StateName"][states["StateId"].indexOf(stateid)];
 data.push(statename);
 }
 if(countyExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 countyname = counties["CountyName"][countyindex];
 data.push(countyname);
 }
 if(basinsExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 subbasinid = counties["SubBasinId"][countyindex];
 subbasinindex = basinSub["SubBasinId"].indexOf(subbasinid);
 basinid = basinSub["BasinId"][subbasinindex];
 basinindex = basin["BasinId"].indexOf(basinid);
 basinname = basin["BasinName"][basinindex];
 data.push(basinname);
 } 
 if(subBasinsExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 subbasinid = counties["SubBasinId"][countyindex];
 subbasinindex = basinSub["SubBasinId"].indexOf(subbasinid);
 subbasinname = basinSub["SubBasinName"][subbasinindex];
 data.push(subbasinname);
 } 
 if(paddsExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 subpaddid = counties["SubPaddId"][countyindex];
 subpaddindex = paddSub["SubPaddId"].indexOf(subpaddid);
 paddid = paddSub["PaddId"][subpaddindex];
 paddindex = padd["PaddId"].indexOf(paddid);
 paddname = padd["PaddName"][paddindex];
 data.push(paddname);
 } 
 if(subPaddsExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 subpaddid = counties["SubPaddId"][countyindex];
 subpaddname = paddSub["SubPaddName"][paddSub["SubPaddId"].indexOf(subpaddid)];
 data.push(subpaddname);
 } 
 if(fullNameExport == 1) {
 companyindex = getCompanyInfo["MergeKey_lvl00"].indexOf(plantData["OperatorId"][i]);
 fullname = getCompanyInfo["FullName"][companyindex];
 data.push(fullname);
 } 
 if(shortNameExport == 1) {
 companyindex = getCompanyInfo["MergeKey_lvl00"].indexOf(plantData["OperatorId"][i]);
 shortname = getCompanyInfo["ShortName"][companyindex];
 data.push(shortname);
 } 
 if(tickerExport == 1) {
 companyindex = getCompanyInfo["MergeKey_lvl00"].indexOf(plantData["OperatorId"][i]);
 ticker = getCompanyInfo["Ticker"][companyindex];
 data.push(ticker);
 }
 volumeindex = plantData1["MergeKey_lvl00"].indexOf(plantData["MergeKey_lvl00"][i]);
 startdate = plantData1["MonthStartDate"][volumeindex];
 volumetypeindex = plantData2["VolumeTypeId"].indexOf(plantData1["VolumeTypeId"][j]);
 volumetype = plantData2["VolumeType"][volumetypeindex];
 volumeunit = plantData2["Unit"][volumetypeindex];
 volume = plantData1["Volume"][volumeindex];
 data.push(startdate);
 data.push(volumetype);
 data.push(volumeunit);
 data.push(volume);
 }
 /* * Convert our data to CSV string */
 var CSVString = prepCSVRow(titles, titles.length, '');
 CSVString = prepCSVRow(data, titles.length, CSVString);
 /* * Make CSV downloadable*/
 var downloadLink = document.createElement("a");
 var blob = new Blob(["\ufeff", CSVString]);
 var url = URL.createObjectURL(blob);
 downloadLink.href = url;
 downloadLink.download = "data.csv";
 /** Actually download CSV */
 document.body.appendChild(downloadLink);
 downloadLink.click();
 document.body.removeChild(downloadLink);
 }

Here is where I convert the data array to csv string:

 function prepCSVRow(arr, columnCount, initial) {
 var row = ''; // this will hold data
 var delimeter = ','; // data slice separator, in excel it's `;`, in usual CSv it's `,`
 var newLine = '\r\n'; // newline separator for CSV row
 /* * Convert [1,2,3,4] into [[1,2], [3,4]] while count is 2
 * @param _arr {Array} - the actual array to split
 * @param _count {Number} - the amount to split
 * return {Array} - splitted array */
 function splitArray(_arr, _count) {
 var splitted = [];
 var result = [];
 _arr.forEach(function(item, idx) {
 if ((idx + 1) % _count === 0) {
 splitted.push('"' + item + '"');
 result.push(splitted);
 splitted = [];
 } else {
 splitted.push('"' + item + '"');
 }
 });
 return result;
 }
 var plainArr = splitArray(arr, columnCount);
 // don't know how to explain this
 // you just have to like follow the code
 // and you understand, it's pretty simple
 // it converts `['a', 'b', 'c']` to `a,b,c` string
 plainArr.forEach(function(arrItem) {
 arrItem.forEach(function(item, idx) {
 row += item + ((idx + 1) === arrItem.length ? '' : delimeter);
 });
 row += newLine;
 });
 return initial + row;
 } 

Any idea on how I could speed this up? We have over 6,000 rows of data in the database.

Answer 1

• Inner function splitArray() is called only once with two first parameters of outer function prepCSVRow(). This function in turn, is called twice, once with these parameters: (titles, titles.length, ''). It means that only thing splitArray() will have to do in this case, is to add quotation marks around every element of titles array and enclose titles itself in an array. This can be done with one, more efficient line:

  return [_arr.map(element => '"' + element + '"')];
  

  or

  return [_arr.map(element => \`"${element}"\`)];
  

  I suspect the first version may be more efficient.

  Add an if to check for condition in which the above can be used and voilà:

  if (_arr.length === _count) {
   return [_arr.map(element => '"' + element + '"')];
  }
  

• I love this part:

  // don't know how to explain this // you just have to like follow the code // and you understand, it's pretty simple // it converts ['a', 'b', 'c'] to a,b,c string var plainArr = splitArray(arr, columnCount); plainArr.forEach(function(arrItem) { arrItem.forEach(function(item, idx) { row += item + ((idx + 1) === arrItem.length ? '' : delimeter); }); row += newLine; });

  Especially since it can be replaced with:

  // Convert array of arrays into multiline string. Elements of inner arrays
  // are joined by delimiter. Each line represents one inner array and is
  // appended by newLine. Result is stored in variable row.
  const plainArr = splitArray(arr, columnCount);
  return plainArr.map(innerArray => innerArray.join(delimiter)).join(newLine) + newLine;
  

  row won't be needed anymore. Also, typo: it's delimiter not delimeter.

• array.push() can be even almost 15 times slower than array[array.length].

• These two lines:

  counties['CountyId'].indexOf(plantData['LocationId'][i]);
  getCompanyInfo['MergeKey_lvl00'].indexOf(plantData['OperatorId'][i]);
  

  have respectively 6 and 3 occurrences and as such results of their evaluation should be stored as constant.

• Object.property looks way better than Object['property'], especially when the latter is numerous.

I'm unable to help you not having the whole code, but it definitely should be rewritten. The method of solving your problem should be changed too, but even if it would be retained, the code should be rewritten. Here is the entire code with few changes, but I can't guarantee I didn't introduced some mistakes, since I could never run the actual code.

• lostInTheTetons: Thank you. This cut my export time down by half or more. I also used array[array.length] = .... instead of array.push. This seems to have helped speed it up as well.
• Przemek: @Teton-Coder: I'm glad it had helped! Good luck with further optimizing :).

Answer 2

Consider replacing data.push(...) with data[data.length] = ...

See this question on Stack Overflow for some of the details - the short version of it is "it's faster on some browsers".

if(statesExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 stateid = counties["StateId"][countyindex];
 statename = states["StateName"][states["StateId"].indexOf(stateid)];
 data.push(statename);
}
if(countyExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 countyname = counties["CountyName"][countyindex];
 data.push(countyname);
}
if(basinsExport == 1) {
 countyindex = counties["CountyId"].indexOf(plantData["LocationId"][i]);
 subbasinid = counties["SubBasinId"][countyindex];
 subbasinindex = basinSub["SubBasinId"].indexOf(subbasinid);
 basinid = basinSub["BasinId"][subbasinindex];
 basinindex = basin["BasinId"].indexOf(basinid);
 basinname = basin["BasinName"][basinindex];
 data.push(basinname);
}

In this section of code, you're getting the countyindex three times. Consider calling that only once and then storing that index - indexOf on a large array can be pretty expensive. The same goes for the companyindex later on.

If you really need speed, then you'll possibly have to get rid of data and work directly with a csv string. First storing everything in an array and then converting it to an array is effectively iterating over everything twice.

• lostInTheTetons: data[data.length] = .. seems to be a lot faster. Thank you!