SQL Server (και άλλες databases)

Απ: Boήθεια με Insert into

spaceman — Thu, 16 Dec 2010 00:24:32 GMT

neoklis:
Το αποτέλεσμα είτε με udf, είτε με custom θα είναι το ίδιο. Ποιές όμως είναι διαφορές τους..; Με ποιά κριτήρια διαλέγει κάποιος αν θα χρησιμοποιήσει την μία ή άλλη λύση..; Έχει να να κάνει με την ταχύτητα εκτέλεσης;

It depends (i hate this answer, but it is true). Εχεις x λυσεις για το προβλημα σου, οποτε τις δοκιμαζεις ολες και καταληγεις σε αυτην που καλυτερα εξυπηρετει τον σκοπο σου, χωρις φυσικα να αποκλειεις τις υπολοιπες λυσεις ιδιως για μελλοντικη χρηση οταν ο ογκος των δεδομενων κ το περιβαλλον της βασης αλλαζει. Ο χρονος εκτελεσης μπορει να ειναι ή να μην ειναι ενα απο τα κριτηρια επιλογης. Ο χρονος εκτελεσης ειναι "σχετικος" : πχ. φορτωνεις bulk δεδομενα (εκατομμυρια εγγραφες) στις 4 το πρωι και οι χρηστες συνδεονται στην βαση στις 8:30 το πρωι. Μεσα σε ενα χρονικο περιθωριο 4.5 ωρων εχει ιδιαιτερο νοημα αν η διεργασια εισαγωγης δεδομενων χρειαζεται 1 λεπτο, 1 ωρα , 2 ωρες κοκ ? Στις 8:30 οι χρηστες θα εχουν τα δεδομενα τους κ everyone is happy.
Στο παραδειγμα με το concatenation, εστω οτι πρεπει να "συνδεσεις-ενωσεις" εγγραφες για/σε 10 διαφορετικους πινακες. Αν επιλεξεις την tsql udf τοτε θα πρεπει να εχεις 10 διαφορετικες functions ενω απο την αλλη η/μια custom aggregate μπορει να χρησιμοποιηθει και για τους 10 πινακες. Η συντηρηση του κωδικα μπορει να ειναι ενα επιπλεον κριτηριο.
Φυσικα η "πλατφορμα" στην οποια βρισκεται η βαση (hardware & software) ειναι ενα προσθετο κριτηριο.

Σε ενα γρηγορο τεστ για τις λυσεις που εχουν προαναφερθει, σε πινακα με 280Κ εγγραφες, περιπου 4000 unique ids (extreme conditions id with 4000 or 2000 records to be concatenated) ο χρονος εκτελεσης ηταν:
FOR XML PATH() : fastest - 11 secs (parallelism kicked in)
Custom aggregate: 24 secs (no parallelism)
UDF : over 1 minute (udfs kill parallelism in general)

Σε γενικες γραμμες, οι scalar udfs στο select, εκτελουνται row by row on a single thread και "σκοτωνουν" τα parallel execution plans.
Στο παρελθον, εχω χρησιμοποιησει tsql udf, για complicated string comparisons και ηταν "αποδοτικη". Καποια στιγμη, οταν ο πινακας ξεπερασε το 1 εκατομμυριο εγγραφες, ο χρονος εκτελεσης αυξηθηκε δραματικα (table scan needed whatsoever). Αντικαταστησαμε την tsql udf με CLR κ ο χρονος εκτελεσης επεσε στο 1 sec (table scan as usual but 16 parallel streams kicked in).

Κανε τα δικα σου tests, στο δικο σου περιβαλλον και επελεξε την λυση που ικανοποιει τις προδιαγραφες σου και με την οποια αισθανεσαι πιο "ανετα" και μπορεις να την υποστηριξεις. Στην τελικη, ΕΣΥ θα εισαι ο υπευθυνος οταν ο κωδικας χρησιμοποιηθει σε production και κανεις αλλος, οποτε ο τροπος με τον οποιο προσεγγιζεις αλλαγες στον κωδικα, urgent fixes κτλ θα πρεπει να ειναι ενα απο τα κριτηρια επιλογης της "καλυτερης" λυσης.

--ΗΤΗ--

Απ: Boήθεια με Insert into

neoklis — Wed, 15 Dec 2010 22:24:25 GMT

Αντώνη σε ευχαριστώ για τον χρόνο σου.

Σαν συνέχεια έχω μια ερώτηση και για αυτό άνοιξα νέο θέμα.

http://www.dotnetzone.gr/cs/forums/thread/61678.aspx

Απ: Boήθεια με Insert into

Antonios Chatzipavlis — Wed, 15 Dec 2010 20:59:44 GMT

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

