Pour des raisons de performance il peut être utile d’utiliser des procédures stockées dans entity framework (par exemple si on connait l’id de l’entité et que l’on veut mettre à jour son contenu sans remonter l’entité).
Pour notre exemple, voici un script qui ajoute une table ‘Contenu’ et une procédure stockée ‘MajContenu’ qui met à jour le contenu selon l’id donné.
Cette procédure stockée renvoie le nombre de lignes mise à jour.
Dans le code si aucune ou plus d’une ligne a été mise à jour on renvoie une exception.
Création de la table Contenu :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contenu](
[IdContenu] [uniqueidentifier] NOT NULL,
[Contenu] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Contenu] PRIMARY KEY CLUSTERED
(
[IdContenu] 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
ALTER TABLE [dbo].[Contenu] ADD CONSTRAINT [DF_Contenu_IdContenu] DEFAULT (newid()) FOR [IdContenu]
GO
Création de la procédure stockée MajContenu:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MajContenu](
-- Add the parameters for the stored procedure here,
@IdContenu uniqueidentifier,
@Contenu nvarchar(max))
AS
BEGIN
SET NOCOUNT ON;
update Contenu set Contenu = @Contenu
where IdContenu = @IdContenu;
select @@RowCount;
END
GO
Clic droit sur l’edmx, mettre à jour le modéle à partir de la base de données et ajoutez la table Contenu et la procédure stockée MajContenu :
Seul la table apparait, pour utiliser la procédure stockée il faut l’ajouter à l’entity container.
Il faut accéder à l’explorateur de modele, clic droit sur l’edmx ‘Explorateur de modele’ :
Recherchez la procédure stockée et ajoutez une importation de fonction :
Reste à choisir un type de retour de la collection, on peut choisir les types classiques entier/string, on peut aussi choisir de retourner une entité ou un type complexe.
Dans notre cas, notre procédure stockée retourne un entier:
Reste à utiliser la procédure stockée dans notre code (ce qui est beaucoup plus facile qu’avec un sqlcommand) :
public void MajContenu(string contenu, Guid contenuId)
{
using (testEntities entity = new testEntities())
{
int? resu = entity.MajContenu(contenuId, contenu).FirstOrDefault();
if (!resu.HasValue || resu.Value != 1)
{
throw new Exception("Erreur la procédure stockée MajContenu a modifié " + resu.Value + " enregistrements pour l'id " + contenuId);
}
}
}
Utiliser Entity Framework est dans ce cas un vrai délice intuitif, Facile à utiliser et à comprendre, difficile de s’en passer. Enjoy…
Aujourd’hui j’ai encore eu affaire à un double load d’une même page à chaque accés sur cette page, la cause de ce double load :
<asp:Image runat="server" /> ou <asp:ImageButton runat="server">
qui se transforme en :
<img src="" />
Pourquoi ces dysfonctionnements? D’aprés les normes rfc, les navigateurs doivent remplacer les urls vides des images par l’url de base : http://www.ietf.org/rfc/rfc1808.txt, Section 4, Step 2, a).
Là où ça se complique, les navigateurs ont des comportements différents quand ils rencontrent img src=''
- Quand on interroge ie si l’image est placée sur une url de type ‘http://toto.com/toto/mapage.html’ la requete sera effectuée sur le repertoire dans lequel la page est executée : ‘http://toto.com/toto/’
- Safari et chrome font une requête sur la page en cours
- Opera ne fait aucune requête
- Firefox 3 et avant ont le même comportement que Safari et Chrome, Firefox 3.5 ignore comme Opéra la requête.
Le double postback entraine une surcharge de requêtes inutiles pour le serveur, on passe ainsi d’1 page visitée à 2, de 10000 à 20000, de plus l’internaute réexecute une requete serveur et doit attendre le chargement de l’image pour voir sa page.
Enfin même si la requête ne renvoie pas d’affichage, les cookies et autres informations du header sont quand même envoyés à l’internaute, ce qui peut provoquer pas mal de dysfonctionnements.
Donc sur un lien pas de probleme il faut cliquer dessus pour éxecuter la page, mais sur une image la page est réexecuter pour afficher le résultat. même si l’image est cachée via javascript (dans un div en display none) le code est quand même réexecuté. Le probleme concerne les images et les balises :
<script scr='' />
<link href='' />
<input type='image' src='' />
concernant ces balises, les navigateurs ont aussi des comportements différents :
- pour la balise input type=’image’ les navigateurs ont le même comportement que pour la balise img
- pour les balise script et link, seul firefox 3 et avant, Safari et Chrome font une requête, les autres ne prennent pas la balise en compte.
Pour remédier au probleme, la solution la plus simple est de pointer par défaut sur une image transparente d’1pixel sur 1 pixel
Attention aussi aux controles externes comme l’AJAX DropDown extender si vous n’utilisez pas l’attribut DropArrowImageUrl alors il génére un src vide pensez du coup à y mettre l’image vide…
Bon courage.
Sources :
http://www.nczonline.net/blog/2009/11/30/empty-image-src-can-destroy-your-site/
http://geekswithblogs.net/bcaraway/archive/2007/08/24/114945.aspx
5 janvier 2011| catégorie:
ASP.NET,
UI | tags:
.net,
asp:image,
asp:imagebutton,
c#,
img,
javascript,
Lille,
limit,
maxlength,
postback,
splist,
src,
ui,
webcontrol |
pas de commentaire
Testez en Téléchargeant le projet : TextboxMultilineWithMaxLength (43)
Bizarrement la propriété maxlength est inactive si le mode de la textbox est multiline.
Pour y remedier nous allons donc créer un composant, ce composant devra être facile à intégrer dans un projet et techniquement sans faille, pour cela nous allons intégrer ce composant appelé webcontrol à une bibliotheque de classe UI.
Créez une bilbiothéque de classe et ajoutez un fichier c#, appelez le TextBoxWithMaxLength cette classe hérite de la classe TextBox :
[ToolboxData(@"<{0}:TextBoxWithMaxLength runat=""server"" \>")]
public class TextBoxWithMaxLength : TextBox
L’instruction toolboxdata permet de spécifier le tag par défaut quand on fait un drag/drop à partir de la toolbox.
Ce webcontrol utilisera un javascript pour limiter le nombre de caractères tappé, j’utilise le formFieldLimiter : http://www.dynamicdrive.com/dynamicindex16/limitinput.htm
j’ai ajouté quelques modifications à ce javascript pour utiliser le ctrl+a, ctrl+c quand on arrive à la fin de la textbox, testé sur ie6/7/8/Firefox.
Ligne 9 : uncheckedkeycodes: /(8)|(37)|(38)|(39)|(40)|(46)/,
Ligne 15 : var keyunicode=(e.keyCode ? e.keyCode : e.which);
Ligne 17 : if (!this.uncheckedkeycodes.test(keyunicode) && !e.ctrlKey)
ajoutez les fonctions javascript dans un fichier js : formFieldLimiter.js, ajoutez ce fichier à la bilbitohèque de classe, dans les propriétés du fichier : Build Action => Embedded Resource.
appel de la fonction dans TextBoxWithMaxLength
[assembly: WebResource("Test.UI.formFieldLimiter.js", "text/javascript")]
namespace Test.UI
{
[ToolboxData(@"<{0}:TextBoxWithMaxLength runat=""server"" \>")]
public class TextBoxWithMaxLength : TextBox
{
#region CONSTANTES
public const string JsRessource = "Test.UI.formFieldLimiter.js";
#endregion
#region EVENTS
/// <summary>
/// Pre render
/// </summary>
/// <param name="e"></param>
protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
if (MaxLength > 0)
{
ClientScriptManager cs = this.Page.ClientScript;
cs.RegisterClientScriptResource(typeof(Test.UI.TextBoxWithMaxLength), JsRessource);
}
}
/// <summary>
/// Render
/// </summary>
/// <param name="e"></param>
protected override void Render(HtmlTextWriter writer)
{
base.Render(writer);
if (MaxLength > 0)
{
string appelJs = @"
<script type='text/javascript' language='javascript' style='display:none;' >
fieldlimiter.setup({
thefield: document.getElementById('" + this.ClientID + @"'),
maxlength: " + this.MaxLength.ToString() + @",
statusids: []});
</script>";
new Literal() { Text = appelJs }.RenderControl(writer);
}
}
#endregion
Dans le PreRender, l’appel au ClientScriptManager permet de s’assurer qu’il n’y aura qu’une déclaration des fonctions sur une page même si plusieurs contrôles sont utilisés dans une même page.
Dans le Render, on appel la fonction setup du formlimiter pour notre textbox.
Mais si le client désactive le javascript notre fonction de vérification ne sert plus à rien, nous allons la renforcer en obligeant la déclaration d’un RegularExpressionValidator
Déclaration de la propriété RegularExpressionValidatorID :
#region PROPERTIES
/// <summary>
/// Id de RegularExpressionValidator lié à la Textbox
/// </summary>
[BindableAttribute(true)]
[IDReferencePropertyAttribute(typeof(RegularExpressionValidator))]
[DescriptionAttribute("l'id du RegularExpressionValidator")]
public string RegularExpressionValidatorID
{
get
{
return (string)this.ViewState["RegularExpressionValidatorID"] ?? String.Empty;
}
set
{
this.ViewState["RegularExpressionValidatorID"] = value;
}
}
#endregion
Modification de la méthode PreRender pour vérifier que le regularExpressionValidator est bien paramétré et ajout de l’expression de validation ‘^[\s\S]{0,LeMaxLengthDeLaTextBox}$’ :
/// <summary>
/// Pre render
/// </summary>
/// <param name="e"></param>
protected override void OnPreRender(EventArgs e)
{
base.OnPreRender(e);
if (MaxLength > 0)
{
if (string.IsNullOrEmpty(RegularExpressionValidatorID) ||
!(FindControl(this.RegularExpressionValidatorID) is RegularExpressionValidator) ||
((RegularExpressionValidator)FindControl(this.RegularExpressionValidatorID)).ControlToValidate != this.ID)
{
throw new Exception("Error the RegularExpressionValidator must be filled and the ControlToValidate must be " + this.ClientID);
}
((RegularExpressionValidator)FindControl(this.RegularExpressionValidatorID)).ValidationExpression = @"^[\s\S]{0," + this.MaxLength.ToString() + "}$";
ClientScriptManager cs = this.Page.ClientScript;
cs.RegisterClientScriptResource(typeof(Test.UI.TextBoxWithMaxLength), JsRessource);
}
}
Et voici comment utiliser ce contrôle :
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TestWeb._Default" %>
<%@ Register TagPrefix="UI" Assembly="Test.UI" Namespace="Test.UI" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<UI:TextBoxWithMaxLength RegularExpressionValidatorID="RegularExpressionValidator1" TextMode="MultiLine" MaxLength="10" ID="tbMessagePopup" runat="server" />
<asp:RegularExpressionValidator runat="server" ID="RegularExpressionValidator1" ErrorMessage="Erreur vous ne pouvez dépasser 10 caractères" ControlToValidate="tbMessagePopup"></asp:RegularExpressionValidator>
</div>
</form>
</body>
</html>
Testez en Téléchargeant le projet : TextboxMultilineWithMaxLength (43)
A vous maintenant d’enrichir cet UI en créant vos propres WebControls.
Une liste sharepoint peut avoir besoin de données de configuration qui soit stockées de manière persistante.
Par exemple, si l’on créé une liste sharepoint (via code) qui doit être unique, comment pouvoir retrouver cette liste sachant qu’une liste du même nom peut être créé ou que le nom de cette liste peut être modifiée?
Pour se faire nous allons utiliser le dossier associé à la liste : RootFolder de type SpRoot. Via le spRoot on peut stocker des données persistantes grâce à la propriété : Properties.
La création de la liste se fait simplement (j’utilise le template par défaut CustomGrid):
Guid ListGuid = spWeb.Lists.Add(title, description, SPListTemplateType.CustomGrid);
SPList maList = spWeb.Lists[ListGuid];
On va associer à la liste un nom unique appelé NomUniqueDeMaListe (Attention on doit changer le AllowUnsafeUpdate pour réaliser la méthode RootFolder.Update()):
bool unSafeUpdate = maList.ParentWeb.AllowUnsafeUpdates;
try
{
maList.ParentWeb.AllowUnsafeUpdates = true;
maList.RootFolder.Properties["NomUnique"] = "NomUniqueDeMaListe";
maList.RootFolder.Update();
}
finally
{
maList.ParentWeb.AllowUnsafeUpdates = unSafeUpdate;
}
Maintenant voyons comment retrouver notre liste selon son nom unique (nous allons utiliser une méthode d’extension pour étendre l’objet SpWeb avec le mot clef this):
/// <summary>
/// Permet de retourner une liste de splist selon son nom unique (stocké dans le RootFolder de la liste) avec la valeur donnée
/// </summary>
/// <param name="spwCurr">WebSite demandé</param>
/// <param name="value">Valeur</param>
/// <returns></returns>
public SPList GetMySpList(this SPWeb spwCurr, string value)
{
foreach (SPList list in spwCurr.Lists)
{
if (list.RootFolder.Properties.Contains("NomUnique") && list.RootFolder.Properties["NomUnique"].Equals(value))
{
return list;
}
}
return null;
}
Vous pouvez stocker bien d’autres valeurs de configuration dans le Rootfolder, ce qui vous permettra de catégoriser/configurer vos listes.
Les CTE, Common Table Expression ou Expression de Table Commune sont des requêtes recursives apparuent avec SQL Server 2005 qui permettent dans de nombreux cas de simplifier et d’optimiser le code.
Les CTE ont 2 modes :
- le mode itératif : elles sont alors comparables aux tables dérivées par contre elles peuvent être réutilisées au sein d’une même requête.
- comparaison entre le mode itératif et les tables dérivées : nous verrons les différences de performance et les + qu’apportent les CTE dans la programmation.
- le mode récursif : ce mode nous permet avec simplicité d’utiliser récursivement le résultat d’une requete.
les requêtes cte sont initiés par le mot clef « with » suivi de l’alias donné au résultat de la requete.
exemple :
with monresultat(macolonne)
(select macolonne from matable)
select macolonne from monresultat.
Le helloWorld en cte :
with MonCTE(x)
as
(select 'helloWorld' as x)
select x from MonCTE;
ici la requête cte est: select ‘helloWorld’ as x, elle est utilisée dans l’instruction suivante : select x from MonCTE.
L’exemple le plus probant est l’extraction de résultat parmi des millions/milliards d’enregistrement en vue d’une pagination (comme un extranet listant les commandes d’un site e-commerce).
Pour l’exemple nous allons créer une table commande :
Create table Commande
(
[idCommande] [int] IDENTITY(1,1) NOT NULL,
[nomUser] varchar(255) NOT NULL,
[prenomUser] varchar(255) NOT NULL,
[email] varchar(255) NOT NULL,
[prix] float NOT NULL,
[dateCreation] datetime NOT NULL default GETDATE(),
CONSTRAINT [PK_Commande] PRIMARY KEY CLUSTERED
(
[IdCommande] ASC
));
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi1','toto1','titi1@titi.com',20);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi2','toto2','titi2@titi.com',30);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi3','toto3','titi3@titi.com',4);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi4','toto4','titi4@titi.com',50);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi5','toto5','titi5@titi.com',60);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi6','toto6','titi6@titi.com',70);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi7','toto7','titi7@titi.com',10.55);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi8','toto8','titi8@titi.com',20);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi9','toto9','titi9@titi.com',50);
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
values('titi10','toto10','titi10@titi.com',2);
Test de montée en charge oblige, nous multiplions le nombre de commande par un million pour cela nous allons utiliser une requête cte recursive (temps estimé 2 à 3 minutes pour un core 2 duo):
with cteCommande as
(
select [nomUser],[prenomUser],[email] ,[prix],1 as countMax from commande
UNION ALL
select [nomUser],[prenomUser],[email] ,[prix], countMax+1 from cteCommande
where countMax +1 <=10
),
cteCommande2 as
(
select [nomUser],[prenomUser],[email] ,[prix],1 as countMax from cteCommande
UNION ALL
select [nomUser],[prenomUser],[email] ,[prix], countMax+1 from cteCommande2
where countMax +1 <=10000
)
insert into Commande ([nomUser],[prenomUser],[email] ,[prix])
select [nomUser],[prenomUser],[email] ,[prix]
from cteCommande2
OPTION (MAXRECURSION 10001);
Dans cet exemple nous sommes obligés de combiner 2 cte, le nombre de récursion maximale étant de 32765.
La première requête selectionne les 10 enregistrements de la table et * par 10, la deuxieme requête prend les 100 résultats de la premiere requête et multiplie par 10000.
L’option (maxrecursion) permet de s’assurer que nous n’allons pas rentrer dans une boucle sans fin.
Pour choisir la requête classique de pagination, nous allons la générer via linq to sql (on aurait put aussi utiliser linqpad pour générer la requête), pour ça création d’un petit projet application console en c#, ajoutez un dbml au projet (classe Linq to sql) appelez le monDbml, ajoutez la table commande, et ajoutez le code suivant :
class Program
{
static void Main(string[] args)
{
StringWriter stw = new StringWriter();
using (monDbmlDataContext context= new monDbmlDataContext())
{
context.Log = stw;
var l = context.Commande.Skip(500000).Take(10).ToList()
}
Console.Write(stw.ToString());
Console.ReadLine();
}
}
en plaçant un espion sur le stw.ToString() à la fin du programme nous pouvons voir la requête générée par linq.
cette requête est :
SELECT [t1].[IdCommande], [t1].[nomUser], [t1].[prenomUser], [t1].[email], [t1].[prix], [t1].[DateCreation]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]) AS [ROW_NUMBER], [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]
FROM [dbo].[Commande] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER];
Linq utilise nativement la table dérivée, comparons le temps de traitement à une requête Cte, en Cte la requête de pagination est :
-- notre requête cte
With cteCommande As(
Select *,ROW_NUMBER() OVER (ORDER BY IdCommande ASC) as
RowNumber
From Commande
)
SELECT cteCommande.IdCommande,cteCommande.NomUser,cteCommande.PrenomUser,cteCommande.Email, cteCommande.[prix], cteCommande.[DateCreation]
FROM cteCommande
WHERE RowNumber BETWEEN @p0 + 1 AND @p0 + @p1;
Pour tester les 2 requêtes nous activons les statistiques et ajoutons les paramétres avant la définition des requêtes :
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
Declare @p0 as int
Declare @p1 as int
Set @p0 = 500000;
Set @p1 = 10;
-- requête utilisé par Linq
SELECT [t1].[IdCommande], [t1].[nomUser], [t1].[prenomUser], [t1].[email], [t1].[prix], [t1].[DateCreation]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]) AS [ROW_NUMBER], [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]
FROM [dbo].[Commande] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER];
-- notre requête cte
With cteCommande As(
Select *,ROW_NUMBER() OVER (ORDER BY IdCommande ASC) as
RowNumber
From Commande
)
SELECT cteCommande.IdCommande,cteCommande.NomUser,cteCommande.PrenomUser,cteCommande.Email, cteCommande.[prix], cteCommande.[DateCreation]
FROM cteCommande
WHERE RowNumber BETWEEN @p0 + 1 AND @p0 + @p1;
le résultat me donne le même nombre de lecteur logique et sensiblement le même temps d’execution (0.400ms).
autre cas de figure, pour réaliser une pagination il faut aussi connaitre le nombre d’élément répondant aux critères de la requête et ainsi en déduire le nombre de pages. Pourquoi ne pas exploiter le résultat de la première cte pour connaitre le nombre d’éléments dans une autre cte, le prix minimal, maximal pour les user ayant un nom commençant par titi
With cteCommande As(
Select *,ROW_NUMBER() OVER (ORDER BY IdCommande ASC) as
RowNumber
From Commande
where nomUser like 'titi%'
),
cteCountRow as
(
select count(1) as nbRow ,max(prix) as maxPrix,min(prix) as minPrix from cteCommande
)
SELECT cteCommande.IdCommande,cteCommande.NomUser,cteCommande.PrenomUser,cteCommande.Email, cteCommande.[prix], cteCommande.[DateCreation],
cteCountRow.nbRow,cteCountRow.maxPrix,cteCountRow.minPrix
FROM cteCommande,cteCountRow
WHERE RowNumber BETWEEN @p0 + 1 AND @p0 + @p1;
Par contre dans la requête classique il nous faut répéter la requête d’extraction des données ce qui donne ce code:
SELECT [t1].[IdCommande], [t1].[nomUser], [t1].[prenomUser], [t1].[email], [t1].[prix], [t1].[DateCreation],[t2].[nbRow],[t2].[maxPrix],[t2].[minPrix]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]) AS [ROW_NUMBER], [t0].[IdCommande], [t0].[nomUser], [t0].[prenomUser], [t0].[email], [t0].[prix], [t0].[DateCreation]
FROM [dbo].[Commande] AS [t0]
where nomUser like 'titi%'
) AS [t1],
(
select count(1) nbRow,max(prix) maxPrix,min(prix) minPrix from [dbo].[Commande]
where nomUser like 'titi%'
) as [t2]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER];
execution de la requête, et même constat les performances sont identiques le seul intérêt du cte itératif (et pas des moindre) est de pouvoir exploiter le résultat d’une requête sans avoir à la réécrire.
Voici l’article du MVP Pinal Dave qui explique la même chose : http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/
Attention par contre à ce cas de figure qui lui execute 3 fois la requête cte et augmente le temps d’execution :
With cteCommande As(
Select *,ROW_NUMBER() OVER (ORDER BY IdCommande ASC) as
RowNumber
From Commande
where nomUser like 'titi%'
)
SELECT cteCommande.IdCommande,cteCommande.NomUser,cteCommande.PrenomUser,cteCommande.Email, cteCommande.[prix], cteCommande.[DateCreation],
nbRow = (select count(1) from cteCommande),maxPrix = (select max(prix) from cteCommande),minPrix= (select min(prix) from cteCommande)
FROM cteCommande
WHERE RowNumber BETWEEN @p0 + 1 AND @p0 + @p1
le plan d’execution nous revele 4 scan d’index sur la table commande au lieu de 2 ce qui alourdit considérablement le temps de réponse.
Elles se caractérisent par une requête d’initialisation, le mot clef UNION ALL et la requête de récursion, attention vous pouvez entrer dans une boucle infinie, utilisez le mot clef OPTION (MAXRECURSION nombreDeBoucle) pour les éviter.
Pour l’exemple nous allons rechercher les procédures stockées et les vues contenant un texte spécifique.
Si la définition d’une table est modifiée, il est important de vérifier que toutes les procédures stockées ainisi que les vues qui l’utilisent fonctionnent correctement.
Pour les retrouver voici la requete d’initialisation :
SELECT *
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
(syso.xtype = 'P' or
syso.xtype = 'V')
AND
(syso.category = 0)
and text like '%Commande%'
Cette requête n’est pas suffisante, car d’autres procédures stockées ou d’autres vues peuvent utiliser la vue qui contient la table, nous allons donc utiliser la récursion pour les connaitre :
WITH CTE_RechercheRequete(objectName,objectText,objectType,objectReferenceName, objectLevel)
AS
(
SELECT name,text,syso.xtype , CAST('' AS VARCHAR(MAX)) AS name, 1
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
syso.xtype in ('P','V')
AND
syso.category = 0
and text like '%Commande%'
UNION ALL
SELECT name,text,syso.xtype, CAST(c.objectName AS VARCHAR(MAX)), objectLevel +1
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
inner join CTE_RechercheRequete c on (sysc.text like '%'+c.objectName+'%' and syso.name <>c.objectName)
WHERE
syso.xtype in ('P','V')
AND
(syso.category = 0)
)
select * from
CTE_RechercheRequete
OPTION (MAXRECURSION 30000);
Cette requête (qui peut être encore optimisée) nous permet d’avoir un bon aperçu de la puissance de la recursion.
A vous de jouer.
