MS SQL Server
  1. MS SQL Server
  3. Performances bizarres de la fonction table SQL Server 2005
Intereting Posts
Sélectionnez 50 files randoms par jour Gérer les Big Data sur un server SQL comparaison des jointures SQL à Linq pour les jointures interne et gauche MSSQL – Extrait deuxième à partir du time EOF inattendu du server sur le server SQL Spécifiez les locations de files SQL Server lors de l'utilisation d'Entity Framework Database.Create () comment importer des données à partir d'une autre database dans SQL Server 2005 Le pilote JDBC ne prend pas en charge la mise à jour par lots avec la récupération de la colonne d'identité. Pourquoi? Grouper par avec date min et max en utilisant T-SQL login à SQL Server à l'aide de PowerShell LINQ to SQL: "Méthode 'Boolean Contient (System.Ssortingng)' n'a pas de traduction prise en charge à SQL." Sql Server – ordre par numéro Conversion de nvarchar (4000) en nvarchar (max) SQL – Obtention de la valeur TOP par atsortingbut dans l'set de résultats

Performances bizarres de la fonction table SQL Server 2005

J'ai une énorme différence d'exécution de time entre une requête de 1 minute et la même dans une fonction de table.

Mais le truc le plus bizarre est qu'exécuter le UDF avec un autre argument (valide) company_id me donne un résultat dans ~ 40 secondes et dès que je change ce company_id pour 12 (valide encore), il ne s'arrête jamais. Les plans d'exécution de ces deux requêtes ne sont absolument pas les mêmes et bien sûr, le plus long est le plus compliqué. MAIS le plan d'exécution entre la version batch et la version UDF sont les mêmes ET la version batch est rapide …!

Si je fais la requête suivante "à la main", le time d'exécution est 1min36s avec 306 lignes: 

SELECT dbo.date_only(Call.date) AS date, count(DISTINCT customer_id) AS new_customers FROM Call LEFT OUTER JOIN dbo.company_new_customers(12, 2009, 2009) new_customers ON dbo.date_only(new_customers.date) = dbo.date_only(Call.date) WHERE company_id = 12 AND year(Call.date) >= 2009 AND year(Call.date) <= 2009 GROUP BY dbo.date_only(Call.date)

J'ai stocké exactement cette même requête dans une fonction et l'ai exécuté comme ça: 

 SELECT * FROM company_new_customers_count(12, 2009, 2009)

13 minutes pour l'instant qu'il est en cours d'exécution … Et je suis sûr que cela ne me donnera jamais aucun résultat.

Hier, j'ai eu exactement le même comportement de boucle infinie pendant plus de 4h (donc je l'ai arrêté).

Voici la définition de la fonction: 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION company_new_customers_count ( @company_id int, @start_year int, @end_year int ) RETURNS TABLE AS RETURN ( SELECT dbo.date_only(Call.date) AS date, count(DISTINCT customer_id) AS new_customers FROM Call LEFT OUTER JOIN dbo.company_new_customers(@company_id, @start_year, @end_year) new_customers ON dbo.date_only(new_customers.date) = dbo.date_only(Call.date) WHERE company_id = @company_id AND year(Call.date) >= @start_year AND year(Call.date) <= @end_year GROUP BY dbo.date_only(Call.date) ) GO

Je serais très heureux de comprendre ce qui se passe.

Merci

Additionnel:

Définition de company_new_customers: 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: Create the list of new customers of @company_id -- in the given period. -- ============================================= CREATE FUNCTION company_new_customers ( @company_id int, @start_year int, @end_year int ) RETURNS TABLE AS RETURN ( SELECT customer_id, date FROM ( -- select apparition dates of cutomers before @end_year SELECT min(date) AS date, customer_id FROM Call JOIN Call_Customer ON Call_Customer.call_id = Call.call_id WHERE company_id = @company_id AND year(date) <= @end_year GROUP BY customer_id ) new_customers WHERE year(date) >= @start_year -- select apparition dates of cutomers after @start_year ) GO

Définition de date_only: 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Julio Guerra -- Create date: 14/10/2010 -- Description: Return only the date part of a datetime value -- Example: date_only('2010-10-25 13:00:12') returns 2010-10-25 -- ============================================= CREATE FUNCTION date_only ( @datetime datetime ) RETURNS datetime AS BEGIN RETURN dateadd(dd, 0, datediff(dd, 0, @datetime)) END GO

Plan d'exécution de SELECT * FROM company_new_customers_count (8, 2009, 2009) texte alt

Plan d'exécution de SELECT * FROM company_new_customers_count (12, 2009, 2009) texte alt

À partir de ces plans de requête, il semble que vous puissiez bénéficier d'un index comme celui-ci (si j'ai inféré votre schéma de database à droite): 

 CREATE INDEX IX_call_company_date ON call (company_id, date)

En général, cela semble être un problème d'optimization de requête standard et les fonctions de table ne font pas de différence ici.

