Unable to fetch data using SQL Server datalength function due to performance issues

Question

I have a documents table in my database. The document table structure is like

CREATE TABLE [dbo].[Documents]
(
    [DocumentId] [BIGINT] NOT NULL IDENTITY(1, 1),      
    [ObjectType] [VARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,       
    [ObjectId] [BIGINT] NOT NULL,       
    [DocumentName] [VARCHAR](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,        
    [DocumentFile] [VARBINARY](MAX) NOT NULL,       
    [isTemp] [BIT] NOT NULL CONSTRAINT [DF_Documents_isTemp] DEFAULT ((0)),     
    [IsActive] [BIT] NOT NULL,      
    [RowGuid] [UNIQUEIDENTIFIER] NOT NULL CONSTRAINT [DF_Documents_RowGuid] DEFAULT (newid()),      
    [CreatedBy] [BIGINT] NOT NULL,      
    [CreatedOn] [DATETIME] NOT NULL,        
    [UpdatedBy] [BIGINT] NULL,      
    [UpdatedOn] [DATETIME] NULL             
)       
GO      

-- Constraints and Indexes      
ALTER TABLE [dbo].[Documents] 
    ADD CONSTRAINT [PK_Documents] 
        PRIMARY KEY CLUSTERED ([DocumentId])        
GO      

CREATE NONCLUSTERED INDEX [ix_DocumentName] 
ON [dbo].[Documents] ([DocumentName])       
GO      

CREATE NONCLUSTERED INDEX [ix_ObjectId] 
ON [dbo].[Documents] ([ObjectId])       
GO      

CREATE NONCLUSTERED INDEX [ix_ObjectType] 
ON [dbo].[Documents] ([ObjectType])     
GO

In the documents table there are 100k records.

This is the C# method which is fetching single document on the basis of objectid.

public IQueryable<Document> GetDocumentData(long objectId, string objectType)
{
        var searchResults = new MerrickEntities().Documents.Where(c => c.ObjectId == objectId && c.ObjectType == objectType && SqlFunctions.DataLength(c.DocumentFile) > 0);
        return searchResults.AsQueryable();
}

In this query is not fetching data and it seems the issue is caused by datalength function.

How can I optimize this query?

@jdweng if i remove the datalength the result will come but i also need to check that the documentFile length.For that I have used datalength function. — Aman Mehta, May 02 '19 at 19:25
What is datatype in database? Should be binary. If it is binary then either there is not data in the column or the column name is wrong. — jdweng, May 02 '19 at 19:33
@jdweng I have mention the sql table above. The datatype is varbinary [DocumentFile] [VARBINARY](MAX) NOT NULL, . I ahve also search other articles where they have mention that DATALENGTH evaluates every row and as I have lakhs of records in table so it is taking time. — Aman Mehta, May 02 '19 at 19:37
@GarethD This link is helpful and the approach looks correct. Will try this. But also I want to ask that can I do like this. ` public IQueryable GetDocumentData(long objectId, string objectType) { var searchResults = new MerrickEntities().Documents.Where(c => c.ObjectId == objectId && c.ObjectType == objectType).ToList(); var result = searchResults.Where(c => c.DocumentFile.Length > 0); return result.AsQueryable(); } ` — Aman Mehta, May 02 '19 at 20:17
I would think that would work - you could also just chain as `.AsEnumerable().Where(...)` which should force client side evaluation of `Length` test. — NetMage, May 02 '19 at 21:03

Unable to fetch data using SQL Server datalength function due to performance issues

0 Answers0