mssql - Optimere stored procedure kald? - Udvikleren.dk - Programmering, webdesign og grafik

Tags: mssql stored-procedure

Bruger #9814 @ 07.09.12 15:35

Jeg har et view i en ms-sql database hvor to af kolonnerne bliver beregnet ved stored procedure kald. Problemet er, at det tager uhyggelig lang tid:

Mit sql-script:

SQL kode

 SELECT dbo.HISTORICAL_BALANCE_DAYS(h.ID) AS BALANCE_DAYS, dbo.HISTORICAL_BALANCE_HOURS(h.ID) AS BALANCE_HOURS..........

Og den ene - HISTORICAL_BALANCE_HOURS - af de to stored procedures herunder (de er magen til hinanden, men summerer på to forskellige felter). Den laver en sum over x antal rows og returnerer resultatet.

SQL kode

 @txid varbinary(max),
   @return_value_argument float(53)  OUTPUT
 AS 
   BEGIN
       DECLARE
         @res float(53)
 
       BEGIN TRY
         SELECT @res = isnull(sum(-HISTORICAL_BALANCE.HOURS), 0)
         FROM dbo.HISTORICAL_BALANCE
         WHERE HISTORICAL_BALANCE.TRANSACTION_ID = @txid
         SET @return_value_argument = @res
         RETURN 
       END TRY
       BEGIN CATCH
         BEGIN
             SET @return_value_argument = NULL
             RETURN 
         END
       END CATCH
   END

Problemet er, at den skal kalde de to stored procedures for mere end 120.000 rows og det tager LANG tid - så lang tid, at jeg får en time-out i mit C# program.

Kan ovenstående optimeres på en eller anden måde og kan man give mere "power" til disse stored procedure kald? Jeg har forsøgt alt fra regenerering af indexes og hvad jeg ellers har kunnet google.

(spørgsmålet er også på en anden hjemmeside - så får jeg svar der, skrives det selvfølgelig ind her i tråden)

6 svar postet i denne tråd vises herunder
3 indlæg har modtaget i alt 12 karma

Sorter efter stemmer Sorter efter dato

Bruger #17223 @ 07.09.12 17:11

SQL kode

 SELECT h.TRANSACTION_ID, 
 ISNULL(SUM(h.HOURS), 0) AS HOURS,
 ISNULL(SUM(h.DAYS), 0) AS DAYS
 FROM dbo.HISTORICAL_BALANCE AS h
 GROUP BY h.TRANSACTION_ID

Hvorfor vil du beregne de to kolonner i funktioner?

Bruger #17223 @ 07.09.12 19:28

Stored procedures er også meget anvendt i SQL-Server, men i dit eksempel bruger du funktioner og ikke stored procedures.

Hvis du har brug for flere kolonner fra din tabel joiner du bare på den igen.

SQL kode

 SELECT h.a, h.b, hg.HOURS, hg.DAYS
 FROM dbo.HISTORICAL_BALANCE AS h
 INNER JOIN (SELECT h.TRANSACTION_ID,
             ISNULL(SUM(h.HOURS), 0) AS HOURS,
             ISNULL(SUM(h.DAYS), 0) AS DAYS
             FROM dbo.HISTORICAL_BALANCE AS h
             GROUP BY h.TRANSACTION_ID) AS hg
 ON h.TRANSACTION_ID = hg.TRANSACTION_ID

Bruger #17223 @ 07.09.12 21:39

Er det dette du prøver at gøre?

SQL kode

 SELECT
 ht.ID,
 ht.APP_USER_ID,
 ht.DEPARTMENT_NAME,
 hg.HOURS,
 hg.DAYS
 FROM (SELECT h.TRANSACTION_ID,
       ISNULL(SUM(h.HOURS), 0) AS HOURS,
       ISNULL(SUM(h.DAYS), 0) AS DAYS
       FROM dbo.HISTORICAL_BALANCE AS h
       GROUP BY h.TRANSACTION_ID) AS hg
 INNER JOIN dbo.HISTORICAL_TRANSACTION AS ht
 ON hg.TRANSACTION_ID = ht.ID
 AND (ht.ABSENCE_CANCEL_STATUS IS NULL OR ht.ABSENCE_CANCEL_STATUS = 0)

Bruger #9814 @ 07.09.12 18:35

1.302

SQL kode

 SELECT h.TRANSACTION_ID, 
 ISNULL(SUM(h.HOURS), 0) AS HOURS,
 ISNULL(SUM(h.DAYS), 0) AS DAYS
 FROM dbo.HISTORICAL_BALANCE AS h
 GROUP BY h.TRANSACTION_ID

