MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Re(12): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Thu, 16 Apr 2009 08:54:10 GMT

da ich wieder mal vor diesem Problem stehe, gibts jetzt ein kleines Update:

Ausgangssituation:

uniqueid, text, lfdnr

seit SQL2005 gibt es die Funktion HASHBYTES, mit der man mit verschiedenen Algorithmen (MD5 etc.) VARBINARY-Hashes generieren lassen kann ...

also erstell ich jetzt einfach eine temp-table und vergleich dann in dieser die MD5Hashes! bei 6 Mio. Datensätze dauert das erstellen der Table zwar auch noch eine Weile, aber besser als ein self join auf CONVERT(VARCHAR(8000), text))

Re(5): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Tue, 14 Oct 2008 19:27:47 GMT

Ich denke den Convert mußt gar nicht explizit angeben, daher halt ichs für schneller.

Mit der, den Temp Table(s) kannst besser die Ergebnisse besser kontrollieren, der Ablauf wird zeitlich überschaubarer und du hast sichere Zwischenschritte. Ein Rollback von mehreren Millionen Zeilen ist eher nicht so lustig.

Re(5): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Tue, 14 Oct 2008 19:27:47 GMT

Ich denke den Convert mußt gar nicht explizit angeben, daher halt ichs für schneller.

Mit der, den Temp Table(s) kannst die Ergebnisse besser kontrollieren, der Ablauf wird zeitlich überschaubarer und du hast sichere Zwischenschritte. Ein Rollback von mehreren Millionen Zeilen ist eher nicht so lustig.

Re(4): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Tue, 14 Oct 2008 19:21:36 GMT

ah hoppala ... du meintest ja group by in der temptable ... naja nichts desto trotz macht es ja keinen Unterschied, ob ich ein convert innerhalb einer select-Abfrage mach oder ob ich eine temptable mit den selben Parametern generiere - oder täusch ich mich?

Re(3): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Tue, 14 Oct 2008 19:00:11 GMT

Also doch, Du hast mein Posting nicht gelesen / verstanden.
Schade. Viel Glück mit deinem convert.

Re(2): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Tue, 14 Oct 2008 18:54:48 GMT

wäre an sich eine Möglichkeit, wenn group by text möglich wäre ... ist es aber leider nicht!

ich werd wahrscheinlich einfach nur einen self join mit convert in nvarchar machen ... eine top 100 Abfrage dauert ja nur ca. 5min (4x2,66GHz Xeon, 14GB RAM) ... mal schauen, wie lang ich für den ganzen delete-Befehl brauchen werd (laut count sind es ca. 2 Mio. doppelte Datensätze)

Re(12): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Tue, 14 Oct 2008 18:51:35 GMT

weil ich deine Lösung noch nicht genau durchdacht hatte ... momentan ist es @ work echt a bissl zu viel des guten!

Re(11): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Tue, 14 Oct 2008 18:41:08 GMT

Jetzt frag ich mich aber, warum du auf meine Lösung nicht reagierst, meine Antwort deckt doch genau dein Problem ab. Wars nicht deutlich genug?

Re(10): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Tue, 14 Oct 2008 12:51:29 GMT

der Aufbau der Tabelle ist wie folgt:

ID, Datum, Text, Aktiv, lfdNr

zB:

1, 01.01.2000, bla, 1, 1
1, 01.01.2001, bla, 0, 2
2, 01.05.2007, bla, 1, 3
2, 01.07.2007, blabla, 0, 4

also hier würde zB die lfdNr 1 oder 2 raus gelöscht werden!

Re(9): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

ruprecht69 — Tue, 14 Oct 2008 11:37:26 GMT

Beispiel folgende Spalten:

  Index,
  Wert1,
  Textfeld (das auf doppelte zu checkende),
  Wert2,
  Änderer,
  etc.

1 , 200, Blöder Text, 300, Michi
2 ,   0, Doppelter Text, 400, Gretl
3 ,  12, Irgendwas, 400, Gretl
4 , 300, Doppelter Text, 10, Lisl

Wenn du nun die doppelten Sätze (mit dem identischen Textfeld z.B. "Doppelter Text") unwillkürlich rausschmeisst verlierst du die Informationen des gelöschten Satzes (bspw. Wert1, Wert2, Änderer). Also ist nun Satz #2 oder Satz #4 korrekt - d.h. welchen haust du raus?

