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.

Number Retrieval Argument Problem

8 posts in DataWindow Last posting was on 2008-07-22 17:55:43.0Z
Theo Posted on 2008-07-07 22:10:30.0Z
From: Theo <tfruchtl@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Number Retrieval Argument Problem
Date: Mon, 7 Jul 2008 15:10:30 -0700 (PDT)
Organization: http://groups.google.com
Lines: 24
Message-ID: <f5e88882-3a50-4c0c-af9e-b58244cc22d1@z66g2000hsc.googlegroups.com>
NNTP-Posting-Host: 63.64.39.28
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1215468631 7680 127.0.0.1 (7 Jul 2008 22:10:31 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 7 Jul 2008 22:10:31 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: z66g2000hsc.googlegroups.com; posting-host=63.64.39.28; posting-account=vT_wsQoAAABzisCd6ZCWpoE4WeJXhMz0
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9) Gecko/2008052906 Firefox/3.0,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!z66g2000hsc.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87326
Article PK: 416583

Using Sybase Adaptive Server Anywhere Network Server Version
7.0.3.2046 and Powerbuilder 9.0.1.7066 on Windows XP SP2. I have a
table with 660631 records in it. I have suspected a problem with
using a Number retrieval argument in datawindows, so I constructed a
basic data window for testing. Here is the SQL it uses:
SELECT "transactions"."tran_num"
FROM "transactions"
WHERE ( "transactions"."tran_num" >= :start_tran ) and
( "transactions"."tran_num" <= :end_tran )
Even if I pass the same number for both start_tran and end_tran so
that it retrieves just one record, it still takes 25 seconds to
retrieve. After reading some posts on this newsgroup, I've tried
changing the WHERE clause to this:
WHERE ( "transactions"."tran_num" >= CAST(:start_tran as integer) )
and
( "transactions"."tran_num" <= CAST(:end_tran as integer) ),
and that does make a huge difference (5 seconds - restarted database
engine to clear cache too).
I just need to know if this is a known problem with the Powerbuilder
datawindow and the Number argument type and if it is, is it addressed/
fixed in a later build of PowerBuilder?

TIA,
Ted


Chris Keating (Sybase iAnywhere) Posted on 2008-07-08 01:03:12.0Z
From: "Chris Keating (Sybase iAnywhere)" <keating_spam_free@ianywhere.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Number Retrieval Argument Problem
References: <f5e88882-3a50-4c0c-af9e-b58244cc22d1@z66g2000hsc.googlegroups.com>
In-Reply-To: <f5e88882-3a50-4c0c-af9e-b58244cc22d1@z66g2000hsc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4872bcd0@forums-1-dub>
Date: 7 Jul 2008 18:03:12 -0700
X-Trace: forums-1-dub 1215478992 10.22.241.152 (7 Jul 2008 18:03:12 -0700)
X-Original-Trace: 7 Jul 2008 18:03:12 -0700, vip152.sybase.com
Lines: 54
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87327
Article PK: 416585

It is likely that the datawindow is binding the column as a double --
check a pb database trace to verify. In doing so, many databases will
find it difficult to take advantage of an index. The CAST() forces a
better datatype, an integer, but needs to be evaluated for each
potential row so it is not as cheap as natural integer.

Newer SQL Anywhere versions now evaluate bound values and if it appears
to be an integer will interpret and process it as such.

Is there a reason to store integers in a non integer type?
If you don't bind, the performance will be generally better. Can you
verify the impact of not binding>
--

Chris Keating

****************************************
Please only post to the newsgroup

SQL Anywhere Developer Community
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center
http://www.sybase.com/sqlanyblogs
Maintenance releases and EBFs
http://downloads.sybase.com/swx/sdmain.stm
Use Case Express to report bugs
http://case-express.sybase.com

