Pobieranie metadanych tabel w SQL Server

Ograniczanie liczby złączeń

Standard SQL Wymusza na twórcach baz danych udostępnianie informacji o tabelach, kolumnach i innych obiektach w postaci relacyjnej. Oznacza to, że dane te możemy pobrać korzystając ze zwykłej składni SQL, a same dane znajdują się w widokach lub tabelach systemowych. Nie zmienia to jednak faktu, że każdy silników baz danych posiada zestaw niestandardowych, dodatkowych funkcji i procedur realizujących podobne funkcje. Co warte podkreślenia, relacyjne bazy danych to rodzaj aplikacji, to logiczna warstwa interpretująca polecenia wydawane w języku SQL. Standard definiuje sposób działania, ale nie definiuje sposobu implementacji. To, że na zewnątrz widzimy ładny widok systemowy nie oznacza wcale, że pod spodem w takiej samej formie te dane są składowane. Jest wręcz pewne, że niektóre struktury, zwłaszcza te częściej wykorzystywane, przechowywane są w postaci obiektów umieszczonych w pamięci, w postaci tablic z haszowaniem, drzew binarnych - jednym słowem struktur, które umożliwiają bardzo szybki dostęp do potrzebnych informacji. Warto takie funkcje poznać. O tym będzie dzisiejszy wpis. Pokażę kilka przydatnych zapytań i alternatywne sposoby zapisu ich z wykorzystaniem wbudowanych funkcji.

Wyszukanie tabeli posiadającej kolumnę

Skrypt wykorzystywany jest wtedy, gdy podejrzewamy nazwę kolumny, ale nie znamy bazy i znajdujących się w niej tabel. Przypuśćmy, że chcemy odnaleźć wszystkie tabele, ze schematami włącznie, które posiadają kolumnę zawierającą w sobie słowo Numer. Popatrzmy na pierwsze rozwiązanie:

SELECT s.name+'.'+t.name [Table], c.name [Column]
FROM sys.schemas S
JOIN sys.tables T ON s.[schema_id]=T.[schema_id]
JOIN sys.columns C ON T.[object_id]=C.[object_id]
WHERE C.name LIKE '%Numer%'

Widać wyraźnie, że wykorzystywane są trzy widoki systemowe: kolumn, tabel i schematów. To naturalny zapis języka SQL, ale nie tak optymalny jak ten, który skorzysta z funkcji do pobierania metadanych:

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+OBJECT_NAME([object_id]) [Table], [name] [Column]
FROM sys.columns
WHERE name LIKE '%Numer%'

Drugie zapytanie jest nie tylko krótsze w zapisie - krótszy i prostszy jest plan wykonania. Zapytanie wykona się szybciej nie tylko w teorii (plan wykonania pokazuje 81% czasu dla pierwszej instrukcji i 19% dla drugiej), ale i w praktyce. Nie są to różnice duże, bo i tabele rzadko osiągają znaczne rozmiary. Rzadko też takie informacje są potrzebne po stronie aplikacji korzystających z baz danych. W przypadku narządzi typu ORM jest to jednak zauważalna różnica.

Wskazanie obiektów, z których korzysta wyzwalacz

Tym razem zadanie polega na odnalezieniu wszystkich obiektów, które są wymagane do poprawnego działania wyzwalacza. Wyzwalacz taki, identyfikowany przez nazwę, może korzystać z różnych tabel, może wywoływać inne procedury i funkcje. Popatrzmy na przykładowe rozwiązanie:

SELECT O.name Obiekt, RO.name Zależność, C.name Kolumna
FROM sys.sql_dependencies D
JOIN sys.objects O ON D.[object_id]=O.[object_id]
JOIN sys.objects RO ON D.referenced_major_id=RO.[object_id]
LEFT JOIN sys.columns C ON D.referenced_minor_id=C.column_id AND D.referenced_major_id=C.[object_id]
WHERE O.name='tr_Wyzwalacz'

Po wykonaniu zapytania otrzymamy trzy kolumny:

Jeżeli wyzwalacz wymaga kolumny z tabeli, w kolumnie Kolumna znajduje się wykorzystywana kolumna. Jeżeli relacja zależności wskazuje na procedurę lub inny specyficzny obiekt, kolumna Kolumna jest pusta. To samo zapytanie można zapisać nieco inaczej:

