T-SQL Quiz, multivalues

Συζητήσεις για τον Microsoft SQL Server

Συντονιστές: WebDev Moderators, Super-Moderators

Απάντηση
Άβαταρ μέλους
skeftomilos
Script Master
Δημοσιεύσεις: 2888
Εγγραφή: 07 Ιαν 2005 07:22
Τοποθεσία: Αθήνα

T-SQL Quiz, multivalues

Δημοσίευση από skeftomilos » 01 Ιούλ 2006 19:28

Έστω ότι σε μια βάση MS SQL Server έχουμε ένα πίνακα που περιέχει συνδυασμένες αριθμητικές τιμές σε ένα πεδίο varchar:

Κώδικας: Επιλογή όλων

Id Multi
-- -----------------
1  '002;005;008'
2  '001;019'
3  '004;008;012;016'
Το ζητούμενο είναι να τον μετατρέψουμε σε ένα κανονικό πίνακα όπου κάθε τιμή θα βρίσκεται σε ξεχωριστή εγγραφή:

Κώδικας: Επιλογή όλων

Id Number
-- -----------------
1  2
1  5
1  8
2  1
2  19
3  4
3  8
3  12
3  16
Υπάρχει τρόπος να γίνει χωρίς χρήση Cursor και χωρίς πολλαπλά Select; Υποθέστε ότι αυτή η μετατροπή θα γίνεται σε καθημερινή βάση, ο πίνακας έχει εκατομμύρια εγγραφές, και η ταχύτητα είναι σημαντική.
The pure and simple truth is rarely pure and never simple. Ο μη νους δε σκέπτεται μη σκέψεις για το τίποτα.

Άβαταρ μέλους
skeftomilos
Script Master
Δημοσιεύσεις: 2888
Εγγραφή: 07 Ιαν 2005 07:22
Τοποθεσία: Αθήνα

T-SQL Quiz, multivalues

Δημοσίευση από skeftomilos » 03 Ιούλ 2006 02:15

Η πιο προφανής επιλογή είναι η χρήση cursor. Θα ανοίξουμε έναν cursor και θα διαβάσουμε μία-μία τις εγγραφές του πίνακα. Για κάθε εγγραφή θα κάνουμε parsing του πεδίου Multi, και για κάθε τιμή που θα παίρνουμε θα κάνουμε και από ένα INSERT σε έναν άλλο πίνακα. Αν ο αρχικός πίνακας ονομάζεται Composite, τότε ο T-SQL κώδικας θα είναι κάπως έτσι:

Κώδικας: Επιλογή όλων

SET NOCOUNT ON
DECLARE @Results table (Id INT, Number INT)
DECLARE C CURSOR FOR SELECT Id, Multi FROM Composite
DECLARE @Id INT, @Multi VARCHAR(256)
DECLARE @i INT

OPEN C
FETCH NEXT FROM C INTO @Id, @Multi
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @i = 1
  WHILE @i <= LEN&#40;@Multi&#41; BEGIN
    INSERT INTO @Results &#40;Id, Number&#41; VALUES &#40;@Id, CONVERT&#40;INT, SUBSTRING&#40;@Multi, @i, 3&#41;&#41;&#41;
    SET @i = @i + 4
  END
  FETCH NEXT FROM C INTO @Id, @Multi
END
CLOSE C
DEALLOCATE C
Μετά το τέλος της μετατροπής μπορούμε να διαβάσουμε τον πίνακα @Results με ένα SELECT query. Δε χρειάζεται να διαβάσουμε όλες τις εγγραφές, μόνο μερικά αθροίσματα:

Κώδικας: Επιλογή όλων

SELECT COUNT&#40;Number&#41; AS CountOfNumber, SUM&#40;Number&#41; AS SumOfNumber FROM @Results
Λοιπόν το ζητούμενο είναι να φτάσουμε στο ίδιο αποτέλεσμα χωρίς να χρησιμοποιήσουμε cursor. Μην εκπλαγείτε αν βρεθεί εναλλακτική λύση με 20-30 φορές καλύτερη απόδοση!
The pure and simple truth is rarely pure and never simple. Ο μη νους δε σκέπτεται μη σκέψεις για το τίποτα.

