Discussion :

[kedare]

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]

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]

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]

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]

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]

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 +