CTE i wydajność

Home

Ten post będzie o tym jak można zrobić sobie krzywdę stosując skądinąd bardzo fajne narzędzia. W tym przypadku mam na myśli CTE (ang. Common Table Expressions). Moim zdaniem stosowane z umiarem podnoszą czytelność kodu, z drugiej jednak strony użycie CTE może wpłynąć negatywnie na wydajność naszych zapytań.

Należy pamiętać o tym, że CTE nie mają fizycznej reprezentacji w tempdb tak jak tabele tymczasowe czy zmienne tabelaryczne. Na CTE można patrzeć jak na taki tymczasowy, nie zmaterializowany widok. Kiedy MSSQL wykonuje zapytanie i napotka CTE to odwołanie do tego CTE zastępuję jego definicją. W związku z tym jeśli dane CTE jest używane kilkakrotnie w danym zapytaniu to ten sam kod zostanie wykonany kilka razy i MSSQL tego nie optymalizuje. Ten kod pokazuje to zachowanie:

WITH  Test (Id)  
AS (SELECT  NEWID())
SELECT * FROM Test
UNION ALL
SELECT * FROM Test

Na ekran zostaną wypisane 2 różne identyfikatory. Gdyby Test było tabelą otrzymalibyśmy ten sam wynik. W skrajnych przypadkach może to spowodować, że zapytanie będzie koszmarnie wolne. Optymalizacja jest natomiast prosta. Wystarczy w pewnym momencie wrzucić dane do tabeli tymczasowej i dalej używać tej tabeli zamiast odwoływać się do CTE.

Jakiś czas temu widziałem przypadek gdzie dzięki temu jakże prostemu zabiegowi zapytanie zamiast wykonywać się kilka minut wykonywało się kilka sekund!

Przydaś do rysowania wykresów

Home

Od jakiegoś czasu sporo pracuję z różnymi seriami danych numerycznych np.: sekwencje identyfikatorów metod wywołanych w programie. Serie takie mogą być bardzo długie i potrzebowałem narzędzia, które łatwo i szybko pozwoli mi je wizualizować czyli wygenerować wykres, tak abym mógł szybko ocenić co znajduje się w danym pliku. Kombajnów do rysowania wykresów jest dużo, choćby Excel, ale ja potrzebowałem czegoś jeszcze prostszego. Idealnie abym mógł po prostu zaznaczyć plik z danymi i z menu kontekstowego wybrać polecenie Narysuj wykres. Możliwe, że znalazłbym coś takiego, ale napisanie takie narzędzia samemu zajęło mi dosłownie chwilę.

Po pierwsze zdecydowałem się użyć biblioteki OxyPlot bo jest bardzo prosta w użyciu, a także radzi sobie z długimi seriami danych i pozwala narysować wiele różnych typów wykresów. W drugim kroku stworzyłem bardzo prostą aplikację z jednym oknem w WPF'ie. Do projektu dodałem referencje do bibliotek OxyPlot oraz OxyPlot.Wpf. XAML dla głównego i jedynego okna wygląda w następujący sposób:

<Window x:Class="DrawPlot.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
        xmlns:oxy="http://oxyplot.codeplex.com"
        xmlns:local="clr-namespace:DrawPlot"
        Title="DrawPlot" Height="350" Width="525">
    <Window.DataContext>
        <local:MainViewModel/>
    </Window.DataContext>
    <Grid>
        <oxy:Plot Model="{Binding MyModel}"/>
    </Grid>
</Window>

Trzeci krok to napisanie klasy MainViewModel, która stworzy i zainicjuje obiekt klasy PlotModel. Obiekt ten będzie zawierał informacje potrzebne do narysowania wykresu i przy pomocy binding'u zostanie przekazany do kontrolki Plot. W podstawowej wersji kod tej klasy jest bardziej niż prosty. Zakładam z góry określony format pliku wejściowego tj. każda linia zawiera jedną liczbę, a część ułamkowa musi być oddzielona od części całkowitej kropką. Użyłem klasy LineSeries czyli rysuję wykres liniowy. W poniższym kodzie celowo pominąłem również bardziej przyjazną obsługę błędów czyli jeśli coś się nie powiedzie to po prostu wyświetlam treść błędu i zamykam aplikację.

namespace DrawPlot
{
    public class MainViewModel
    {
        public PlotModel MyModel { get; private set; }

        public MainViewModel()
        {
            try
            {
                var args = Environment.GetCommandLineArgs();

                var lines = File.ReadAllLines(args[1]);

                var lineSeries = new LineSeries();
                for (var i = 0; i < lines.Length; ++i)
                    lineSeries.Points.Add(new DataPoint(i, Double.Parse(lines[i], CultureInfo.InvariantCulture)));

                var model = new PlotModel(Path.GetFileName(args[1]));
                model.Series.Add(lineSeries);
                MyModel = model;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                Application.Current.Shutdown();
            }
        }
    }
}

Ostatni krok to dodanie nowej pozycji do menu kontekstowego. Uzyskałem to przy pomocy dodania odpowiedniego wpisu w rejestrze. W moim przypadku skompilowana aplikacja znajduje się w lokalizacji T:\bin\DrawPlot\DrawPlot.exe.

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\*\Shell\Draw plot]

[HKEY_CLASSES_ROOT\*\Shell\Draw plot\command]
@="T:\\bin\\DrawPlot\\DrawPlot.exe \"%1\""