Theo wrote:
> Using Sybase Adaptive Server Anywhere Network Server Version
> 7.0.3.2046 and Powerbuilder 9.0.1.7066 on Windows XP SP2. I have a
> table with 660631 records in it. I have suspected a problem with
> using a Number retrieval argument in datawindows, so I constructed a
> basic data window for testing. Here is the SQL it uses:
> SELECT "transactions"."tran_num"
> FROM "transactions"
> WHERE ( "transactions"."tran_num" >= :start_tran ) and
> ( "transactions"."tran_num" <= :end_tran )
> Even if I pass the same number for both start_tran and end_tran so
> that it retrieves just one record, it still takes 25 seconds to
> retrieve. After reading some posts on this newsgroup, I've tried
> changing the WHERE clause to this:
> WHERE ( "transactions"."tran_num" >= CAST(:start_tran as integer) )
> and
> ( "transactions"."tran_num" <= CAST(:end_tran as integer) ),
> and that does make a huge difference (5 seconds - restarted database
> engine to clear cache too).
> I just need to know if this is a known problem with the Powerbuilder
> datawindow and the Number argument type and if it is, is it addressed/
> fixed in a later build of PowerBuilder?
>
> TIA,
> Ted


Theo Posted on 2008-07-08 14:07:39.0Z
Sender: 5f04.4873718e.1804289383@sybase.com
From: Theo
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Number Retrieval Argument Problem
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <487374ab.5f6d.1681692777@sybase.com>
References: <4872bcd0@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Jul 2008 07:07:39 -0700
X-Trace: forums-1-dub 1215526059 10.22.241.41 (8 Jul 2008 07:07:39 -0700)
X-Original-Trace: 8 Jul 2008 07:07:39 -0700, 10.22.241.41
Lines: 97
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87336
Article PK: 416592

Chris,
Below are the results from a pb trace. The
"transactions"."tran_num" column is defined as an integer in
the database. Regarding "verify the impact of not binding",
do you mean to modify the StaticBind setting in the DBParm
parameter?

TIA,
Ted

(14f0008): LOGIN: (109 MilliSeconds)
(14f0008): CONNECT TO trace ODBC:
(14f0008):
DBPARM=ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'
,Connectstring='DBN=TEDSERV;DSN=TED;UID=DBA;PWD=SQL;'
(14f0008): SERVER=tednb (0 MilliSeconds)
(14f0008): PREPARE: (0 MilliSeconds)
(14f0008): PREPARE WITH BIND VARIABLES:
(14f0008): SELECT "transactions"."tran_num" FROM
"transactions" WHERE ( "transactions"."tran_num" >= ?)
and ( "transactions"."tran_num" <= ?)
(14f0008): FLOAT Length=0 ID=:start_tran
*662097.0000000000000*
(14f0008): FLOAT Length=0 ID=:end_tran
*662097.0000000000000* (281 MilliSeconds)
(14f0008): BIND SELECT OUTPUT BUFFER (DataWindow): (0
MilliSeconds)
(14f0008):
,len=44,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(14f0008): EXECUTE: (78 MilliSeconds)
(14f0008): FETCH NEXT: (21937 MilliSeconds)
(14f0008): COLUMN=662097
(14f0008): FETCH NEXT: (0 MilliSeconds)
(14f0008):
Error 1 (rc 100)
(14f0008): COMMIT: (0 MilliSeconds)
(14f0008): DISCONNECT: (0 MilliSeconds)
(14f0008): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