Le plan court utilise HashJoin et les parsings d'index en cluster sur PK_CALL . Le plan long utilise NestedLoops et cherche répétée dans UK_Pair_... Très probablement, les estimations de cardinalité pour '12, 2009, 2009 'excluent HashJoin en raison d'une memory système insuffisante, de sorte que vous vous retrouvez avec un plan pire (malgré les searchs au lieu des balayages). Probablement company_id 12 a beaucoup plus de clients que company_id 8.

Donner une solution est impossible sans informations exactes sur tous les facteurs impliqués (le schéma exact utilisé, y compris chaque index, et les statistics exactes et la cardinalité de chaque table impliquée). Une voie simple à suivre serait d'utiliser un guide de plan, voir Conception et mise en œuvre des guides de plan .

Il y a quelques parties de la réponse ici. Pour la première partie, je vais essayer de répondre à la question (vous n'avez pas demandé) pourquoi aucune des requêtes n'est particulièrement rapide. Cela a un rapport avec la question que vous avez effectivement posée, alors supportez-moi.

Vos critères de date ne sont généralement pas SARGable comme vous les avez spécifiés – par exemple dans votre fonction company_new_customers . Cela signifie que le server ne peut pas utiliser ses statistics pour déterminer à quel point vos critères sont sélectifs. Cela signifie que vos plans de requête vont être très, très sensibles à la sélectivité de votre critère customer_id , quel que soit le nombre de lignes de date que vous avez par client.

L'utilisation d'une requête range avec un index sur date et call_id dans votre table d'appel devrait grandement améliorer les performances dans tous les cas et réduire la sensibilité de votre requête à la sélectivité du customer_id. En supposant que la date est sur votre table d'appel, je réécrirais votre UDF interne quelque chose comme ça et modifier les parameters d'input pour utiliser des dates à la place. Cela rendra votre UDF plus polyvalente: 

 CREATE FUNCTION company_new_customers ( @company_id INT, @start_date DATETIME, @end_date DATETIME ) RETURNS TABLE AS RETURN ( SELECT MIN(c.[date]) AS [date], c.customer_id FROM dbo.[Call] c JOIN dbo.[Call_Customer] cc ON cc.call_id = c.call_id WHERE c.company_id = @company_id AND c.[date] <= @end_date AND NOT EXISTS ( SELECT * FROM dbo.[Call] c1 WHERE c1.customer_id = c.customer_id AND c1.[date] <= @start_date ) GROUP BY c.customer_id ) GO

Idem pour votre autre vue. En utilisant year () et vos fonctions date_only (), vous rendez les statistics ou les index de vos dates pratiquement inutiles (bien que l'optimiseur puisse les utiliser pour limiter la quantité de données analysées, mais c'est une discussion plus importante).

Maintenant alors – pourquoi votre UDF prend pour toujours? Comme il appelle un autre UDF et que vous utilisez date_only () comme argument de jointure, il ne peut pratiquement rien savoir de ce à quoi s'attendre dans la sous-requête UDF, il a donc choisi de se connecter en boucle. Il est probable que vous choisissiez ce plan, car il convient à certaines valeurs de customer_id. Il est probable que vous ayez exécuté une requête sur l'un de ces customer_ids sélectifs peu de time après la création du file UDF et que le plan de cette requête a été mis en cache, même s'il n'est pas approprié pour les autres valeurs de customer_id.

Pourquoi un proc stocké ne prend pas une éternité? Parce que la première fois que vous l'exécutez, le process stocké génère un plan basé sur les premiers critères que vous lui donnez. Peut-être que la première fois que vous avez exécuté le SP, vous avez utilisé l'ID client non sélectif et le proc stocké a choisi de join le hash. Même chose avec la requête ad-hoc. L'optimiseur "remarque" que vous lui avez passé un customer_id non sélectif et choisit de créer une jointure de hachage pour vous.

Quoi qu'il en soit, à less que vous ne maîsortingsiez le problème de date-SARGability, vous findez que toutes vos requêtes seront très sensibles à votre input customer_id, et en fonction de vos habitudes d'utilisation, elles pourraient exploser votre visage en termes de performance – UDFs ou non.

J'espère que cela t'aides!

J'ai vu cela avec SQL Server 2005. Lorsque nous avons utilisé une fonction de valeur de table pour notre requête particulière, nous avons obtenu des performances effroyables. Pris exactement le même text de la requête, les parameters et tous, les mettre dans un proc stocké et obtenu de manière fiable un plan de requête merveilleux. Appeler la fonction avec les mêmes parameters que le proc stocké produit un comportement différent (nous avons démarré les deux à partir d'un cache froid). Très décevant!

Malheureusement, nous n'avons pas eu le time de diagnostiquer ce comportement étrange plus profondément et avons déplacé le projet sur l'évitement des fonctions de valeur de table en 2005.

Cela peut indiquer un bogue dans SQL Server 2005.