Narzędzie jest bardzo proste, ale spełnia swoje zadanie. Możne je również łatwo rozbudować na przykład użyć innego rodzaju serii aby narysować wykres kołowy, dodać obsługę innych formatów plików wejściowych itd.

Niepozorna pomyłka

Home

Regularnie przeglądam różne portale na temat szeroko pojętego bezpieczeństwa w IT i co raz to dziwię się jak pozornie małe błędy programistów doprowadzają do poważnych problemów i awarii. Czytając ostatnio taki artykuł przypomniałem sobie rozmowę na temat implementacji stronicowania (ang. paging), czyli podziału dużej liczby rekordów na mniejsze porcje i przesyłanie tych porcji do klienta m.in. w celu ograniczenia ruchu w sieci.

W tym przypadku domyślna wielkość strony była skonfigurowana po stronie serwera, ale wysyłając żądanie klient mógł ją nadpisać i podać własną wielkość strony. Rozwiązanie w miarę standardowe, coś podobnego można zrobić odpytując Active Directory, o czym pisałem w tym artykule. Problem w tym przypadku polegał na tym, że wartość podana przez klienta nie była w żaden sposób sprawdzana po stronie serwera. Teoretycznie można więc było zażądać zwrócenie z serwera dowolnie dużej porcji danych. Innymi słowy, wysyłając żądanie wielkości kilku kilobajtów można było otrzymać odpowiedzieć wielkości na przykład 10 MB, a to przecież gigantyczne zwielokrotnienie. A gdyby takich żądań wysłać kilkadziesiąt, kilkaset, a może kilkanaście tysięcy, a do tego podmienić w żądaniu adres odbiorcy?

O czymś podobnym można było niedawno przeczytać w kontekście protokołu NTP. Wspomniany przeze mnie przypadek to w porównaniu z tym pikuś, gdyż użyty protokół komunikacyjny był specyficzny dla danej aplikacji, liczba użytkowników była niewielka itd. więc i zakres potencjalnego ataku był bardzo ograniczony. Z drugiej strony, skoro programista napisał taki kod raz, to może go napisać drugi, trzeci i w którymś momencie nie będzie to mała aplikacja. Na takie rzeczy należy, więc zawsze zwracać uwagę, nawet jeśli w danym przypadku wydaje się to nadmiarowe. Możliwe, że twórca kodu wybrał takie rozwiązanie celowo, ale równie dobrze może to być po prostu przeoczenie lub pomyłka.

This feature has been disabled by your administrator

Home

Jakiś czas temu zacząłem używać pakiety Office 2013. Wszystko było w porządku dopóki nie zorientowałem się, że OneNote przestał synchronizować się z dyskiem SkyDrive. Dokładniej przy próbie zalogowania się do usługi krzyczał komunikatem:

This feature has been disabled by your administrator

Szybko znalazłem ten artykuł, z którego wynikało, że winne mogą być złe wpisy w rejestrze tj.:

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\SignIn\SignInOptions
HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Internet\UseOnlineContent

W moim przypadku ich brakowało, a więc je dodałem ustawiając na domyślne wartości (odpowiednio 0 oraz 3). Potem zrestartowałem OneNote'a. Po ponownym uruchomieniu aplikacja dłużej zastanawiała się co zrobić, ale w końcu znowu wyświetliła ten samym komunikat.

Postanowiłem, więc użyć programu Process Monitor i zobaczyć jakich kluczy używa jeszcze OneNote. Jest ich bardzo dużo, ale dopisało mi szczęście ponieważ zauważyłem,  że na samym początku odczytywany jest następujący klucz:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\Common\SignIn\SignInOptions

Nie był on wymieniony we wspomnianym artykule, ale jak łatwo zauważyć ścieżka do tego klucza jest prawie taka sama jak do jednego ze wspomnianych kluczy. Postanowiłem, więc skonfigurować go w taki sam sposób czyli ustawić na wartość 0. Okazało się to strzałem w dziesiątkę i teraz OneNote działa mi poprawnie :)

Jeśli nie na tabela tymczasowa to co?

Home

W poście Quiz - coś do poduszki opublikowałem zestaw pytań z Quiz'u jaki zorganizowałem dla kolegów z pracy. Od czasu do czasu wpadają mi do głowy różne zagadki dlatego postanowiłem, że co ciekawsze będę wrzucał ku pamięci na bloga. Oto pierwsza z nich.

Po wykonaniu poniższego kodu na ekran zostanie wypisana wartość 0. Czego należy użyć zamiast tabeli tymczasowej aby aby na ekran została wypisana wartość 2?

CREATE TABLE #Temp (
Id INT IDENTITY,
Value VARCHAR(10)
);

BEGIN TRANSACTION;
INSERT INTO #Temp VALUES('aaa');
INSERT INTO #Temp VALUES('bbb');
ROLLBACK;

SELECT COUNT(1) FROM #Temp;

DROP TABLE #Temp;

Pokaż/Ukryj odpowiedź

Zamiast tabeli tymczasowej należy użyć zmiennej tabelarycznej.

DECLARE @Temp TABLE (
Id INT IDENTITY,
Value VARCHAR(10)
);

BEGIN TRANSACTION;
INSERT INTO @Temp VALUES('aaa');
INSERT INTO @Temp VALUES('bbb');
ROLLBACK;

SELECT COUNT(1) FROM @Temp;