haben wir, aber der hilft mir nicht beim Analysieren der Daten!

??? wie können dann Duplikate drin sein ???

Re(8): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Tue, 14 Oct 2008 11:12:12 GMT

Wozu willst du Joinen für ein Group by?

will ich ja nicht, ich wollts ja ursprünglich mit einem self join vergleichen!

Welcher der beiden Datensätze mit der einen übereinstimmenden Spalte ist denn nun der richtige?

es handelt sich um die gleiche Spalte (self join)

Anonsten empfehle ich dir für solche Fälle in Zukunft einen unique Key

haben wir, aber der hilft mir nicht beim Analysieren der Daten!

Re(9): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

ruprecht69 — Tue, 14 Oct 2008 11:07:11 GMT

Like ist so ziemlich das unperformanteste was du einer DB antun kannst - abgesehen von nicht vorhandenen Indizes.

Re(7): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

ruprecht69 — Tue, 14 Oct 2008 11:04:58 GMT

Wozu willst du Joinen für ein Group by?

Ich glaub da hast auch ein bissle eine semantische Krux. Welcher der beiden Datensätze mit der einen übereinstimmenden Spalte ist denn nun der richtige? D.h. automatisch aussortieren wird schwierig/falsche Ergebnisse hinterlassen.

Anonsten empfehle ich dir für solche Fälle in Zukunft einen unique Key.

Re: MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Mon, 13 Oct 2008 18:27:58 GMT

Hmm ... gibts echt ein Problem mit Text?

Dann mach dir eine Temp Table mit einem nvarchar (und falls notwendig mit primary key Feldern) und insert alle Zeilen der Text Spalte.

Danach kannst auf die Temp Table Spalte gruppieren.

Re: MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Mon, 13 Oct 2008 18:27:58 GMT

Hmm ... gibts echt ein Problem mit Text?

Dann mach dir eine TempTable mit einem nvarchar2 (und falls notwendig mit PrimaryKey Feld) und insert alle Zeilen der Text Spalte.

Danach kannst auf die TempTable.nvarchar2 Spalte gruppieren.

Falls du unique Zeilen in der BigTable haben willst:

select Min(PrimaryKey) into TempTable2
from TempTable
group by text

delete from BigTable
where PrimaryKey not in (select PrimaryKey from TempTable2)

Re(12): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Mon, 13 Oct 2008 18:24:54 GMT

Ui, der Aufwand ...
n * lesen
n * mit n Zeilen vergleichen

> ∑n² ?

Re(12): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

Xane — Mon, 13 Oct 2008 18:24:54 GMT

Ui, der Aufwand ...
n * lesen
n * mit n Zeilen vergleichen

> n² ?

Re(12): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Fri, 10 Oct 2008 07:22:12 GMT

hm klingt auch nicht grad blöd ... werd ich mir auf jeden Fall mal durch den Kopf gehen lassen!

vielen Dank!

Re(11): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

weisnet — Fri, 10 Oct 2008 07:02:25 GMT

ich würds russisch machen.

1. temp table anlegen
2. md5-hash für jedes feld berechnen und in temp table schreiben
3. vor jeden schreiben in die temp table checken ob der hash wert schon vorhanden ist. wenn ja => löschen in der text-tabelle, nein => schreiben in die temptable.

programmiert ist sowas relativ schnell. und wenns programm etwas länger läuft, ist doch wurscht. am freitag nachmittag gestartet, am montag wirds schon durch sein. wenn nicht, dann halt nächstes we wieder laufen lassen.

PS: nicht vergessen vorher zu sichern!

Re(10): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Thu, 09 Oct 2008 08:46:22 GMT

Warum änderst du nicht einfach die Spalte in nvarchar?

ich kenne die Quellcodes der Programme, die auf diese Spalte zugreifen, nicht auswendig ... vielleicht benötigt ja irgendeines explizit den Datentyp text!

Leider nicht, da braucht man wohl eine MS SQL-spezifische Sonderlösung, wenn es überhaupt eine bessere gibt als manuelles Aussortieren der Duplikate...

Re(10): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Thu, 09 Oct 2008 08:46:22 GMT

Warum änderst du nicht einfach die Spalte in nvarchar?

