[Entity Framework] Performances désastreuses sur la pagination

**kedare** · 14/01/2011, 23h22

Hello,
J'ai un site interne en ASP.NET MVC2 qui utilise Entity Framework pour se connecter a une base de données SQL Server, j'ai un controleur qui contient cette méthode:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
    public class HomeController : Controller
    {
        int perPage = 100;
 
        [OutputCache(Duration = 60, VaryByParam = "None")]
        public ActionResult Index(int page = 0)
        {
            IronViperEntities db = new IronViperEntities();
            var messages = (from globalView in db.GlobalViews orderby globalView.MessageId descending select globalView).Skip(page*perPage).Take(perPage);
            ViewData["page"] = page;
            ViewData["messages"] = messages;
            return View();
        }

Voici un exemple de SQL généré:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT TOP (100) 
[Extent1].[MessageId] AS [MessageId], 
[Extent1].[MessageUuid] AS [MessageUuid], 
[Extent1].[MessageData] AS [MessageData], 
[Extent1].[MessagePostDate] AS [MessagePostDate], 
[Extent1].[ChannelName] AS [ChannelName], 
[Extent1].[UserName] AS [UserName], 
[Extent1].[UserUuid] AS [UserUuid], 
[Extent1].[ChannelUuid] AS [ChannelUuid]
FROM ( SELECT [Extent1].[MessageId] AS [MessageId], [Extent1].[MessageUuid] AS [MessageUuid], [Extent1].[MessageData] AS [MessageData], [Extent1].[MessagePostDate] AS [MessagePostDate], [Extent1].[ChannelName] AS [ChannelName], [Extent1].[UserName] AS [UserName], [Extent1].[UserUuid] AS [UserUuid], [Extent1].[ChannelUuid] AS [ChannelUuid], row_number() OVER (ORDER BY [Extent1].[MessageId] DESC) AS [row_number]
	FROM (SELECT 
      [GlobalView].[MessageId] AS [MessageId], 
      [GlobalView].[MessageUuid] AS [MessageUuid], 
      [GlobalView].[MessageData] AS [MessageData], 
      [GlobalView].[MessagePostDate] AS [MessagePostDate], 
      [GlobalView].[ChannelName] AS [ChannelName], 
      [GlobalView].[UserName] AS [UserName], 
      [GlobalView].[UserUuid] AS [UserUuid], 
      [GlobalView].[ChannelUuid] AS [ChannelUuid]
      FROM [dbo].[GlobalView] AS [GlobalView]) AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 139000
ORDER BY [Extent1].[MessageId] DESC

(Ici page 13900)

Quand je suis sur une page faible (genre par 2), tout fonctionne correctement, avec des temps de réponse correct, mais dès que je passe sur un grosse page comme ici, le temps de réponse explose (plus de 7 secondes), en consommant énormement de CPU... J'ai jamais eu ce problème avec Java et MySQL/PostgreSQL sur des volumétries identiques et impossible de trouver de solution a ce problème depuis plusieurs mois, du coup je songe sérieusement a tout migrer bien que j'aimerais éviter...

SQL Profiler ne trouve aucun problème (les indexes sont OK)
Le cout en "Logical IO" est de 7,224,307.00 et le "CPU Time" à 6,888.39ms (50% sur deux cores)
SQL Server à 1Go a lui tout seul, la base de donnée complète fait a peu prêt 750Mo, et la table ~1,400,000 lignes.
Je poste sur le forum SQL Server car je n'ai eu de réponse sur le forum Entity Framework.

Le résultat est le même sur la station de developpement (Windows 7) que sur le serveur (Windows Server 2008 R2), la version est SQL Server 2008 R2 Data Center Edition

Le query plan est disponible au format XML ici: http://pastebin.com/X09aFDfm

Des idées ?

Merci

**SQLpro** · 15/01/2011, 00h07

Il n'y a pas de miracle. Pour paginer sur un grand nombre de lignes, il faut générer toutes les lignes pour ne retenir que les lignes de la page affichée.
Un moyen d'optimiser cela est de faire vous même votre pagination en utilisant une procédure stockée, plutôt qu'une couteuse couche d'ORM...

Les ORM possédant tous les même défaut, celui de tuer les performances !

A +

**kedare** · 15/01/2011, 00h24

Pourtant j'ai jamais eu de problèmes de performance aussi énorme avec un ORM...
La lenteur de la requête me semble plus anormal qu'un problème d'ORM

**SQLpro** · 15/01/2011, 12h46

les performances dans un SGBDR ne sont pas linéaires. Avec un faible volume de données, tout est exécuté en RAM. Dès que le volume des données commence à être important les traitements ne peuvent plus se faire intégralement en RAM (sauf à l'augmenter considérablement) il y a donc pagination disque (en fait c'est l'inverse, mais peu importe, l'explication est plus simple).
L'écart intrinsèque de vitesse de lecture entre la RAM (9ns) et le disque (9 ms) est de 1 million ! En réalité avec les différents bus pour acheminer les données, cela se réduit entre 1 000 et 10 000.

Les ORM écrivent des requêtes désastreuses du fait du nivellement vers le bas des requêtes SQL (il faut s'adapter à tous les SGBDR, y compris MySQL) !

Avec la requête pissé, vous lisez 7 millions de pages à lire, soit 52 Go de données à lire !!!!

Essayez de récrire la requête à l'aide d'une fonction table comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE FUNCTION dbo.F_T_GLOBAL_VIEW (@NUMPAGE   INT, 
                                 @NB_LIGNES SMALLINT)
RETURNS TABLE
AS
RETURN (WITH T AS (SELECT MessageId,
                          MessageUuid,
                          MessageData,
                          MessagePostDate,
                          ChannelName,
                          UserName,
                          UserUuid,
                          ChannelUuid,
                          ROW_NUMBER() OVER (ORDER BY MessageId DESC) AS ORDRE
                          FROM dbo.GlobalView)
        SELECT * 
        FROM   T
        WHERE  ORDRE BETWEEN ((@NUMPAGE - 1) * @NB_LIGNES) + 1 
                         AND @NUMPAGE * @NB_LIGNES
        ORDER  BY ORDRE)

Dès lors pour aller à la 5eme page avec des pages de 16 lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT *
FROM   dbo.F_T_GLOBAL_VIEW(5, 16)

Voyez ce que cela donne !

Si cela ne suffit pas :
1) créez un index couvrant, voire une vue indexée.
2) augmentez la RAM du serveur
3) filtrez votre requête pour diminuer le nombre de lignes

