Pytanie Czy SQL Server przyspiesza manipulowanie danymi - wyłącz rejestrowanie transakcji?


Używam SQL Server 2005 jako magazynu danych dla wielu danych, w których pracuję analitycznie. To nie jest transakcyjna baza danych, ponieważ nie trafiam na nią aktualizacjami ani nie przechwytuję danych w czasie rzeczywistym. Dostaję kilka danych od moich klientów, ładuję je do SQL Server i wykonuję serię manipulacji. Następnie pobieram fragmenty tych danych i przeciągam je do R, gdzie wykonuję większość moich analiz. Następnie przesyłaję trochę danych do tabel w SQL Server i może zrobić połączenie lub dwa.

Mam sporo czasu, gdy dzienniki w SQL Server stają się coraz większe i zakładam, że tworzenie ich wymaga pewnego nakładu pracy. Jak mogę skonfigurować program SQL Server tak, aby działał z małym rejestrem lub bez logowania? Jeśli rzeczy ulegną uszkodzeniu, cieszę się, że zaczynam od początku. Wszelkie pomysły, jak zrobić to wszystko szybciej?

BTW, nie trzeba mi mówić, jak zmniejszyć logi, już to robię. Ale szkoda, że ​​nie musiałem robić dzienników w pierwszej kolejności. Używam DB tylko do przechowywania danych, ponieważ jest zbyt duży, aby zmieścić się w pamięci w R.

Czy powinienem używać prostszego DB niż Sql Server? Możesz mi powiedzieć, że zabijam mrówkę młotem kowalskim. Ale proszę polecić młot o odpowiedniej wielkości. :)


14
2018-02-21 00:18


pochodzenie


Dlaczego chcesz być restrykcyjny przy zmniejszaniu plików baz danych: karaszi.com/SQLServer/info_dont_shrink.asp - JohnB


Odpowiedzi:


Jak mogę skonfigurować program SQL Server tak, aby działał z małym rejestrem lub bez logowania? ja

Nie wierzę, że możesz.

Jeśli jednak skonfigurujesz bazę danych (każda baza danych na serwerze może być różna) dla prostych kopii zapasowych, plik dziennika nie będzie się rozwijał, dopóki go nie utworzysz. Odbywa się to poprzez ustawienie trybu odzyskiwania na "prosty".

W przypadku prostych kopii zapasowych dziennik służy wyłącznie do przechowywania stanu transakcji, dopóki nie zostaną w całości zapisane w głównej bazie danych.


9
2018-02-21 00:22



Być może właśnie to muszę zrobić. Dzięki za naprawdę szybką odpowiedź. - JD Long


Jednym ze sposobów uniknięcia rejestrowania podczas pracy z dużymi zbiorami danych jest użycie SELECT / INTO. Utworzy on nową tabelę, ale żadna z nich nie zostanie zarejestrowana.

W tym celu należy zwrócić uwagę na kilka rzeczy:

  • Obliczone kolumny stają się zwykłymi kolumnami danych
  • Trzeba też ustanowić kolumny indeksowania i tożsamości

Po prawidłowym wykonaniu może zaoszczędzić nie tylko miejsce, ale i czas przetwarzania.

Alternatywą jest coś, co robię teraz, jako przykład:

UPDATE [MyTable] 
SET    [Message] = REPLACE([Message], N'Content_Type', N'Content-Type')

Działa dobrze, ale aktualizuje całą tabelę, tworząc jeden ogromny zestaw transakcji, zamiast tego możesz:

DECLARE @IDs TABLE ([id] int)
DECLARE @Batch TABLE ([id] int)

INSERT INTO @IDs ([ID]) SELECT [ID] FROM [MyTable]

WHILE EXISTS (SELECT TOP 1 [ID] FROM @IDs)
BEGIN
  INSERT INTO @Batch ([ID]) SELECT TOP 1000 [Id] FROM @IDS

  UPDATE [MyTable] 
  SET    [Message] = REPLACE([Message], N'Content_Type', N'Content-Type') 
  WHERE  [Id] IN (SELECT [Id] FROM @Batch)

  DELETE @IDs WHERE [Id] IN (SELECT [Id] FROM @Batch)
  DELETE @Batch
END

Aktualizuje to tabelę 1000 wierszy jednocześnie, zmniejszając rozmiar transakcji.


6
2018-05-25 13:54





Możesz zminimalizować zużycie dzienników na serwerze SQL, zmieniając model odzyskiwania bazy danych na prosty połączyć. Skoro nie zajmujesz się współbieżnością, a transakcje uważasz za Microsoft Access?


5
2018-02-21 00:37