ich kenne die Quellcodes der Programme, die auf diese Spalte zugreifen, nicht auswendig ... vielleicht benötigt ja irgendeines explizit den Datentyp text!

Leider nicht, da braucht man wohl eine MS SQL-spezifische Sonderlösung, wenn es überhaupt eine bessere gibt als manuelles Aussortieren der Duplikate...

manuell aussortieren stell ich mir bei 10 Mio. Datensätze ziemlich zach vor ... hm meine Idee war auch, dass ich die Tabelle extrahiere und mit einem externen Programm vergleiche ... aber das war nur ein Ansatz ... keine Ahnung, wie und womit ich das umsetzen könnte! die Datenmenge ist halt leider enorm ...

exec sp_spaceused liefert folgendes

rows 10822133
reserved 3865864 KB
data 2548856 KB
index_size 1303552 KB
unused 13456 KB

wenn ich die Tabelle sauber bereinige, haben wir sicher gleich mal 1 - 1,5GB gewonnen - also das zu schaffen, würd sich schon auszahlen!

Re(10): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Thu, 09 Oct 2008 08:46:22 GMT

Warum änderst du nicht einfach die Spalte in nvarchar?</blockquote<

ich kenne die Quellcodes der Programme, die auf diese Spalte zugreifen, nicht auswendig ... vielleicht benötigt ja irgendeines explizit den Datentyp text!

Leider nicht, da braucht man wohl eine MS SQL-spezifische Sonderlösung, wenn es überhaupt eine bessere gibt als manuelles Aussortieren der Duplikate...

manuell aussortieren stell ich mir bei 10 Mio. Datensätze ziemlich zach vor ... hm meine Idee war auch, dass ich die Tabelle extrahiere und mit einem externen Programm vergleiche ... aber das war nur ein Ansatz ... keine Ahnung, wie und womit ich das umsetzen könnte! die Datenmenge ist halt leider enorm ...

Re(9): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

mjy@geizhals.at — Thu, 09 Oct 2008 08:41:17 GMT

ja, ich nutze 2005 ... aber worin besteht jetzt performancemäßig der Unterschied, ob ich text mit like vergleiche oder ob ich Text vorher noch konvertiere und ihn dann mit = vergleiche?

Warum änderst du nicht einfach die Spalte in nvarchar?

fällt dir sonst noch eine Möglichkeit ein?

Leider nicht, da braucht man wohl eine MS SQL-spezifische Sonderlösung, wenn es überhaupt eine bessere gibt als manuelles Aussortieren der Duplikate...

Re(8): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

-Transformer2K- — Thu, 09 Oct 2008 08:39:42 GMT

If you are using SQL 2005, change it to nvarchar(max).

The text/ntext datatypes are being deprecated and must be converted to varchar(max)/nvarchar(max).

ja, ich nutze 2005 ... aber worin besteht jetzt performancemäßig der Unterschied, ob ich text mit like vergleiche oder ob ich Text vorher noch konvertiere und ihn dann mit = vergleiche?

beides ist möglich, steht außer Frage - aber ist es wirklich performanter, wenn ich noch 2 converts einbaue? ich glaub kaum

trotzdem vielen Dank! fällt dir sonst noch eine Möglichkeit ein?

Re(7): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen

mjy@geizhals.at — Thu, 09 Oct 2008 08:31:09 GMT

dass man auf Text-Datentypen ja gar nicht joinen kann

Das ist aber eine miese DB


Re: HOW-TO Change "text" column to "ntext" that is NOT NULL
Arnie Rowland


If you are using SQL 2005, change it to nvarchar(max).

The text/ntext datatypes are being deprecated and must be converted to varchar(max)/nvarchar(max).




Top
Re: HOW-TO Change "text" column to "ntext" that is NOT NULL
Manivannan.D.Sekaran

In SQL Server 2000, you can't modify the text column datatype.

But there is a workaround available..

Code Snippet

IF EXISTS(SELECT * FROM dbo.tablename)

SELECT othercolumns,Cast(colname as Ntext) as colname INTO dbo.Tmp_tablename FROM dbo.tablename TABLOCKX

go

DROP TABLE dbo.tablename

go

EXECUTE sp_rename N'dbo.Tmp_tablename', N'tablename', 'OBJECT'

usw.

Re(6): MSSQL2005: Text-Spalten performant auf Redundanz vergleichen