Stored Procedures: Using an Index on a Temp Table

Stored Procedures: Using an Index on a Temp Table

Post by Freshwa » Thu, 25 Feb 1999 04:00:00



I have built a stored procedure in Sybase, that creates a few temp tables.  On
one  of those tables I have created an index:

CREATE TABLE #items_ldgr
                        (fund_code char(8) NULL,
                        fund char(60) NULL,
                        fund_name char(60) NULL,
                        currency char(3) NULL,
                        value_date datetime NULL,
                        match_no integer NULL,
                        ref_no char(16) NULL,
                        description char(50) NULL,
                        cusip char(9) NULL,
                        trade_date datetime NULL,
                        amount Decimal (18,2) NULL,
                        match_date datetime NULL,
                        stmt_date datetime NULL,
                        status char(1) NULL,
                        gin integer NULL )

CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)

     Then I joined this with another table temp table with a similar index.  I
did put the index in the join clause so that it would force the index:

FROM            #items_ldgr l (index items_ldgr_idx),

     When I run this in the Query Planner, it tells me that it cannot find the
index.  Are there any steps that I am missing, or am I doing this correctly?  I
am approaching this the same way as you would for a normal table index.

          Thanks,
            Jeff

 
 
 

Stored Procedures: Using an Index on a Temp Table

Post by Leonard Niseno » Thu, 25 Feb 1999 04:00:00




Quote:> I have built a stored procedure in Sybase, that creates a few temp tables.  On
> one  of those tables I have created an index:

> CREATE TABLE #items_ldgr
>                    (fund_code char(8) NULL,
>                    fund char(60) NULL,
>                    fund_name char(60) NULL,
>                    currency char(3) NULL,
>                    value_date datetime NULL,
>                    match_no integer NULL,
>                    ref_no char(16) NULL,
>                    description char(50) NULL,
>                    cusip char(9) NULL,
>                    trade_date datetime NULL,
>                    amount Decimal (18,2) NULL,
>                    match_date datetime NULL,
>                    stmt_date datetime NULL,
>                    status char(1) NULL,
>                    gin integer NULL )

> CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)

>      Then I joined this with another table temp table with a similar index.  I
> did put the index in the join clause so that it would force the index:

> FROM               #items_ldgr l (index items_ldgr_idx),

>      When I run this in the Query Planner, it tells me that it cannot find the
> index.  Are there any steps that I am missing, or am I doing this correctly?  I
> am approaching this the same way as you would for a normal table index.

>           Thanks,
>             Jeff

Funny you should mention this problem.  I had the exact same problem with
this (except my was an update based on the index) and posted to the
group.  The problem is that the index doesn't exist when the optimizer
creates the stored procedure, hence it has no statistics for the index
and will do table scans.  Move the select that needs the index to another
stored procedure (procedure 1 calls procedure 2) and the optimizer will
pick it up.  I was hoping someone here knew of a better way, but it
appears this is the way it has to be done.  I hope this helps!

Leonard

 
 
 

Stored Procedures: Using an Index on a Temp Table

Post by Kyle Hache » Thu, 25 Feb 1999 04:00:00


There was an earlier post concerning this issue.  The problem is the index doesn't
exist when the procedure is compiled.  You need to run the query in a separate
sub-procedure.  Create procedure A which creates the table and index.  Have
procedure A then call procedure B which will run the query.  This should work.

Kyle Hachey,
PLATINUM technology


> I have built a stored procedure in Sybase, that creates a few temp tables.  On
> one  of those tables I have created an index:

> CREATE TABLE #items_ldgr
>                         (fund_code char(8) NULL,
>                         fund char(60) NULL,
>                         fund_name char(60) NULL,
>                         currency char(3) NULL,
>                         value_date datetime NULL,
>                         match_no integer NULL,
>                         ref_no char(16) NULL,
>                         description char(50) NULL,
>                         cusip char(9) NULL,
>                         trade_date datetime NULL,
>                         amount Decimal (18,2) NULL,
>                         match_date datetime NULL,
>                         stmt_date datetime NULL,
>                         status char(1) NULL,
>                         gin integer NULL )

> CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)

>      Then I joined this with another table temp table with a similar index.  I
> did put the index in the join clause so that it would force the index:

> FROM            #items_ldgr l (index items_ldgr_idx),

>      When I run this in the Query Planner, it tells me that it cannot find the
> index.  Are there any steps that I am missing, or am I doing this correctly?  I
> am approaching this the same way as you would for a normal table index.

>           Thanks,
>             Jeff

 
 
 

Stored Procedures: Using an Index on a Temp Table

Post by Gene Williams, J » Fri, 26 Feb 1999 04:00:00


Now, I'm not promoting this as a good approach, but you could also do
the following:

Build the temp table outside of the stored proc.  That means it would
be a 'permanent' table, but you could place it on tempdb by using
CREATE TABLE TEMPDB..items_ldgr.  Then create the index.

If the first step in the stored proc is a 'DELETE TEMPDB..items_ldgr'
statement, then you'll start with an empty table each time.  Assuming
that this proc is the only process that will use the table, you should
have no problems.  If more than one instance of the proc is running
simultaneously, one of the instances will simply block until the other
process is finished.

Gene





>> I have built a stored procedure in Sybase, that creates a few temp tables.  On
>> one  of those tables I have created an index:

>> CREATE TABLE #items_ldgr
>>                        (fund_code char(8) NULL,
>>                        fund char(60) NULL,
>>                        fund_name char(60) NULL,
>>                        currency char(3) NULL,
>>                        value_date datetime NULL,
>>                        match_no integer NULL,
>>                        ref_no char(16) NULL,
>>                        description char(50) NULL,
>>                        cusip char(9) NULL,
>>                        trade_date datetime NULL,
>>                        amount Decimal (18,2) NULL,
>>                        match_date datetime NULL,
>>                        stmt_date datetime NULL,
>>                        status char(1) NULL,
>>                        gin integer NULL )

>> CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)

>>      Then I joined this with another table temp table with a similar index.  I
>> did put the index in the join clause so that it would force the index:

>> FROM           #items_ldgr l (index items_ldgr_idx),

>>      When I run this in the Query Planner, it tells me that it cannot find the
>> index.  Are there any steps that I am missing, or am I doing this correctly?  I
>> am approaching this the same way as you would for a normal table index.

>>           Thanks,
>>             Jeff

>Funny you should mention this problem.  I had the exact same problem with
>this (except my was an update based on the index) and posted to the
>group.  The problem is that the index doesn't exist when the optimizer
>creates the stored procedure, hence it has no statistics for the index
>and will do table scans.  Move the select that needs the index to another
>stored procedure (procedure 1 calls procedure 2) and the optimizer will
>pick it up.  I was hoping someone here knew of a better way, but it
>appears this is the way it has to be done.  I hope this helps!

>Leonard

 
 
 

Stored Procedures: Using an Index on a Temp Table

Post by Marvi » Fri, 26 Feb 1999 04:00:00


hi there,

for all those who have no printed manuals:
you can find this at
http://sybooks.sybase.com/onlinebooks/group-as/asg1150e/perform/
the stored procedure problem is described at chapter 11.

regards
--marvin

Quote:

>>      When I run this in the Query Planner, it tells me that it cannot
find the
>> index.  Are there any steps that I am missing, or am I doing this
correctly?  I
>> am approaching this the same way as you would for a normal table index.

>>           Thanks,
>>             Jeff

>Funny you should mention this problem.  I had the exact same problem with
>this (except my was an update based on the index) and posted to the
>group.  The problem is that the index doesn't exist when the optimizer
>creates the stored procedure, hence it has no statistics for the index
>and will do table scans.  Move the select that needs the index to another
>stored procedure (procedure 1 calls procedure 2) and the optimizer will
>pick it up.  I was hoping someone here knew of a better way, but it
>appears this is the way it has to be done.  I hope this helps!

>Leonard

 
 
 

Stored Procedures: Using an Index on a Temp Table

Post by Michael Pepple » Fri, 26 Feb 1999 04:00:00



> Now, I'm not promoting this as a good approach, but you could also do
> the following:

