Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Unanswered: Using variables within a stored proc

    Hi Folks,

    Been a bit out of date in development and would appreciate any help.

    Situation.

    Within a proc,

    1) a @variable is assigned a value in SELECT1
    2) and used for SELECT2 and is not picking up the index.

    The showplan is alright if the select is run manually with values. Checked the datatypes used in procedure and the table datatypes and it matches.

    The last I remember, sybase doesnt guarantee the above and recommends splitting the procs.

    Question is, is there any other workaround ?

    Possible I can think of.

    1) Force index (reluctant to do it)
    2) Re-write the proc with maybe a sub-select and see if that works.

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Forgot to add.

    This was working alright for about 2 years until one sudden weekend, started performing table scans.

    1. Not much data increase
    2. Checked optdiag and it looks alright

  3. #3
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    If the stored proc last recompile found uncommon statistics,
    or that execution happened to assign uncommon values to
    @variable, then recompile it now. If the query plan (the
    compiled in-memory version) stays a long time in the procedure
    cache, ASE may be executing a bad plan chosen days ago, when
    the situation was different.

    For instance, I've had problems with tables which begun empty
    but soon got millions of rows. ASE brought the query plan into
    the procedure cache (that is, compiled the stored proc) when
    it was empty, chose a tablescan, then kept table-scanning even
    when the table was huge. We had to force recompilations to solve.

    Remember the sintax: sp_recompile TABLE
    where TABLE is one the tables in your stored proc; better use the
    table having table scans.

    Hope it helps,
    Mariano Corral

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    thanks for your reply... But actually its the other way around... What stored procs were doing index scans is doing table scans now... And I did do the recompile just to be sure even before you said it , but no luck.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I use to have a similar problem where a proc will just fail to work after several months of no problems. Recompile didn't work but generating the ddl then drop and recreate the proc solved the problem every time. I still don't know why it happened and think it was on a 12.0 server.

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The problem has to do with the default selectivity of the column, vs. the actual selectivity of the data in the variable. IIRC the default selectivity is 0.33 in this situation, so if the statistics on the index are too broad ASE will estimate that enough of the table will have to be read for a table scan to become "optimal".

    You can use optdiag to get the statistics information for the table (and its indexes). Consider using update index statistics, and using more than the default number of steps to get better stats.

    Michael

  7. #7
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    <duplicated message removed...>

  8. #8
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    as Michael explained some time ago, when an index is created on a #temp table, the stored proc is optimised again. I'm not sure this kind of optimisation applies to the @variable, which may correspond to a very selective or a very broad value in the index.

    But you can try, just as a possible alternative, to use a temp table instead of a variable. Then, build an index on the temp table. This may not help with the selectivity issue, but may force a re-optimisation of the stored proc.

    Andrew

Posting Permissions

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