Select query stucks when select large file bytes form sql server

Question

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 27-Nov-14 22:07pm

Aravindba

Updated 27-Nov-14 22:43pm

v3

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2014-11-27T22:17:00

Solution 2

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!

Posted 27-Nov-14 22:17pm

OriginalGriff

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!

Sinisa Hajnal · Answer 2 · 2014-11-27T22:13:00

Solution 1

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.

Posted 27-Nov-14 22:13pm

Sinisa Hajnal

Updated 27-Nov-14 22:14pm

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:(