A +

**kedare** · 15/01/2011, 16h20

J'ai l’impression que ca ne fonctionne pas, j'ai apporté quelques modifications (juste renommage de variables pour que tous soit en anglais) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE FUNCTION dbo.GetGlobalViewPage (@page   INT, 
                                 @lines SMALLINT)
RETURNS TABLE
AS
RETURN (WITH T AS (SELECT MessageId,
                          MessageUuid,
                          MessageData,
                          MessagePostDate,
                          ChannelName,
                          UserName,
                          UserUuid,
                          ChannelUuid,
                          ROW_NUMBER() OVER (ORDER BY MessageId DESC) AS sortOrder
                          FROM dbo.GlobalView)
        SELECT * 
        FROM   T
        WHERE  sortOrder BETWEEN ((@page - 1) * @lines) + 1 
                         AND @page * @lines
        ORDER  BY sortOrder)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Msg 1033, Level 15, State 1, Procedure GetGlobalViewPage, Line 19
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

GlobalView est une vue, ça pourrait être la cause ?
J'ai aussi augmenté la RAM allouée a SQL Server à 2 Go, mais il a pas l'air d'en avoir besoin (il dépasse pas les 900Mo de RAM consommé)

**SQLpro** · 15/01/2011, 20h56

Quelle version / Edition de SQL Server ? 2000, 2005 , 2008, 2008 R2
Edition express, standard, enterprise ?

2 Go de RAM, c'est très peu, Un SGBDR a besoin de beaucoup de RAM, car toutes les données doivent être en mémoire pour faire une requête.

A +

**kedare** · 15/01/2011, 22h17

