MSSQL - INSERT - kartesisches Produkt

[Froschkoenig84]

Ist es eigentlich möglich den RESULT einer solchen Abfrage in eine Variable zu schreiben? Der besteht ja aus mehreren Spalten und Zeilen.

 

[ukulele]

Du kannst das Result in eine Temp Tabelle schreiben und diese abfragen, das ist aber eigentlich unnötig denn du kannst ja auch direkt einen Select innerhalb der SP aufrufen und erhälst dann das Ergebnis beim Aufruf der SP.

Ich würde alles in einer SP machen. Wie es jetzt genau am performantesten ist da bin ich mir nicht sicher, aber je einfacher das ganze gehalten ist desto besser:
1) Prüfen, ob bereits 100 Entfernungen zu deinem Ausgangspunkt existieren (wenn ja, dann müssten das zwangsläufig die 100 nähsten sein).
2) Wenn es unter 100 sind alle 100 berechnen und die, die nicht im Bestand sind, in den Bestand schreiben.
3) Aus dem Bestand 100 Entfernungen ausgeben.

 

[Froschkoenig84]

Eigentlich hast du Recht. - Dann passe ich das nochmal an.

Aktuell hab ich:
SELECT distances INTO #TempTable (FROM Destinations) //Datensammlung
SELECT #TempTable //Rückgabe der Daten
MERGE(#TempTable in Distances)-INSERT/UPDATE //Distances-Tabelle durch Daten ergänzen/aktualisieren
DROP #TempTable //löschen der Daten

Also werde ich jetzt noch auf die COUNT(100) und das updated-Feld prüfen, bevor ich das tatsächlich MERGE.
Vielleicht bin ich auch faul, übergebe nur noch die ID der aktuellen Destination und hol mir die weiteren Infos (lat, lon, market) über einen JOIN.

Hab gerade mal geprüft, wie lange er braucht und es sind kaum mehr als eine Hand voll Millisekunden.
Also selbst bei 1K Zugriffen in der Minute sollte es keine Performance-Probleme geben.

 

[ukulele]

Da gehe ich auch von aus. Aber: Wenn du vorher nicht auf bereits vorhandene Entfernungen prüfst, macht es keinen Sinn sie überhaupt zu speichern weil deine Berechnung ja jedes mal ausgeführt wird.

 

[Froschkoenig84]

Na ja, ursprünglich wollte ich clientseitig (programmseitig) die Entfernungen abfragen und falls nicht vorhanden, dann sollte er rechnen. Doch da ich jetzt sowieso schon dabei bin, alles in SPs auszulagern, dann kann ich das auch alles in eines werfen oder notfalls mehrere SPs verschachteln. Dann muss ich via Programm nur noch die ID der aktuellen Destination hinschicken und bekomme die nächsten 100 Distanzen zurück.

 

[ukulele]

Ich meinte nur unabhängig davon wer rechnet, wenn du nicht vor der Berechnung prüfst, ob du die Daten schon (vollständig) hast, sondern gleich alles berechnest und dann nur fehlende Daten in die DB schreibst, ist deine Datenhaltung zwecklos.

 

[Froschkoenig84]

Was? - Ähmm ja, klar. Natürlich, wieso sollte ich eine prozessor-/speicherlastige Berechnung laufen lassen, wenn die Prüfung zuvor ergeben würde, dass die Berechnung überhaupt nicht nicht benötigt wird. - Nur deswegen prüfe ich das ja überhaupt.

USE [MyDatabase]
GO
-- user: MN
-- date: 20151214
-- desc: distances check (existing/outdate) -> if not existing then new calculation (insert/update) -> output
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetDistancesBetweenDestinations]
    @id INT,
    @limit INT = 25,
    @outdays INT = 180,
    @meters INT = 111045 /* this is the distance between equator and poles / 90° */
    --@meters FLOAT = 111045.00
    --@kilometers FLOAT = 111.045,
    --@miles FLOAT = 69.00,
    --@seamiles FLOAT = 60.00,
    --@furlongs FLOAT = 552.00,
    --@feet FLOAT = 364320
