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.

Cursor size

4 posts in ,  AdministrationGeneral DiscussionWindows NT Performance and Tuning Last posting was on 1998-03-29 18:08:18.0Z
Icatu Hartford S.A. Posted on 1997-10-13 10:16:21.0Z
From: "Icatu Hartford S.A." <informat@centroin.com.br>
Subject: Cursor size
Organization: Icatu Hartford S.A.
Message-ID: <01bcbde7$facdd620$70d7ffc8@dba>
X-Newsreader: Microsoft Internet News 4.70.1155
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Date: Mon, 13 Oct 1997 06:16:21 -0400
Lines: 21
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3341 sybase.public.sqlserver.general:796 sybase.public.sqlserver.nt:392 sybase.public.sqlserver.performance+tuning:245
Article PK: 1065504

Hi folks,

I have some applications that has to use cursors. As Sybase's cursors are
known to be the worst choice of doing anything, what should I use ?

First choice - A forty thousand row cursor processed one time. Get all 4
clients and the 10000 operations each one have.
Second choice - A ten thousand rows cursor processed four times. Get one
client and its operations, the second one and so forth.

(A cursor for only one client, where the first cursor have ClientId from 1
to 10000, second from 10001 to 20000, third from 20001 to 30000 and fourth
from 30001 to 40000)

In the second option I create, open, fetch, close and deallocate four times
a cursor and in the first one I do it only once.

Thanks for all.

Luiz Eduardo
DBA - ICatu Hartford
lguimaraes@bigfoot.com


greg shaull Posted on 1997-11-21 16:28:21.0Z
From: "greg shaull" <gregory.shaull@gmills.com>
Subject: Re: Cursor size
References: <01bcbde7$facdd620$70d7ffc8@dba>
Message-ID: <01bcf6a3$427e3200$2c01fe0a@lyn-0268>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Date: Fri, 21 Nov 1997 11:28:21 -0500
Lines: 24
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3339 sybase.public.sqlserver.general:794 sybase.public.sqlserver.nt:390 sybase.public.sqlserver.performance+tuning:243
Article PK: 1065500

Our experience with cursors leads me to say that opening the cursor once is
better than four times. Also it is not necessary to declare and deallocate
the cursor each time. If the declare cursor select includes parameters,
you can close the cursor, change the parameters and reopen it and get a
different result set. I doubt that either of these points will make a big
difference in your case.

The most important thing we have learned about cursors is to retrieve as
much of the required data in the initial cursor open as possible. So for
example, it is better to do a 4 table join in the declare cursor select
than it is to select from 1 table and then inside a logic loop retrieve
from the other 3 tables. Taking data lookups out of the logic loops and
putting them in the declare cursor select has reduced some of our jobs from
hours to minutes. I was shocked!!

greg shaull, dba
guilford mills, inc.


Michael Simon Posted on 1998-03-29 18:08:18.0Z
From: MSIMON@IX.NETCOM.COM (Michael Simon)
Subject: Re: Cursor size
Date: Sun, 29 Mar 1998 18:08:18 GMT
Message-ID: <351e84fe.4965214@forums.powersoft.com>
References: <01bcbde7$facdd620$70d7ffc8@dba> <01bcf6a3$427e3200$2c01fe0a@lyn-0268>
X-Newsreader: Forte Agent 1.5/32.452
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.performance+tuning
Lines: 120
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.performance+tuning:5014
Article PK: 1101335

On Fri, 21 Nov 1997 11:28:21 -0500, "greg shaull"

<gregory.shaull@gmills.com> wrote:

>Our experience with cursors leads me to say that opening the cursor once is
>better than four times. Also it is not necessary to declare and deallocate
>the cursor each time. If the declare cursor select includes parameters,
>you can close the cursor, change the parameters and reopen it and get a
>different result set. I doubt that either of these points will make a big
>difference in your case.
>
>The most important thing we have learned about cursors is to retrieve as
>much of the required data in the initial cursor open as possible. So for
>example, it is better to do a 4 table join in the declare cursor select
>than it is to select from 1 table and then inside a logic loop retrieve
>from the other 3 tables. Taking data lookups out of the logic loops and
>putting them in the declare cursor select has reduced some of our jobs from
>hours to minutes. I was shocked!!
>
>greg shaull, dba
>guilford mills, inc.
>

Greg,
I am using cursors to go through a temp table that is created based on
parts that were changed in the past X days. Where X is the number of
days that I want to go back from today. So sometimes I run this job
daily others weekly. The list of parts can range from 500 - 20,000.
For each of these parts I need to run two seperate stored procedures
to go through and calculate my customer's cost and pricing parameters.

Once that information is known, I do an update to the temp table that
I'm running the cursor against. (The initial temp table doesn't have a
price filled in so I need to update against the part I'm stepping
through.)

