Click here to Skip to main content
15,937,198 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I am save large size file(more than 100mb) as bytes in sql server,when i try to select document name and its bytes from sql server it take long time then give result if i run in sql server window.

If i use that query inside windows application got big problem,it stop the application and it show following error message,pls look this link for screenshot [screenshot]

My code in widows application as below

VB
Dim facons As New SqlConnection(objCI.FTClientConnection1())
Dim fadrs As New SqlDataAdapter("select [DocumentName],[DocumentBytes]  from FileAC where [DocumentName]='sample.pdf' ", facons)
Dim fadts As New DataTable
facons.Open()
fadrs.Fill(fadts) 'Here code stopped and show time out expiary
facons.Close()
facons.Dispose()
fadrs.Dispose()


Pls reply me any other way to download bytes form sql server.

And following code i use save bytes as file in temp folder
VB
Dim buffer As Byte()
Dim aspfile as String="D:\Temp\sample.pdf"
buffer = fadts(0)("DocumentBytes")
Dim fs As FileStream = File.Create(aspfile)
fs.Write(buffer, 0, buffer.Length)
fs.Close()
fs.Dispose()
buffer = Nothing


Regards
Aravind
Posted
Updated 27-Nov-14 22:43pm
v3
Comments
Shweta N Mishra 28-Nov-14 4:14am    
which SQL version you are using ?
Aravindba 28-Nov-14 4:17am    
sql server 2012 management studio

Well yes, it will.
Your query:
SQL
select [DocumentName],[DocumentBytes]  from FileAC 

Is trying to return every single file in the table: and if they are all ">100MB" then that can take some considerable memory, and some considerable bandwidth.

Think about it:
1) If the SQL server is on the same machine: SQL must allocate memory for each file, copy it to your app, which must also allocate memory for each file: so if you have 10GB of files, probably 30GB of memory is needed (including the buffer in the middle)
2) If your SQL server is on a different machine: SQL must allocate memory for each file, and transfer it via the connection between the machines - if this is (say) 100Mb/sec, that's around 10 seconds per 100MB file. Then your app needs to allocate the memory as well.

So - how much physical memory do you have?
Don't do it. Don't store large binary data in SQL, store it in files somewhere that is accessible to your client PCs, and store the filepath in SQL. And only SELECT the data you actually want!
 
Share this answer
 
Comments
Sinisa Hajnal 28-Nov-14 4:21am    
Much better then mine! :)
Aravindba 28-Nov-14 4:40am    
I forgot to type where condition,actually i am select only one file,it contains large amount bytes( actual size of the file is 100 mb and more),in my Sql server install folder (C Drive ) got 30 GB Free.

And thank you for ur reply,pls when u reply take important thing,this project is not a final year project,this Document Management System and used in big companies,like more than 1000 of people working in a company,they save file using Document Management System and files are stored in main server,if user need file ,need to retrieve as bytes and show in window,so cant save file in any local drive.
And one thing we have web application ,we retrieve file and show in web application also with same database,i mean we have Document Management System in Web and windows.
Note:In web application no problem,file open in web application,but same file open in windows application got problem.
OriginalGriff 28-Nov-14 4:48am    
30GB free in SQL folder isn't the same as physical memory: when you run out of "real" memory it'll get paged to the server HDD and that slows things down horribly.
So it'll get worse, not better.
Trust me: if you have a number of users trying to retrieve 100MB files directly through SQL, it's going to start crawling like a slug!

Seriously: don't store the docs inside the SQL DB: let the file system take care of them instead. Otehrwise all you are doing is ladding another layer of abstraction and another application that needs memory to store the data.
And...it's going to make SQL backups really, really slow and difficult.
Storing files on a different machine lets SQL do what it's good at, and allows a degree of scalability that you can't get cheaply from SQL.

I know it's easier for your code to store it in SQL, but that doesn't mean it's a good idea!
Aravindba 28-Nov-14 5:00am    
Ok,if store file in folder in server and store file path in sql server,if anybody accidentally delete that file ,then what happen ? sometimes server maintenance that time reformat system then what happen ? if store in sql server easily get backup before format and restore anytime.And we enable FileStream for that database.

Note:100 mb file can retrive and show in web application using Flex paper viewer,but same file same query used in windows application and need to show in pdfxchnage viewer got problem,only for 70 and more mb files
OriginalGriff 28-Nov-14 5:09am    
A folder full of individual files which total 30GB is a lot, lot easier and quicker to back up than a single file that is 30GB and is accessed and modified continually by 1000 users!
And don't forget: you can have different user access with different access permissions: "standard users" have read only access to the shared folder, update software uses a different user who has better permissions. Exactly as you would have to do for your SQL user control!
You have to increase maxRequestLength and executionTimeout in your web.config app.config.

MSDN for web.config[^]

Also, I recommend you use try..catch...finally and call dispose in finally block or you'll have memory leak whenever Open or Fill throw an exception. Same for fs.Write.


If this helps please take time to accept the solution. Thank you.
 
Share this answer
 
v2
Comments
Aravindba 28-Nov-14 4:19am    
I am ask in windows application,not in asp.net,u strick web.config but MSDN link show for web.config.we are use asp.net also and set that values,no problem in asp.net,in windows application ?
Sinisa Hajnal 28-Nov-14 4:20am    
Sorry, too quick to answer :(
Aravindba 28-Nov-14 4:28am    
its okay:(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900