2

A table with 20 columns (ints, bools and varchar(50)'s) retrieve is very slow because of one column with the type of varchar(max).

The table has 680 rows, and i 'm retrieving max 5 or 6 results.

For some reason the select statement is very slow if the result contains a column with type varchar(MAX).

SELECT     Id, Title, Description, FieldType, FieldName, DynamicMenuId, RelatedDynamicMenuId, RelatedDynamicMenuField, RelatedDynamicMenuFieldDisplay, 
                  CustomTableId, Position, ShowInGrid, AllowSelection, DefaultValue, DocumentUploadPath, RelatedWhereClause, RequiresSearch, IsRequired, DataType, 
                  FieldGroupId
FROM         dbo.SMDynamicMenuField
WHERE     (DynamicMenuId = 55)

FYI; We have moved the contents (schema & data) of the table from one server to another using a scriptfile.

Any idea's how this is possible?

// Inserting more information. Ok, the plot thickens.

Created indexes, but it doesn't make any (noticeable) performance boost.

I'm getting it on more databases (same server) now. Take the following query from the profiler (query from entity framework):

exec sp_executesql N'SELECT 
[Project1].[Id] AS [Id], 
[Project1].[AppartmentId] AS [AppartmentId], 
[Project1].[ImageTitle] AS [ImageTitle], 
[Project1].[ImageDescription] AS [ImageDescription], 
[Project1].[ImageFile] AS [ImageFile], 
[Project1].[Position] AS [Position]
FROM ( SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[AppartmentId] AS [AppartmentId], 
[Extent1].[ImageTitle] AS [ImageTitle], 
[Extent1].[ImageDescription] AS [ImageDescription], 
[Extent1].[ImageFile] AS [ImageFile], 
[Extent1].[Position] AS [Position]
FROM [dbo].[AppartmentImage] AS [Extent1]
WHERE [Extent1].[AppartmentId] = @p__linq__0
)  AS [Project1]
ORDER BY [Project1].[Position] ASC',N'@p__linq__0 int',@p__linq__0=56
go

This sometimes increases loading time. No matter the amount of records, it's always increases around 16-18 seconds loading time.

The complete sql server is not handling any requests (to other databases too), so it causes all the sites to block and wait untill this operation is done, and then continues.

Almost like it's filling memory with each request!

Server specs are fair enough; webserver 8gigs ram, sql has the same. Currently hosting about 20 sites on IIS7 (all .net MVC using EF 5)

stephan
  • 61
  • 8
  • Have you also scripted the indexes of the table? – Radu Gheorghiu May 22 '15 at 12:34
  • What does the execution plan show you? – Zohar Peled May 22 '15 at 12:34
  • Kind of related: http://stackoverflow.com/questions/1701808/should-i-use-an-inline-varcharmax-column-or-store-it-in-a-separate-table – Mackan May 22 '15 at 12:44
  • Also a duplicate, but without accepted answer: http://stackoverflow.com/questions/10707773/slow-select-query-with-varcharmax Getting a bit interesting :o – Mackan May 22 '15 at 12:53
  • `varchar(max)` screams SQL Server, so I added the tag. Also, please quantify "very slow" (how much slower). – Gordon Linoff May 22 '15 at 13:35
  • For 680 rows it should be fast no matter of index presence. – Giorgi Nakeuri May 22 '15 at 13:45
  • entity framework? Are you viewing this in an application? have you considered that the app is the issue? You need to that SQL seperate from the app and see how fast it is. – Nick.Mc May 22 '15 at 13:52
  • I think the problem is more related to the "sort" required by "order by". When you have varchar(max), the sort can be done on disk which is slow. Try get rid of the subquery (and create index on position) or limit its result. – Tim3880 May 22 '15 at 14:19
  • @Tim3880 looking at the originally posted query, there is no order by or subquery. This must be some deeper issue – Mackan May 22 '15 at 14:25
  • i was referring to the second query he took from sql profiler. – Tim3880 May 22 '15 at 14:31
  • OP: Is the first query the **actual** offending query, or just an example? You posted two quite different queries, and the second can have many reasons to be slow. Not the first though. – Mackan May 22 '15 at 14:35
  • It's a different query, but again, it contains a column varchar(max) causing the same behaviour. – stephan May 22 '15 at 15:01
  • Adding more memory to the sql server did fix the issue. We had 3.2 gb in use from 4 gb. Adding another 4 gb fixed it. I'm still amazed how much impact this varchar(max) had on this server. We have never experienced this before on a sqlserver serving 20 sites. – stephan May 26 '15 at 11:21

0 Answers0