Καταρχή να προσθέσω κάτι στο παράδειγμα του spaceman για να είναι ορθή 100% η λύση. Θα πρέπει να έχεις ενεργοποιήσεις το CLR Integration στον SQL Server για να σου παίξει η λύση αυτή. Μπορείς να το κάνεις με το εξής script από τον SSMS:

Sp_configure ‘clr enabled’,1

reconfigure

Στην ουσία αυτό το μόνο που κάνει είναι να κόβει την δυνατότητα στο SQL Server να παίζει σε Windows fiber mode (lightweight pooling) όσον αφορά το context switching στο SQLOS.

Αυτό σημαίνει πρακτικά ότι αν ενεργοποιήσεις σε ένα server το fiber mode (αν και η Ms δεν το συνιστά) κάποια όμορφα πραγματάκια θα κρεμάσουν όπως τα HierarchyID, Geometry, Geography data types , Declarative Management Framework and Change Data Capture.

Όσον αφορά τώρα το ερώτημα σου υπάρχει μια σειρά από tips που έχει δώσει η Microsoft για το πότε ή όχι να χρησιμοποιείται το SQL Server CLR Integration

Όταν έχουμε να ασχοληθούμε αποκλειστικά με data τότε προτιμούμε παραδοσιακούς τρόπους όπως transact-sql και όχι clr.

Εάν όμως έχουμε διαδικασίες που είναι cpu intensive ή θέλουμε να προσπελάσουμε πράγματα εκτός sql server πχ file system, registry, ή έχουμε διαδικασίες που με t-sql είτε είναι δύσκολες στην υλοποίηση είτε είναι κακές σε επιδόσεις τότε πάμε με clr.

Εξάλλου το clr μας δίνει ένα σαφώς καλύτερο προγραμματιστικό μοντέλο το οποίο σε συνδυασμό με το βελτιωμένο security που έχει, αλλά και τις δυνατότητες να ορίσω δικά μου aggregations, data types κλπ είναι σαφώς ένα δυνατό όπλο στην φαρέτρα του database developer.

Για όλα αυτά αλλά και για το performance μπορείς να δεις το παρακάτω link το οποίο είναι από το 2005 αλλά είναι επίκαιρο και για τις επόμενες εκδόσεις.

http://msdn.microsoft.com/en-us/library/ms345136(SQL.90).aspx

Απ: Boήθεια με Insert into

neoklis — Wed, 15 Dec 2010 18:25:48 GMT

Τι να πω spaceman.. έχω μέινει άφωνος.. Δεν έχω παίξει στο παρελθόν με custom aggregate function και πάντα είχα στο πίσω μέρος του μυαλού μου κάποια στιγμή να το κάνω.. μου έχεις δώσει τα πάντα στο πιάτο.. τι να πώ.. να η ευκαιρεία που έξαχνα..

Σ' ευχαριστώ...

Το αποτέλεσμα είτε με udf, είτε με custom θα είναι το ίδιο. Ποιές όμως είναι διαφορές τους..; Με ποιά κριτήρια διαλέγει κάποιος αν θα χρησιμοποιήσει την μία ή άλλη λύση..; Έχει να να κάνει με την ταχύτητα εκτέλεσης;

Απ: Boήθεια με Insert into

spaceman — Wed, 15 Dec 2010 04:20:31 GMT

Εναλλακτικα (και εφοσον χρησιμοποιεις SQL 2008) θα μπορουσες να χρησιμοποιησεις μια custom aggregate function

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate
    (
        Format.UserDefined, //use clr serialization to serialize the intermediate result, Binary Serialization - StringBuilder
        IsInvariantToNulls = true, //optimizer property, true= nulls do not change the result
        IsInvariantToDuplicates = false, //optimizer property, false = duplicates change the final result
        IsInvariantToOrder = false, //optimizer property, false = order changes the result
        MaxByteSize = -1 //maximum size in bytes of persisted value, -1 = size up to 2GB
    )
]


public class Concatenate : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder intermediateResult;
    private string _delimiter = null;
    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
        this._delimiter = null;
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    /// <param name="delimiter"></param> 
    public void Accumulate(SqlString value, SqlString delimiter)
    {
        if (value.IsNull | delimiter.IsNull)
        {
            return;
        }
        if (this._delimiter == null)
        {
            this._delimiter = delimiter.Value;
        }

        this.intermediateResult.Append(delimiter.Value).Append(value.Value);
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        if (this._delimiter == null)
        {
            this._delimiter = other._delimiter;
        }
            this.intermediateResult.Append(other.intermediateResult.ToString());
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = null;

        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString().Remove(0, this._delimiter.Length);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
      this._delimiter = r.ReadString();
      this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this._delimiter);
        w.Write(this.intermediateResult.ToString());
    }
}

Build the project (υποθετωντας με ονομα MyConcatenateProject)

