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.

set showplan very slow

6 posts in General Discussion Last posting was on 2008-01-18 20:34:08.0Z
Pieter Coene Posted on 2008-01-15 08:12:02.0Z
Sender: 24e4.478b3692.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.workspace.general
Subject: set showplan very slow
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <478c6ad2.3fd3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jan 2008 00:12:02 -0800
X-Trace: forums-1-dub 1200384722 10.22.241.41 (15 Jan 2008 00:12:02 -0800)
X-Original-Trace: 15 Jan 2008 00:12:02 -0800, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:562
Article PK: 1088248

Hi,

when executing 'set showplan on' before a stored procedure,
we get the showplan of it. We have some large stored
procedures, and in WS 2.0, this takes several minutes (5
minutes) to complete the showplan. If i execute the same
statements in DBISQL, it takes only seconds to give the the
result.

Is this known?

Greetz
Pieter


Samir Nigam Posted on 2008-01-15 17:53:34.0Z
From: "Samir Nigam" <samir_dot_nigam@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <478c6ad2.3fd3.1681692777@sybase.com>
Subject: Re: set showplan very slow
Lines: 25
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: snigamxp.sybase.com
X-Original-NNTP-Posting-Host: snigamxp.sybase.com
Message-ID: <478cf31e$1@forums-1-dub>
Date: 15 Jan 2008 09:53:34 -0800
X-Trace: forums-1-dub 1200419614 10.22.85.206 (15 Jan 2008 09:53:34 -0800)
X-Original-Trace: 15 Jan 2008 09:53:34 -0800, snigamxp.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:563
Article PK: 1088249

Pieter,

Sorry to hear that. Would it be possible to send us the stored procedure or
help us with details to set an environment that can reproduce the problem.
We can then address it asap.

Regards,
Samir

<Pieter Coene> wrote in message news:478c6ad2.3fd3.1681692777@sybase.com...
> Hi,
>
> when executing 'set showplan on' before a stored procedure,
> we get the showplan of it. We have some large stored
> procedures, and in WS 2.0, this takes several minutes (5
> minutes) to complete the showplan. If i execute the same
> statements in DBISQL, it takes only seconds to give the the
> result.
>
> Is this known?
>
> Greetz
> Pieter


Pieter Coene Posted on 2008-01-16 08:20:12.0Z
Sender: 24e4.478b3692.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.workspace.general
Subject: Re: set showplan very slow
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <478dbe3c.6269.1681692777@sybase.com>
References: <478cf31e$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Jan 2008 00:20:12 -0800
X-Trace: forums-1-dub 1200471612 10.22.241.41 (16 Jan 2008 00:20:12 -0800)
X-Original-Trace: 16 Jan 2008 00:20:12 -0800, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:564
Article PK: 1088250

Hi Samir,

its any (long) stored procedure we have. The first 20
seconds is see the result of the showplan scrolling down,
but after that every couple of seconds i get a little pauze,
which slows the process down. Its like some buffer is full
to write in the SQL results...

If you want to test it, run sp_help <table> with showplan
on. It takes minutes to complete (comparing to seconds in
DBISQL).

Greetz
Pieter

> Pieter,
>
> Sorry to hear that. Would it be possible to send us the
> stored procedure or help us with details to set an
> environment that can reproduce the problem. We can then
> address it asap.
>
> Regards,
> Samir
>
> <Pieter Coene> wrote in message
> > news:478c6ad2.3fd3.1681692777@sybase.com... Hi,
> >
> > when executing 'set showplan on' before a stored
> > procedure, we get the showplan of it. We have some large
> > stored procedures, and in WS 2.0, this takes several
> > minutes (5 minutes) to complete the showplan. If i
> > execute the same statements in DBISQL, it takes only
> > seconds to give the the result.
> >
> > Is this known?
> >
> > Greetz
> > Pieter
>
>


