Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Indexes on temporary tables in stored proc

9 posts in Windows NT Last posting was on 2001-04-27 20:25:30.0Z
Michael J. Austin Posted on 2001-04-15 23:27:57.0Z
Reply-To: "Michael J. Austin" <maustin@hevanet.com>
From: "Michael J. Austin" <maustin@hevanet.com>
Subject: Indexes on temporary tables in stored proc
Date: Sun, 15 Apr 2001 16:27:57 -0700
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <TkldWagxAHA.205@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: ts03-ip72.hevanet.com 206.163.60.144
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:949
Article PK: 1087557

All:

I have been told by a DBA that if I create an index on a temporary table in
a stored proc that the index will never be used. The query plan looks at the
temp table, sees that it has no rows and decides not to use the index. The
solution, according to the DBA, is to put the code that you want to use the
indexes in a separate stored proc with the recompile parameter specified.
Then, each time the stored proc is called, the query plan will be
regenerated and the indexes will be used.

Is there an easier way to force an index on a temp table to be used?

BTW, this is my first posting to a Sybase newsgroup in some time, so if
there is a better place to post this message please let me know.

TIA,

Mike


Eric Miner Posted on 2001-04-16 16:55:00.0Z
Message-ID: <3ADB23E4.C6CAA4BD@sybase.com>
Date: Mon, 16 Apr 2001 09:55:00 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: "Michael J. Austin" <maustin@hevanet.com>
CC: eminer@sybase.com
Subject: Re: Indexes on temporary tables in stored proc
References: <TkldWagxAHA.205@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 233
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:948
Article PK: 1087555

Hello Mike,

Well, what you've been told is a 'myth'.

I'm including a mail I posted the to Sybase List a few weeks ago on this
subject.

Let me know if you have any questions.

Eric Miner
Sybase
ESD Engineering
Optimizer Group
===============================================
The docs are wrong, well sort of anyway. This 'rule' will apply to a batch
query, but not a procs.

Let's take a look: ( see my notes following the <----- s ) If this confuses
more let me know. The bottom line is that the create index changes the table and
thus 'bumps the internal schema count this forces a recompilation but execution
can be restarted at the SARG.

This has been the case with procs for some time. I need to talk to the docs
folks I guess. Even the example in the docs is optimizable as a proc.

Later,

Eric

1> create proc t1 as
2> drop table #t1
3> select * into #t1 from tpcd..lineitem
4> create index i1 on #t1(l_orderkey)
5> select count (*) from #t1 where l_partkey <= 100
6> and l_orderkey = 10000
7> go
1> exec t1
2> go

We first have to do an initial compilation of the proc ---

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is EXECUTE.



*******************************
Beginning selection of qualifying indexes for table 'tpcd..lineitem',
varno = 0, objectid 240003886.
The table (Datarows) has 600572 rows, 44308 pages,
The table's Data Page Cluster Ratio 1.000000

Table scan cost is 600572 rows, 44489 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement


*******************************
Beginning selection of qualifying indexes for table '#t1',
varno = 0, objectid 0.
The table (Allpages) has 100 rows, 10 pages, <----- at this point we don't
know anything about the size of the table, thus 'magic numbers'.
Data Page Cluster Ratio 0.000000

Table scan cost is 100 rows, 10 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement


Selecting best index for the SEARCH CLAUSE:
#t1.l_orderkey = 10000
#t1.l_partkey <= 100

No statistics available for l_partkey,
using the default range selectivity to estimate selectivity.

Estimated selectivity for l_partkey,
selectivity = 0.330000.

No statistics available for
l_orderkey, <-- no
stats yet since we haven't executed the create index
using the default equality selectivity to estimate selectivity.

Estimated selectivity for l_orderkey,
selectivity = 0.100000.

QUERY PLAN FOR STATEMENT 1 (at line 2).


STEP 1
The type of query is DROP TABLE.


QUERY PLAN FOR STATEMENT 2 (at line 3).
Executed in parallel by coordinating process and 20 worker processes.


STEP 1
The type of query is CREATE TABLE.
Executed by coordinating process.