Hvorfor vil du beregne de to kolonner i funktioner?

Det er konverteret fra en Oracle database, og der er stored procedures meget anvendt (det er det i hvertfald i denne database :-) ) og jeg kan godt se pointen i dit eksempel, men jeg kan ikke umiddelbart gruppere på transaction_id, da jeg har mange andre kolonner med i min select som jeg ikke tog med i mit eksempel for overskuelighedens skyld. Jeg tror at flere rækker vil gå tabt - måske. Men jeg vil arbejde lidt videre med dit eksempel, så tak for det.

Bruger #9814 @ 07.09.12 20:13

1.302

Stored procedures er også meget anvendt i SQL-Server, men i dit eksempel bruger du funktioner og ikke stored procedures.

Hvis du har brug for flere kolonner fra din tabel joiner du bare på den igen.

SQL kode

 SELECT h.a, h.b, hg.HOURS, hg.DAYS
 FROM dbo.HISTORICAL_BALANCE AS h
 INNER JOIN (SELECT h.TRANSACTION_ID,
             ISNULL(SUM(h.HOURS), 0) AS HOURS,
             ISNULL(SUM(h.DAYS), 0) AS DAYS
             FROM dbo.HISTORICAL_BALANCE AS h
             GROUP BY h.TRANSACTION_ID) AS hg
 ON h.TRANSACTION_ID = hg.TRANSACTION_ID

Kigger jeg i management studio ligger de nu under stored procedures og ikke functions - men hvad ved jeg :-)

Hvis dit eksempel skal bygges ind i dette sql-script, kan det så lade sig gøre?

SQL kode

 SELECT     
 ht.ID, 
 ht.APP_USER_ID,
 ht.DEPARTMENT_NAME
 
 FROM         
 dbo.APP_USER AS a INNER JOIN
 dbo.HISTORICAL_TRANSACTION AS ht LEFT OUTER JOIN
 dbo.WAGE_PERIOD AS wp ON wp.ID = ht.WAGE_PERIOD_ID ON a.ID = ht.APP_USER_ID
 
 WHERE     
 (ht.ABSENCE_CANCEL_STATUS IS NULL) OR (ht.ABSENCE_CANCEL_STATUS = 0)

~~Jeg har selvfølige forsøgt, men det fejler på din sidste linje "ON h.transaction...." med at h.transaction_id ikke findes. Og den findes fint nok i historical_balance~~

*edit*

Nu fik jeg brygget dette sammen:

SQL kode

 SELECT     
 ht.ID, 
 ht.APP_USER_ID,
 ht.DEPARTMENT_NAME,
 hg.hours,
 hg.DAYS
 
 FROM         
 (dbo.HISTORICAL_BALANCE AS h
 INNER JOIN (SELECT h.TRANSACTION_ID,
 ISNULL(SUM(h.HOURS), 0) AS HOURS,
 ISNULL(SUM(h.DAYS), 0) AS DAYS
 FROM dbo.HISTORICAL_BALANCE AS h
 GROUP BY h.TRANSACTION_ID) AS hg
 ON h.TRANSACTION_ID = hg.TRANSACTION_ID),
 
 dbo.APP_USER AS a INNER JOIN
 dbo.HISTORICAL_TRANSACTION AS ht LEFT OUTER JOIN
 dbo.WAGE_PERIOD AS wp ON wp.ID = ht.WAGE_PERIOD_ID ON a.ID = ht.APP_USER_ID
 
 WHERE     
 (ht.ABSENCE_CANCEL_STATUS IS NULL) OR (ht.ABSENCE_CANCEL_STATUS = 0)

Men den returnerer samme værdi for alle rækker i hg.hours og hg.days :-( og den kører lige så lang tid som mit oprindelige script.

Indlæg senest redigeret d. 07.09.2012 20:24 af Bruger #9814

Bruger #9814 @ 07.09.12 22:39

1.302

Det er sgu fedt - nu tæller den rigtigt og det er hurtigt. Men for at få alle de rigtige transaktioner med skal jeg lige have de to andre betingelser med:

dbo.APP_USER AS a INNER JOIN
dbo.WAGE_PERIOD AS wp ON wp.ID = ht.WAGE_PERIOD_ID ON a.ID = ht.APP_USER_ID

Men jeg kan ikke gennemskue deres placering i scriptet... Jeg skal vist have læst op på ON begrebet, da jeg ikke helt forstår det.

MEN TUSIND TAK! Du har været en stor hjælp.

Optimere stored procedure kald?

Karma barometer (30 dage)

Modtaget

Givet

Favorit hos
Forum tråde
Artikler