AS
BEGIN
    -- // GET DISTANCES //--
    SELECT
        CASE WHEN id_a = @id THEN id_b ELSE id_a END AS 'id',
        distance
    INTO   
        [tempdb].[dbo].[TmpDistancesForDestinations]
    FROM
        [MyDatabase].[dbo].[Distances]
    WHERE
        DATEDIFF(DAY, updated, GETDATE()) < @outdays AND
        id_a = @id OR id_b = @id AND
        id_a <> id_b AND
        distance > 0
    ORDER BY
        distance ASC
    ;
    -- // CHECK DISTANCES //--
    IF (SELECT COUNT(*) FROM [tempdb].[dbo].[TmpDistancesForDestinations]) < @limit BEGIN
        -- // GET NEW DISTANCES (BY DESTINATIONS) //--
        SELECT TOP (@limit)
            D.[id] AS 'id',
            ROUND(@meters * DEGREES(ACOS(COS(RADIANS(Q.[geo_lat])) * COS(RADIANS(D.[geo_lat])) * COS(RADIANS(Q.[geo_lon]) - RADIANS(D.[geo_lon])) + SIN(RADIANS(Q.[geo_lat])) * SIN(RADIANS(D.[geo_lat])))),0) AS 'distance'
        INTO   
            [tempdb].[dbo].[TmpDestinationDistances]
        FROM
            [MyDatabase].[dbo].[Destinations] AS D
        LEFT JOIN 
            [MyDatabase].[dbo].[Destinations] AS Q
            ON(Q.id = @id)
        WHERE
            D.[is_country] <> 1 AND
            D.[market] = Q.market AND
            D.[geo_lat] IS NOT NULL AND D.[geo_lat] <> 0 AND D.[geo_lat] <> Q.[geo_lat] AND
            D.[geo_lon] IS NOT NULL AND D.[geo_lon] <> 0 AND D.[geo_lon] <> Q.[geo_lon] AND
            D.[count_pois] >= 2 AND
            D.[tg_core_region_priority_rank] BETWEEN 1 AND 1000
        ORDER BY
            'distance' ASC
        ;
        -- // SHOW NEW (TEMP) DISTANCES //--
        SELECT TOP (@limit)
            *
        FROM
            [tempdb].[dbo].[TmpDestinationDistances]
        ORDER BY
            [distance] ASC
        ;
        -- // MERGE NEW (TEMP) DISTANCES INTO (OLD) DISTANCES TABLE //--
        MERGE [MyDatabase].[dbo].[Distances] AS TARGET
        USING [tempdb].[dbo].[TmpDestinationDistances] AS SOURCE
            ON ((TARGET.id_a = @id AND TARGET.id_b = SOURCE.id) OR (TARGET.id_a = SOURCE.id AND TARGET.id_b = @id))
            WHEN NOT MATCHED BY TARGET THEN
                INSERT(
                    id_a, id_b, distance
                )
                VALUES(
                    @id, SOURCE.id, SOURCE.distance
                )
            WHEN MATCHED THEN
                UPDATE
                SET
                    TARGET.distance = SOURCE.distance,
                    TARGET.updated = GETDATE()
        ;
        -- // DELETE TEMP DISTANCES //--
        DROP TABLE [tempdb].[dbo].[TmpDestinationDistances]
    END    ELSE BEGIN
        -- // SHOW (OLD) DISTANCES //--
        SELECT TOP (@limit)
            *
        FROM
            [tempdb].[dbo].[TmpDistancesForDestinations]
        ORDER BY
            [distance] ASC
        ;
    END
    -- // DELETE TEMP DISTANCES //--
    DROP TABLE [tempdb].[dbo].[TmpDistancesForDestinations]
END

Klappt jetzt soweit. Ich hoffe, dass es keinen Stress gibt, wenn 1'000 Clients gleichzeitig diese Prozedur aufrufen und dann quasi "zeitgleich" versuchen TempTables anzulegen, sonst brauchen die noch einen Zeitstempel im Namen. :/

 

[Froschkoenig84]

Okay, ich habe also nun eine StoredProcedure... aber wie bekomme ich das Ergebnis nun in mein C# (VisualStudio)?

    public static string GetDistancesBetweenDestinations(int Id)
    {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("dbo.GetDistancesBetweenDestinations", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter IN_Id = cmd.Parameters.Add("@id", SqlDbType.Int, 11);
            IN_Id.Value = 12;
            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            return rdr[1].ToString();
    }

... klappt leider schon mal nicht. :/

Meine Tabellen habe ich bisher immer via *.DBML ins Projekt gezogen. Aber mit Prozeduren scheint das irgendwie nicht zu funktionieren.

Ich weiß, das sind dämliche Anfängerfragen, ... aber kann mir jemand helfen?


Da meine Prozedur einen SELECT enthält, gibt sie einfach eine Tabelle (ID, DISTANCE) mit DestinationID und Distanz zur ursprünglich angeforderten CurrentDestinationID zurück (beide Felder sind INTs)...