Mike Crocker Posted on 2008-01-16 18:16:05.0Z
From: "Mike Crocker" <crocker@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <478cf31e$1@forums-1-dub> <478dbe3c.6269.1681692777@sybase.com>
Subject: Re: set showplan very slow
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: crockerxp.sybase.com
X-Original-NNTP-Posting-Host: crockerxp.sybase.com
Message-ID: <478e49e5$1@forums-1-dub>
Date: 16 Jan 2008 10:16:05 -0800
X-Trace: forums-1-dub 1200507365 10.24.112.185 (16 Jan 2008 10:16:05 -0800)
X-Original-Trace: 16 Jan 2008 10:16:05 -0800, crockerxp.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:565
Article PK: 1088251

Pieter,

I have recreated the problem and opened CR 489084.

Do you notice any differences if you have the SQL Results View displayed as
text and/or in a single tab?

For me the first time was slow, but then it was quicker after that. Did you
see this?

thanks,
mike

<Pieter Coene> wrote in message news:478dbe3c.6269.1681692777@sybase.com...
> Hi Samir,
>
> its any (long) stored procedure we have. The first 20
> seconds is see the result of the showplan scrolling down,
> but after that every couple of seconds i get a little pauze,
> which slows the process down. Its like some buffer is full
> to write in the SQL results...
>
> If you want to test it, run sp_help <table> with showplan
> on. It takes minutes to complete (comparing to seconds in
> DBISQL).
>
> Greetz
> Pieter
>
>> Pieter,
>>
>> Sorry to hear that. Would it be possible to send us the
>> stored procedure or help us with details to set an
>> environment that can reproduce the problem. We can then
>> address it asap.
>>
>> Regards,
>> Samir
>>
>> <Pieter Coene> wrote in message
>> > news:478c6ad2.3fd3.1681692777@sybase.com... Hi,
>> >
>> > when executing 'set showplan on' before a stored
>> > procedure, we get the showplan of it. We have some large
>> > stored procedures, and in WS 2.0, this takes several
>> > minutes (5 minutes) to complete the showplan. If i
>> > execute the same statements in DBISQL, it takes only
>> > seconds to give the the result.
>> >
>> > Is this known?
>> >
>> > Greetz
>> > Pieter
>>
>>


Pieter Coene Posted on 2008-01-17 08:49:38.0Z
Sender: 24e4.478b3692.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.workspace.general
Subject: Re: set showplan very slow
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <478f16a2.51f.1681692777@sybase.com>
References: <478e49e5$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Jan 2008 00:49:38 -0800
X-Trace: forums-1-dub 1200559778 10.22.241.41 (17 Jan 2008 00:49:38 -0800)
X-Original-Trace: 17 Jan 2008 00:49:38 -0800, 10.22.241.41
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:566
Article PK: 1088252

Hi Mike,

for sp_help, the quickest mode is Grid mode with multible
tabs. Maybe because the different resultsets (10 in total)
keeps the results smaller. I notice that the first seconds
of a result is always fast, but then it slows down...

Greetz
Pieter

> Pieter,
>
> I have recreated the problem and opened CR 489084.
>
> Do you notice any differences if you have the SQL Results
> View displayed as text and/or in a single tab?
>
> For me the first time was slow, but then it was quicker
> after that. Did you see this?
>
> thanks,
> mike
>
> <Pieter Coene> wrote in message
> > news:478dbe3c.6269.1681692777@sybase.com... Hi Samir,
> >
> > its any (long) stored procedure we have. The first 20
> > seconds is see the result of the showplan scrolling down
> > , but after that every couple of seconds i get a little
> > pauze, which slows the process down. Its like some
> > buffer is full to write in the SQL results...
> >
> > If you want to test it, run sp_help <table> with
> > showplan on. It takes minutes to complete (comparing to
> > seconds in DBISQL).
> >
> > Greetz
> > Pieter
> >
> >> Pieter,
> >>
> >> Sorry to hear that. Would it be possible to send us the
> >> stored procedure or help us with details to set an
> >> environment that can reproduce the problem. We can
> then >> address it asap.
> >>
> >> Regards,
> >> Samir
> >>
> >> <Pieter Coene> wrote in message
> >> > news:478c6ad2.3fd3.1681692777@sybase.com... Hi,
> >> >
> >> > when executing 'set showplan on' before a stored
> >> > procedure, we get the showplan of it. We have some
> large >> > stored procedures, and in WS 2.0, this takes
> several >> > minutes (5 minutes) to complete the showplan.
> If i >> > execute the same statements in DBISQL, it takes
> only >> > seconds to give the the result.
> >> >
> >> > Is this known?
> >> >
> >> > Greetz
> >> > Pieter
> >>
> >>
>
>


