Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Unhappy Unanswered: Procedure problems

    Hello!

    I'm having a problem in the following Store Procedures.

    -------------------------------------

    CREATE PROC TestProcedure
    AS
    BEGIN

    CREATE TABLE #TempA (
    A_1 int,
    A_2 char(1),
    A_3 datetime NULL,
    A_4 datetime NULL,
    A_5 datetime NULL
    )

    CREATE TABLE #TempB (
    B_1 numeric(10,0) identity,
    B_2 int,
    B_3 char(1),
    B_4 datetime,
    B_5 float
    )

    CREATE TABLE #TempC (
    C_1 int,
    C_2 char(1),
    C_3 datetime
    )

    INSERT #TempA
    SELECT x.Q, x.W, x.E, x.R, y.T
    FROM TableX x, TableY y
    WHERE x.Id = y.Id

    INSERT #TempB (B_2, B_3, B_4, B_5)
    SELECT W, E, R, T, Y
    FROM TableX x, TableY y, TableZ z
    WHERE x.Id = y.Id AND y.Id = z.Id

    INSERT #TempC
    SELECT t1.B_2, t1.B_3, MAX(t1.B_4)
    FROM #TempB t1, #TempB t2
    WHERE t1.B_1 = (t2.B_1 - 1)
    AND t1.B_2 = t2.B_2
    AND t1.B_3 = t2.B_3
    AND t1.B_5 <> t2.B_5
    GROUP BY t1.B_2, t1.B_3

    SELECT * FROM #TempC

    DROP TABLE #TempA
    DROP TABLE #TempB
    DROP TABLE #TempC
    END
    go

    -------------------------------------

    The problem is on the query that inserts information on the table #TempC.
    If executed the procedure gets stuck on the "SELECT ... FROM #TempB t1, #TempB t2 ...".
    Executing all queries one by one (without using the procedure) this problem doesn't occur
    and all is processed normally.

    One more piece of information. Normally the #TempB table contains around 120000 rows.

    Any clue of what could be happening?

    Thanks in advance.

    Regards,
    Fernando

  2. #2
    Join Date
    Aug 2003
    Posts
    123
    Check the insert into #TEMPC in the from class both are TempB, is it intentional or typo.

    --Jaggu

  3. #3
    Join Date
    Feb 2002
    Posts
    8
    Having two times #TempB in the select is intentional.
    Because I need to select rows based on conditions of the next row.

  4. #4
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    Since #TempB is a somewhat large table joined
    to itself, an index on it shoud speed the join.
    For instance, on cols B_1, B_2 and B_3.
    There's the chance that ASE creates a dinamic
    index when the statements are run outside a
    stored proc; this could be the reason why
    it run quite quickly despite the lack of indexes.

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •