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.

large query

3 posts in Windows NT Last posting was on 2000-05-24 07:25:51.0Z
joan caceres Posted on 2000-05-23 13:47:32.0Z
Reply-To: "joan caceres" <joanc@else.com.pe>
From: "joan caceres" <joanc@else.com.pe>
Subject: large query
Date: Tue, 23 May 2000 08:47:32 -0500
Lines: 15
Organization: ELSE
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Message-ID: <vp05L6Lx$GA.261@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 200.48.162.5
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2122
Article PK: 1089529

Hi,

I have a problem with a table of 13 million records and i do a query upon
this table, the query response time is about 1 hour with certain conditions,
i want to know how can i speed the response of the query.

is there any tool that let me calculate the needs of hardware of the
server(s) with the data of the number of records in the database, number of
simultaneous querys and tpm.

thanks in advance

Joan


"Mark A. Parsons" <pegasys Posted on 2000-05-24 07:25:51.0Z
Message-ID: <392B83FE.43ED6D59@_internet.co.nz>
Date: Wed, 24 May 2000 19:25:51 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: large query
References: <vp05L6Lx$GA.261@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 38
NNTP-Posting-Host: p506.ipa1-n8-16.iconz.net.nz 210.48.25.250
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2119
Article PK: 1089524

In terms of 'speeding' up your query ... you'll want to take a gander at the
results of running 'set showplan on' for your query. If you find that you're
doing table scans then you'll want to look at your query and see if it's using
the indices on your table. If it's not using the desired index you'll want to
check and make sure the stats (update statistics) for the table are up to date.
If you don't have the necessary index in place you'll either need to re-write
your query to use a current index, or add an index.

This is a minimum 'basic' process to tuning a query ... depending on the results
of the above process you could move in several different directions in terms of
tuning your query. (Look at how big the Performance and Tuning - P&T - manual
gets each time a new release comes out ... there's a *LOT* of ways to tune
queries, systems, applications, etc.)

There are a few ways to learn more about tuning ... 1) read the P&T manual, 2)
take a course in Sybase P&T, 3) find a good book (sorry, I don't have any
recommendations here), 4) work with an experienced P&T DBA (either already on
your team or brought in on contract for tutoring/training and actual P&T work),
or 5) work with the folks here in the forum (realizing that this forum is made
up mostly of volunteer folks so you may not always get an immediate response to
your posts).

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz


Peter Veilleux Posted on 2000-05-23 14:42:47.0Z
Message-ID: <392A98E6.48920342@nvestservices.com>
Date: Tue, 23 May 2000 10:42:47 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: large query
References: <vp05L6Lx$GA.261@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 28
NNTP-Posting-Host: nefclient166.mutualfunds.com 206.136.227.166
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2121
Article PK: 1089527

Joan,

lots of factors here:

1. which version of the server are you on?
2. are there variables in the query/are they dynamic queries?
3. do you have useful indexes on the tables?
4. what are the nature of your queries (SARGs, joins, exists, in, between,
union, outer joins, etc.)
5. is this a sudden degradation in performance?
6. how much memory does Sybase have?
7. is there anything else running on this box?

there is probably more, but that's all I can think of off the top of my head.
Performance tuning is a very broad topic. Help us narrow down the area and sure
someone can lend a hand....

HTH,
Peter

joan caceres wrote:

> Hi,
>
> I have a problem with a table of 13 million records and i do a query upon
> this table, the query response time is about 1 hour with certain conditions,
> i want to know how can i speed the response of the query.
>
> is there any tool that let me calculate the needs of hardware of the
> server(s) with the data of the number of records in the database, number of
> simultaneous querys and tpm.
>
> thanks in advance
>
> Joan