SELECT OBJECT_NAME(D.[object_id]) Obiekt, OBJECT_NAME(referenced_major_id) Zależność, C.name Kolumna
FROM sys.sql_dependencies D
LEFT JOIN sys.columns C ON D.referenced_minor_id=C.column_id AND D.referenced_major_id=C.[object_id]
WHERE D.[object_id]=OBJECT_ID('tr_Wyzwalacz')

Korzystanie z funkcji OBJECT_NAME oraz OBJECT_ID nie wymaga wykonywania złączeń, więc plan wykonania będzie prostszy (w środowisku testowym SQL Server 2012 pokazuje 69% dla pierwszej instrukcji i 31% dla drugiej). To tylko jedno z wielu zastosowań. Popatrzmy na jeszcze inny przykład i nowe funkcje do pobierania metadanych.

Pobieranie szacunkowej liczby rekordów w tabelach

Duże tabele mają jeden problem - wykonywanie na nich zapytań wymagających pełnego skanowania wszystkich rekordów (wszystkich liści drzewa indeksu grupującego lub sterty) może chwilę potrwać. Wiele aplikacji nie wymaga bardzo precyzyjnych szacunków, wiele aplikacji działa w trybie pracy ciągłej i kilka rekordów pomyłki nie wpływa negatywnie na cały proces. To, że wyświetlimy na interfejsie, że mamy 136981 rekordów nie oznacza, że za kilka sekund, gdy użytkownik zechce zobaczyć ostatnie wpisy, nie będzie tych rekordów 136984 lub 136985. Aplikacja tak czy tak powinna sobie z tym radzić. Istnieje w bazie danych dynamiczny widok, który z dużą dokładnością poda nam liczbę rekordów w tabeli - sys.dm_db_partition_stats.

Popatrzmy na kolejną porcję wbudowanych funkcji do pobierania metadanych.

SELECT
    T.name [Name],
    MAX(row_count) [Rows]
FROM sys.dm_db_partition_stats S
JOIN sys.tables T ON S.[object_id]=T.[object_id]
GROUP BY s.[object_id], t.name
ORDER BY MAX(row_count) DESC

W przykładowym systemie moglibyśmy otrzymać taki oto rezultat:

W pokazanym zapytaniu wykorzystałem pewną właściwość - widok systemowy sys.tables zwraca tylko obiekty (tabele) użytkownika. Gdybyśmy zechcieli pobrać wszystkie obiekty, należałoby zamiast widoku sys.tables złączyć tabelę sys.objects i wykorzystać do filtrowania kolumnę is_ms_shipped. W wersji wykorzystującej wbudowane funkcje również można nałożyć taki sam filtr i będzie on wyglądał następująco:

SELECT
    OBJECT_NAME(S.[object_id]) [Name],
    MAX(S.row_count) [Rows]
FROM sys.dm_db_partition_stats S
WHERE OBJECTPROPERTY(S.[object_id], 'IsUserTable')=1
GROUP BY S.[object_id]
ORDER BY MAX(row_count) DESC

W tym przypadku jawnie wskazujemy, że chcemy tylko tabele użytkownika. Znów plan wykonania wyraźnie wskazuje różnicę (68% dla pierwszego zapytania i 32% dla drugiego). Warto bliżej przyjrzeć się pozostałym wartościom pierwszego parametru funkcji OBJECTPROPERTY. Można tam znaleźć mnóstwo różnych atrybutów różnych obiektów i warto, choćby pobieżnie, przyjrzeć się im.

Podsumowanie

Wykorzystanie funkcji pomocniczych do pobierania metadanych wiąże się najczęściej z ograniczeniem liczby wymaganych złączeń. Ograniczenie liczby złączeń to nie tylko obniżenie kosztu wykonania zapytania. To także łatwiejsze grupowanie i agregacje, ze względu na pewien wymóg - wszystkie kolumny, które nie są w funkcji agregującej, muszą się znajdować na liście kolumn w sekcji GROUP BY. A tak swoją drogą - najważniejsze to poznanie kolejnego narzędzia, które, nie wiadomo kiedy, może się przydać. A dla tych, którzy wolą rozwiązania tradycyjne, może przydadzą się przykłady, z życia wziętych, przedstawionych we wpisie rozwiązań popularnych problemów.

Kategoria:SQL Server