x

NVarchar(Max) Slow Query Performance

Hi there,

I have a table with 15 columns and 15000 rows of data, however, whenever I try to query it it takes about 10 minutes to return a result. I am assuming that this has to do with the NVarchar(max) column. Is there anyway to speed up the performance of this query?

TIA

more ▼

asked Mar 17 at 04:00 PM in Default

avatar image

David 2 1
782 54 60 67

Kinda depends on what the query is, what the table structure is, and what the data looks like (particularly indexes & statistics on those indexes). And the quality of the storage, the speed of the server, the traffic on the network, whether the prevailing wind is in the south...

I think what I'm getting at here is - "it depends." and "we need more information."

Mar 17 at 04:15 PM ThomasRushton ♦♦

Added my comment below.

Mar 17 at 04:44 PM David 2 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I don't have access to the table right now as it's an issue I noticed the other day on a clients estate. However, the statistics are up to date and there is a clustered index on the ID column. There are no covering indexes. What I'm really asking is if I create a nonclustered index like below then would it improve performance for the client. I don't have access to test this at present.

 CREATE NONCLUSTERED INDEX IDX02 ON Test(ID) INCLUDE(Col1,Col2,Col3,Col4...,Col15)

Or would a Full-Text index work better?

Edit--- Thinking through the issue I believe I may have come up with a solution. The NVarchar(max) column basically stores pdf's internally in the database. If I turn turn on FILESTREAM and move the pdf's out of the table onto the file system the query performance will improve?

more ▼

answered Mar 17 at 04:43 PM

avatar image

David 2 1
782 54 60 67

how are you querying the table? Are you filtering on the ID? And typically how many of the 15000 rows are you trying to return? To be honest returning all 15 columns for the whole 15000 should not take 10 mins. It feels like a network bottleneck, where each row has used that nvarchar(max) to it's capacity (i.e. 2GB)

Mar 17 at 05:12 PM Kev Riley ♦♦

I queried the clients table using select * then I looked at the estimated execution plan and noticed that it was doing a full table scan. The NVarchar(max) column contains file data so is very large. Unfortunately I didn't get the chance to do a max(len(col15)). There's also a computed column of the NVarchar(max) column doing a convert(). At present I've no idea why they are doing that convert. How could I test to see if it's network related? I'm querying directly on the server.

Mar 17 at 05:25 PM David 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407
x297
x149
x12

asked: Mar 17 at 04:00 PM

Seen: 69 times

Last Updated: Mar 19 at 12:10 AM

Copyright 2016 Redgate Software. Privacy Policy