Przeprowadziłem się do SQL Server, ponieważ regularnie waliłem w głowę o limit 2 GB w programie Access. Prawie zadałem to pytanie w formie "jak mogę przekonać program SQL Server do działania bardziej podobnego do Access", ale bałem się, że otrzymam wiele BS o tym, jak Access sucks, yada yada. Potrzebuję tylko dobrego magazynu danych! - JD Long
Czy możesz podzielić swoje dane na kilka plików baz danych programu Access? Połączona semantyka tabeli w programie Access bardzo ułatwiłaby logiczne ustawienie głównego pliku odwołującego się do kilku plików danych dostępu dziecka. - James
Dobry pomysł, ale uwzględnienie wielu tablic Access będzie całkowitym kludge, które utrudniają analizę. Mając to w SQL Server pozwala mi również przesłać drogie zapytania do bardziej wydajnego serwera. Dostęp wymagałby ode mnie wykonania tych zapytań na komputerze klienta. - JD Long
@mghaoui. sarkazm i dowcipy są mile widziane na stackoverflow. Nie wierzę w żaden sarkazm związany z Accessem. Myślałem, że te uwagi i ta odpowiedź były bardzo pouczające. Dzięki. - TamusJRoyce


Nie sprawisz, że serwer SQL Server będzie prawie szybszy, wyłączając rejestrowanie transakcji, ale rozmiar dziennika może być mniejszy, przechodząc do prostego lub zbiorczego trybu odzyskiwania, jak już sugerowali inni.

Uważam, że nie powinieneś nigdy włączać trybu pełnego przywracania, z wyjątkiem specjalnych przypadków, takich jak twoje, gdy na pewno nie jest to konieczne.

Głównym powodem tego jest to, że dziennik transakcji w pełnej odzysku może być jedyną szansą na odzyskanie w przypadku przypadkowego wykonania aktualizacji, usunięcia lub TRUNCATE, gdy nie masz kopii zapasowych lub wszystkie dane nie znajdują się w kopiach zapasowych.

Istnieje kilka wątków na ten temat, w których czytanie dziennika transakcji było ostatnią nadzieją na odzyskanie.

Jak mogę przywrócić zapytanie UPDATE w SQL Server 2005?

Jak cofnąć operację usuwania w SQL Server 2005? 

Ponownie, w twoim konkretnym przypadku nie jest to prawdopodobnie problem, ale domyślam się, że może być użyteczny dla innych.


3
2018-04-16 18:37





Aby zminimalizować rejestrowanie, korzystaj z prostego modelu odzyskiwania i wykonuj swoją pracę partiami.


2
2018-02-21 15:08



Ponownie czytałem te odpowiedzi i wspomniałem o partiach, które wpadły mi w oko. Czy możesz dać mi lepszy wgląd w to, co masz na myśli, robiąc rzeczy w partiach? Czy wykonanie długiego skryptu z 30 krokami różni się od uruchamiania 30 skryptów? Dzięki za pomoc. - JD Long
przez partie mam na myśli na przykład, jeśli musisz zaktualizować / usunąć 50.000 wierszy, zrobić to w partiach po 1000. i każdej partii w swojej własnej transakcji. możesz to zrobić z pętlą while. w przypadku wkładek można używać funkcji wstawiania zbiorczego. - Mladen Prajdic
w prawdziwym świecie, przynajmniej w Oracle (ouch!), zawsze szybciej przetwarzać dane jako kompletny zestaw, nie rozdzielając go na mniejsze fragmenty. COMMITs wykonują pracę, podobnie jak rozpoczynające się i kończące transakcje. Inną radą jest to, że najszybszym sposobem aktualizacji WSZYSTKICH (lub większości) wierszy w tabeli jest utworzenie nowej tabeli. - Neil Kodner


 Kod przy użyciu EntityFramework do konfiguracji bazy danych, takiej jak odpowiedź Richardsa opisuje:

using (var dbInstance = new YourEntityFrameworkDB_Context())
{
    var sqlConfigConn = dbInstance.Database.Connection as SqlConnection;
    sqlConfigConn.Open();

    using (var sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = sqlConfigConn as SqlConnection;
        sqlCmd.CommandText = String.Format("ALTER DATABASE model SET RECOVERY SIMPLE");
        var result = sqlCmd.ExecuteNonQuery();
    }
    sqlConfigConn.Close();
}

Aby sprawdzić, czy to się udało, po prostu uruchom Management Studio i uruchom: Screenshot Management Studio


EDYTUJ Luty 2018:

MSDN opis dotyczący modelu odzyskiwania

╔══════════╦══════════════════════╦══════════════════════════════════════════╗
║ Recovery ║    Description       ║      Recover to a point in time?         ║
║  model   ║                      ║                                          ║
╠══════════╬══════════════════════╬══════════════════════════════════════════╣
║ Simple   ║ No log backups       ║ Can recover only to the end of a backup. ║
║          ║                      ║                                          ║
║ Full     ║ Requires log backups ║ Can recover to a specific point in time, ║
║          ║                      ║ assuming that your backups are complete  ║
║          ║                      ║ up to that point in time.                ║
║          ║                      ║                                          ║
║ Bulk     ║ Requires log backups ║ Can recover to the end of any backup.    ║
║ logged   ║                      ║                                          ║
╚══════════╩══════════════════════╩══════════════════════════════════════════╝

-1
2018-04-08 07:42