Άβαταρ μέλους
cordis
Administrator, [F|H]ounder, [C|S]EO
Δημοσιεύσεις: 27616
Εγγραφή: 09 Οκτ 1999 03:00
Τοποθεσία: Greece
Επικοινωνία:

T-SQL Quiz, multivalues

Δημοσίευση από cordis » 03 Ιούλ 2006 12:55

Με πρόλαβες.. θα σου απαντούσα σήμερα.. :P
Δεν απαντάω σε προσωπικά μηνύματα με ερωτήσεις που καλύπτονται από τις ενότητες του forum. Για ο,τι άλλο είμαι εδώ για εσάς.
- follow me @twitter

Άβαταρ μέλους
skeftomilos
Script Master
Δημοσιεύσεις: 2888
Εγγραφή: 07 Ιαν 2005 07:22
Τοποθεσία: Αθήνα

T-SQL Quiz, multivalues

Δημοσίευση από skeftomilos » 04 Ιούλ 2006 01:07

Μα δεν απάντησα! :P Το quiz είναι αν υπάρχει τρόπος χωρίς cursor. Υπάρχει τουλάχιστον ένας πολύ καλύτερος, άλλά περιμένω να δω μπας και βρείτε κανέναν ακόμα καλύτερο. :)
The pure and simple truth is rarely pure and never simple. Ο μη νους δε σκέπτεται μη σκέψεις για το τίποτα.

Άβαταρ μέλους
cordis
Administrator, [F|H]ounder, [C|S]EO
Δημοσιεύσεις: 27616
Εγγραφή: 09 Οκτ 1999 03:00
Τοποθεσία: Greece
Επικοινωνία:

T-SQL Quiz, multivalues

Δημοσίευση από cordis » 04 Ιούλ 2006 13:07

έλα.. χωρίς cursor..

Κώδικας: Επιλογή όλων

SELECT c.Id , CONVERT&#40;INT, SUBSTRING&#40;c.Multi, q.rownum * 4 + 1, 3&#41;&#41; MyNumber
FROM 
	&#40; SELECT rownum=&#40; SELECT COUNT&#40;*&#41; 
	           FROM sysusers 
	                WHERE name <= T.name &#41; - 1 
	    FROM     sysusers T &#41; q, 
	Composite c
WHERE SUBSTRING&#40;c.Multi, q.rownum * 4 + 1, 3&#41; <> ''
ORDER BY c.Id
αν αφαιρέσεις το order που είναι για ομορφιά κερδίζεις και λίγο χρόνο.. ;)
παράγει:

Κώδικας: Επιλογή όλων

1	2
1	5
1	8
2	19
2	1
3	4
3	8
3	12
3	16
Δεν απαντάω σε προσωπικά μηνύματα με ερωτήσεις που καλύπτονται από τις ενότητες του forum. Για ο,τι άλλο είμαι εδώ για εσάς.
- follow me @twitter

Άβαταρ μέλους
skeftomilos
Script Master
Δημοσιεύσεις: 2888
Εγγραφή: 07 Ιαν 2005 07:22
Τοποθεσία: Αθήνα

T-SQL Quiz, multivalues

Δημοσίευση από skeftomilos » 05 Ιούλ 2006 01:31

Σωστόοος !!! :D Αυτή τη λύση βρήκα κι εγώ, αν και δε μου πέρασε από το μυαλό η σατανική ιδέα του system table για τη δημιουργία του Index table. :o Αντ' αυτού έφτιαξα ένα table in-memory και το ονόμασα @Joker:

Κώδικας: Επιλογή όλων

DECLARE @max_values_count INT
SET @max_values_count = &#40;SELECT &#40;MAX&#40;LEN&#40;Multi&#41;&#41; + 1&#41; / 4 FROM Composite&#41;

DECLARE @Joker table &#40;&#91;Index&#93; INT&#41;
DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= @max_values_count BEGIN
  INSERT INTO @Joker &#40;&#91;Index&#93;&#41; VALUES &#40;@cnt&#41;
  SET @cnt = @cnt + 1
END

SELECT Composite.Id, CONVERT&#40;int, SUBSTRING&#40;Multi, Joker.&#91;Index&#93; * 4 - 3, 3&#41;&#41; AS Number FROM Composite
  INNER JOIN @Joker AS Joker ON Joker.&#91;Index&#93; <= &#40;LEN&#40;multi&#41; + 1&#41; / 4
  ORDER BY Composite.Id