2008 R2 Datacenter Edition 64bits (via MSDNA) sous Windows Server 2008 R2 Datacenter Edition (ou Windows 7 selon le poste, mais le résultat est le même sous les deux)

Je lui ai alloué 2Go de RAM, mais j'ai pas l'impression qu'il en ai besoin vu que la consommation de RAM plusieurs heures après la modification ne dépasse pas les 900Mo..

Il y a peut-être des paramètres a régler (façon buffer pool d'InnoDB ou shared buffer de PostgreSQL) ? J'ai cru comprendre que tout était géré automatiquement...

D'ailleurs la moindre lecture longue bloque complètement la base de données aux autres connexions... Je pensais que SQL Serveur gérais mieux que ça la concurrence o_O

**SQLpro** · 16/01/2011, 04h37

Je comprend mal : vous utilisez la version DATACENTER avec 2 Go de RAM ???
Êtes vous sur un serveur virtuel ?

La fonction que je vous ai donné a été testée chez moi et fonctionne parfaitement sous SQL Server 2008.

Êtes vous sur que la base est en 2008 ?
Vérifiez avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT compatibility_level 
FROM   sys.databases
WHERE  name = 'MaBase'

A +

**kedare** · 16/01/2011, 12h18

Envoyé par SQLpro

Je comprend mal : vous utilisez la version DATACENTER avec 2 Go de RAM ???
Êtes vous sur un serveur virtuel ?

La fonction que je vous ai donné a été testée chez moi et fonctionne parfaitement sous SQL Server 2008.

Êtes vous sur que la base est en 2008 ?
Vérifiez avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT compatibility_level 
FROM   sys.databases
WHERE  name = 'MaBase'

A +

J'ai pris la version la plus complète pour le developpement et les testes, que j'ai de toute façon gratuitement.
compatibility_level=100

**SQLpro** · 16/01/2011, 21h09

Vous avez incorporé la requête que je vous ais donné dans une fonction table. Dans ce cas il ne doit pas y avoir de clause ORDER BY !
Mais c'est de ma faute !!!

A +

**kedare** · 17/01/2011, 21h06

J'ai retiré le ORDER BY, ca fonctionne, mais le problème de performance est toujours là

D'ailleurs pendant la requête il n'y a quasiment aucune activité disque, seulement un core chargé a 100% par SQL Server.
C'est la jointure qui a l'air de demander trop de ressources..
Ajouter un SCHEMABINDING a la vue ne change rien

**SQLpro** · 18/01/2011, 00h24

Donne la description de la vue et des éventuels objets imbriqués dans cette vue (autres vues, UDF...)

A +

**kedare** · 18/01/2011, 01h10

La vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE [IronViper]
GO
 
/****** Object:  View [dbo].[GlobalView]    Script Date: 01/18/2011 00:09:45 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[GlobalView] WITH SCHEMABINDING
AS
SELECT     dbo.Messages.Id AS MessageId, dbo.Messages.Uuid AS MessageUuid, dbo.Messages.Data AS MessageData, dbo.Messages.PostDate AS MessagePostDate, 
                      dbo.Channels.Name AS ChannelName, dbo.Users.Name AS UserName, dbo.Users.Uuid AS UserUuid, dbo.Channels.Uuid AS ChannelUuid
FROM         dbo.Messages INNER JOIN
                      dbo.Users ON dbo.Messages.UserId = dbo.Users.Id INNER JOIN
                      dbo.Channels ON dbo.Messages.ChannelId = dbo.Channels.Id
 
 
GO

Les trois tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
USE [IronViper]
GO
 
/****** Object:  Table [dbo].[Messages]    Script Date: 01/18/2011 00:08:29 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Messages](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Uuid] [uniqueidentifier] NOT NULL,
	[Data] [ntext] NULL,
	[UserId] [bigint] NOT NULL,
	[ChannelId] [bigint] NOT NULL,
	[PostDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
	[Id] 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]
 
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Messages_ChannelId]    Script Date: 01/18/2011 00:08:29 ******/
CREATE NONCLUSTERED INDEX [IX_Messages_ChannelId] ON [dbo].[Messages] 
(
	[ChannelId] ASC
)
INCLUDE ( [Id],
[Uuid],
[UserId],
[PostDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Messages_UserId]    Script Date: 01/18/2011 00:08:29 ******/
CREATE NONCLUSTERED INDEX [IX_Messages_UserId] ON [dbo].[Messages] 
(
	[UserId] ASC
)
INCLUDE ( [Id],
[Uuid],
[ChannelId],
[PostDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Messages_Uuid]    Script Date: 01/18/2011 00:08:29 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Messages_Uuid] ON [dbo].[Messages] 
(
	[Uuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
/****** Object:  FullTextIndex     Script Date: 01/18/2011 00:08:29 ******/
CREATE FULLTEXT INDEX ON [dbo].[Messages](
[Data] LANGUAGE [English])
KEY INDEX [PK_Messages]ON ([IronViperFullTextCatalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
 
 
GO
 
ALTER TABLE [dbo].[Messages]  WITH CHECK ADD  CONSTRAINT [FK_Messages_ChannelId] FOREIGN KEY([ChannelId])
REFERENCES [dbo].[Channels] ([Id])
GO
 
ALTER TABLE [dbo].[Messages] CHECK CONSTRAINT [FK_Messages_ChannelId]
GO
 
ALTER TABLE [dbo].[Messages]  WITH CHECK ADD  CONSTRAINT [FK_Messages_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
 
ALTER TABLE [dbo].[Messages] CHECK CONSTRAINT [FK_Messages_UserId]
GO
 
ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [DF_Messages_Uuid]  DEFAULT (newid()) FOR [Uuid]
GO
 
ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [DF_Messages_PostDate]  DEFAULT (getdate()) FOR [PostDate]
GO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
USE [IronViper]
GO
 
/****** Object:  Table [dbo].[Channels]    Script Date: 01/18/2011 00:09:10 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Channels](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Uuid] [uniqueidentifier] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[RegistrationDate] [datetime] NOT NULL,
	[LastActivity] [datetime] NOT NULL,
	[Flags] [varchar](10) NULL,
 CONSTRAINT [PK_Channels] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Channels_Name]    Script Date: 01/18/2011 00:09:10 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Channels_Name] ON [dbo].[Channels] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Channels_Uuid]    Script Date: 01/18/2011 00:09:10 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Channels_Uuid] ON [dbo].[Channels] 
(
	[Uuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
/****** Object:  FullTextIndex     Script Date: 01/18/2011 00:09:10 ******/
CREATE FULLTEXT INDEX ON [dbo].[Channels](
[Flags] LANGUAGE [English], 
[Name] LANGUAGE [English])
KEY INDEX [PK_Channels]ON ([IronViperFullTextCatalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
 
 
GO
 
ALTER TABLE [dbo].[Channels] ADD  CONSTRAINT [DF_Channels_Uuid]  DEFAULT (newid()) FOR [Uuid]
GO
 
ALTER TABLE [dbo].[Channels] ADD  CONSTRAINT [DF_Channels_RegistrationDate]  DEFAULT (getdate()) FOR [RegistrationDate]
GO
 
ALTER TABLE [dbo].[Channels] ADD  CONSTRAINT [DF_Channels_LastActivity]  DEFAULT (getdate()) FOR [LastActivity]
GO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
USE [IronViper]
GO
 
/****** Object:  Table [dbo].[Users]    Script Date: 01/18/2011 00:09:29 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Users](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Uuid] [uniqueidentifier] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[Host] [varchar](255) NOT NULL,
	[AdminLevel] [smallint] NOT NULL,
	[LastActivity] [datetime] NOT NULL,
	[RegistrationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Users_AdminLevel]    Script Date: 01/18/2011 00:09:29 ******/
CREATE NONCLUSTERED INDEX [IX_Users_AdminLevel] ON [dbo].[Users] 
(
	[AdminLevel] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Users_Name_Host]    Script Date: 01/18/2011 00:09:29 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Users_Name_Host] ON [dbo].[Users] 
(
	[Name] ASC,
	[Host] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
USE [IronViper]
/****** Object:  Index [IX_Users_Uuid]    Script Date: 01/18/2011 00:09:29 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Users_Uuid] ON [dbo].[Users] 
(
	[Uuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
/****** Object:  FullTextIndex     Script Date: 01/18/2011 00:09:29 ******/
CREATE FULLTEXT INDEX ON [dbo].[Users](
[Host] LANGUAGE [English], 
[Name] LANGUAGE [English])
KEY INDEX [PK_Users]ON ([IronViperFullTextCatalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
 
 
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Uuid]  DEFAULT (newid()) FOR [Uuid]
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_AdminLevel]  DEFAULT ((0)) FOR [AdminLevel]
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_LastActivity]  DEFAULT (getdate()) FOR [LastActivity]
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_RegistrationDate]  DEFAULT (getdate()) FOR [RegistrationDate]
GO

**SQLpro** · 18/01/2011, 16h39

1) vous utilisez un type de données obsolète (ntext) depuis la version 2005 dans votre table Messages, pour la colonne DATA
Utilisez NVARCHAR(max).
2) vous utilisez du varchar donc codé en ASCII pour les données littérale de toutes les colonnes des tables, sauf pour la colonne DATA de la table des message codée en UNICODE. Pourquoi utiliser 2 fois plus d'octets ? Soyez cohérent. Donc VARCHAR(max)
3) le fait de mettre un CLOB (DATA) dans une vue est toujours pénalisant. Tous les LOBs (BLOBs, CLOBs, NCLOBs) ne sont, par nature, pas relationnels, ce qui oblige à de multiples opérations d'IO.
4) il est inutile d'inclure les colonnes de la clef primaire lorsque celle-cio est CLUSTERED dans un index NONCLUSTERED. Ces colonnes y sont par défaut comme repère de ligne de table.

En conclusion, supprimez ce blob de la définition de votre vue et vous y gagnerez en performances !!!!!
Les blobs ne sont pas fait pour faire des opérations relationnelles, mais juste pour l'affichage !

Remaniez le type de données et virez les colonnes inutiles de vos index

PS : je pense que vous auriez besoin d'un sérieux cours sur l'optimisation de SQL Server...

A +

**aieeeuuuuu** · 18/01/2011, 16h58

Bonjour,

De plus, tu utilises le type BIGINT pour tes clefs primaires...

Comptes-tu vraiment avoir plus de 2 147 483 647 utilisateurs sur ton site ?

Je pense qu'un INT fera largement l'affaire, et tes jointures seront d'autant plus rapides...

**SQLpro** · 18/01/2011, 17h43

Attention, il est en 64 bits, donc le type BIGINT est parfaitement justifié car le INT est très légèrement moins performant en 64 bits et inversement pour OS 32 bits ....

A +

**kedare** · 18/01/2011, 17h49

Envoyé par SQLpro

En conclusion, supprimez ce blob de la définition de votre vue et vous y gagnerez en performances !!!!!
Les blobs ne sont pas fait pour faire des opérations relationnelles, mais juste pour l'affichage !

Oui mais si j'ai besoin de les récupérer (les blob) ?

**SQLpro** · 18/01/2011, 17h52

Faites une requête lien d'affichage dans l'IHM.
Vous n'allez tout de même pas afficher une tableau de 65465346132 message de 2 Go dans une grille ?

A +

**kedare** · 18/01/2011, 18h09

Envoyé par SQLpro

Faites une requête lien d'affichage dans l'IHM.
Vous n'allez tout de même pas afficher une tableau de 65465346132 message de 2 Go dans une grille ?

A +

Non, c'est une liste sur une interface web qui affiche par 100 lignes (y comprit la colonne data).

Supprimer le blob (que j'ai transformé en nvarchar) de la vue ne change rien non plus

J'ai aussi supprimé les colonnes des clés redondantes.

**SQLpro** · 18/01/2011, 18h27

Tu affiche une colonne BLOB dans une grille de 100 lignes pour des messages de deux milliards de caractères ???

D'autre part tu n'as pas répondu s'"il s'agissait d'un serveur virtuel...

A +

[Entity Framework] Performances désastreuses sur la pagination

Développement SQL Server

Discussions similaires

Partager

Partager