Utilisation du plan d'explication pour améliorer les performances des requêtes Babelfish

À partir de la version 2.1.0, Babelfish inclut deux fonctions qui utilisent de manière transparente l'optimiseur PostgreSQL pour générer des plans de requêtes estimés et réels pour les requêtes T-SQL sur le port TDS. Ces fonctions sont similaires à l'utilisation de SET STATISTICS PROFILE ou de SET SHOWPLAN_ALL avec des bases de données SQL Server pour identifier et améliorer les requêtes s'exécutant lentement.

Note

L'obtention de plans de requête à partir de fonctions, de flux de contrôle et de curseurs n'est actuellement pas prise en charge.

Le tableau fournit une comparaison des fonctions d'explication de plan de requête sur SQL Server, Babelfish et PostgreSQL.

SQL Server	Babelfish	PostgreSQL
SHOWPLAN_ALL	BABELFISH_SHOWPLAN_ALL	EXPLAIN
STATISTICS PROFILE	BABELFISH_STATISTICS PROFILE	EXPLAIN ANALYZE
Utilise l'optimiseur SQL Server	Utilise l'optimiseur PostgreSQL	Utilise l'optimiseur PostgreSQL
Format d'entrée et de sortie SQL Server	Format d'entrée SQL Server et de sortie PostgreSQL	Format d'entrée et de sortie PostgreSQL
Défini pour la session	Défini pour la session	Appliquer à une instruction spécifique
Prend en charge : SELECT INSERT MISE A JOUR DELETE CURSOR CREATE EXECUTE EXEC et fonctions, y compris le flux de contrôle (CASE, WHILE-BREAK-CONTINUE, WAITFOR, BEGIN-END, IF-ELSE, etc.)	Prend en charge : SELECT INSERT MISE A JOUR DELETE CREATE EXECUTE EXEC RAISEERROR THROW PRINT USE	Prend en charge : SELECT INSERT MISE A JOUR DELETE CURSOR CREATE EXECUTE

Utilisez les fonctions Babelfish comme suit :

• SET BABELFISH_SHOWPLAN_ALL [ON|OFF] : définissez la valeur ON pour générer un plan d'exécution de requête estimé. Cette fonction implémente le comportement de la commande PostgreSQL EXPLAIN. Utilisez cette commande pour obtenir le plan d'explication pour une requête donnée.

• SET BABELFISH_STATISTICS PROFILE [ON|OFF] : définissez la valeur ON pour les plans d'exécution de requête réels. Cette fonction implémente le comportement de la commande PostgreSQL EXPLAIN ANALYZE.

Pour plus d'informations sur EXPLAIN et EXPLAIN ANALYZE de PostgreSQL, consultez EXPLAIN dans la documentation PostgreSQL.

Note

À partir de la version 2.2.0, vous pouvez définir le paramètre escape_hatch_showplan_all sur ignore afin d'éviter l'utilisation du préfixe BABELFISH_ dans la syntaxe SQL Server pour les commandes SET SHOWPLAN_ALL et STATISTICS PROFILE.

Par exemple, la séquence de commandes suivante active la planification des requêtes, puis renvoie un plan d'exécution de requête estimé pour l'instruction SELECT sans exécuter la requête. Cet exemple utilise l'exemple de base de données SQL Server northwind qui utilise l'outil de ligne de commande sqlcmd pour interroger le port TDS :

1> SET BABELFISH_SHOWPLAN_ALL ON
2> GO
1> SELECT t.territoryid, e.employeeid FROM
2> dbo.employeeterritories e, dbo.territories t
3> WHERE e.territoryid=e.territoryid ORDER BY t.territoryid;
4> GO

QUERY PLAN                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------                                                                                                              
Query Text: SELECT t.territoryid, e.employeeid FROM
dbo.employeeterritories e, dbo.territories t
WHERE e.territoryid=e.territoryid ORDER BY t.territoryid
Sort  (cost=6231.74..6399.22 rows=66992 width=10)
  Sort Key: t.territoryid NULLS FIRST
  ->  Nested Loop  (cost=0.00..861.76 rows=66992 width=10)  
        ->  Seq Scan on employeeterritories e  (cost=0.00..22.70 rows=1264 width=4)
              Filter: ((territoryid)::"varchar" IS NOT NULL)
        ->  Materialize  (cost=0.00..1.79 rows=53 width=6)
              ->  Seq Scan on territories t  (cost=0.00..1.53 rows=53 width=6)

Lorsque vous avez terminé d'examiner et d'ajuster votre requête, désactivez la fonction comme indiqué ci-dessous :

1> SET BABELFISH_SHOWPLAN_ALL OFF

Lorsque BABELFISH_STATISTICS PROFILE est défini sur ON, chaque requête exécutée renvoie son jeu de résultats normal suivi d'un jeu de résultats supplémentaire qui affiche les plans d'exécution de requêtes réels. Babelfish génère le plan de requête qui fournit le jeu de résultats le plus rapide lorsqu'il appelle l'instruction SELECT.

