Aplikacje okienkowe napisane przy pomocy biblioteki Perl/Tk to rozwiązanie dające całkiem duże możliwości. Chciałbym pokazać kilka sztuczek, których sposób realizacji może łatwo umknąć w natłoku rozmaitej dokumentacji. Poniższe przykładowe fragmenty kodu odnoszą się do następującego skryptu szkieletowego:
#!/usr/bin/perl
use Tk;
use strict;
use warnings;
my $Win = MainWindow->new (
-title => 'Testowe okienko'
);
# dodatkowe ustawienia koniecznie przed MainLoop
MainLoop;
1. Blokowanie zmiany rozmiaru okna
Aby pozbawić naszą aplikację (lub jedno z jej okien) możliwości zmiany rozmiaru okna, musimy podpiąć do zdarzenia <Configure> tzw. callback - czyli de facto obsługę zdarzenia. W callbacku spowodujemy, że mimo działań użytkownika podejmującego próbę zmiany rozmiaru, początkowe rozmiary okna zostaną natychmiast przywrócone:
$Win->bind('<Configure>' => sub {
my $event = $Win->XEvent;
$Win->maxsize($event->w, $event->h);
$Win->minsize($event->w, $event->h);
});
A propos, sztywne zdefiniowanie rozmiarów okna może być osiągnięte w taki sposób:
$Win->minsize(qw(700 525));
gdzie 700 to oczywiście szerokość, a 525 - wysokość.
2. Obsługa zamknięcia okna
Na analogicznej zasadzie jak w przykładzie powyżej możemy zablokować zamknięcie okienka przez przycisk [x] z prawego górnego rogu okna. Wymaga to użycia metody protocol:
$Win->protocol('WM_DELETE_WINDOW' => sub {});
W momencie zastosowania powyższej konstrukcji aplikacja może zostać pozbawiona podstawowej funkcjonalności - czyli możliwości jej zamknięcia. Dlatego warto przekierować obsługę zdarzenia protokołu managera okien WM_DELETE_WINDOW na nie-pustą metodę, w której np. wyświetlony zostanie komunikat z odpowiednim pytaniem, nastąpią określone czynności typu zalogowanie zdarzenia zamknięcia aplikacji itd. W sytuacji wyświetlenia pytania potwierdzającego chęć zamknięcia oka (wyjścia z aplikacji), przykładowy kod wygląda następująco:
sub ExitApp {
my $response = $Win->messageBox(
-icon => 'question',
-message => 'Zamknac okno?',
-title => 'Pytanie',
-type => 'YesNo',
-default => 'No');
# zaniechanie jeśli 'Nie'
return if $response eq 'No';
exit;
}
$Win->protocol('WM_DELETE_WINDOW' => \&ExitApp);
3. Skróty klawiszowe
Każda porządna aplikacją powinna obsługiwać skróty klawiszowe. Perk/Tk oczywiście umożliwia wprowadzanie takiej funkcjonalności. Aby przykładowy skrót CTRL-X spowodował takie samo działania, jak wybranie przycisku [x] (zakładając zastosowanie kodu z poprzedniego punktu), wystarczy podpiąć pod określone widget event funkcję ExitApp:
$Win->bind('<Control-x>' => \&ExitApp);
Można sobie wyobrazić wiele scenariuszy, w których aplikacje, skrypty, czy usługi dokonują operacji dodawania nowych danych do bazy SQL Server. O ile w przypadku małych ilości rekordów wydajność może być praktycznie pominięta w rozważaniach, o tyle w przypadku aplikacji (platform) integracyjnych, ilości rekordów wrzucanych do bazy mogą dochodzić do setek tysięcy/milionów dziennie. Wydajność zaczyna mieć wtedy znaczenie co najmniej istotne, a często - priorytetowe.
Załóżmy sytuację początkową - aplikacja napisana w Perlu (lub Javie), która co kilkanaście sekund łączy się do określonego serwera FTP i sprawdza istnienie pliku o danej nazwie w odpowiednim katalogu. Jeżeli plik zostanie odnaleziony, aplikacja odczytuje go i odpowiednio przetwarza jego zawartość. Efektem tego przetwarzania jest około 50000 elementów w tablicy asocjacyjnej (lub zmiennej innego typu) zawierających dane teleadresowe, które mają być wrzucone do tabeli jako 50000 rekordów. Aplikacja łączy się do bazy SQL Server przez ODBC (lub JDBC) i - co wydaje się naturalne - per każdy element tablicy wykonuje operację typu:
INSERT INTO client_data (colA, colB, colC, ...) VALUES ('aaaa', 'bbbb', 'cccc', ...)
Proces dodawania 50000 rekordów do tabeli trwa w produkcyjnych warunkach około 1-2 min.
Opisany scenariusz można pewnie spotkać w nie jednej firmie. Co w nim można zoptymalizować? Jak się okazuje - przynajmniej dwie kwestie.
Przede wszystkim chciałbym uściślić/sprecyzować jedno pojęcie, którym będę się posługiwał - jest nim batch. Batch to zbiór instrukcji T-SQL przesłanych jednocześnie z aplikacji do SQL Server. Batchem bywa więc zarówno jedna instrukcja (SELECT, INSERT, UPDATE), jak i skomplikowany skrypt. Batch jest traktowany i przetwarzanie przez SQL Server jako łączny zestaw operacji, tzn. jest całościowo parsowany, bindowany, algebryzowany i optymalizowany (przy czym algebryzacja i optymalizacja logicznie dotyczą poszczególnych wyrażeń).
Jakie to ma znaczenie w odniesieniu do opisanego powyżej scenariusza? Otóż zasadnicze. Inserty wysyłane są do serwera pojedynczo, tzn. jeden per batch, to po prostu marnotrawstwo zasobów (istnieje tu pewna analogia do charakterystyki kursorów). Dla każdego batcha SQL Server musi wykonać te same stosunkowo złożone czynności kompilacyjne. Dodatkowo dochodzi jeszcze bardzo ważna kwestia warstwy transportu - w pakietach z jedną operacją INSERT stosunek treści (czyli kodu T-SQL) do całościowej ilości przesyłanych informacji jest bardzo niewielki. Skutkuje to tym, że sumarycznie więcej w pakietach informacji sterujących (nagłówkowych) niż meritum - transmisja ma więc minimalną efektywność, a koszty związane z jej obsługą są relatywnie bardzo wysokie. To tak jakby komunikować się za pomocą e-maili czy SMSów, wysyłając w poszczególnych wiadomościach wyłącznie jedną literę.
Należy więc doprowadzić do sytuacji, w której aplikacja będzie wysyłała do SQL Server batche zawierające np. kilkaset operacji INSERT. Wtedy wydajniejsza będzie zarówno komunikacja z serwerem, jak też i procedowanie tych operacji w bazie danych. W środowisku testowym udawało mi się uzyskiwać nawet kilkunastokrotne zwiększenie wydajności opisanego na wstępie scenariusza. Trzeba drogą empiryczną ustalić stosunek ilości insertów w batchu, dla którego wydajność importu całej grupy rekordów jest największa. W moich testach były to okolice dziesiątej części liczby rekordów (dla 50000 było to ok. 6500 insertów w batchu).
Nie należy się raczej obawiać przekroczenia ograniczeń co do ilości znaków w batchu. Maksimum wynosi: 64 KB * rozmiar pakietu (zwykle 4 KB), co daje 256 MB.
Implementacja powyższego zalecenia w warstwie DAL aplikacji jest bardzo prosta, wystarczy w pętli agregować treść instrukcji INSERT (koniecznie oddzielanych nową linią) do zmiennej typu string, a po przekroczeniu ustalonej ich ilości, wysłać tak stworzone zapytanie do bazy danych.
Dodatkowym zabiegiem optymalizacyjnym będzie wyłączenie domyślnej opcji AutoCommit (większość providerów ustawia ją podczas zestawiania połączenia do bazy, domyślnie jest włączona) i objęcie wszystkich batchy jedną transakcją. Spowoduje to dodatkowo zwykle kilkunastoprocentowy wzrost wydajności, gdyż SQL Server nie będzie musiał zatwierdzać transakcji po każdym dodaniu rekordu, które nie zakończy się błędem (AutoCommit) - zrobi to jednorazowo dla wszystkich insertów.
Miałem okazję uczestniczyć w konferencji Microsoft Technology Summit 2006, która odbyła się 24-25 października 2006 w Pałacu Kultury w Warszawie. Poniżej przedstawiam kilka moich subiektywnych uwag i spostrzeżeń związanych z tym wydarzeniem.
Na początek opinie o charakterze ogólnym:
1. Miejsce konferencji, ze względu na dobrą lokalizację i możliwości przestrzenne - trafnie dobrane.
2. Organizacja stosunkowo dobra (w końcu było kilka tysięcy uczestników).
3. Atrakcyjne hostessy - zwłaszcza stanowisko z Xboxami - zdjęcie :)
4. Wyżywienie na akceptowalnym poziomie (przy czym smaczniejsze od obiadów były przekąski).
5. Zbyt krótkie sesje. Według mnie lepiej byłoby zorganizować mniej - ale dłuższych sesji (jak rok temu). Godzina w większości przypadków to stanowczo za mało.
Zależało mi na jak najbardziej zaawansowanych sesjach dotyczących SQL Server 2005 - w nich też starałem się uczestniczyć. Wypadkowy poziom merytoryczny sesji, na których byłem obecny, oceniam na średnio zaawansowany. Dało się usłyszeć sporo ciekawych informacji - z drugiej strony były też sesje, na których czułem zażenowanie i znudzenie (szczególnie na sesji Tomasza Skurniaka, który m.in. nie wie jak wymawia się słowo "deny").
Mój prywatny ranking top 3 prelegentów:
1. Tomasz Kopacz
2. Maciej Pilecki
3. Rafał Łukawiecki
Top 3+1 sejse:
1. Bezpieczenstwo SQL Server 2005
2. Tworzenie wydajnych aplikacji bazodanowych z użyciem SQL Server 2005
3. Strojenie SQL Server 2005
4*. sesje Tomasza Kopacza dotycząca tworzenia aplikacji pod Windows Vista
Ogólnie rzecz biorąc konferencję należy uznać za udaną. Każdy, kto pracuje z technologiami Microsoft, powinien pomyśleć o uczestnictwie w MTS 2008.
W jednym z poprzednich postów napisałem, że SQL Server 2005 teoretycznie ignoruje klauzulę sortującą zawartą w definicji widoku, a jedynym prawidłowym i zalecanym miejscem na jej definiowanie jest zewnętrzny SELECT z widoku. Jak można się domyślić, istnieją sposoby na enkapsulowanie kolejności wierszy w samym obiekcie view - są to jednak tylko efekty uboczne innych operacji. Opiszę dwa z nich:
1. TOP X PERCENT ... ORDER BY kolumna
W wersji SQL Server 2000 efekt sortowania dawała taka konstrukcja:
CREATE VIEW widok AS
SELECT TOP 100 PERCENT *
FROM tabela
ORDER BY kolumna;
W wersji SQL Server 2005 powyższa definicja sortowania nie wywoła. Wydaje się, że programiści Yukonu celowo zablokowali efekt sortujący tego dość popularnego sposobu. Wystarczy jednak zamienić TOP 100 na np. TOP 99.999(..), aby wynikowy recordset znowu był posortowany. Rozwiązanie takie ma pewną wadę: może nie zwrócić wszystkich wierszy. Ze względu na ograniczenie precyzji typu numeric do 38 pozycji, największą liczbą w klauzuli TOP jest:
SELECT TOP (99.999999999999999999999999999999999999) ...
co jednocześnie determinuje maksymalną ilość wierszy wyświetlanych w widoku teoretycznie do liczby 10^38 rekordów. Praktycznie jednak liczba ta jest znacznie mniejsza, więc przy dużych tabelach raczej nie można polegać na takim rozwiązaniu.
2. ROW_NUMBER() OVER (ORDER BY col)
Poniższy kod:
CREATE VIEW widok AS
SELECT kol1, kol2, ..., ROW_NUMBER() OVER (ORDER BY kolX) as kol_sort
FROM tabela;
również sprawi, że utworzony widok będzie posortowany. Powodem tego efektu (ubocznego) jest oczywiście zastosowanie funkcji partycjonującej do stworzenia jednej z kolumn. Funkcja partycjonująca określa porządek rekordów, którym funkcja rankingowa ROW_NUMBER() przyporządkowuje odpowiednie wartości - w sposób naturalny następuje więc sortowanie. Co oczywiste, SQL Server po wykonaniu powyższej operacji nie aplikuje już randomizacji kolejności rekordów, zwracane są więc one w porządku narzuconym podczas generowania rezultatu. Rozwiązanie takie ma pewną wadę: kolumna kol_sort musi występować wśród kolumn odczytywanych z widoku (oczywiście może to odbywać się w sposób niejawny - przez "gwiazdkę") - inaczej SQL Server zaniecha sortowania.
Każde z rozwiązań "sortowania na siłę" ma dość poważne wady, należy więc stosować je w ostateczności, a we wszystkich możliwych sytuacjach sortowanie narzucać w zewnętrznym zapytaniu z widoku. Należy też pamiętać, że opisane wyżej zachowania mogą być zmienione bez ostrzeżenia np. przez service pack.
Interpretacja składni T-SQL przez SQL Server ściśle zależy od systemu powiązań bazy danych (database collation), a czasem - całego serwera (server collation). W dokumentacji nie jest to mocno artykułowane i często kwestia ta bywa niesłusznie pomijana. Poniższy przykład zobrazuje potencjalny problem. Stwórzmy testową bazę danych:
CREATE DATABASE collation_test;
Następnie ustawmy collation na Polish_CI_AS, czyli system powiązań, który nie uwzględnia wielkości liter:
ALTER DATABASE collation_test COLLATE Polish_CI_AS;
Stwórzmy tabelę o nazwie tEstoWa:
CREATE TABLE tEstoWa ( colA int );
Teraz odwołamy się do tabeli, deklarując jej nazwę wyłącznie małymi literami:
-- USE testowa;
SELECT colA FROM testowa;
Zapytanie wykonało się bez żadnego błędu. Zmieńmy następnie collation na Polish_CS_AS (case sensitive):
ALTER DATABASE collation_test COLLATE Polish_CS_AS;
A następnie wykonajmy ponownie zapytanie. Uzyskamy taki oto błąd:
-- USE testowa;
-- SELECT colA FROM testowa;
Msg 208, Level 16, State 1, Line 1
Invalid object name 'testowa'.
SQL Server zwróci analogiczny błąd, jeśli określimy nazwę kolumny nie uwzględniając wielkości liter. Należy pamiętać, że reguły wielkości liter odnoszą się do nazw wszystkich obiektów w bazie danych - m.in. tabel, procedur, funkcji, także zmiennych i parametrów. Niezależne od collation są tylko słowa kluczowe języka T-SQL.
Kilka dni temu w pracy miałem sytuację, że mimo case insensitive collation bazy danych przeniesionej na nowy serwer, procedury, w których do składni podchodzono dość swobodnie (do zmiennej zadeklarowanej jako @zmienna odwoływano się jako @ZMIENNA), zaczęły rzucać błędami. Pomogła dopiero zmiana collation dla całego serwera na case insensitive. Niestety zmiana server collation jest bardzo kłopotliwa, gdyż odbywa się przez rekonfigurację serwera w trybie setup i wymaga przebudowy bazy master (wszystkie inne bazy są dropowane). Warto więc pomyśleć o opisywanych kwestiach już na etapie instalacji SQL Servera.
Chciałbym zwrócić uwagę na potencjalne problemy związane z budowaniem zapytań zawierających podzapytania nieskorelowane (uncorrelated subqueries). W pewnych sytuacjach występują zasadnicze rozbieżności między tym, jak zapytanie pojmuje deweloper, a jak - procesor kwerend SQL Servera. Załóżmy istnienie dwóch tabel:
CREATE TABLE tab1
(
colA int,
colB nvarchar(10)
)
CREATE TABLE tab2
(
colX int
)
-- wypełnianie tabel przykładowymi danymi
INSERT INTO tab1 (colA, colB) VALUES (1,'A')
INSERT INTO tab1 (colA, colB) VALUES (2,'B')
INSERT INTO tab1 (colA, colB) VALUES (3,'C')
INSERT INTO tab1 (colA, colB) VALUES (4,'D')
INSERT INTO tab2 (colX) VALUES (44)
INSERT INTO tab2 (colX) VALUES (55)
Następujące zapytanie:
SELECT colA
FROM tab2
oczywiście zwróci błąd 207 ( Msg 207, Level 16, State 1, Line 1: Invalid column name 'colA'.), gdyż kolumna colA nie istnieje w tabeli tab2. Jeżeli jednak powyższe zapytanie zostanie użyte jako podzapytanie w następującej kwerendzie:
SELECT colB
FROM tab1
WHERE colA IN
(SELECT colA FROM tab2)
SQL Server nie zwróci błędu i co więcej - wykona zapytanie praktycznie w sposób równoważny sytuacji braku warunku WHERE. Chyba nie trzeba nikogo przekonywać, jakie niebezpieczeństwa może to powodować przy okazji operacji modyfikacji danych.
Nasuwa się proste pytanie: o co chodzi i czy jest to błąd? Semantyka podpowiadałaby, że zapytanie wykonywane jest błędnie, w rzeczywistości jednak SQL Server przetwarza je zgodnie ze standardami języków blokowo-strukturalnych, do których zaliczany jest także SQL.
Powyższa kwerenda logicznie realizowane jest w następujący sposób:
1. sprawdzenie istnienia kolumny colA w tabeli tab2
2. kolumna nie istnieje, procesor szuka najbliższej logicznie klauzuli FROM
3. sprawdzenie, czy tabela określona w znalezionej klauzuli FROM zawiera colA
4. kolumna istnieje w tabeli tab1, zostaje więc uwzględniona w podzapytaniu
W efekcie powyższego zapytanie przetwarzane jest jako:
SELECT tab1.colB
FROM tab1
WHERE tab1.colA IN
(SELECT tab1.colA FROM tab2)
SQL Server będzie realizował takie zapytanie przez LEFT SEMI JOINa z warunkiem złączenia:
[tab1].[colA]=[tab1].[colA]
Przy takim warunku złączenia wynikowy zbiór rekordów nie jest w żaden sposób zawężany - to dlatego problematyczna kwerenda zwraca wszystkie rekordy z tabeli tab1. Analogicznie zachowują się także inne silniki BD (w tym Oracle, PostgreSQL, MySQL).
Aby uniknąć problemów, które mogą wynikać z opisanych uwarunkowań, należy deklarować nazwy kolumn w sposób jednoznaczny, tj. albo przez pełną kwalifikację, albo przez aliasy.
Dziękuję znajomemu z pracy - Rafałowi Zwolakowi za wskazanie problematycznego zapytania.
Zadanie: określić jak długo wykonywał się dowolny fragment kodu. Interesuje nas czas w sekundach, który można od razu wyświetlić w dowolnej kontrolce (rzutowanie na string). Poniżej kod w C#:
long Ticks = System.DateTime.Now.Ticks;
// początek kodu objętego pomiarem
for (int i=0;i<1000000;i++)
{
i = i+1;
}
// koniec kodu objętego pomiarem
Ticks = System.DateTime.Now.Ticks - Ticks;
string strSeconds = new System.TimeSpan(Ticks).TotalSeconds.ToString();
Struktura TimeSpan przechowuje czas w postaci określonej liczby tzw. ticków (1 tick = 100 nanosekund), które w powyższym przykładzie przekazywane są do konstruktora za pomocą zmiennej Ticks (typ long integer). TimeSpan udostępnia czas w bardziej dla człowieka zrozumiałej formie przy pomocy kilku property methods:
- TotalMilliseconds
- TotalSeconds
- TotalMinutes
- TotalHours
- TotalDays
|
