WN - Avancerad SQL-fråga

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - Avancerad SQL-fråga (https://www.wn.se/forum/showthread.php?t=1057534)

Efter lite intrimning så kom jag fram till den här SQL-satsen som blir lite snabbare särskilt om det är många rader man vill behålla:

Kod:

DELETE FROM `shoutbox`

WHERE message_id <= 

(

    SELECT message_id

    FROM 

    (

      SELECT message_id

      FROM `shoutbox`

      ORDER BY message_id DESC

      LIMIT 1 OFFSET 30 -- Behåll så här många tupler

    ) foo

);

Gör om det hela till en Stored Procedure så blir det enklare att anropa från prograkoden och det blir enklare att göra ändringar:

Kod:

USE `secag`;



DROP procedure IF EXISTS `DeleteAllMessagesButLast30`;



DELIMITER $$

USE `secag`$$

CREATE PROCEDURE `secag`.`DeleteAllMessagesButLast30` ()

BEGIN

        DELETE FROM `shoutbox`

        WHERE message_id <= 

        (

                SELECT message_id

                FROM 

                (

                  SELECT message_id

                  FROM `shoutbox`

                  ORDER BY message_id DESC

                  LIMIT 1 OFFSET 30 -- Behåll så här många tupler

                ) foo

        );

END$$

DELIMITER ;

För att anropa SPn från ditt program behöver du bara köra denna SQL-sats:

Kod:

CALL `secag`.`DeleteAllMessagesButLast30` ();

Conny, efter testast MySQL koden så fick jag detta:

PHP-kod:


		
			
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Citat:

Ursprungligen postat av secag (Inlägg 20467357)

Conny, efter testast MySQL koden så fick jag detta:

PHP-kod:


		
			
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Jag såg detta fel, har lösningen i inlägg 11 och 12....

Såg inte att det fanns en sida 2 när jag postade :-)

Det funkar nu i alla fall. Men PROCEDURE är typ som en funktion fast i SQL? Hur tillämpar jag detta då? Jag kör liksom bara en enkel $db->query("SQL"); i mitt PHP-skript. Ska jag lägga in den där proceduren i SQL innan?

Du måste först lägga in proceduren i MySQL, det gör du från administrationsverktyget och kör detta som en SQL-sats (enligt första Code-rutan i inlägg #12 i denna tråd). När det väl har körts kan du testa att den funkar enligt andra Code-rutan i inlägg #12 i denna tråd.

Så här skriver du i PHP för att anropa en SP:

Kod:

if (!$db->query("CALL DeleteAllMessagesButLast30()")) 

{

    echo "CALL failed: (" . $db->errno . ") " . $db->error;

}

Se mer info på länken: http://php.net/manual/en/mysqli.quic...procedures.php

Stor cred till Conny för hans hjälpsamhet som i många andra SQL-trådar.

Jag vill bara passa på att säga att stored procedures har både många fördelar och nackdelar. För det mesta används de inte för webb-applikationer med MySQL (förutom möjligtvis inom ASP.NET-världen där de nog inte är ovanliga trots MySQL). Men det finns 100 anledningar till att använda dom och 100 anledningar till att inte använda dom beroende av situation, arkitektur, applikation, organisation och inte minst databasmjukvaran.

Eftersom MySQL stöder SP numera så finns möjligheten att använda SP och då höjer du prestanda och förbättrar enkelheten i dina PHP-applikationer samt du kan höja säkerheten.

Man har dessutom möjligheten till att samla all affärslogik på ett enda ställe, i databasen.

Fördelarna är många, nackdelarna väldigt få.

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20467391)

Regler i all ära. Men svart och vitt är svårt att hitta.

SP anses generellt vara snabbare för att frågan bara behöver parsas och optimeras en gång.

Med MySQL så har varje session (tråd) en egen cache som måste byggas. Det betyder dels att nyttan blir väldigt liten och att det vid hög concurrency och många SPs riskerar att få totalt motsatt effekt då minnesanvändning per tråd riskerar att dra iväg. Detta förutsatt att du inte har någon connection pooling på applikationsnivå, vilket är sant för över 99% av webbapplikationer med PHP.

Så ur prestandasynpunkt finns det för MySQL ingen poäng att använda SPs förutom om man har en effektiv connection pooling för sin applikation.

Angående huruvida det rent arkitekturmässigt är en bra approach finns det många aspekter att väga in, t ex:
- Versionshantering och continous integration - blir mycket mer avancerade och omständiga om man väljer att lägga sin affärslogik i SPs.
- Du blir beroende av en databas. Vill du stödja flera olika databassystem senare med samma mjukvara så är det bara att börja om från början med ALL affärslogik.
- Vill man ha all affärslogik i databasen blir det allt som oftast en konflikt vid hantering av större mängder binär data. I databasen trots att det är ett ineffektivt val eller separera den biten av affärslogiken och därmed inte ha allt i databaslagret.
- Ska flera avdelningar/företag/applikationer osv gå direkt mot samma databas KAN det vara en fördel att använda SPs men det gör inte sällan att det skapas förutsättningar om hur andra ska använda datan.
- SPs är lätta att testa isolerat, men gör integrationstester desto svårare.
- Användande av SPs lägger ofta onödigt mycket last i databaslagret vilket är den svåraste delen att skala och optimera.
- Använder du ett smidigt applikationsspråk så är utvecklingstiden och tiden som går åt till underhåll desto högre i SPs (inkluderar ASP.NET, PHP, RoR etc).

Inte på något sätt uttömmande. Det finns mängder av IFs och BUTs som gör det lämpligare eller olämpligare. Men att blanda affärslogik i applikationen och i SPs skulle jag vilja påstå är något man helt och hållet ska hålla sig borta ifrån. Vilket nog precis blir fallet för TS.

Intressant, har tidigare funderat på varför SP's anses mer av "best practice" i asp.net världen medans det i php/mysql inte är så diskuterat eller använt.

Hur brukar ni hantera stora SQL frågor, lägger ni dem i ett "datalager" ?
Det känns bökigt när man har sql frågor med typ 10 joins att lägga in dem i en vanlig php sträng, jag har tidigare övervägt att lägga in dessa i SP's istället för att få enklare underhåll.