STEP 2
The type of query is INSERT.
The update mode is direct.
Executed in parallel by 20 worker processes.

FROM TABLE
tpcd..lineitem
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 20-way hash scan.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
#t1


QUERY PLAN FOR STATEMENT 3 (at line 4).


STEP 1
The type of query is CREATE INDEX.
TO TABLE
#t1


QUERY PLAN FOR STATEMENT 4 (at line 5).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
#t1
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

STEP 2
The type of query is SELECT.

<------at this initial point compilation ends
Msg 3701, Level 11, State
1: <----- here execution
begins
Procedure 't1', Line 2:
Cannot drop the table '#t1', because it doesn't exist in the system catalogs.
The sort for #t1__________01000010012172325 is done in Serial




*******************************
Beginning selection of qualifying indexes for table 'tpcd..lineitem',
varno = 0, objectid 240003886.
The table (Datarows) has 600572 rows, 44308 pages,
The table's Data Page Cluster Ratio 1.000000

Table scan cost is 600572 rows, 44489 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement

<------ here
costing the SARG begins, we've already created the index. The create index
'bumps' the schema count and tells us
that
something has changed the table and we need to recompile. However, a place
marker tells us that we only need to

execute beginning here.


******************************* <------Now we see the index and
stats and can use them both in optimization
Beginning selection of qualifying indexes for table '#t1',
varno = 0, objectid 1046069601.
The table (Allpages) has 5000 rows, 344 pages,
Data Page Cluster Ratio 0.999990

Table scan cost is 5000 rows, 344 pages,
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement


Selecting best index for the SEARCH CLAUSE:
#t1.l_orderkey = 10000
#t1.l_partkey <= 100

No statistics available for l_partkey,
using the default range selectivity to estimate selectivity.

Estimated selectivity for l_partkey,
selectivity = 0.330000.


Estimated selectivity for l_orderkey,
selectivity = 0.000988, upper limit = 0.053800.

Estimating selectivity of index 'i1', indid 2
scan selectivity 0.000988, filter selectivity 0.000988
5 rows, 3 pages, index height 1,
Data Row Cluster Ratio 0.999785,
Index Page Cluster Ratio 1.000000,
Data Page Cluster Ratio 0.976821


The best qualifying index is 'i1' (indid 2)
costing 3 pages,
with an estimate of 2 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Search argument selectivity is 0.000326.

*******************************

QUERY PLAN FOR STATEMENT 1 (at line 2).


STEP 1
The type of query is DROP TABLE.


QUERY PLAN FOR STATEMENT 2 (at line 3).
Executed in parallel by coordinating process and 20 worker processes.


STEP 1
The type of query is CREATE TABLE.
Executed by coordinating process.

STEP 2
The type of query is INSERT.
The update mode is direct.
Executed in parallel by 20 worker processes.

FROM TABLE
tpcd..lineitem
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 20-way hash scan.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
#t1


QUERY PLAN FOR STATEMENT 3 (at line 4).


STEP 1
The type of query is CREATE INDEX.
TO TABLE
#t1


QUERY PLAN FOR STATEMENT 4 (at line 5).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped COUNT AGGREGATE.

FROM TABLE
#t1
Nested iteration.
Index : i1
Forward scan.
Positioning by key.
Keys are:
l_orderkey ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

STEP 2
The type of query is SELECT.


-----------
0

(1 row affected)
(return status = -1)
1>

"Michael J. Austin" wrote:

> All:
>
> I have been told by a DBA that if I create an index on a temporary table in
> a stored proc that the index will never be used. The query plan looks at the
> temp table, sees that it has no rows and decides not to use the index. The
> solution, according to the DBA, is to put the code that you want to use the
> indexes in a separate stored proc with the recompile parameter specified.
> Then, each time the stored proc is called, the query plan will be
> regenerated and the indexes will be used.
>
> Is there an easier way to force an index on a temp table to be used?
>
> BTW, this is my first posting to a Sybase newsgroup in some time, so if
> there is a better place to post this message please let me know.
>
> TIA,
>
> Mike


