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.

DW Retrieval Argument Performance

6 posts in DataWindow Last posting was on 2008-10-31 15:44:59.0Z
Chris Griffin Posted on 2008-10-30 00:27:26.0Z
Reply-To: "Chris Griffin" <cgriffin@dsvusa.com>
From: "Chris Griffin" <cgriffin@dsvusa.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: DW Retrieval Argument Performance
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4908ff6e@forums-1-dub>
Date: 29 Oct 2008 16:27:26 -0800
X-Trace: forums-1-dub 1225326446 10.22.241.152 (29 Oct 2008 16:27:26 -0800)
X-Original-Trace: 29 Oct 2008 16:27:26 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88078
Article PK: 417331

I have a table with a few hundred thousand rows in it. I've noticed that if
I create a DW without any retrieval arguments and hard code in the argument
value the DW performs well. However, as soon as I use a retrieval argument,
allow the DW to prompt me for the value or use that DW in my code, the
performance of the DW goes horribly bad, meaning upwards of 20 minutes to
get data, whereas seconds when not using a Retrieval Argument. Has anyone
else seen this or have a possible solution?

Any help here would be appreciated.

Thanks,
Chris


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-10-30 01:43:52.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4908ff6e@forums-1-dub>
Subject: Re: DW Retrieval Argument Performance
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49091158@forums-1-dub>
Date: 29 Oct 2008 17:43:52 -0800
X-Trace: forums-1-dub 1225331032 10.22.241.152 (29 Oct 2008 17:43:52 -0800)
X-Original-Trace: 29 Oct 2008 17:43:52 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88079
Article PK: 417332

That sounds like an indexed retrieval versus a full table scan. That
suggests that the way the DW is formatting the retrieval argument is keeping
the DBMS from recognizing that it can use an index.
It's pretty easy to change the SQL in a DW to add a Where clause, so I'd
suggest doing that. See the example code in the help for Modify.
Without seeing the SQL and the table definition, or even knowing which DBMS
it's pretty hard to give any other advice.

"Chris Griffin" <cgriffin@dsvusa.com> wrote in message
news:4908ff6e@forums-1-dub...
>I have a table with a few hundred thousand rows in it. I've noticed that
>if I create a DW without any retrieval arguments and hard code in the
>argument value the DW performs well. However, as soon as I use a retrieval
>argument, allow the DW to prompt me for the value or use that DW in my
>code, the performance of the DW goes horribly bad, meaning upwards of 20
>minutes to get data, whereas seconds when not using a Retrieval Argument.
>Has anyone else seen this or have a possible solution?
>
> Any help here would be appreciated.
>
> Thanks,
> Chris
>
>


Chris Griffin Posted on 2008-10-30 16:32:14.0Z
Reply-To: "Chris Griffin" <cgriffin@dsvusa.com>
From: "Chris Griffin" <cgriffin@dsvusa.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4908ff6e@forums-1-dub> <49091158@forums-1-dub>
Subject: Re: DW Retrieval Argument Performance
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4909e18e@forums-1-dub>
Date: 30 Oct 2008 08:32:14 -0800
X-Trace: forums-1-dub 1225384334 10.22.241.152 (30 Oct 2008 08:32:14 -0800)
X-Original-Trace: 30 Oct 2008 08:32:14 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88082
Article PK: 417334

That's the path I've taken, using GetSQLSelect and Modifying the DW SQL
Where clause. I didn't want to have to go evaluate most of my other DWs to
see where this approach needs to be taken. If this is the only solution, I
can go with it, was just hoping for some "magic" answer with the DW
retrieval arguments.

Thanks.

"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com> wrote in message
news:49091158@forums-1-dub...
> That sounds like an indexed retrieval versus a full table scan. That
> suggests that the way the DW is formatting the retrieval argument is
> keeping the DBMS from recognizing that it can use an index.
> It's pretty easy to change the SQL in a DW to add a Where clause, so I'd
> suggest doing that. See the example code in the help for Modify.
> Without seeing the SQL and the table definition, or even knowing which
> DBMS it's pretty hard to give any other advice.
>
> "Chris Griffin" <cgriffin@dsvusa.com> wrote in message
> news:4908ff6e@forums-1-dub...
>>I have a table with a few hundred thousand rows in it. I've noticed that
>>if I create a DW without any retrieval arguments and hard code in the
>>argument value the DW performs well. However, as soon as I use a
>>retrieval argument, allow the DW to prompt me for the value or use that DW
>>in my code, the performance of the DW goes horribly bad, meaning upwards
>>of 20 minutes to get data, whereas seconds when not using a Retrieval
>>Argument. Has anyone else seen this or have a possible solution?
>>
>> Any help here would be appreciated.
>>
>> Thanks,
>> Chris
>>
>>
>
>


Scott Morris Posted on 2008-10-30 17:02:23.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4908ff6e@forums-1-dub> <49091158@forums-1-dub> <4909e18e@forums-1-dub>
Subject: Re: DW Retrieval Argument Performance
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4909e89f@forums-1-dub>
Date: 30 Oct 2008 09:02:23 -0800
X-Trace: forums-1-dub 1225386143 10.22.241.152 (30 Oct 2008 09:02:23 -0800)
X-Original-Trace: 30 Oct 2008 09:02:23 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88083
Article PK: 417336


