Why is Sybase running my query so slow?
gle...@mindspring.com
I am trying to get the following wuery to run under Sybase 11.0 for
WindowsNT:
select product_tag, market_tag, period_tag, "modalp", catgry_cd,
ad_price,
from tmp_modal
group by product_tag, market_tag, period_tag
having pricecount=Max(pricecount) and ad_price=Max(ad_price)
the problem is that it is running extremely slow ( about 1.5 rows per
second) even though it is the only job running on a dual Pentium Pro
machine with an Ultra-wide SCSI RAID controller. Whats really strange
is that there is virtually no hard drive activity and the SQL server
is only using 50% of CPU time ( the other half is the system idle
process).
Anyone have any ideas?
Glen Koundry
gle...@mindspring.com
Stephen Miller
You probably don't have the correct indexes on your tables. Read the
performance section of the on-line help. I found indexes made a tremendous
difference. Also, if you have big tables it can make a difference if there
is a clustered index on the table. If there is no clustered index, then it
is a heap and it can run slowly even if you have other indexes available.
...>the problem is that it is running extremely slow ( about 1.5 rows per
>second) even though it is the only job running on a dual Pentium Pro
>machine with an Ultra-wide SCSI RAID controller. Whats really strange
>is that there is virtually no hard drive activity and the SQL server
>is only using 50% of CPU time ( the other half is the system idle
>process).
...
Anthony Mandic
gle...@mindspring.com wrote:
>
> I am trying to get the following wuery to run under Sybase 11.0 for
> WindowsNT:
>
> select product_tag, market_tag, period_tag, "modalp", catgry_cd,
> ad_price,
> from tmp_modal
>
> group by product_tag, market_tag, period_tag
>
> having pricecount=Max(pricecount) and ad_price=Max(ad_price)
>
> second) even though it is the only job running on a dual Pentium Pro
> machine with an Ultra-wide SCSI RAID controller. Whats really strange
> is that there is virtually no hard drive activity and the SQL server
> is only using 50% of CPU time ( the other half is the system idle
> process).
>
Look at what you're trying to do. You have two max()'s and
a group by. That'd be 3 worktables at least (not sure now
what the having would do). No wonder it runs like a dog.
Set showplan on to see what its doing and then try reworking
your query.
-am
Robert McIlree
In article <34740341....@200.1.1.100>,
gle...@mindspring.com wrote:
>I am trying to get the following wuery to run under Sybase 11.0 for
>WindowsNT:
>
>select product_tag, market_tag, period_tag, "modalp", catgry_cd,
>ad_price,
>from tmp_modal
>
>group by product_tag, market_tag, period_tag
>
>having pricecount=Max(pricecount) and ad_price=Max(ad_price)
>
>the problem is that it is running extremely slow ( about 1.5 rows per
>second) even though it is the only job running on a dual Pentium Pro
>machine with an Ultra-wide SCSI RAID controller. Whats really strange
>is that there is virtually no hard drive activity and the SQL server
>is only using 50% of CPU time ( the other half is the system idle
>process).
>
>Anyone have any ideas?
What indexing are you using? I'd start with that.
Bob McIlree
rjm...@ix.netcom.com
Tony Imbierski
> >the problem is that it is running extremely slow ( about 1.5 rows per
> >second) even though it is the only job running on a dual Pentium Pro
> >machine with an Ultra-wide SCSI RAID controller. Whats really strange
> >is that there is virtually no hard drive activity and the SQL server
> >is only using 50% of CPU time ( the other half is the system idle
> >process).
Hi,
The reason you're "only" seeing 50% cpu use is probably because sybase
is 100% active on one of your two processors. In 11.0 a single user
connection is pretty much single-threaded. If you have multiple users
running this query, start a second engine and you'll then see both
processors in use.
Adaptive server 11.5 is designed to make better use of multiple cpu's as
it can spin off parallel threads for a single user query.
cheers,
tonyi
--
=============================================================
Tony Imbierski
Sybase Dataserver Engineering / Enterprise NT Group
-------------------------------------------------------------
=============================================================
Jes St. Clare
> I am trying to get the following wuery to run under Sybase 11.0 for
> WindowsNT:
> select product_tag, market_tag, period_tag, "modalp", catgry_cd,
> ad_price,
> from tmp_modal
> group by product_tag, market_tag, period_tag
> having pricecount=Max(pricecount) and ad_price=Max(ad_price)
> second) even though it is the only job running on a dual Pentium Pro
> machine with an Ultra-wide SCSI RAID controller. Whats really strange
> is that there is virtually no hard drive activity and the SQL server
> is only using 50% of CPU time ( the other half is the system idle
> process).