| Description |
Replace the CSV option with a Bulk Export CSV which would use BCP. BCP is extremely fast and can export 1 million records in 15-20 seconds, which means 100k records would export in 1-2 seconds. Another option is SQLCMD but it's not as fast and has some other challenges (see pros/cons below).
We need to benchmark exporting to Excel to see how many records can be exported in 5 seconds. The idea is that we limit the formatted export options like Excel, PDF and Text to around 5-10 seconds or a certain number of records like 10,000 (we still need to determine this number). Once we pass that threshold, we would disable those 3 options and only allow the CSV option using BCP since it's the fastest way to export.
Make CSV the first option in the Export menu drop down. We want to push users to pick that option first since it's several times faster than the other options.
!pastedImage_d108991_0.png|height=121,width=124!
It would be ideal if we can do some basic date and number formatting using BCP. One option is to use the Format command in a SQL query.
*Here is an example using BCP*
{color:#800000}bcp "SELECT TOP (1000000) dtmDate,strBatchId,intAccountId,dblDebit,dblCredit,strDescription,strCode,strReference,FORMAT(dtmDateEntered, 'MM/dd/yyyy hh.mm tt', 'en-US' ) AS 'Date Entered',FORMAT(dtmTransactionDate, 'MM/dd/yyyy hh.mm tt', 'en-US' ) AS 'Transaction Date',strJournalLineDescription,ysnIsUnposted,intUserId,intEntityId,strTransactionId,strTransactionType,strTransactionForm,strModuleName,strComments FROM tblGLDetail" queryout D:\3\GLDetail_bcp.csv -t, -c -d mcpco01 -T -S STEVE-PALM\SQL2017{color}* Exported 1 million rows from MCP’s database (with some date formatting)
* Takes 13 seconds to export
*Here is an example using SQLCMD* (see pros and cons below)
{color:#800000}sqlcmd -S STEVE-PALM\SQL2017 -d mcpco01 -E -s, -W -Q "SELECT TOP (1000000) dtmDate,strBatchId,intAccountId,dblDebit,dblCredit,strDescription,strCode,strReference,FORMAT(dtmDateEntered, 'MM/dd/yyyy hh.mm tt', 'en-US' ) AS 'Date Entered',FORMAT(dtmTransactionDate, 'MM/dd/yyyy hh.mm tt', 'en-US' ) AS 'Transaction Date',strJournalLineDescription,ysnIsUnposted,intUserId,intEntityId,strTransactionId,strTransactionType,strTransactionForm,strModuleName,strComments FROM tblGLDetail" > GLDetail_sqlcmd.csv{color}* Exported 1 million rows from MCP’s database (with some date formatting)
* Takes 18 seconds to export
*BCP and SQLCMD Pros/Cons*
# BCP does not export file headers, so you have to use SQLCMD or a Union query with BCP.
# SQLCMD includes a second header line "----,----,----", so you have to use powershell/bash or something else to remove it.
# SQLCMD includes padding chars in a column, so you have to use the -W option to remove that.
# But if you have a very long text column which gets truncated, you have to use the -y/-Y option, but this contradicts #3, because -W and -y/-Y are exclusive.
# In order to extract a long column, you have to use the BCP utility, which contradicts #1.
# It seems like we have no choice but to use BCP and figure out a way to include the headers.
## BCP is faster at exporting than SQLCMD by at least 25%.
## We can include basic formatting via the FORMAT function in a SQL query.
# Another issue is how can we export the plain English headers instead of the table column names (ex: strCompanyName vs Company Name).
## Can we wrap that into the query or get it using EF or some other method?
|