MS SQL Server
  1. MS SQL Server
  3. SQL Server: comment améliorer les performances pour les requêtes avec plusieurs CTE et sous-requêtes dans la clause WHERE
Intereting Posts
SQL: insert la date automatiquement Comment accéder à une variable dans l'set du projet de database du server SQL? Comment écrire une foreach dans SQL Server? Docsortingne se connecter avec mssql SQL Server – récupère toutes les bases de données avec MDF et LDF File Location SQL Server: comment améliorer les performances pour les requêtes avec plusieurs CTE et sous-requêtes dans la clause WHERE SQL Server: Syntaxe near keyword 'CREATE' lors de l'exécution par lots CREATE SCHEMA et CREATE TABLE Rejoindre le résultat du return SQL Server en fonction du classment Comment déterminer les types de données des résultats d'un SQL? SQL Server: search de relations entre des tables sans contrainte de key étrangère Comment améliorer cette clause Group by Groupe par semaine de l'année (numéro de la semaine) dans Linq to SQL Blob de SQL Server dans asp.net (c #) logging avec un nom de file incorrect Déclarez le slider dans SQL dynamic Comment se connecter à une database de server MSSQL via Unity3d editor / standalone?

SQL Server: comment améliorer les performances pour les requêtes avec plusieurs CTE et sous-requêtes dans la clause WHERE

J'ai les deux tables suivantes: 

CREATE TABLE Portfolio.DailyPortfolio ( BbgID varchar(30) NOT NULL, Ticker varchar(22) NULL, Cusip char(9) NULL, SecurityDescription varchar(50) NOT NULL, AssetCategory varchar(25) NOT NULL, LSPosition char(3) NULL, Ccy varchar(25) NOT NULL, Quantity int NULL, AvgCost decimal(7,3) NULL, PriceLocal decimal(7,3) NULL, Cost int NULL, MktValNet int NULL, GLPeriod int NULL, Beta decimal(4,2) NULL, BetaExpNet int NULL, BetaExpGross int NULL, Delta decimal(4,2) NULL, DeltaExpNet int NULL, DeltaExpGross int NULL, Issuer varchar(48) NOT NULL, Country varchar(30) NOT NULL, Region varchar(20) NOT NULL, Sector varchar(30) NOT NULL, Industry varchar(48) NOT NULL, MktCapCategory varchar(24) NULL, MktCapEnd int NULL, Date date NOT NULL, PortfolioID AS BbgID+LSPosition+ Convert(varchar(8),Date,112) Persisted Primary Key ) GO

Voici la deuxième table: 

  CREATE TABLE Portfolio.DailyStats ( Date date NOT NULL Primary Key, NAV int NOT NULL, SP500 decimal(8,4) NULL, R2K decimal(8,4) NULL, NetExp decimal(8,4) NULL, GrossExp decimal(8,4) NULL, ) GO ALTER TABLE Portfolio.DailyStats ADD [YrMn] as CONVERT(varchar(7), Date) GO

Entre 80 et 100 lignes sont ajoutées à la table DailyPortfolio chaque jour ouvrable (la table contient actuellement environ 32 000 lignes). Une ligne est ajoutée à la table DailyStats chaque jour ouvrable (elle count actuellement environ 500 lignes). La colonne Date de la table Daily Portfolio a une relation de key étrangère avec la colonne Date de la table DailyStats.

J'ai dû créer une vue qui comprenait quelques colonnes des deux tables en utilisant le dernier sortingmestre comme plage de dates. La dernière colonne de cette vue utilise la colonne Moyenne de la valeur liquidative dans son calcul où la moyenne est calculée en utilisant la VNI au 1er jour de chacun des 3 mois du sortingmestre. Voici mon DDL pour la vue: 

  CREATE VIEW Portfolio.PNLLastQTD AS WITH CTE1 AS ( Select Date, NAV,YrMn, ROW_NUMBER() OVER (PARTITION BY YrMn ORDER BY Date) AS Row FROM Portfolio.DailyStats WHERE DATE BETWEEN (SELECT Convert(date, DATEADD(q, DATEDIFF(q,0,GETDATE()) -1 ,0))) AND (SELECT Convert(date, DATEADD(s,-1,DATEADD(q, DATEDIFF(q,0,GETDATE()),0)))) ), CTE2 AS ( SELECT AvG (NAV) As AvgNAV FROM CTE1 WHERE Row=1 ) SELECT IssuerLS, Issuer, Ticker, SUM (GLPeriod) As [PNL], CAST(SUM(GLPeriod)As Decimal (13,2)) / CAST(CTE2.[AvgNAV] As Decimal (13,2)) as [%ofNAV] FROM Portfolio.DailyPortfolioIssuerLS ls JOIN cte2 on 1=1 WHERE ReportDate BETWEEN (SELECT Convert(date, DATEADD(q, DATEDIFF(q,0,GETDATE()) -1 ,0))) AND (SELECT Convert(date, DATEADD(s,-1,DATEADD(q, DATEDIFF(q,0,GETDATE()),0)))) GROUP BY Issuer, Ticker, IssuerLS, CTE2.[AvgNAV] GO

La vue fonctionne bien mais prend presque 20 secondes à exécuter! J'ai quelques questions ici:

  1. Devrait-il y avoir quelques modifications apscopes à mon DDL pour la vue?
  2. Est-ce une bonne idée de créer un index non cluster sur la colonne de date (si possible) de la table DailyPortfolio?
  3. Y at-il autre chose que je devrais penser pour améliorer les performances de requête pour ce problème particulier?

Merci beaucoup pour votre aide. S'il vous plaît pardonner les erreurs flagrantes que je suis nouveau à SQL.

Je voulais boucler la boucle sur cette question. Ce que je devais faire ici était de créer deux indices non groupés. J'ai utilisé les étapes suivantes:

  1. Placé ma requête sur la window de requête.
  2. J'ai cliqué sur le button "Afficher le plan d'exécution estimé" de ma boîte à outils qui m'a immédiatement informé de l'absence d'un index non cluster.

  3. Créé le premier index non cluster: 

     USE [OurDB] GO CREATE NONCLUSTERED INDEX NCI_DailyPort_Issuer_Date ON [Portfolio].[DailyPortfolio] ([Issuer],[Date]) GO

  4. Répétez l'étape 2 et créez le deuxième index non clusterisé comme recommandé: 

     USE [OurDB] GO CREATE NONCLUSTERED INDEX NCI_DailyPort_Date_INC_DexpN_Issuer ON [Portfolio].[DailyPortfolio] ([Date]) INCLUDE ([DeltaExpNet],[Issuer]) GO

La requête prend maintenant less de 3 secondes à exécuter, ce qui est nettement mieux que les 24 secondes précédentes.

Remarque: Si vous faites un clic droit sur la ligne qui vous informe sur l'index manquant, vous pouvez choisir une option pour voir le code de l'index qui vous fait gagner du time.