και deploy στον SQL Server ή χρησιμοποιεις T-SQL

πχ

CREATE ASSEMBLY MyConcatenateProject AUTHORIZATION dbo 
--pay attention to authorization especially if a sql login is the db owner and the database is moved to another server(assembly owner conflict)
FROM 'c:\MyConcatenateProject.dll'
WITH PERMISSION_SET = SAFE

κ στην συνεχεια δημιουργεις την sql aggregate function

CREATE AGGREGATE dbo.fn_concatit(@value NVARCHAR(MAX), @delimiter NVARCHAR(50)) 
RETURNS NVARCHAR(MAX)  
EXTERNAL NAME MyConcatenateProject.Concatenate

την οποια χρησιμοποιεις οπως τις system aggregate functions (MAX(), COUNT(), AVG() κτλ)

πχ.

select id,  dbo.fn_concatit(description, N',') as mydescription 
from dbo.myTable
group by id

Η custom aggregate function δεν ειναι bound σε κανενα πινακα (η tsql udf ειναι) οποτε προσφερει μια ποιο "γενικη" λυση.

Φυσικα το προβλημα με το order by λυνεται με μη-συμβατικο τροπο :

SELECT id, dbo.fn_concatit(t.description, N',')
FROM 
(
	SELECT TOP 100 PERCENT id, description, order_fld
	FROM dbo.mytable
	GROUP BY id, order_fld, description
	ORDER BY id, order_field
) AS t
GROUP BY t.id

--
SELECT t.id, dbo.fn_concatit(t.description, N',')
FROM 
(	--not that elegant but it does the trick
	SELECT TOP (99.999999999999) PERCENT id, description, order_fld
	FROM dbo.mytable
	ORDER BY id, order_field
) AS t
GROUP BY t.id 
 
ή
SELECT t.id, dbo.fn_concatit(t.description, N',')
FROM 
(	--not that elegant but it does the trick
	SELECT TOP (9223372036854775807) id, description, order_fld
	FROM dbo.mytable
	ORDER BY id, order_field
) AS t
GROUP BY t.id

Ο κωδικας για την CLR (με hardcoded delimiter το κομμα ",") προσφερεται στα samples του SQL Server : http://msdn.microsoft.com/en-us/library/ms131056.aspx

--ΗΤΗ--

Απ: Boήθεια με Insert into

cap — Tue, 14 Dec 2010 22:57:03 GMT

Εγώ θα πρότεινα να μαρκάρεις όλες τις απαντήσεις που σε βοήθησαν η/και δούλεψαν, ανεξαρτήτως αν τις χρησιμοποίησες τελικά ή οχι.

Απ: Boήθεια με Insert into

neoklis — Tue, 14 Dec 2010 22:43:55 GMT

Τελικά χρησιμοποίησα την δικιά σου udf τροποποιημένη από τον spaceman, γιατί είναι διαδικασία που θα επαναλαμβάνετε σε καθημερινή βάση... μήπως πρέπει να μαρκάρω και την δική σου βοήθεια ως απάντηση...;

Χμμ.. μάλλον έτσι πρέπει να κάνω...

Απ: Boήθεια με Insert into

cap — Tue, 14 Dec 2010 21:32:19 GMT

Τελικά neoklis τι εκ των τριών λύσεων (udf, cursor, for xml) επέλεξες να χρησιμοποιήσεις και για ποιούς λόγους;

Απ: Boήθεια με Insert into

neoklis — Tue, 14 Dec 2010 20:50:02 GMT

Ευχαριστώ για την βοήθεια...

Απ: Boήθεια με Insert into

neoklis — Tue, 14 Dec 2010 00:07:49 GMT

Υπάρχει μία περίπτωση (θα την εξετάσω) οι εγγραφές στον πίνακα να είναι με την σωστή αλληλουχία.. όπως και να έχει όμως, η επισήμανση για το order by μ' άρεσε πάρα πολύ!

Απ: Boήθεια με Insert into

cap — Tue, 14 Dec 2010 00:02:06 GMT

spaceman:
CREATE TABLE dbo.myTable(clusterkey_desc INT IDENTITY(1,1), id int, description nvarchar(max))
CREATE CLUSTERED INDEX clidx_clusterkey ON dbo.myTable(clusterkey_desc DESC)--for any reason
...

Σωστά. Προφανές αλλά αγνοηθέν! Αν η σειρά με την οποία κάνεις το concatenation παίζει ρόλο, τότε χρειάζεσαι ένα order by, εκτός αν το natural order σε καλύπτει.

Απ: Boήθεια με Insert into

cap — Mon, 13 Dec 2010 23:59:29 GMT

