Menggunakan rencana explain untuk meningkatkan performa kueri Babelfish

Mulai versi 2.1.0, Babelfish menyertakan dua fungsi yang secara transparan menggunakan pengoptimal PostgreSQL guna menghasilkan perkiraan dan rencana kueri aktual untuk kueri T-SQL di port TDS. Fungsi tersebut mirip dengan penggunaan SET STATISTICS PROFILE atau SET SHOWPLAN_ALL dengan basis data SQL Server untuk mengidentifikasi dan meningkatkan kueri yang berjalan lambat.

Di tabel, Anda dapat menemukan perbandingan fungsi explain rencana kueri di SQL Server, Babelfish, dan PostgreSQL.

Gunakan fungsi Babelfish sebagai berikut:

Untuk informasi selengkapnya tentang EXPLAIN dan EXPLAIN ANALYZE PostgreSQL, lihat EXPLAIN di dokumentasi PostgreSQL.

Misalnya, urutan perintah berikut mengaktifkan perencanaan kueri, lalu mengembalikan perkiraan rencana eksekusi kueri untuk pernyataan SELECT tanpa menjalankan kueri. Contoh ini menggunakan contoh basis data northwind SQL Server menggunakan alat baris perintah sqlcmd untuk mengkueri 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)

Ketika Anda selesai meninjau dan menyesuaikan kueri, Anda dapat menonaktifkan fungsi seperti yang ditunjukkan berikut:

1> SET BABELFISH_SHOWPLAN_ALL OFF

Dengan menetapkan BABELFISH_STATISTICS PROFILE ke ON, setiap kueri yang dieksekusi akan mengembalikan set hasil regulernya, yang diikuti oleh kumpulan hasil tambahan yang menunjukkan rencana eksekusi kueri aktual. Babelfish menghasilkan rencana kueri yang menyediakan set hasil tercepat saat menginvokasi pernyataan 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

Set hasil dan rencana kueri dikembalikan (contoh ini hanya menunjukkan rencana kueri).

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)

Untuk mempelajari cara menganalisis kueri dan hasil yang dikembalikan oleh pengoptimal PostgreSQL, lihat explain.depesz.com. Untuk informasi lebih lanjut tentang EXPLAIN dan EXPLAIN ANALYZE PostgreSQL, lihat EXPLAIN dalam dokumentasi PostgreSQL.

Parameter yang mengontrol opsi explain Babelfish

Anda dapat menggunakan parameter yang ditunjukkan dalam tabel berikut untuk mengontrol jenis informasi yang ditampilkan oleh rencana kueri Anda.

Anda dapat memeriksa nilai parameter terkait Babelfish pada sistem Anda dengan menggunakan klien PostgreSQL atau klien SQL Server. Jalankan perintah berikut untuk mendapatkan nilai parameter Anda saat ini:

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

Di output berikut, Anda dapat melihat bahwa semua pengaturan di klaster Babelfish DB khusus ini berada pada nilai defaultnya. Tidak semua output ditampilkan dalam contoh ini.

             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)

Anda dapat mengubah pengaturan untuk parameter ini menggunakan sp_babelfish_configure, seperti yang ditunjukkan pada contoh berikut.

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

Jika Anda ingin membuat pengaturan permanen di tingkat seluruh klaster, sertakan kata kunci server, seperti yang ditunjukkan di contoh berikut.

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