从varbinary（MAX）字段检索数据时的性能问题 [英] Performance issue in retrieving data from a varbinary(MAX) field

问题描述

我如何提高包含Varbinary（Max）数据类型的sq语句的性能



表结构

how i can improve the performance of the sq statement which contains Varbinary(Max) datatype

Table Structure

CREATE TABLE [dbo].[Table_1](
    [EmpID] [numeric](18, 0) NOT NULL,
    [SrNo] [numeric](18, 0) NOT NULL,
    [Type] [varchar](10) NULL,
    [FileName] [varchar](100) NULL,
    [D1] [varchar](50) NULL,
    [D2] [varchar](50) NULL,
    [Data] [varbinary](max) NULL,
    [CreatedBy] [varchar](50) NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [EmpID] ASC,
    [SrNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SELECT Data FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 2 to 3 seconds to execute because it contains blob data
SELECT FileName,Type FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 0 seconds to execute

我是什么尝试过：

What I have tried:

Checked Indexing of the Table

推荐答案

我们不知道您选择的是什么查询或者它正在尝试做什么 - 所以我们没有什么可以直接做的，以加快它，甚至建议改进。



所以我们所能做的就是指导你到性能监控和调优工具 - SQL Server | Microsoft Docs [ ^ ]并建议您开始阅读！

We have no idea what you select query is, or what it's trying to do - so there is nothing we can directly do to speed it up, or even suggest improvements.

So all we can do is direct you to the Performance Monitoring and Tuning Tools - SQL Server | Microsoft Docs[^] and suggest that you start reading!

Quote：

我有一个简单的SQL语句

SELECT Data FROM [Table_1]其中EmpID = 661和SrNo = 2

以上查询需要大约2到3秒才能执行，因为它包含blob数据

SELECT FileName，类型FROM [Table_1]其中EmpID = 661和SrNo = 2

以上查询需要大约0秒才能执行





有什么办法可以提高性能。

i have a simple SQL statement
SELECT Data FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 2 to 3 seconds to execute because it contains blob data
SELECT FileName,Type FROM [Table_1] where EmpID=661 and SrNo=2
the above Query takes about 0 seconds to execute


is there any way to improve the performance.

否。这是一个非常简单的查询 - 正在传输大量数据的时间，这需要一段有限的时间，具体取决于服务器和服务器之间的连接带宽。客户。

猜测，你是在您的数据库中存储非常大的数据，这总是一个坏主意。而是将数据存储在文件服务器上，并存储数据库中文件的链接。

检查blob大小： DATALENGTH（Transact-SQL） - SQL Server Microsoft Docs [ ^ ]我很确定它会非常大......

No. That's a very simple query - the time is being taken in the sheer amount of data being transferred, which takes a finite time depending on the bandwidth of the connection between the server and the client.
At a guess, you are storing very large data in your DB, which is always a bad idea. Instead, store the data on a file server, and store a link to the file in your DB.
Check your blob size: DATALENGTH (Transact-SQL) - SQL Server | Microsoft Docs[^] and I'm pretty sure it'll be quite large...

这篇关于从varbinary（MAX）字段检索数据时的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！