从sql server中选择大文件字节时选择查询卡住 [英] Select query stucks when select large file bytes form sql server

问题描述

嗨
我在sql server中保存大文件（超过100mb）作为字节，当我尝试从sql server选择文件名及其字节时需要很长时间然后给出结果如果我在sql中运行服务器窗口。



如果我在Windows应用程序中使用该查询遇到大问题，它会停止应用程序并显示以下错误消息，请查看此链接以获取屏幕截图 [截图]



我在widows应用程序中的代码如下

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

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

请回复我从sql server下载字节的任何其他方式。



以下代码我使用保存字节作为临时文件夹中的文件

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

And following code i use save bytes as file in temp folder

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

问候

Aravind

Regards
Aravind

推荐答案

是的，它会。

您的查询：

Well yes, it will.
Your query:

select [DocumentName],[DocumentBytes]  from FileAC 

试图返回表格中的每个文件：如果它们都是> 100MB，那么这可能需要相当大的内存和一些相当大的带宽。



想一想：

1）如果SQL服务器在同一台机器上：SQL必须为每个文件分配内存，将它复制到你的应用程序，它还必须为每个文件分配内存：所以如果你有10GB的文件，可能需要30GB的内存（包括中间的缓冲区）

2）如果你的SQL服务器在不同的机器上：SQL必须为每个文件分配内存，并通过机器之间的连接传输它 - 如果这是（比方说）100Mb /秒，那么每100MB文件大约10秒。然后你的应用程序也需要分配内存。



那么 - 你有多少物理内存？

不要这样做。不要在SQL中存储大型二进制数据，将其存储在客户端PC可访问的文件中，并将文件路径存储在SQL中。并且只选择您想要的数据！

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!

您必须在 web.config app.config中增加maxRequestLength和executionTimeout。



MSDN for web.config [ ^ ]



另外，我建议你最后使用try..catch ...并在finally块中调用dispose，否则每当Open或者你都会有内存泄漏或者填写异常。对于fs.Write也一样。





如果这有帮助请花时间接受解决方案。谢谢。

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.

这篇关于从sql server中选择大文件字节时选择查询卡住的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！