Eric Miner Posted on 2001-04-16 17:02:01.0Z
Message-ID: <3ADB2589.B922C006@sybase.com>
Date: Mon, 16 Apr 2001 10:02:01 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Indexes on temporary tables in stored proc
References: <TkldWagxAHA.205@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.performance+tuning
Lines: 22
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:701
Article PK: 1097146

I'm also posting this to the P&T newsgroup

"Michael J. Austin" wrote:

> All:
>
> I have been told by a DBA that if I create an index on a temporary table in
> a stored proc that the index will never be used. The query plan looks at the
> temp table, sees that it has no rows and decides not to use the index. The
> solution, according to the DBA, is to put the code that you want to use the
> indexes in a separate stored proc with the recompile parameter specified.
> Then, each time the stored proc is called, the query plan will be
> regenerated and the indexes will be used.
>
> Is there an easier way to force an index on a temp table to be used?
>
> BTW, this is my first posting to a Sybase newsgroup in some time, so if
> there is a better place to post this message please let me know.
>
> TIA,
>
> Mike


Ray DiMarcello Posted on 2001-04-27 20:25:30.0Z
From: "Ray DiMarcello" <rdimarcello@ingva.com>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com>
Subject: Re: Indexes on temporary tables in stored proc
Date: Fri, 27 Apr 2001 16:25:30 -0400
Lines: 157
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <BpHags1zAHA.178@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.performance+tuning
NNTP-Posting-Host: 207.245.124.67
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:557
Article PK: 1097004