> It is likely that the datawindow is binding the column as
> a double -- check a pb database trace to verify. In doing
> so, many databases will find it difficult to take
> advantage of an index. The CAST() forces a better
> datatype, an integer, but needs to be evaluated for each
> potential row so it is not as cheap as natural integer.
>
> Newer SQL Anywhere versions now evaluate bound values and
> if it appears to be an integer will interpret and process
> it as such.
>
> Is there a reason to store integers in a non integer type?
> If you don't bind, the performance will be generally
> better. Can you verify the impact of not binding>
> --
>
> Chris Keating
>
> ****************************************
> Please only post to the newsgroup
>
> SQL Anywhere Developer Community
>
>
http://www.sybase.com/developer/library/sql-anywhere-techcorner
> SQL Anywhere Blog Center
> http://www.sybase.com/sqlanyblogs
> Maintenance releases and EBFs
> http://downloads.sybase.com/swx/sdmain.stm
> Use Case Express to report bugs
> http://case-express.sybase.com
>
>
> Theo wrote:
> > Using Sybase Adaptive Server Anywhere Network Server
> > Version 7.0.3.2046 and Powerbuilder 9.0.1.7066 on
> > Windows XP SP2. I have a table with 660631 records in
> > it. I have suspected a problem with using a Number
> > retrieval argument in datawindows, so I constructed a
> > basic data window for testing. Here is the SQL it uses:
> > SELECT "transactions"."tran_num" FROM "transactions"
> > WHERE ( "transactions"."tran_num" >= :start_tran ) and
> > ( "transactions"."tran_num" <= :end_tran )
> > Even if I pass the same number for both start_tran and
> > end_tran so that it retrieves just one record, it still
> > takes 25 seconds to retrieve. After reading some posts
> > on this newsgroup, I've tried changing the WHERE clause
> > to this: WHERE ( "transactions"."tran_num" >=
> > CAST(:start_tran as integer) ) and
> > ( "transactions"."tran_num" <= CAST(:end_tran as
> > integer) ), and that does make a huge difference (5
> > seconds - restarted database engine to clear cache too).
> > I just need to know if this is a known problem with the
> > Powerbuilder datawindow and the Number argument type and
> > if it is, is it addressed/ fixed in a later build of
> > PowerBuilder?
> > TIA,
> > Ted


Theo Posted on 2008-07-08 15:32:41.0Z
Sender: 5f04.4873718e.1804289383@sybase.com
From: Theo
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Number Retrieval Argument Problem
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48738899.6208.1681692777@sybase.com>
References: <4872bcd0@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Jul 2008 08:32:41 -0700
X-Trace: forums-1-dub 1215531161 10.22.241.41 (8 Jul 2008 08:32:41 -0700)
X-Original-Trace: 8 Jul 2008 08:32:41 -0700, 10.22.241.41
Lines: 101
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87338
Article PK: 416595

Chris,
Perhaps you meant adjusting the DisableBind parameter. I
tried modifying both the SaticBind and DisableBind parameter
separately. I don't see any impact regardess of these
settings. Below is a pb trace with DisableBind = 1:

(2544238): LOGIN: (141 MilliSeconds)
(2544238): CONNECT TO trace ODBC:
(2544238):
DBPARM=ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'
,Connectstring='DBN=TEDSERV;DSN=TED;UID=DBA;PWD=SQL;DisableBind=1;
'
(2544238): SERVER=tednb (0 MilliSeconds)
(2544238): PREPARE: (0 MilliSeconds)
(2544238): PREPARE WITH BIND VARIABLES:
(2544238): SELECT "transactions"."tran_num"
FROM "transactions"
WHERE ( "transactions"."tran_num" >= ? ) AND
( "transactions"."tran_num" <= ? )

(2544238): FLOAT Length=0 ID=:start_tran
*662097.0000000000000*
(2544238): FLOAT Length=0 ID=:end_tran
*662097.0000000000000* (296 MilliSeconds)
(2544238): BIND SELECT OUTPUT BUFFER (DataWindow): (0
MilliSeconds)
(2544238):
,len=44,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
(2544238): EXECUTE: (79 MilliSeconds)
(2544238): FETCH NEXT: (22109 MilliSeconds)
(2544238): COLUMN=662097
(2544238): FETCH NEXT: (0 MilliSeconds)
(2544238):
Error 1 (rc 100)
(2544238): COMMIT: (0 MilliSeconds)
(2544238): DISCONNECT: (16 MilliSeconds)
(2544238): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

