Strona używa cookies (ciasteczek). Dowiedz się więcej o celu ich używania i zmianach ustawień. Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.    X

MSSQL i LIMIT z mySQL'a

Mój pierwsyz post :-) Wybaczcie niedociągnięcia.

Dzisiaj przyszło mi się zmierzyć z tematem:Implementing Virtual Mode with Just-In-Time Data Loading in the Window...

W pewnym momencie kodu znajduje się polecenie budujące zapytanie SQL:   command.CommandText = "Select Top " + rowsPerPage + " " + CommaSeparatedListOfColumnNames + " From " + tableName + " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " + lowerPageBoundary + " " + columnToSortBy + " From " + tableName + " Order By " + columnToSortBy + ") Order By " + columnToSortBy; adapter.SelectCommand = command;

Pomyślałem sobie wtedy: "ale by się przydała klauzula LIMIT z mySQL’a."
Zrobiłem test na jednej z tabel w mojej bazie danych CREATE TABLE [dbo].[Expedition]( [ID_Expedition] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ID_Receiver] [int] NOT NULL, -- klucz obcy [ExpeditionDate] [datetime] NULL, [ExpeditionNumber] [varchar](16) NOT NULL ) Są w niej 8192 rekordy. Postanowiłem sprawdzić jak wydajnościowo będzie się miało to zapytanie SQL.
Utworzyłem kod SQL który wyciąga po 10 kolejnych wierszy z tabeli, wrzuca do tabeli tymczasowej żeby ich nie wyświetlało w SSMS: DECLARE @FIRST INT, @COUNT INT, @CNT INT SET @COUNT=10 SET @FIRST=0 SET @CNT=(SELECT COUNT(*) FROM Expedition) PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121) WHILE @FIRST<@CNT BEGIN SELECT * INTO #tmp FROM ( SELECT TOP (@COUNT) * FROM Expedition WHERE ID_Expedition NOT IN ( SELECT TOP (@FIRST) ID_Expedition FROM Expedition ORDER BY ID_Expedition ASC ) ORDER BY ID_Expedition ASC ) AS A DROP TABLE #tmp SET @FIRST=@FIRST+10 END PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121) GO

Czas realizacji: 3.627 sec
START 2011-06-22 13:12:49.310
END 2011-06-22 13:12:52.937

Hmmm… zacząłem się zastanawiać czy da się szybciej.
Pogrzebałem i znalazłem najbardziej powtarzające się zapytanie z podwójnym TOP i sortowaniem. Sprawdziłem: DECLARE @FIRST INT, @COUNT INT, @CNT INT SET @COUNT=10 SET @FIRST=0 SET @CNT=(SELECT COUNT(*) FROM Expedition) PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121) WHILE @FIRST<@CNT BEGIN SELECT * INTO #tmp FROM ( SELECT TOP (@COUNT) * FROM ( SELECT TOP (@COUNT+@FIRST) * FROM Expedition ORDER BY ID_Expedition ASC ) AS D ORDER BY ID_Expedition DESC ) AS A ORDER BY ID_Expedition ASC DROP TABLE #tmp SET @FIRST=@FIRST+10 END PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121) GO

Czas realizacji: 7.140 sec
START 2011-06-22 12:54:54.467
END 2011-06-22 12:55:01.607

Hmmm…. Dwa razy dłużej. Odpada.

Pomyślałem sobie: „Zaraz zaraz. Tabela ma przecież kolumnę ID_Expedition typu IDENTITY. Zakładając, że ma kolejne ID od 1 co 1 ….”.
No to sprawdziłem: DECLARE @FIRST INT, @COUNT INT, @CNT INT SET @COUNT=10 SET @FIRST=1 SET @CNT=(SELECT COUNT(*) FROM Expedition) PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121) WHILE @FIRST<@CNT BEGIN SELECT TOP (@COUNT) * INTO #Tmp FROM Expedition WHERE ID_Expedition>=@FIRST ORDER BY ID_Expedition ASC DROP TABLE #tmp SET @FIRST=@FIRST+10 END PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121) GO

Czas realizacji: 0.757 sec
START 2011-06-22 12:55:01.623
END 2011-06-22 12:55:02.390

No rewelacja. Nieporównywalnie szybciej niż pierwsze rozwiązanie.

No tak ale co będzie jak pokasuję rekordy albo będę miał jako klucz więcej niż jedną kolumnę np. przy złączeniach tabel. Tutaj postanowiłem sprawdzić jak będzie się miało w tym przypadku wydajność z ROW_NUMBER(). DECLARE @FIRST INT, @COUNT INT, @CNT INT SET @COUNT=10 SET @FIRST=1 SET @CNT=(SELECT COUNT(*) FROM Expedition) PRINT 'START ' + CONVERT(CHAR(28), GETDATE(), 121) WHILE @FIRST<=@CNT BEGIN SELECT TOP (@COUNT) * INTO #Tmp FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID_Expedition ASC) AS RowNumber, * FROM Expedition ) AS a WHERE RowNumber >= @First ORDER BY RowNumber DROP TABLE #tmp SET @FIRST=@FIRST+10 END PRINT 'END ' + CONVERT(CHAR(28), GETDATE(), 121) GO