Not true. Again and again I create temp tables in stored procs, populate
them with data, then create an index, then use the table in a query and the
index is used (although the showplan says something like "index name not
found").


2> create proc test_temp_idx (@arg numeric(7))
3> as
4> declare @cConDisplay char(10)
5>
6> select iIdCon, cConDisplay into #temp from admin_prod..Contract
7> create index temp_idx on #temp (iIdCon)
8>
9> select @cConDisplay = cConDisplay from #temp where iIdCon = @arg
10> select iIdCon from #temp where cConDisplay = @cConDisplay
11> return 0
12> go
Execution Time (ms.): 1000 Clock Time (ms.): 1000
1> test_temp_idx 12345
2> go
Table: Contract scan count 1, logical reads: (regular=13790 apf=0
total=13790), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #temp________01000290017884795 scan count 0, logical reads:
(regular=190168 apf=0 total=190168), physical reads: (regular=0 apf=0
total=0),
apf IOs
used=0
Total writes for this command: 2311
Total writes for this command: 1028
Table: #temp________01000290017884795 scan count 0, logical reads:
(regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf
IOs u
sed=0
Total writes for this command: 0
Table: #temp________01000290017884795 scan count 1, logical reads:
(regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf
IOs u
sed=0
Total writes for this command: 0
iIdCon
----------
12345
Table: #temp________01000290017884795 scan count 1, logical reads:
(regular=2110 apf=0 total=2110), physical reads: (regular=0 apf=0 total=0),
apf
IOs
used=0
Total writes for this command: 0

(1 row affected)
Total writes for this command: 0
(return status = 0)
Total writes for this command: 2
Execution Time (ms.): 14000 Clock Time (ms.): 14000
1>


Look at the last two selects in the proc. The first could use the index I
created on #temp and seems to, completing the query in 4 logical reads. The
last scans because it's searching on a column with no index and needs 2110
reads.

Here it is again with an index on the other column also, and both queries
now need only 4 logical reads - indicating the use of a NC index with 3 leaf
levels ...



1>
2> create proc test_temp_idx (@arg numeric(7))
3> as
4> declare @cConDisplay char(10)
5>
6> select iIdCon, cConDisplay into #temp from admin_prod..Contract
7> create index temp_idx on #temp (iIdCon)
8> create index temp_idx2 on #temp (cConDisplay)
9>
10> select @cConDisplay = cConDisplay from #temp where iIdCon = @arg
11> select iIdCon from #temp where cConDisplay = @cConDisplay
12> return 0
13> go
Execution Time (ms.): 0 Clock Time (ms.): 0
1> test_temp_idx 12345
2> go
Table: Contract scan count 1, logical reads: (regular=13790 apf=0
total=13790), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #temp________01000290017884795 scan count 0, logical reads:
(regular=190168 apf=0 total=190168), physical reads: (regular=0 apf=0
total=0),
apf IOs
used=0
Total writes for this command: 1539
Total writes for this command: 1031
Total writes for this command: 2059
Table: #temp________01000290017884795 scan count 0, logical reads:
(regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf
IOs u
sed=0
Total writes for this command: 0
Table: #temp________01000290017884795 scan count 1, logical reads:
(regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf
IOs u
sed=0
Total writes for this command: 0
iIdCon
----------
12345
Table: #temp________01000290017884795 scan count 1, logical reads:
(regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf
IOs u
sed=0
Total writes for this command: 0

(1 row affected)
Total writes for this command: 0
(return status = 0)
Total writes for this command: 2
Execution Time (ms.): 17000 Clock Time (ms.): 17000
1>




RD

Eric Miner wrote in message <3ADB2589.B922C006@sybase.com>...
>I'm also posting this to the P&T newsgroup
>
>"Michael J. Austin" wrote:
>
>> All:
>>
>> I have been told by a DBA that if I create an index on a temporary table
in
>> a stored proc that the index will never be used. The query plan looks at
the
>> temp table, sees that it has no rows and decides not to use the index.
The
>> solution, according to the DBA, is to put the code that you want to use
the
>> indexes in a separate stored proc with the recompile parameter specified.
>> Then, each time the stored proc is called, the query plan will be
>> regenerated and the indexes will be used.
>>
>> Is there an easier way to force an index on a temp table to be used?
>>
>> BTW, this is my first posting to a Sybase newsgroup in some time, so if
>> there is a better place to post this message please let me know.
>>
>> TIA,
>>
>> Mike
>


The VipahMan Posted on 2001-04-18 12:46:04.0Z
From: "The VipahMan" <whoelse@whereelse.com>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com>
Subject: Re: Indexes on temporary tables in stored proc
Date: Wed, 18 Apr 2001 08:46:04 -0400
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <XVom8fAyAHA.191@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.performance+tuning
NNTP-Posting-Host: 209.208.136.88
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:684
Article PK: 1097132

sybase uses magic numbers to optimize temp tables. i think it assumes 100
rows and x pages. i don't remember the value for x.

the DBA recommended method is the safest.

my $0.01 after stock market declines.

"Eric Miner" <eminer@sybase.com> wrote in message
news:3ADB2589.B922C006@sybase.com...
> I'm also posting this to the P&T newsgroup
>
> "Michael J. Austin" wrote:
>
> > All:
> >
> > I have been told by a DBA that if I create an index on a temporary table
in
> > a stored proc that the index will never be used. The query plan looks at
the
> > temp table, sees that it has no rows and decides not to use the index.
The
> > solution, according to the DBA, is to put the code that you want to use
the
> > indexes in a separate stored proc with the recompile parameter
specified.
> > Then, each time the stored proc is called, the query plan will be
> > regenerated and the indexes will be used.
> >
> > Is there an easier way to force an index on a temp table to be used?
> >
> > BTW, this is my first posting to a Sybase newsgroup in some time, so if
> > there is a better place to post this message please let me know.
> >
> > TIA,
> >
> > Mike
>


Johan Posted on 2001-04-18 18:10:42.0Z
From: Johan
Date: Wed, 18 Apr 2001 14:10:42 -0400
Newsgroups: sybase.public.sqlserver.performance+tuning
Subject: Re: Indexes on temporary tables in stored proc
Message-ID: <639494F66F76B4160063DB4F85256A32.00483B0785256A32@webforums>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com> <XVom8fAyAHA.191@forums.sybase.com>
Lines: 26
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:674
Article PK: 1097122

See my posting on procedures and re-resolution, actually re-optimization. -
thanks Eric :¬)

That should help a bit, and maybe slightly surprise your DBA as well. Do
not always believe the docs.

set statistics io on
set showplan on

...
and run your proc.

(Ask me), the truth will surprise. (remember that an index on any empty
table has no statistics, which means the optimiser cannot properly
optimize.) Further more, if I remember correctly the default assumption for
a #table with no indexes is 100 pages 10 rows per page. Is that still the
case in 12 / 12.5 ?
Once the table is indexed the server has more accurate stats.

All things considered #tables should be used with care though, and I would
always attempt to minize the use of it.

There are quite a few postings on the woes of #tables in this group, if you
care to look it up. This does not mean do not use, it just means use
prudently and with care.


Jim Egan Posted on 2001-04-19 05:48:00.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Indexes on temporary tables in stored proc
Date: Wed, 18 Apr 2001 23:48:00 -0600
Message-ID: <MPG.154829f74bb40ebd98b2ad@forums.sybase.com>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com> <XVom8fAyAHA.191@forums.sybase.com> <639494F66F76B4160063DB4F85256A32.00483B0785256A32@webforums>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.sqlserver.performance+tuning
Lines: 15
NNTP-Posting-Host: c1420400-b.hiland1.co.home.com 65.7.153.228
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:666
Article PK: 1097114


Johan wrote...
> (remember that an index on any empty=20
> table has no statistics, which means the optimiser cannot properly=20
> optimize.)
>

OK, I'm being really picky here but an empty table CAN have statistics. There are a number
of ways to get them. But what you're talking about is creating a table, creating an index
and THEN adding the data. In this case there are no column level statistics.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com


Johan Posted on 2001-04-19 08:53:07.0Z
From: Johan
Date: Thu, 19 Apr 2001 04:53:07 -0400
Newsgroups: sybase.public.sqlserver.performance+tuning
Subject: Re: Indexes on temporary tables in stored proc
Message-ID: <9308C5E2F45375A30030CF0085256A33.0022247A85256A33@webforums>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com> <XVom8fAyAHA.191@forums.sybase.com> <639494F66F76B4160063DB4F85256A32.00483B0785256A32@webforums> <MPG.154829f74bb40ebd98b2ad@forums.sybase.com>
Lines: 14
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:660
Article PK: 1097108

You have my attention re: "number of ways to get them." ("them" being stats
on an empty table).
Could you elaborate please, as I'm definitely interested.

>> Johan wrote...
>> (remember that an index on any empty table has no statistics, which

means the optimiser cannot properly optimize.)

>OK, I'm being really picky here but an empty table CAN have statistics.
There are a number of ways to get them.
>But what you're talking about is creating a table, creating an index and
THEN adding the data.
>In this case there are no column level statistics. -- Jim Egan
[TeamSybase] Senior Consultant Sybase Professional Services


Jim Egan Posted on 2001-04-19 14:30:13.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Indexes on temporary tables in stored proc
Date: Thu, 19 Apr 2001 08:30:13 -0600
Message-ID: <MPG.1548a45dcf60b7be98b2b6@forums.sybase.com>
References: <TkldWagxAHA.205@forums.sybase.com> <3ADB2589.B922C006@sybase.com> <XVom8fAyAHA.191@forums.sybase.com> <639494F66F76B4160063DB4F85256A32.00483B0785256A32@webforums> <MPG.154829f74bb40ebd98b2ad@forums.sybase.com> <9308C5E2F45375A30030CF0085256A33.0022247A85256A33@webforums>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.sqlserver.performance+tuning
Lines: 18
NNTP-Posting-Host: jpe650.sybase.com 157.133.56.43
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:657
Article PK: 1097105


Johan wrote...
> You have my attention re: "number of ways to get them." ("them" being stats
> on an empty table).
> Could you elaborate please, as I'm definitely interested.
>

1. Create a table, populate it with data, generate statistics, truncate the table.
2. Use OptDiag to load statistics.
3. Write SQL to populate the sysstatistics and systabstats tables.

I haven't done #3 and it sounds like a fair amount of work. Since there is no public API
for what's in the tables and how they get populated that option probably isn't a good one.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com