there's long version of question, , short version.
the short version:
why both linq , ef slow @ inserting single, large (7 mb) record remote sql server database ?
and here's long version (with information workarounds, might useful other readers):
all of following example code does run okay, users in europe , our data centers based in america, damned slow. if run same code on virtual pc in america, runs instantly. (and no, sadly company wants keep data in-house, can't use azure, amazon cloud services, etc)
quite few of corporate apps involve reading/writing data excel sql server, , often, we'll want save raw-copy of excel file in sql server table.
this straightforward do, reading in raw data local file, , saving record.
private int savefiletosqlserver(string filename) { // read in excel file, , store in sql server [external_file] record. // // returns id of [external_file] record added. // datetime lastmodifed = system.io.file.getlastwritetime(filename); byte[] filedata = file.readallbytes(filename); // create new sql server database record, containing our file's raw data // (note: table has identity primary-key, generate extfile_id us.) external_file newfile = new external_file() { extfile_filename = system.io.path.getfilename(filename), extfile_data = filedata, extfile_last_modified = lastmodifed, update_by = "mike", update_time = datetime.utcnow }; dc.external_files.insertonsubmit(newfile); dc.submitchanges(); return newfile.extfile_id; } yup, no surprises there, , works fine.
but, noticed large excel files (7-8mb), code insert 1 (large!) record take 40-50 seconds run. put in background thread, , worked fine, but, of course, if user quit application, process killed off, cause problems.
as test, tried replace function code this:
- copy file shared directory on sql server machine
- called stored procedure read raw data (blob) same table
using method, entire process take 3-4 seconds.
if you're interested, here's stored procedure used upload file (which must stored in folder on sql server machine itself) database record:
create procedure [dbo].[uploadfiletodatabase] @localfilename nvarchar(400) begin -- far, quickest way copy file onto sql server machine, call stored -- procedure read raw data [external_file] record, , link pricing account record. -- -- exec [dbo].[uploadpricingtoolfile] 'd:\importdata\someexcelfile.xlsm' -- -- returns: -1 if went wrong (eg file didn't exist) or id of our new [external_file] record -- -- note insert go wrong, if user doesn't have "bulkadmin" rights. -- "you not have permission use bulk load statement." -- exec master..sp_addsrvrolemember @loginame = n'gpp_srv', @rolename = n'bulkadmin' -- set nocount on; declare @filename nvarchar(300), -- eg "somefilename.xlsx" (without path) @sql nvarchar(2000), @new_extfile_id int -- extract (just) filename our path+filename parameter set @filename = right(@localfilename,charindex('\',reverse(@localfilename))-1) set @sql = 'insert [external_file] ([extfile_filename], [extfile_data]) ' set @sql = @sql + 'select ''' + @filename + ''', * set @sql = @sql + ' openrowset(bulk ''' + @localfilename +''', single_blob) rs' print convert(nvarchar, getdate(), 108) + ' running: ' + @sql begin try exec (@sql) select @new_extfile_id = @@identity end try begin catch print convert(nvarchar, getdate(), 108) + ' exception occurred.' select -1 return end catch print convert(nvarchar, getdate(), 108) + ' finished.' -- return id of our new [external_file] record select @new_extfile_id end the key code builds sql command this:
insert [external_file] ([extfile_filename], [extfile_data]) select 'somefilename.xlsm', * openrowset(bulk n'd:\importdata\someexcelfile.xlsm', single_blob) rs .. and, both database , file uploaded both on same machine, runs instantly.
as said, overall, took 3-4 seconds copy file folder on sql server machine, , run stored procedure, compared 40-50 seconds same using c# code linq or ef.
exporting blob data sql server external file
and, of course, same true in opposite direction.
first, wrote c#/linq code load 1 (7mb !) database record , write binary data raw-file. took 30-40 seconds run.
but if exported sql server data file (saved on sql server machine) first..
exec master..xp_cmdshell 'bcp "select ef.extfile_data [external_file] ef extfile_id = 585" queryout "d:\importdata\someexcelfile.xslx" -t -n' ...and copied file sql server folder user's folder, once again, ran in couple of seconds.
and question: why both linq , ef bad @ inserting single large record database ?
i assume latency (distance between us, here in europe, , our data centers in states) major cause of delay, it's odd bog-standard file-copy can faster.
am missing ?
obviously, i've found walkarounds these problems, involve added permissions our sql server machines , shared folders on sql server machines, , our dbas don't granting rights things "xp_cmdshell"...
a few months later...
i had same issue again week, , tried kevin h's suggestion use bulk-insert insert large (6mb) record sql server.
using bulk-insert, took around 90 seconds insert 6mb record, though our data centre 6,000 miles away.
so, moral of story: when inserting very-large database records, avoid using regular submitchanges() command, , stick using bulk-insert.
you try using profiler see entity framework doing insert. example, if it's selecting data out of table, taking long time return data on wire, , may not notice locally.
i have found best way load large amount of data (both record count , record size) sql server c# use sqlbulkcopy class. though inserting 1 record, may still benefit change.
to use bulk copy, create datatable matches structure of table. call code this.
using (sqlconnection destinationconnection = new sqlconnection(connectionstring)) using (sqlbulkcopy bulkcopy = new sqlbulkcopy(destinationconnection)) { bulkcopy.destinationtablename = "external_file"; bulkcopy.writetoserver(datatable); } 
Comments
Post a Comment