Am I setting that parameter correctly?

TIA,
Ted

> It is likely that the datawindow is binding the column as
> a double -- check a pb database trace to verify. In doing
> so, many databases will find it difficult to take
> advantage of an index. The CAST() forces a better
> datatype, an integer, but needs to be evaluated for each
> potential row so it is not as cheap as natural integer.
>
> Newer SQL Anywhere versions now evaluate bound values and
> if it appears to be an integer will interpret and process
> it as such.
>
> Is there a reason to store integers in a non integer type?
> If you don't bind, the performance will be generally
> better. Can you verify the impact of not binding>
> --
>
> Chris Keating
>
> ****************************************
> Please only post to the newsgroup
>
> SQL Anywhere Developer Community
>
>
http://www.sybase.com/developer/library/sql-anywhere-techcorner
> SQL Anywhere Blog Center
> http://www.sybase.com/sqlanyblogs
> Maintenance releases and EBFs
> http://downloads.sybase.com/swx/sdmain.stm
> Use Case Express to report bugs
> http://case-express.sybase.com
>
>
> Theo wrote:
> > Using Sybase Adaptive Server Anywhere Network Server
> > Version 7.0.3.2046 and Powerbuilder 9.0.1.7066 on
> > Windows XP SP2. I have a table with 660631 records in
> > it. I have suspected a problem with using a Number
> > retrieval argument in datawindows, so I constructed a
> > basic data window for testing. Here is the SQL it uses:
> > SELECT "transactions"."tran_num" FROM "transactions"
> > WHERE ( "transactions"."tran_num" >= :start_tran ) and
> > ( "transactions"."tran_num" <= :end_tran )
> > Even if I pass the same number for both start_tran and
> > end_tran so that it retrieves just one record, it still
> > takes 25 seconds to retrieve. After reading some posts
> > on this newsgroup, I've tried changing the WHERE clause
> > to this: WHERE ( "transactions"."tran_num" >=
> > CAST(:start_tran as integer) ) and
> > ( "transactions"."tran_num" <= CAST(:end_tran as
> > integer) ), and that does make a huge difference (5
> > seconds - restarted database engine to clear cache too).
> > I just need to know if this is a known problem with the
> > Powerbuilder datawindow and the Number argument type and
> > if it is, is it addressed/ fixed in a later build of
> > PowerBuilder?
> > TIA,
> > Ted


Tyler Cruse Posted on 2008-07-08 21:42:56.0Z
Reply-To: "Tyler Cruse" <tcruse@trlx.com>
From: "Tyler Cruse" <tcruse@trlx.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4872bcd0@forums-1-dub> <48738899.6208.1681692777@sybase.com>
In-Reply-To: <48738899.6208.1681692777@sybase.com>
Subject: Re: Number Retrieval Argument Problem
Lines: 107
Organization: Trellix Engineering
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4873df60$1@forums-1-dub>
Date: 8 Jul 2008 14:42:56 -0700
X-Trace: forums-1-dub 1215553376 10.22.241.152 (8 Jul 2008 14:42:56 -0700)
X-Original-Trace: 8 Jul 2008 14:42:56 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87342
Article PK: 416600

Using stored procedures instead of Select statment should also resolve the
issue as well as improve performance in other ways.

