Masowe usuwanie zdjęć z bazy sello

[hezer 0]

Witam,

mam drobny problem ze zdjęciami w bazie sello.

Obecnie moja baza zajmuje ponad 10GB z czego ponad 8GB to są zdjęcia i szukam sposobu na ich usunięcie.

 

Niestety stanąłem w martwym punkcie dlatego też postanowiłem szukać pomocy tutaj.

 

Po kolei co już zrobiłem (uprzedzam, że moja znajomość SQL jest praktycznie zerowa):

 

1. Usunąłem wszystkie powiązania zdjęć z towarami z poziomu sello.

 

2. Sprawdziłem ID wszystkich zdjęć prostym zapytaniem:

 

Select im_ID from Im__Image

 

3. Sprawdziłem ID zdjęć użytych w aukcjach prostym zapytaniem:

 

Select ap_pictureID from au_picture

 

4. Przyrównałem obydwie kolumny i odseparowałem ID zdjęć, które są użyte w aukcjach.

 

5. Otrzymałem 14000 ID zdjęć, które teoretycznie mogę usunąć.

 

Teraz tu mam problem. Nie mam pomysłu jak hurtem je usunąć. Sprawa jest o tyle trudna, że te ID nie zawsze idą jeden po drugim.

 

Tam gdzie przykładowo miałem 50+ ID jeden po drugim to usunąłem je zapytaniem:

 

delete from Im__Image where im_Id >=10000 and im_Id <=10050

 

No i faktycznie to usuwanie powoli zmniejszało moją bazę, ale większość tych zdjęć ma ok 7-10 ID pod rząd więc zapytaniem usuń od do nie wchodzi w grę przy tak dużej liczbie zdjęć.

 

Jest jakiś sposób żeby je usunąć mając już listę tych ID?

Idzie je może jakoś 'uporządkować'?

Czy może istnieje jakiś prostszy sposób na usunięcie tych zdjęć?

 

Mam wrażenie, że robię to na około, ale tak jak mówiłem, moja wiedza na temat SQL jest dość marna, ale kombinuję jak mogę.

 

Proszę o pomoc

[PanBanBan Marcin 185]

Podpowiem tak - próbowałem się kiedyś z tym bawić od strony bazy ale za dużo myślenia Po prostu są takie czynności w obsłudze Sello, że nie opłaca się dorabiać rozwiązań czy bawić od strony bazy - np. ręczne usunięcie zdjęć z towarów z Sello jest szybsze niż całe to kombinowanie. A ze starych aukcji to trzeba opisy usunąć.

 

No i jeśli jeszcze nie sprawdzałeś to w liście porad pkt 3.41 masz całą procedurę jak wyczyścić bazę Sello - przynajmniej to co wiedziałem gdy to pisałem ale chyba nic nie doszło nowego podczas mojej nieobecności na forum

[HotKey . 249]

No i jeśli jeszcze nie sprawdzałeś to w liście porad pkt 3.41 masz całą procedurę jak wyczyścić bazę Sello - przynajmniej to co wiedziałem gdy to pisałem ale chyba nic nie doszło nowego podczas mojej nieobecności na forum

 

 

Nie raczej nic nie doszło, ja pilnowałem 

[hezer 0]

Oczywiście punkt 3.41 jest mi znany. Problem w tym, że zaczyna to nie wystarczać, a liczba 14000 nieużywanych zdjęć ( bez połączenia z towarem, miniaturką, aukcją ) dała mi nadzieję na to, że bazę da się zmniejszyć.

 

Proszę mi tylko powiedzieć, czy generalnie mój pomysł ma sens i nie naruszy mi w żaden sposób bazy. Gdybym się spiął i postanowił usunąć każde zdjęcie ID po ID.

 

[itbrat ☺ 12]

Zrób sobie najlepiej kopie przed takimi zabawami

Za pomocą Management Console zrób backup bazy i wyeksportuj ją do pliku.

Na jakiejś innej instancji SQL serwera, innym kompie lub jak umiesz zmieniać dane, to na tej samej. Wgraj tą bazę jako inaczej nazwaną np. sello_test.

 

