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.

Procedure Problems

2 posts in ,  UnixPerformance and TuningGeneral DiscussionLinux Windows NT Last posting was on 2000-01-21 23:49:46.0Z
Jorge Alberto Arévalo Ruiz Posted on 2000-01-21 22:34:12.0Z
From: "Jorge Alberto Arévalo Ruiz" <jaarevalor@hempseed.com>
Subject: Procedure Problems
Date: Fri, 21 Jan 2000 17:34:12 -0500
Lines: 106
Organization: SAES LTDA
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <8wG3f4FZ$GA.300@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.linux,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning,sybase.public.sqlserver.unix
NNTP-Posting-Host: 209.64.35.120
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3660 sybase.public.sqlserver.linux:311 sybase.public.sqlserver.nt:412 sybase.public.sqlserver.performance+tuning:254 sybase.public.sqlserver.unix:235
Article PK: 1074649

Hi!
The following extract of a stored procedure has the following problem:
If i use a variable named @cdgo_vddor, passed by value, for the execution
of the procedure then it doesn't use the index, but if i use a constant it
does
Example:

---- PROCEDURE USING THE VARIABLE--
-- THIS PROCEDURE DONT USE THE INDEX-
create PROCEDURE ps_trae_scrsles_vnddor

@cdgo_vddor INTEGER

AS

-- Proposito : Traer las estadisticas de los clientes compradores.
-- Autor : Jhon Geiler Ordonez
-- Version : 1.0
-- Fecha creación : 11 de Agosto de 1999

select distinct dbo.scrsles_vnddor.cdgo_clnte,
dbo.scrsles_vnddor.cdgo_cnta,
dbo.scrsles_vnddor.cdgo_scrsal,
dbo.scrsles_vnddor.cdgo_zna_vstas,
dbo.scrsles_vnddor.cdgo_vddor
FROM dbo.scrsles_vnddor
WHERE ( dbo.scrsles_vnddor.cdgo_vddor = @cdgo_vddor )

RETURN (0)


---- PROCEDURE USING THE CONSTANT--
-- THIS PROCEDURE USE THE INDEX-
create PROCEDURE ps_trae_scrsles_vnddor

@cdgo_vddor INTEGER

AS

-- Proposito : Traer las estadisticas de los clientes compradores.
-- Autor : Jhon Geiler Ordonez
-- Version : 1.0
-- Fecha creación : 11 de Agosto de 1999

select distinct dbo.scrsles_vnddor.cdgo_clnte,
dbo.scrsles_vnddor.cdgo_cnta,
dbo.scrsles_vnddor.cdgo_scrsal,
dbo.scrsles_vnddor.cdgo_zna_vstas,
dbo.scrsles_vnddor.cdgo_vddor
FROM dbo.scrsles_vnddor
WHERE ( dbo.scrsles_vnddor.cdgo_vddor = 1020 )


RETURN (0)


THE TABLE HAVE THE FOLLOW STRUCTURE, THE INDEX IS SCRSLES_VDDOR_X3

create table dbo.scrsles_vnddor (
cdgo_clnte int not null,
cdgo_cnta smallint not null,
cdgo_scrsal tinyint not null,
cdgo_estrctra_vntas_n1 tinyint not null,
cdgo_estrctra_vntas_n2 tinyint not null,
cdgo_estrctra_vntas_n3 tinyint not null,
cdgo_estrctra_vntas_n4 tinyint not null,
lna_prdcto tinyint not null,
cdgo_sprvsor tinyint not null,
cdgo_vddor smallint not null,
cdgo_zna_vstas smallint not null,
login_usrio char(30) null,
fcha_hra datetime null,
cmndo char(1) null,
id char(30) null,
constraint pk_scrsles_vnddor PRIMARY KEY CLUSTERED

cdgo_clnte,cdgo_cnta,cdgo_scrsal,cdgo_estrctra_vntas_n1,cdgo_estrctra_vntas_
n2,cdgo_estrctra_vntas_n3,cdgo_estrctra_vntas_n4,lna_prdcto )
)
lock allpages
on 'default'
go

print 'scrsles_vnddor_x2'
create unique nonclustered index scrsles_vnddor_x2
on dbo.scrsles_vnddor (cdgo_clnte, cdgo_cnta, cdgo_scrsal, lna_prdcto)
on 'default'
go

print 'scrsles_vnddor_x3'
create nonclustered index scrsles_vnddor_x3
on dbo.scrsles_vnddor (cdgo_vddor)
on 'default'
go


NOTE:
OS Windows NT Server 4.0 Service Pack 3
Adaptive Server 11.9.2
The server have 64M RAM
Total memory = 17920 Pages
Max Sql Text Monitore = 1024
Procedure Data Cache = 25
Any ideas?


Michael Peppler Posted on 2000-01-21 23:49:46.0Z
Message-ID: <3888F09A.90F39F18@peppler.org>
Date: Fri, 21 Jan 2000 15:49:46 -0800
From: Michael Peppler <mpeppler@peppler.org>
Organization: Data Migrations, Inc
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Procedure Problems
References: <8wG3f4FZ$GA.300@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.linux,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning,sybase.public.sqlserver.unix
Lines: 18
NNTP-Posting-Host: gw.peppler.org 206.55.243.57
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3659 sybase.public.sqlserver.linux:310 sybase.public.sqlserver.nt:411 sybase.public.sqlserver.performance+tuning:253 sybase.public.sqlserver.unix:234
Article PK: 1074648


"Jorge Alberto Arévalo Ruiz" wrote:
>
> Hi!
> The following extract of a stored procedure has the following problem:
> If i use a variable named @cdgo_vddor, passed by value, for the execution
> of the procedure then it doesn't use the index, but if i use a constant it
> does

The problem is that the @variable and the column don't have the same datatypes
(one is int and the other smallint).

Declare the @variable as smallint and the index should be used.

Michael
--
Michael Peppler -||- Data Migrations Inc.
mpeppler@peppler.org -||- http://www.mbay.net/~mpeppler
Int. Sybase User Group -||- http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com