"Theo" wrote in message news:48738899.6208.1681692777@sybase.com...
> Chris,
> Perhaps you meant adjusting the DisableBind parameter. I
> tried modifying both the SaticBind and DisableBind parameter
> separately. I don't see any impact regardess of these
> settings. Below is a pb trace with DisableBind = 1:
>
> (2544238): LOGIN: (141 MilliSeconds)
> (2544238): CONNECT TO trace ODBC:
> (2544238):
> DBPARM=ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'
> ,Connectstring='DBN=TEDSERV;DSN=TED;UID=DBA;PWD=SQL;DisableBind=1;
> '
> (2544238): SERVER=tednb (0 MilliSeconds)
> (2544238): PREPARE: (0 MilliSeconds)
> (2544238): PREPARE WITH BIND VARIABLES:
> (2544238): SELECT "transactions"."tran_num"
> FROM "transactions"
> WHERE ( "transactions"."tran_num" >= ? ) AND
> ( "transactions"."tran_num" <= ? )
>
> (2544238): FLOAT Length=0 ID=:start_tran
> *662097.0000000000000*
> (2544238): FLOAT Length=0 ID=:end_tran
> *662097.0000000000000* (296 MilliSeconds)
> (2544238): BIND SELECT OUTPUT BUFFER (DataWindow): (0
> MilliSeconds)
> (2544238):
> ,len=44,type=LONG,pbt=22,dbt=0,ct=0,prec=0,scale=0
> (2544238): EXECUTE: (79 MilliSeconds)
> (2544238): FETCH NEXT: (22109 MilliSeconds)
> (2544238): COLUMN=662097
> (2544238): FETCH NEXT: (0 MilliSeconds)
> (2544238):
> Error 1 (rc 100)
> (2544238): COMMIT: (0 MilliSeconds)
> (2544238): DISCONNECT: (16 MilliSeconds)
> (2544238): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)
>
> Am I setting that parameter correctly?
>
> TIA,
> Ted
>
>> It is likely that the datawindow is binding the column as
>> a double -- check a pb database trace to verify. In doing
>> so, many databases will find it difficult to take
>> advantage of an index. The CAST() forces a better
>> datatype, an integer, but needs to be evaluated for each
>> potential row so it is not as cheap as natural integer.
>>
>> Newer SQL Anywhere versions now evaluate bound values and
>> if it appears to be an integer will interpret and process
>> it as such.
>>
>> Is there a reason to store integers in a non integer type?
>> If you don't bind, the performance will be generally
>> better. Can you verify the impact of not binding>
>> --
>>
>> Chris Keating
>>
>> ****************************************
>> Please only post to the newsgroup
>>
>> SQL Anywhere Developer Community
>>
>>
> http://www.sybase.com/developer/library/sql-anywhere-techcorner
>> SQL Anywhere Blog Center
>> http://www.sybase.com/sqlanyblogs
>> Maintenance releases and EBFs
>> http://downloads.sybase.com/swx/sdmain.stm
>> Use Case Express to report bugs
>> http://case-express.sybase.com
>>
>>
>> Theo wrote:
>> > Using Sybase Adaptive Server Anywhere Network Server
>> > Version 7.0.3.2046 and Powerbuilder 9.0.1.7066 on
>> > Windows XP SP2. I have a table with 660631 records in
>> > it. I have suspected a problem with using a Number
>> > retrieval argument in datawindows, so I constructed a
>> > basic data window for testing. Here is the SQL it uses:
>> > SELECT "transactions"."tran_num" FROM "transactions"
>> > WHERE ( "transactions"."tran_num" >= :start_tran ) and
>> > ( "transactions"."tran_num" <= :end_tran )
>> > Even if I pass the same number for both start_tran and
>> > end_tran so that it retrieves just one record, it still
>> > takes 25 seconds to retrieve. After reading some posts
>> > on this newsgroup, I've tried changing the WHERE clause
>> > to this: WHERE ( "transactions"."tran_num" >=
>> > CAST(:start_tran as integer) ) and
>> > ( "transactions"."tran_num" <= CAST(:end_tran as
>> > integer) ), and that does make a huge difference (5
>> > seconds - restarted database engine to clear cache too).
>> > I just need to know if this is a known problem with the
>> > Powerbuilder datawindow and the Number argument type and
>> > if it is, is it addressed/ fixed in a later build of
>> > PowerBuilder?
>> > TIA,
>> > Ted