1> SET BABELFISH_STATISTICS PROFILE ON
1>
2> GO
1> SELECT e.employeeid, t.territoryid FROM
2> dbo.employeeterritories e, dbo.territories t
3> WHERE t.territoryid=e.territoryid ORDER BY t.territoryid;
4> GO

Le jeu de résultats et le plan de requête sont renvoyés (cet exemple montre uniquement le plan de requête).

QUERY PLAN                                                                                                                                                                                                                                
---------------------------------------------------------------------------
Query Text: SELECT e.employeeid, t.territoryid FROM
dbo.employeeterritories e, dbo.territories t
WHERE t.territoryid=e.territoryid ORDER BY t.territoryid
Sort  (cost=42.44..43.28 rows=337 width=10)
  Sort Key: t.territoryid NULLS FIRST                                                                                                                                               
  ->  Hash Join  (cost=2.19..28.29 rows=337 width=10)
       Hash Cond: ((e.territoryid)::"varchar" = (t.territoryid)::"varchar")
        ->  Seq Scan on employeeterritories e  (cost=0.00..22.70 rows=1270 width=36)
        ->  Hash  (cost=1.53..1.53 rows=53 width=6)
             ->  Seq Scan on territories t  (cost=0.00..1.53 rows=53 width=6)

Pour en savoir plus sur l'analyse de vos requêtes et des résultats renvoyés par l'optimiseur PostgreSQL, consultez explain.depesz.com. Pour plus d'informations sur EXPLAIN et EXPLAIN ANALYZE de PostgreSQL, consultez EXPLAIN dans la documentation PostgreSQL.

Paramètres qui contrôlent les options d'explication Babelfish

Vous pouvez utiliser les paramètres du tableau suivant pour contrôler le type d'informations affichées par votre plan de requête.

Paramètre	Description
babelfishpg_tsql.explain_buffers	Booléen qui active (et désactive) les informations d'utilisation du tampon pour l'optimiseur. (Par défaut : off) (Autorisé : off, on)
babelfishpg_tsql.explain_costs	Booléen qui active (et désactive) les informations de coût total et de démarrage estimé pour l'optimiseur. (Par défaut : on) (Autorisé : off, on)
babelfishpg_tsql.explain_format	Spécifie le format de sortie pour le plan EXPLAIN. (Par défaut : text) (Autorisé : text, xml, json, yaml)
babelfishpg_tsql.explain_settings	Booléen qui active (ou désactive) l'inclusion d'informations sur les paramètres de configuration dans la sortie du plan EXPLAIN. (Par défaut : off) (Autorisé : off, on)
babelfishpg_tsql.explain_summary	Booléen qui active (ou désactive) des informations récapitulatives telles que le temps total après le plan de requête. (Par défaut : on) (Autorisé : off, on)
babelfishpg_tsql.explain_timing	Booléen qui active (ou désactive) l'heure de démarrage réelle et le temps passé dans chaque nœud de la sortie. (Par défaut : on) (Autorisé : off, on)
babelfishpg_tsql.explain_verbose	Booléen qui active (ou désactive) la version la plus détaillée d'un plan d'explication. (Par défaut : off) (Autorisé : off, on)
babelfishpg_tsql.explain_wal	Booléen qui active (ou désactive) la génération d'informations de registre WAL dans le cadre d'un plan d'explication. (Par défaut : off) (Autorisé : off, on)

Vous pouvez vérifier les valeurs de n'importe quel paramètre lié à Babelfish sur votre système à l'aide du client PostgreSQL ou du client SQL Server. Exécutez la commande suivante pour obtenir la valeur de vos paramètres actuels :

1> execute sp_babelfish_configure '%explain%';
2> GO

Dans la sortie suivante, vous observez que tous les paramètres de ce cluster de bases de données Babelfish particulier sont définis sur leurs valeurs par défaut. Les résultats ne sont pas tous affichés dans cet exemple.

             name                   setting                     short_desc
---------------------------------- -------- --------------------------------------------------------
babelfishpg_tsql.explain_buffers   off      Include information on buffer usage
babelfishpg_tsql.explain_costs     on       Include information on estimated startup and total cost
babelfishpg_tsql.explain_format    text     Specify the output format, which can be TEXT, XML, JSON, or YAML
babelfishpg_tsql.explain_settings  off      Include information on configuration parameters
babelfishpg_tsql.explain_summary   on       Include summary information (e.g.,totaled timing information) after the query plan 
babelfishpg_tsql.explain_timing    on       Include actual startup time and time spent in each node in the output
babelfishpg_tsql.explain_verbose   off      Display additional information regarding the plan
babelfishpg_tsql.explain_wal       off      Include information on WAL record generation

(8 rows affected)

Vous pouvez modifier la valeur de ces paramètres avec sp_babelfish_configure, comme illustré dans l'exemple suivant.

1> execute sp_babelfish_configure 'explain_verbose', 'on';
2> GO

Si vous voulez rendre les valeurs permanentes à l'échelle d'un cluster, ajoutez le mot-clé server, comme indiqué dans l'exemple suivant.

1> execute sp_babelfish_configure 'explain_verbose', 'on', 'server';
2> GO