You were saying to select against multiple tables at one time. Would
it be possible instead to take that part number from the table and in
the fetch call a stored procedure at that time? As you can see I
would need multiple output values also.

Or would what you are talking about only work with simple joins from
others tables.

Thanks for your feed back.
Mike Simon
Vanstar Corporation

DECLARE PartNbr CURSOR FOR
SELECT A.Supplier_Part_Nbr
FROM #CustPart A

/**********************************/
/* Open Cursor & Fetch first row */
/**********************************/
OPEN PartNbr
Fetch PartNbr
INTO @Supplier_Part_Nbr
WHILE @@SQLSTATUS = 0
BEGIN /* Begin While */

/* Determine if the customer is authorized for bid pricing and get
** the lowest bid cost for input into GetCustomPriceForNav */
SELECT @BidCost = 0
EXEC OptimizedGetBidCost @Supplier_Part_Nbr,
@CustomerNbr,
'VIPLOW',
@BidNbr OUT,
@BidCost OUT
/* Pass the Bid Cost to the pricing routine */
IF @BidCost > 0
SELECT @Base_Cost = @BidCost
ELSE
SELECT @Base_Cost = NULL
exec @RetVal = GetCustomPriceForNav @CustomerNbr,
@Supplier_Part_Nbr, @Pricing_Method_Code OUT,
@Pricing_Method_Parm OUT,
@Fixed_Price OUT, @Base_Cost OUT
select @CustPrice = null
IF @RetVal <> 1
begin
SELECT @Base_Cost = Unit_Base_Cost
FROM Item_Supplier_Cost
WHERE Supplier_Code = 'CLC' and
Supplier_Part_Nbr = @Supplier_Part_Nbr and
Item_Cost_Type_Code = 'STD'
if @@RowCount > 0
begin
select @CustPrice = @Base_Cost * 1.2
IF @RoundPrice = 'Y'
select @CustPrice = CEILING(@CustPrice)
end
end
SELECT @CustPrice =
CASE
WHEN (@Pricing_Method_Code = 'D' and @CustPrice is Null)
Then @Fixed_Price
WHEN (@Pricing_Method_Code = 'B' and @CustPrice is Null AND

@RoundPrice = 'Y')
Then ceiling( IsNUll(@base_cost,0) / ( 1.00 -
( @Pricing_Method_Parm / 100 )))
WHEN (@Pricing_Method_Code = 'B' and @CustPrice is Null AND

@RoundPrice = 'N')
THEN round( IsNUll(@base_cost,0) / ( 1.00 - (
@Pricing_Method_Parm / 100 )),2 )
WHEN (@Pricing_Method_Code = 'C' and @CustPrice is Null AND

@RoundPrice = 'Y')
Then ceiling( IsNUll(@base_cost,0) * ((
@Pricing_Method_Parm / 100 ) + 1))
WHEN (@Pricing_Method_Code = 'C' and @CustPrice is
Null AND @RoundPrice = 'N')
THEN round( IsNUll(@base_cost,0) * ((
@Pricing_Method_Parm / 100 ) + 1),2 )
WHEN (@Pricing_Method_Code = 'E' and @CustPrice is Null)
Then IsNUll(@base_cost,0)
WHEN @CustPrice Is Not Null
THEN @CustPrice
ELSE IsNull(@Base_Cost,0) * 1.2
END
UPDATE #CustPart
SET CustPrice = IsNull(@CustPrice,0)
WHERE Supplier_Part_Nbr = @Supplier_Part_Nbr

Fetch PartNbr
INTO @Supplier_Part_Nbr
END

Close PartNbr

DEALLOCATE CURSOR PartNbr


Paul V. Piescik Posted on 1997-10-13 14:07:58.0Z
From: "Paul V. Piescik" <piescikp@injersey.com>
Subject: Re: Cursor size
References: <01bcbde7$facdd620$70d7ffc8@dba>
Message-ID: <01bcd7ea$3b893c60$0300a8c0@PiescikP.us.dbisna.com>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt,sybase.public.sqlserver.performance+tuning
Date: Mon, 13 Oct 1997 10:07:58 -0400
Lines: 15
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3340 sybase.public.sqlserver.general:795 sybase.public.sqlserver.nt:391 sybase.public.sqlserver.performance+tuning:244
Article PK: 1065501

I don't see enough detail in your question to be able to answer this!

I have jobs that use cursors to process 500,000 accounts; each account
needs to select from 6 tables for verification, then update 3 tables. No
problems.

Without no idea of what processing you're doing, whether memory, time or
something else is a consideration, and how you evaluate the trade-offs, the
only answer I can give is to try it both ways.

If you give more information, please also mention whether the 4 cursors
would be processed serially or in parallel.

Paul [TeamSybase]