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.

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

Utilisez les fonctions Babelfish comme suit :

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

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.

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