Το σκεπτικό είναι η αντικατάσταση του χρονοβόρου CURSOR με ένα «απλό» JOIN. Οι relational databases είναι super-optimized για να κάνουν αυτό ακριβώς - joins - αλλά στη συγκεκριμένη περίπτωση με τι θα μπορούσε να γίνει join ο πίνακας Composite; Και τι JOIN θα ήταν αυτό;

Θα χρειαστούμε ένα βοηθητικό πίνακα που να έχει ένα πεδίο με αύξουσες τιμές, που επιπλέον θα πρέπει να έχει αρκετές εγγραφές ώστε να καλύψει την πολυπληθέστερη σύνθετη τιμή Multi. Όσο για το είδος του join δε μπορεί να είναι το περιοριστικό INNER JOIN ούτε το απεριόριστο CROSS JOIN, αλλά κάτι ενδιάμεσο. Η Microsoft ονομάζει αυτά τα joins LOOP JOIN, και αν θέλουμε μπορούμε να προσθέσουμε το query hint LOOP ανάμεσα στα keywords INNER και JOIN. Η έκφραση Joker.[Index] <= (LEN(multi) + 1) / 4 φιλτράρει τη διαδικασία του JOIN και κρατάει μόνο τους συνδυασμούς που χρειάζονται.

Η σύγκριση απόδοσης αυτής της μεθόδου σε σχέση με τη μέθοδο CURSOR είναι πολύ ενδιαφέρουσα. Προσωπικά αιφνιδιάστηκα από το μέγεθος της διαφοράς:

Κώδικας: Επιλογή όλων

 Εγγραφές  CURSOR LOOP JOIN Αναλογία
--------- ------- --------- --------
    1.000    0&#58;02      0&#58;03      2/3
   10.000    0&#58;13      0&#58;03      4/1
  100.000    2&#58;05      0&#58;07     18/1
1.000.000   23&#58;53      0&#58;48     30/1
Τα τρία δευτερόλεπτα είναι ο σταθερός χρόνος που χρειάζεται για την εύρεση της @max_values_count σε 1.000.000 εγγραφές. Οι δοκιμές έγιναν στον Query Analyzer με χρήση WHERE Id <= 1000 κ.λπ. σε πίνακα 1.000.000 εγγραφών, με 0 - 9 τιμές σε κάθε εγγραφή του πεδίου Multi, σε υπολογιστή Celeron 2,4ghz. Στις 1.000.000 εγγραφές η μέθοδος CURSOR λίγο πριν ολοκληρωθεί εξάντλησε τη RAM (512MB), και άρχισα να ανησυχώ πως δε θα τελείωνε ποτέ. Τελικά ολοκληρώθηκε τρία λεπτά αργότερα.

Η μέθοδος LOOP JOIN μπορεί να εμφανίσει σημάδια αδυναμίας στην περίπτωση που υπάρχει μεγάλη απόκλιση στο πλήθος των τιμών του πεδίου Multi. Πάντως ακόμα και με δεκαπλάσιο @max_values_count εξακολουθούσε να είναι πολλές φορές ταχύτερη από τη μέθοδο CURSOR.
The pure and simple truth is rarely pure and never simple. Ο μη νους δε σκέπτεται μη σκέψεις για το τίποτα.

Άβαταρ μέλους
cordis
Administrator, [F|H]ounder, [C|S]EO
Δημοσιεύσεις: 27616
Εγγραφή: 09 Οκτ 1999 03:00
Τοποθεσία: Greece
Επικοινωνία:

T-SQL Quiz, multivalues

Δημοσίευση από cordis » 05 Ιούλ 2006 13:09

Για άλλη μια φορά.. καταπληκτικός.. ;)
Δεν απαντάω σε προσωπικά μηνύματα με ερωτήσεις που καλύπτονται από τις ενότητες του forum. Για ο,τι άλλο είμαι εδώ για εσάς.
- follow me @twitter

Απάντηση

Επιστροφή στο “MS SQL Server”

Μέλη σε σύνδεση

Μέλη σε αυτήν τη Δ. Συζήτηση: Δεν υπάρχουν εγγεγραμμένα μέλη και 0 επισκέπτες