Czas realizacji: 2.560 sec
START 2011-06-22 12:55:02.390
END 2011-06-22 12:55:04.950

No to i tak o sekundę lepiej niż rozwiązanie na stronach Microsoftu. Ponadto w klauzuli OVER (ORDER BY ID_Expedition ASC) można umiwścić wiecej kolumn wg, których to ma być posortowane np.: OVER (ORDER BY ID_Receiver ASC, ExpeditionDate ASC).

Tak tylko chciałem sie z Wami podzielić wnioskami. :-)

Pozdrawiam.

P.S. Przydała by sie mozliwość wspawiania pustych spacji szczególnie w kodzie. 

Komentarze

0 nowych
XeonBloomfield   5 #1 22.06.2011 17:13

Jeżeli można coś zrobić szybciej programista stara się to zrobić...

Bardzo ciekawy temat i testy które wykonałeś... ;)

Ardziej   5 #2 22.06.2011 18:32

Coś nowego, dobrze, że samemu ciśniesz temat :)
Co do "tabów" i pustych spacji, no to nie ma co, czekam również :)

flatplanet   3 #3 23.06.2011 00:13

Kursor jest bardziej wydajny niż użycie pętli while.
Warto jeszcze sprawdzić "plan wykonania" może można jeszcze bardziej to zoptymalizować.

pcgyver   5 #4 24.06.2011 08:42

Pętla WHILE posłużyła tylko do zwielokrotnienia operacji SELECT :-)

  #5 24.06.2011 11:31

Mi także brakuje w MSSQL'u instrukcji LIMIT z mySQL'a. Ponadto coś takiego jak mysqldump w MSSQL'u także by się przydało. Może zna ktoś takie polecenie uruchamiające się w linii poleceń a zrzucające wybraną bazkę z MSSQL'a do pliku tekstowego (T-SQL). Chodzi mi o całą bazkę a nie tylko tabelę.

Na marginesie, temat i wpis jak najbardziej ciekawy.

  #6 25.06.2011 11:17

@Andrzej11111, odnośnie dump, użyj Managment Studio, w Object Explorere (okienko po lewej) prawy klawisz myszy na wybranej bazie, Tasks -> Generate Scripts i przeklikaj wizarda, tu masz link http://msdn.microsoft.com/en-us/library/ms178078.aspx

Odnośnie samego wpisu, jeżeli sam to wykoncypowałaś to spoko, chociaż patent jest ogólnie znany. Osobiście uważam że załatwia to sprawę i LIMIT nie jest specjalnie potrzebny.

Dziwią mnie jedynie czasy wykonania, 2,5 sek dla raptem 8k rekordów to za długo. Albo masz zdalnie postawiony serwer i dochodzą narzuty na transferze (słabe łącze z którejś strony), albo mocno obciążony system z lokalnym serwerem, ostatecznie ta pętla WHILE ci tak zamula (jak na moje jest nie potrzebna, nie wnikałem za bardzo w querke), zamiast tabeli tymczasowej możesz użyć WITH

Dobry krótki sample:
http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

Na 4guysfromrolla znajdziesz rozwinięcie tej koncepcji z sortowaniem i filtrowaniem.

pcgyver   5 #7 25.06.2011 23:07

Witam!

Tak długi czas wynika z tego że robię selecty jedynie po 10 rekordów. Czyli z 8k rekordów wychodzi ponad 800 zapytań SQL w pętli WHILE. Gdybym czytał 8 razy po 1000 rekordów to napewno trwało by to krócej. Dlaczego tak po 10? Chodziło o zauważalne sprawdzenie co będzie działać szybciej. Owszem mogłem dokoptować kilkaset tysiecy rekordów do bazy i czytać rzutami po 1000 czy nawet 10k rekordów. Ale mnie chodziło tylko o szybki test z zauważalną różnicą. Wyniki które podałem to jakieś przykładowe z wielu serii testów tych zapytań.

Skąd wogóle wziął się temat? Aplikacja bazodanowa z dedykacją działania przez VPN - wooolne łącze. Mam np okienko z listą wysyłek. W sieci lokalnej okienko pojawia sie prawie odrazu. Ale przez VPN już nie tak odrazu. Stąd pomysł stronicowania wyników. Pokazuje się okienko tylko z np 100 pierwszymi rekordami a reszta doczytuje się w tle, w osobnym wątku. Jak już dojdę do konkretnego działającego kodu to pewnie o tym napiszę. :-)

  #8 11.10.2013 14:54

@Lulz1337 co Ty bredzisz o słabym łączu ... co ma łącze do odpytania bazy ?