Kod który napisałem na szybko, nie gwarantuje, że zadziała. ( U mnie skasował 14000 obrazków - u mnie działa ) Czy jakoś wpłynie źle? Nie wiem. Z punktu bazodanowego, wszystkie relacje zostały usunięte lub nadpisane. Aukcje mają wtedy id miniaturki na Null.

 

USE [twoja_baza_testowa]

CREATE TABLE #TempTable (
 ID INT,
)

INSERT INTO #TempTable (ID)
 SELECT im_Id
 FROM im__Image

   WHERE im_Id IN
   (
     SELECT ap_PictureId
     FROM au_Picture
   )
   AND im_Id >= 1000

UPDATE au__Auction SET au_ThumbnailId = NULL WHERE au_ThumbnailId IN ( SELECT * FROM #TempTable);
DELETE FROM au_Picture WHERE ap_PictureId IN ( SELECT * FROM #TempTable);
DELETE FROM im__Image WHERE im_Id IN ( SELECT * FROM #TempTable);
DROP TABLE #TempTable

 

Proste zapytania SELECT IN + UPDATE + DROP.

Do pisania zapytań używam DataGrip 2017.1.1

 

Pobiera wszystkie fotki z id >= 1000, które znajdują się w tabeli au_Picture (Twój 4 punkt) zapisuje te ID do tymczasowej tabeli. Następnie po schodkach zostają kasowane relacje, a na samym końcu obrazki.

 

Jeśli coś skopiesz, to na własną odpowiedzialność. Ja zaczynam od backupu, a potem dłubie sobie na środowisku testowym, nie produkcyjnym

 

 

LUB Nadpisz zdjęcia zwykłym JPG 1x1

https://forum.sello.pl/index.php?topic=10579.0

 

 

 

Pozdrawiam.

 

 

PS: @BarteK, im_SrcPath w im__Image, to ficzer, nie? :>

 

[hezer 0]

Oczywiście każde testy przeprowadzam sobie na kopii bazy w moim środowisku testowym

 

Dziękuję za to zapytanie, na takie coś w życiu bym nie wpadł.

 

Generalnie dzisiaj rano wpadłem na trochę inny pomysł, który też chyba zadziałał ( tylko jak zwykle nie wiem czy czegoś przy takim czymś nie uszkodziłem )

 

Problem rozwiązałem tak ( proszę o weryfikacje czy mogłem coś przy tym skopać lub o czymś zapomnieć ):

 

- Pkt 1,2,3,4,5 zrobiłem tak jak pisałem na początku.

 

- Otrzymaną listę ID wrzuciłem do Exela i dodałem przy każdym ID ' delete from im__Image where im_Id = '

 

- Otrzymałem +/- 14000 takich wierszy:

 

delete from im__Image where im_Id =26915

delete from im__Image where im_Id =26917

delete from im__Image where im_Id =26923

delete from im__Image where im_Id =26924

delete from im__Image where im_Id =26925

delete from im__Image where im_Id =26927

delete from im__Image where im_Id =26928

delete from im__Image where im_Id =26929

delete from im__Image where im_Id =26930

delete from im__Image where im_Id =26931

delete from im__Image where im_Id =26932

 

- Podzieliłem to na ' paczki ' po 750 linijek ( bo tyle się mieściło w zapytaniu SQL w programie serwisowym )

 

- Wrzuciłem po kolei te 20 paczek.

 

Miało to sens czy wyszedłem na idiotę?

 

Mogłem o czymś zapomnieć?

 

Nie skopie to bazy?

[itbrat ☺ 12]

- Pkt 1,2,3,4,5 zrobiłem tak jak pisałem na początku.

- Otrzymaną listę ID wrzuciłem do Exela i dodałem przy każdym ID ' delete from im__Image where im_Id = '

- Otrzymałem +/- 14000 takich wierszy:

 

delete from im__Image where im_Id =26915

delete from im__Image where im_Id =26917

delete from im__Image where im_Id =26923

delete from im__Image where im_Id =26924

delete from im__Image where im_Id =26925

delete from im__Image where im_Id =26927

delete from im__Image where im_Id =26928

delete from im__Image where im_Id =26929

delete from im__Image where im_Id =26930

delete from im__Image where im_Id =26931

delete from im__Image where im_Id =26932

 

- Podzieliłem to na ' paczki ' po 750 linijek ( bo tyle się mieściło w zapytaniu SQL w programie serwisowym )

 

- Wrzuciłem po kolei te 20 paczek.

 

Miało to sens czy wyszedłem na idiotę?

 

Straciłeś kupę czasu, na coś co można było zrobić prościej zapytaniem wyżej.

DELETE FROM im__Image WHERE im_Id IN ( SELECT * FROM #TempTable);

Twoje 14000 zapytań w excelu w jednej linii.

 

Mogłem o czymś zapomnieć?

O relacjach obrazek - miniaturka - aukcja.

 

Nie skopie to bazy?

Nie powinieneś, gdyż jak istnieje relacja nie usuniesz rekordów.

Jak sello zachowuje się po przeczyszczeniu? Są jakieś anomalie?

 

 

Podpowiem tak - próbowałem się kiedyś z tym bawić od strony bazy ale za dużo myślenia Po prostu są takie czynności w obsłudze Sello, że nie opłaca się dorabiać rozwiązań czy bawić od strony bazy - np. ręczne usunięcie zdjęć z towarów z Sello jest szybsze niż całe to kombinowanie. A ze starych aukcji to trzeba opisy usunąć.

Porządnie przygotowany skrypt, odpalony raz na kilka miesięcy zawsze będzie szybszy. Czytając dokumentację najlepiej zawsze iść od korzenia, po relacjach w innych tabelach.

Dla osoby znającej SQL (MSSQL) to raczej robota na max 3h. (Poznanie dokumentacji i pisanie zapytania wraz z testami).

 

@hezer bądź ostrożny podczas wykonywania zapytań, skoro Twoja znajomość SQL jest średnia. Niepoprawnie napisane polecenie DROP może zaboleć.

 

[hezer 0]

O relacjach obrazek - miniaturka - aukcja

 

W pkt 4 wykluczyłem sobie ID wszystkich zdjęć posiadających relacje tj. tych które były użyte w aukcjach.

 

Jak sello zachowuje się po przeczyszczeniu? Są jakieś anomalie?

 

Po moich manewrach kompaktowanie bazy wywala błąd ' Kompaktowanie bazy nie powiodło się '.

 

Chyba przekombinowałem ;/

 

@hezer bądź ostrożny podczas wykonywania zapytań, skoro Twoja znajomość SQL jest średnia. Niepoprawnie napisane polecenie DROP może zaboleć.

 

Oczywiście nigdy nie wykonam zapytania bez wcześniejszych prób na środowisku testowym i bez wcześniejszego zrobienia kopii.

[hezer 0]

Ponowiłem moje czynności na świeżej kopii bazy.

 

Tym razem podczas usuwania zdjęć kompaktowałem bazę co 3000 usuniętych ID.

 

Baza zmniejszyła się z 10,3GB do 7,3GB !

 

Póki co odchudzona baza wydaje się funkcjonować normalnie, transakcje się pobrały, aukcje tak samo, można wystawić coś nowego, nowe zdjęcia wchodzą do bazy, archiwizacja i kompaktowanie działa.

 

W poniedziałek planuje spróbować już normalnie na niej pracować

 

@efiku dzięki za Twoje zapytanie, nie miałem okazji jeszcze go przetestować, ale myślę że nieraz mi się przyda

[itbrat ☺ 12]

Spoko.

Zapytanie robi dokładnie to samo Twój sposób, tylko dużo szybciej i raczej bez pomyłek.

Ja nie kompaktuje przez ten program, gdyż po prostu nie widzę tam żadnej bazy Wolę Management Studio.

[Next 0]

Odświeżę temat, waga bazy dobija do niebezpiecznych 10GB. Okazuje się, że połowe zajmują zdjęcia, z których nie korzystamy. Po wykonaniu powyższego zapytania pojawia się komunikat:

 

The DELETE statement conflicted with the REFERENCE constraint "FK_it_ItemPictures_im__Image". The conflict occurred in database "Testowa", table "dbo.it_ItemPictures", column 'pc_PictureId'.
The statement has been terminated.

 

Da się temu jakoś zaradzić?

 

Co ciekawe, skrypt usunął zdjęcia z aukcji przygotowanych, trwających oraz zakończonych natomiast zdjęcia w zakładce Towary pozostały. SSMS pokazał, że Shrink zwolni około 150MB.

[Bartosz Rosa 1 770]

To oznacza, że zdjęcia są powiązane z towarami i baza nie pozwoli ich usunąć, dopóki nie usunie się powiązań z tabeli it_itemPictures - oczywiście nie wszystkich, tylko tych, które nie są już potrzebne.

[Bartosz Chęcik 0]

Jest pomysl aby podmienic zdjecia na 1x1px - co sadzicie o tym ?

[Ks. Robak 314]

Jest pomysl aby podmienic zdjecia na 1x1px - co sadzicie o tym ?

 

Kiedyś już ktoś (chyba Bartek) to zaproponował.

Wydaje się to dobrym pomysłem.

 

Ale póki co nie pojawił się na forum przepis, jak tego dokonać we własnym zakresie.

[Next 0]

Na liście towarów podmieniłem zbiorczo miniaturki na 1x1px, odpaliłem skrypt:

 

delete from Im__Image where im_Id >=1000

 

Później Shrink bazy, który odzyskał 50Mb.

 

Zastanawia mnie, dlaczego po wykonaniu skrpytu wyżej, nie zwolniło się miejsce w bazie pomimo tego, że zdjęć w zakładkach przygotowane, trwające i zakonczone ich nie było. Shirnk bazy oczywiście zrobiony.

[Ks. Robak 314]

Na liście towarów podmieniłem zbiorczo miniaturki na 1x1px, odpaliłem skrypt:

 

delete from Im__Image where im_Id >=1000

 

Później Shrink bazy, który odzyskał 50Mb.

 

Zastanawia mnie, dlaczego po wykonaniu skrpytu wyżej, nie zwolniło się miejsce w bazie pomimo tego, że zdjęć w zakładkach przygotowane, trwające i zakonczone ich nie było. Shirnk bazy oczywiście zrobiony.

 

50 MB to faktycznie mało jak na obrazki, ale być może właśnie tyle miałeś tych obrazków ?

 

Zwróć uwagę, że Sello trzyma każdy obrazek tylko 1x, niezależnie od tego do ilu aukcji czy towarów jest podpięty.

Stąd mogło być tak, że usunąłeś obrazki z 1000 aukcji, ale faktycznych obrazków było np. 20.

[hezer 0]

W zakładce Towary zdjęcia pozostały ponieważ są tam używane, trzeba najpierw usunąć je z tamtej zakładki, a potem wykonać zapytanie. Jeżeli zdjęcia nie będą używane w tamtym miejscu to będą one postrzegane jako niepotrzebne w bazie.

 

Ja tak to rozumiałem i tak to zrobiłem.

 

PS.

 

Robiłem czyszczenie moją metodą ( opisaną na początku wątku ) i z bazy usunęło mi 3GB zdjęć.

 

Później zrobiłem to zapytaniem co wysłał @efiku i usunęło mi wszystkie zdjęcia ( z 10 GB do 3GB ). Baza nie posiadała żadnych zdjęć. Po pierwszym pełnym pobieraniu wróciło mi 3GB zdjęć używanych i wszystko gra do dnia dzisiejszego.

 

[itbrat ☺ 12]

Usiądę niebawem nad lepszym skryptem. Podmiana na pi

U nas nie używamy Sello do wystawiania aukcji bo jest to w skrócie: problematyczne. Potrzebowałem czegoś co mi uwolni bazę z niepotrzebnych zdjęć, a wracają tylko potrzebne.

 

 

Tu jest jak coś jpg 1x1 białe tło:

0xFFD8FFE000104A46494600010101006000600000FFDB004300080606070605080707070909080A0C140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27393D38323C2E333432FFDB0043010909090C0B0C180D0D1832211C213232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232FFC00011080001000103012200021101031101FFC4001500010100000000000000000000000000000007FFC40014100100000000000000000000000000000000FFC40014010100000000000000000000000000000000FFC40014110100000000000000000000000000000000FFDA000C03010002110311003F00BF800FFFD9

 

@Ks. Robak, odpisałem na PW.

[Next 0]

Mamy około 37 tysięcy zdjęć, trwających aukcji 3 tysiące a zakończonych około 25 tysięcy. I te wszystkie zdjęcia zniknęły a miejsce się nie zwolniło. Jedyne zdjęcia jakie zostały to w zakładce towary. W tej zakładce można zbiorczo zaznaczyć wszystkie towary i sprawdzić jakie są rozmiary zdjęcia. Żadne z nich nie dochodzi nawet do 100kb. Towarów ze zdjęciem jest około 1500. Zamieniłem je zbiorczo na 1x1px, później shrink i miejsce się nie zwolniło.

[Ks. Robak 314]

Usiądę niebawem nad lepszym skryptem. Podmiana na pi

U nas nie używamy Sello do wystawiania aukcji bo jest to w skrócie: problematyczne.

 

Możesz opisać jak szybciej wystawiacie aukcje ?

Macie jakiś osobny program do tego ?

Czy inny schemat pracy ?

[Bartosz Rosa 1 770]

A co mówi skrypt sprawdzający zajętość miejsca w bazie w konkretnych tabelach?

[itbrat ☺ 12]

IF OBJECT_ID('TEMPDB..tempStats') IS NOT NULL
DROP TABLE IF EXISTS tempStats

  CREATE TABLE tempStats
(
ID int identity(1,1)
,Table_Name VARCHAR(123)
,Table_Rowcnt INT
,ReservedSpaceKB VARCHAR(25)
,SpaceUsedKB VARCHAR(25)
,Index_SizeKB VARCHAR(25)
,UnusedSpaceKB VARCHAR(25)
)
INSERT INTO tempStats
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

-- convert spaces from KB to MB
SELECT
  temp.Table_Name as 'Nazwa Tabeli'
,Table_Rowcnt as 'Liczba wierszy'
,ROUND(CAST(REPLACE(ReservedSpaceKB, 'KB', '') AS FLOAT) / 1024,5) AS 'Przestrzen zarezerwowana [MB]'
,ROUND(CAST(REPLACE(SpaceUsedKB, 'KB', '') AS FLOAT) / 1204,5) AS 'Przestrzen uzywana [MB]'
,ROUND(CAST(REPLACE(Index_SizeKB, 'KB', '') AS FLOAT) / 1024,5) AS 'Rozmiar indeksu [MB]'
,ROUND(CAST(REPLACE(UnusedSpaceKB, 'KB', '') AS FLOAT) / 1024,5) AS 'Nieuzywana przestrzen [MB]'
FROM tempStats temp
ORDER BY 'Przestrzen uzywana [MB]' desc
DROP TABLE IF EXISTS tempStats

 

U mnie największy problem to em_Source z ponad 4GB danych

[itbrat ☺ 12]

Wszystkie zdjęcia aukcji od teraz mają 1x1 obrazek

+

Kasowanie maili z sello do dnia X.

 

= z 8GB do 4,4 zszedłem. Skrypty udostępniam bo czemu by nie

 

EDIT:

Zrobię kiedyś temat gdzie będę umieszczał swoje skrypty.

 

 

 

@Bartek, w wiadomościach em_Email jest kolumna em_Soruce typu Text, zmieńcie ją na typu big int i dajcie referencje do em_Source.es_EmailID, dodajcie też kaskadowe kasowanie. Usuwam rekord z em_email i automatycznie się kasuje też z em_Source i em_Attachment.

 

 

 

Nie wiem czy się przyda, ale przetestujcie robiąc przed tym KOPIĘ

 

https://forum.sello.pl/index.php?topic=10579.0

[lagren ... eee kiedy 1.40? 31]

Nie wiem czy się przyda, ale przetestujcie robiąc przed tym KOPIĘ

 

Dziękuję, przyda się nie jednemu .... zapłacone w $ello plusie (lokalna waluta)

[Bartosz Rosa 1 770]

To są tzw "pomuki" .

 

Zobaczę co z tymi mailami, ale... później