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.

Slow running select

2 posts in General Discussion Last posting was on 2009-08-22 14:47:26.0Z
Anna Posted on 2009-08-21 21:34:08.0Z
Sender: 4e70.4a8f11f4.1804289383@sybase.com
From: Anna
Newsgroups: sybase.public.ase.general
Subject: Slow running select
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a8f12d0.4e96.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Aug 2009 14:34:08 -0700
X-Trace: forums-1-dub 1250890448 10.22.241.41 (21 Aug 2009 14:34:08 -0700)
X-Original-Trace: 21 Aug 2009 14:34:08 -0700, 10.22.241.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28202
Article PK: 77445

Hi! we have a sql that selects columns from two tables with
direct join that has been running very slow for certain
group of data. We've done update statistics and dbcc on the
tables and we didn't get any errors and all the indexes are
correct.

Is there a tool I can run to pinpoint what it is that's
causing the slowdown?

Thanks!


"Mark A. Parsons" <iron_horse Posted on 2009-08-22 14:47:26.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Slow running select
References: <4a8f12d0.4e96.1681692777@sybase.com>
In-Reply-To: <4a8f12d0.4e96.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090821-0, 08/21/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a9004fe$1@forums-1-dub>
Date: 22 Aug 2009 07:47:26 -0700
X-Trace: forums-1-dub 1250952446 10.22.241.152 (22 Aug 2009 07:47:26 -0700)
X-Original-Trace: 22 Aug 2009 07:47:26 -0700, vip152.sybase.com
Lines: 137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28204
Article PK: 77444

re: helpful tools ...

1 - There are a handful of 3rd party application$ that can be configured to collect the information you'll need to tune
your query ... though most (all?) provide little (if any) info in the form of suggestions on how to fix/improve your query.

2 - Sybase ASE 15.0.3 has a new utility called QPTune which collects a bunch of performance stats and then attempts to
make some limited suggestions on how to fix/improve your query.

Each of the above is built on top of monitoring capabilities that you already have access to ... these (above) options
simply cut down on the work in configuring/capturing various types of performance statistics. The parts about
'understanding' the statistics and turning this info into 'recommendations' to improve your query ... that pretty much
still relies on the operator.

So, how does the operator figure out how to turn performance metrics info into useful (re)coding recommendations? Take
some P&T training classes, study the manuals, research various public data sources (eg, newsgroups) for examples, work
with folks who already have the experience you're looking for, and the good ol' on-the-job-training/trial-by-fire method.

"Duh, Mark!" ?

--------------

For your particular situation we (the newsgroup) have very little info (performance metrics) about your problematic
query. Without knowing your level of experience and/or what you've been able to ascertain to date, a few basic
questions/suggestions for capturing some performance stats that may help in deriving a solution:

1 - your version of ASE? (select @@version)

2 - if running ASE 15.x, do you have statement cache enabled? eg,

exec sp_configure 'statement cache'
exec sp_configure 'literal'

3 - you mention that the query runs slow for certain groups of data; it would be helpful to get a description of the
data differences, to include sample queries of 'fast' and 'slow' instances; any info you have about the actual data
would also be helpful (eg, "85% of the entity_id column = 1, 10% of the entity_id column = 2, other 5% of entity_id
column values range between 3 and 50")

4 - is the original query run as a stand alone query or is it part of a stored proc (or trigger)?

5 - if you're running with ASE 12.5.0.3 (or better) I'd recommend you setup/configure the MDA tables; speak with your
DBA about this if you're unsure

6 - provide details on each of the 2 tables in question, eg,

exec sp_help <table_name>
exec sp_spaceused <table_name>,1

7 - Assuming we're talking about a stand alone query (ie, the problematic query is *not* part of a stored proc or
trigger): for both the 'fast' and 'slow' query run the following, making sure to capture all results to an output file
(eg, put the following in a SQL script file and then submit to the dataserver with the isql utility using the '-i', '-o'
and '-e' options):


NOTE: This harness may be invalid if your problematic query is part of a stored proc or trigger.


================== test_harness.{slow|fast}.sql
-- capture initial view of MDA performance
-- stats for this session

select * from master..monProcessActivity where SPID = @@spid
select * from master..monProcessWaits where SPID = @@spid
go

select "TimeStamp - Begin" = convert(varchar,getdate(),109)
go

-- enable query plan generation and capture of
-- io statistics for all follow-on queries

set showplan on
set statistics io on
go

-- insert query ('fast' or 'slow', but not both
-- at the same time); you'll need to run this
-- entire harness once for the 'fast' query, and
-- once for the 'slow' query

select ...

go

-- disable query plan generation and capture of
-- io statistics

set showplan off
set statistics io off
go

select "TimeStamp - End" = convert(varchar,getdate(),109)
go

-- capture MDA performance stats after main query
-- has completed; taking difference between these
-- and the previous MDA stats will give us the total
-- stats for the query in question; a more thorough
-- design would calculate the stats for us, but we'll
-- just eye-ball them for now

select * from master..monProcessActivity where SPID = @@spid
select * from master..monProcessWaits where SPID = @@spid
go

==================

Assuming you have 2 files at this point (test_harness.fast.sql, test_harness.slow.sql), you can submit these scripts to
the dataserver like such:

isql -U<login> -P<password> -S<dataserver> -i test_harness.slow.sql -e -o test_harness.slow.out
isql -U<login> -P<password> -S<dataserver> -i test_harness.fast.sql -e -o test_harness.fast.out

While there may be a need to collect additional info, this first pass should give you a good idea of where to
concentrate your effort.

If you're not sure how to use all of this info then I'd suggest you get your DBA to assist you (assuming the DBA has
tuning experience), bring in someone (in-house developer with tuning experience, outside party with tuning experience)
to help tune your query, or post the results back to this newsgroup to see if there's anyone here that can provide some
pointers.

If you post back here to the newsgroup I'd suggest that you post the results of the 2 SQL batch runs (ie, the *out
files) as file attachments. [file attachments are typically much, Much, MUCH easier to read as opposed to cut-n-pasting
the results into a the body of a newsgroup posting]

Anna wrote:
> Hi! we have a sql that selects columns from two tables with
> direct join that has been running very slow for certain
> group of data. We've done update statistics and dbcc on the
> tables and we didn't get any errors and all the indexes are
> correct.
>
> Is there a tool I can run to pinpoint what it is that's
> causing the slowdown?
>
> Thanks!