Mike Crocker Posted on 2008-01-18 20:34:08.0Z
From: "Mike Crocker" <crocker@sybase.com>
Newsgroups: sybase.public.workspace.general
References: <478e49e5$1@forums-1-dub> <478f16a2.51f.1681692777@sybase.com>
Subject: Re: set showplan very slow
Lines: 73
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vpneast-client-032.sybase.com
X-Original-NNTP-Posting-Host: vpneast-client-032.sybase.com
Message-ID: <47910d40$1@forums-1-dub>
Date: 18 Jan 2008 12:34:08 -0800
X-Trace: forums-1-dub 1200688448 10.18.242.39 (18 Jan 2008 12:34:08 -0800)
X-Original-Trace: 18 Jan 2008 12:34:08 -0800, vpneast-client-032.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.workspace.general:567
Article PK: 1088255

thanks for the feedback.

<Pieter Coene> wrote in message news:478f16a2.51f.1681692777@sybase.com...
> Hi Mike,
>
> for sp_help, the quickest mode is Grid mode with multible
> tabs. Maybe because the different resultsets (10 in total)
> keeps the results smaller. I notice that the first seconds
> of a result is always fast, but then it slows down...
>
> Greetz
> Pieter
>
>
>> Pieter,
>>
>> I have recreated the problem and opened CR 489084.
>>
>> Do you notice any differences if you have the SQL Results
>> View displayed as text and/or in a single tab?
>>
>> For me the first time was slow, but then it was quicker
>> after that. Did you see this?
>>
>> thanks,
>> mike
>>
>> <Pieter Coene> wrote in message
>> > news:478dbe3c.6269.1681692777@sybase.com... Hi Samir,
>> >
>> > its any (long) stored procedure we have. The first 20
>> > seconds is see the result of the showplan scrolling down
>> > , but after that every couple of seconds i get a little
>> > pauze, which slows the process down. Its like some
>> > buffer is full to write in the SQL results...
>> >
>> > If you want to test it, run sp_help <table> with
>> > showplan on. It takes minutes to complete (comparing to
>> > seconds in DBISQL).
>> >
>> > Greetz
>> > Pieter
>> >
>> >> Pieter,
>> >>
>> >> Sorry to hear that. Would it be possible to send us the
>> >> stored procedure or help us with details to set an
>> >> environment that can reproduce the problem. We can
>> then >> address it asap.
>> >>
>> >> Regards,
>> >> Samir
>> >>
>> >> <Pieter Coene> wrote in message
>> >> > news:478c6ad2.3fd3.1681692777@sybase.com... Hi,
>> >> >
>> >> > when executing 'set showplan on' before a stored
>> >> > procedure, we get the showplan of it. We have some
>> large >> > stored procedures, and in WS 2.0, this takes
>> several >> > minutes (5 minutes) to complete the showplan.
>> If i >> > execute the same statements in DBISQL, it takes
>> only >> > seconds to give the the result.
>> >> >
>> >> > Is this known?
>> >> >
>> >> > Greetz
>> >> > Pieter
>> >>
>> >>
>>
>>