> Build the temp table outside of the stored proc.  That means it would
> be a 'permanent' table, but you could place it on tempdb by using
> CREATE TABLE TEMPDB..items_ldgr.  Then create the index.

> If the first step in the stored proc is a 'DELETE TEMPDB..items_ldgr'
> statement, then you'll start with an empty table each time.  Assuming
> that this proc is the only process that will use the table, you should
> have no problems.  If more than one instance of the proc is running
> simultaneously, one of the instances will simply block until the other
> process is finished.

Add a spid column to the temp table. And use that as the first item of your
index. Then multiple processes can access the same table without colliding (modulo
page level locks, of course).

Michael

> Gene





> >> I have built a stored procedure in Sybase, that creates a few temp tables.  On
> >> one  of those tables I have created an index:

> >> CREATE TABLE #items_ldgr
> >>                      (fund_code char(8) NULL,
> >>                      fund char(60) NULL,
> >>                      fund_name char(60) NULL,
> >>                      currency char(3) NULL,
> >>                      value_date datetime NULL,
> >>                      match_no integer NULL,
> >>                      ref_no char(16) NULL,
> >>                      description char(50) NULL,
> >>                      cusip char(9) NULL,
> >>                      trade_date datetime NULL,
> >>                      amount Decimal (18,2) NULL,
> >>                      match_date datetime NULL,
> >>                      stmt_date datetime NULL,
> >>                      status char(1) NULL,
> >>                      gin integer NULL )

> >> CREATE INDEX items_ldgr_idx on #items_ldgr (fund, currency, match_no)

> >>      Then I joined this with another table temp table with a similar index.  I
> >> did put the index in the join clause so that it would force the index:

> >> FROM         #items_ldgr l (index items_ldgr_idx),

> >>      When I run this in the Query Planner, it tells me that it cannot find the
> >> index.  Are there any steps that I am missing, or am I doing this correctly?  I
> >> am approaching this the same way as you would for a normal table index.

> >>           Thanks,
> >>             Jeff

> >Funny you should mention this problem.  I had the exact same problem with
> >this (except my was an update based on the index) and posted to the
> >group.  The problem is that the index doesn't exist when the optimizer
> >creates the stored procedure, hence it has no statistics for the index
> >and will do table scans.  Move the select that needs the index to another
> >stored procedure (procedure 1 calls procedure 2) and the optimizer will
> >pick it up.  I was hoping someone here knew of a better way, but it
> >appears this is the way it has to be done.  I hope this helps!

> >Leonard

--
Michael Peppler         -||-  Data Migrations Inc.

Int. Sybase User Group  -||-  http://www.isug.com

 
 
 

1. Problem using shape recordsets with stored procedure that uses a temp table

Hello,

i use a stored procedure to get data. This sp uses a temporary table to get
the correct results. There is no problem with this sp retrieving the data
directly into an ado recordset, but if i want to use this sp in a shape
command the ODBC driver raises an error that he could not find the temporary
table.
In normal the temporary table is created in the sp and will be deleted after
executing them.

Is there anywhere a documentation on using temporary tables with a
hierarchical recordset, or better knows anybody the solution for this
problem.

The used shape command:
SHAPE {{ CALL sp_GetProductsByCategory (10,3,12) }} AS Products
APPEND ({{ CALL sp_GetMediaFiles() }} AS Mediafiles RELATE 'ProductID' TO
'ProductID') AS Mediafiles

The raised error:
Microsoft OLE DB Provider for ODBC Drivers: -2147467259
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#results'

Thanks a lot for reply
Ciao

2. SQL-DMO Programming Issue or BUG, regarding GetColumnDate

3. 2 Subsummaries print weird in fmp 3.6

4. Temp Tables and Indexes in Stored Procedures

5. Win NT 4 Workstation and SQL 7

6. Index for temp table in Stored Procedures

7. Log Shipping

8. indexing on a temp table in a stored procedure

9. indexes on temp tables in stored procedures

10. More on temp tables with indexes and stored procedures

11. insert into a global temp table using stored procedure

12. Creating a Temp table using results from a store procedure