"Chris Griffin" <cgriffin@dsvusa.com> wrote in message
news:4909e18e@forums-1-dub...
> That's the path I've taken, using GetSQLSelect and Modifying the DW SQL
> Where clause. I didn't want to have to go evaluate most of my other DWs
> to see where this approach needs to be taken. If this is the only
> solution, I can go with it, was just hoping for some "magic" answer with
> the DW retrieval arguments.
>
> Thanks.

I would look at the actual query generated by PB (as executed within the
database engine). Perhaps there is some sort of datatype conversion issue
that causes the engine to not use an index when it otherwise would. Looking
at the execution plans may also other some clues.


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-10-30 17:04:23.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4908ff6e@forums-1-dub> <49091158@forums-1-dub> <4909e18e@forums-1-dub>
Subject: Re: DW Retrieval Argument Performance
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4909e917@forums-1-dub>
Date: 30 Oct 2008 09:04:23 -0800
X-Trace: forums-1-dub 1225386263 10.22.241.152 (30 Oct 2008 09:04:23 -0800)
X-Original-Trace: 30 Oct 2008 09:04:23 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88084
Article PK: 417339

One other idea that comes to mind is that if a column definition has changed
the DW might still have the old one, causing a mismatch. Try opening a DW,
make it think the SQL has changed (drag column order and drag it back, or in
syntax mode space and backspace) and save it. That will cause the column
definitions to be rechecked against the database.

"Chris Griffin" <cgriffin@dsvusa.com> wrote in message
news:4909e18e@forums-1-dub...
> That's the path I've taken, using GetSQLSelect and Modifying the DW SQL
> Where clause. I didn't want to have to go evaluate most of my other DWs
> to see where this approach needs to be taken. If this is the only
> solution, I can go with it, was just hoping for some "magic" answer with
> the DW retrieval arguments.
>
> Thanks.
>
> "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com> wrote in message
> news:49091158@forums-1-dub...
>> That sounds like an indexed retrieval versus a full table scan. That
>> suggests that the way the DW is formatting the retrieval argument is
>> keeping the DBMS from recognizing that it can use an index.
>> It's pretty easy to change the SQL in a DW to add a Where clause, so I'd
>> suggest doing that. See the example code in the help for Modify.
>> Without seeing the SQL and the table definition, or even knowing which
>> DBMS it's pretty hard to give any other advice.
>>
>> "Chris Griffin" <cgriffin@dsvusa.com> wrote in message
>> news:4908ff6e@forums-1-dub...
>>>I have a table with a few hundred thousand rows in it. I've noticed that
>>>if I create a DW without any retrieval arguments and hard code in the
>>>argument value the DW performs well. However, as soon as I use a
>>>retrieval argument, allow the DW to prompt me for the value or use that
>>>DW in my code, the performance of the DW goes horribly bad, meaning
>>>upwards of 20 minutes to get data, whereas seconds when not using a
>>>Retrieval Argument. Has anyone else seen this or have a possible
>>>solution?
>>>
>>> Any help here would be appreciated.
>>>
>>> Thanks,
>>> Chris
>>>
>>>
>>
>>
>
>


M. Searer Posted on 2008-10-31 15:44:59.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4908ff6e@forums-1-dub> <49091158@forums-1-dub> <4909e18e@forums-1-dub>
In-Reply-To: <4909e18e@forums-1-dub>
Subject: Re: DW Retrieval Argument Performance
Lines: 38
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
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.18049
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <490b27fb$1@forums-1-dub>
Date: 31 Oct 2008 07:44:59 -0800
X-Trace: forums-1-dub 1225467899 10.22.241.152 (31 Oct 2008 07:44:59 -0800)
X-Original-Trace: 31 Oct 2008 07:44:59 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88087
Article PK: 417342

Is that as fast as the full table retrieval?
The reason I ask is that it sounds like you might have retrieve as needed turned on in your dw.
That would retrieve back only a relatively small number of rows vs the entire 100K + rows; which would be relatively fast.
Using an argument could cause a table scan which means the database waits until it has your entire result set before returning any
part that.

"Chris Griffin" <cgriffin@dsvusa.com> wrote in message news:4909e18e@forums-1-dub...
> That's the path I've taken, using GetSQLSelect and Modifying the DW SQL Where clause. I didn't want to have to go evaluate most
> of my other DWs to see where this approach needs to be taken. If this is the only solution, I can go with it, was just hoping for
> some "magic" answer with the DW retrieval arguments.
>
> Thanks.
>
> "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com> wrote in message news:49091158@forums-1-dub...
>> That sounds like an indexed retrieval versus a full table scan. That suggests that the way the DW is formatting the retrieval
>> argument is keeping the DBMS from recognizing that it can use an index.
>> It's pretty easy to change the SQL in a DW to add a Where clause, so I'd suggest doing that. See the example code in the help for
>> Modify.
>> Without seeing the SQL and the table definition, or even knowing which DBMS it's pretty hard to give any other advice.
>>
>> "Chris Griffin" <cgriffin@dsvusa.com> wrote in message news:4908ff6e@forums-1-dub...
>>>I have a table with a few hundred thousand rows in it. I've noticed that if I create a DW without any retrieval arguments and
>>>hard code in the argument value the DW performs well. However, as soon as I use a retrieval argument, allow the DW to prompt me
>>>for the value or use that DW in my code, the performance of the DW goes horribly bad, meaning upwards of 20 minutes to get data,
>>>whereas seconds when not using a Retrieval Argument. Has anyone else seen this or have a possible solution?
>>>
>>> Any help here would be appreciated.
>>>
>>> Thanks,
>>> Chris
>>>
>>>
>>
>>
>
>