Αν προτιμήσεις τελικά UDF, προτίμησε τη διορθωμένη έκδοση του spaceman (σε παραπάνω post περιγράφω τα σφάλματα που είχε η δική μου).

Απ: Boήθεια με Insert into

spaceman — Mon, 13 Dec 2010 23:58:45 GMT

cap:
Το order by φαντάζομαι οτι είναι για performance reasons; Αν οχι, θα ήθελα να γνωρίζω και εγώ γιατί πρέπει να έχουμε order by.

CREATE TABLE dbo.myTable(clusterkey_desc INT IDENTITY(1,1), id int, description nvarchar(max))
CREATE CLUSTERED INDEX clidx_clusterkey ON dbo.myTable(clusterkey_desc DESC)--for any reason

INSERT INTO dbo.myTable(id, description) VALUES (1, 'this is')
INSERT INTO dbo.myTable(id, description) VALUES (1, 'a test for 1')
INSERT INTO dbo.myTable(id, description) VALUES (2, 'this is')
INSERT INTO dbo.myTable(id, description) VALUES (2, 'a test for 2')

--dbo.fn_concatit without order by = SELECT dbo.fn_concatit(1) :: N'a test for 1 this is' (Sounds like Yoda)

CREATE FUNCTION dbo.fn_concatit (@id int) returns nvarchar(max)
as
BEGIN
	declare @val nvarchar(max)
	
	select @val =  ISNULL(@val, N'') + N' ' +description
	from dbo.myTable
	where id=@id
	--there should be an order by here
	ORDER BY clusterkey_desc
	return STUFF(@val, 1, 1, N'')
END

--dbo.fn_concatit with order by = SELECT dbo.fn_concatit(1) :: N'this is a test for 1'

--HTH--

Απ: Boήθεια με Insert into

neoklis — Mon, 13 Dec 2010 23:52:38 GMT

Επειδή έιναι κάτι που θα επαναλαμβάνετε θα χρησιμοποίησω function. Μέχρι να δω τα νέα ποστ είχα ήδη παίξει με την λύση που μου πρότεινε ο cap. Σε κάποιες περιπτώσεις το description ήταν null και χτύπαγε error στο return. Και ενώ πειραματιζόμουν για να το διορθώσω, είδα τα νεότερα post. Δοκίμασα α)return rtrim(@val) β)return left(@val, datalength(@val)-1) και στις 2 περιπτώσεις παίζει μια χαρά.

Θα δοκιμάσω και του spaceman...

Απ: Boήθεια με Insert into

cap — Mon, 13 Dec 2010 23:42:28 GMT

Για εκπαιδευτικούς λόγους, ας δούμε τι είχα κάνει λάθος εγώ και τι διορθώθηκε από την έκδοση του spaceman:

Δική μου udf:

CREATE FUNCTION fn_concatit (@id int) returns nvarchar(max)
as
BEGIN
	declare @val nvarchar(max)
	
	select @val = coalesce (@val,'') + description + ' '
	from myTable
	where id=@id

	return left(@val, len(@val)-1)
END

Διορθωμένη από spaceman:

CREATE FUNCTION dbo.fn_concatit (@id int) returns nvarchar(max)
as
BEGIN
	declare @val nvarchar(max)
	
	select @val =  ISNULL(@val, N'') + N' ' +description
	from dbo.myTable
	where id=@id
	--there should be an order by here
	
	return STUFF(@val, 1, 1, N'')
END

Το πρώτο λάθος στη δική μου έκδοση ήταν οτι δεν είχε γίνει create στο dbo schema, κάτι το οποίο μπορεί μεν να είναι implied σε ορισμένες περιπτώσεις, αλλά το σωστότερο είναι να δηλώνεται.

Το δεύτερο λάθος ήταν ότι δεν χρησιμοποίησα unicode strings στο concatenation (Ν), ενώ στην ουσία επιστρέφω nvarchar.

Η χρήση της isnull έναντι της coalesce στο συγκεκριμένο σενάριο δεν ξέρω πού υπερτερεί, ας βοηθήσουν οι υπόλοιποι.

Το order by φαντάζομαι οτι είναι για performance reasons; Αν οχι, θα ήθελα να γνωρίζω και εγώ γιατί πρέπει να έχουμε order by.

Τελος, το return value από εμένα δεν ήταν σωστό ούτε με τη χρήση της rtrim (μπορεί να έχουμε trailing spaces που να τα χρειαζόμαστε) ούτε με τη χρήση της len (δεν μετράει spaces). Οπότε, αυτό που έκανε ο spaceman είναι να προσθέτει το κάθε space ΠΡΙΝ και όχι μετά, έτσι ωστε το τελικό του string να έχει ένα leading space, το οποίο κόβει με τη βοήθεια της stuff. Αυτό ήταν και το πιό σημαντικό σφάλμα.