Sybase is Damm Slow!!

[Rafey]

Hello;

I am using Sybase 5.5 / Delphi4.0 / MIDAS2.0. Sybase hangs up even for 2
users when they request queries simulateneously to Sybase!

When I execute query#1 independently, then it takes 12 secs.
When I execute query#2 independently, then it takes 1 secs.

But when I execute query#1 and #2 simultaneously then :

query#1, then it takes 12 secs.
query#2, then it takes 11 secs.

Whats going on can anybody explain;

query#1
*********
Select
c.AgNym + ' - '+c.CcySwift AgNym ,t.RecState,GLNO
mGlstNo,POSTDATE,DR,CR,REFNO,AGENTREF,
PAGENO,SUPPDETAIL,XTRAREF,ChangeNO,MATCHNO mGlstMatchNo,MatchType
,T.mStatus,t.Status,t.AgNO,'C' FromT
from
Agchld C,GL001 T
Where C.agno=t.AgNo And 'C'='C' and (inTran=0 or inTran is null) and
(POSTDATE>='Mar-16-2000')

Union Select

hc.AgNym + ' - '+hc.CcySwift AgNym,ht.RecState,GLNO
mGlstNo,POSTDATE,DR,CR,REFNO,AGENTREF,PAGENO,SUPPDETAIL,XTRAREF,ChangeNO,MAT
CHNO mGlstMatchNo,MatchType ,hT.mStatus,ht.Status,ht.AgNO,'H' FromT
from
Agchld hC,HGL001 hT
Where
hC.agno=ht.AgNo and 'H'='H' and (inTran=0 or inTran is null) and
(POSTDATE>='Mar-16-2000')
Order By 5 DESC,6

----------------------------------------------------------------------------
----
query#2
*********
select * from param

Thanks;
Rafey.
eng...@yahoo.com

[Dave Wolf [Sybase]]

Please do not cross-post to groups that do not relate to your question.

Dave Wolf
Internet Applications Division

"Rafey" <eng...@yahoo.com> wrote in message
news:djoMzk#7$GA....@forums.sybase.com...

[Breck Carter]

We'll try to help but performance tuning requires a good understanding
of what's going on and only YOU have that. For some tips and ideas
have a look at http://www.bcarter.com/tip077.htm which is written for
version 5.5.

If query #1 is sending the cpu to 100% then query #2 may very well be
the victim, and Version 7 may indeed improve things. But so will
reducing the time query #1 takes. Have a look at the "plan" (see
http://www.bcarter.com/sap03.htm).

Breck

[Leo Tohill]

If you could use UNION ALL instead of plain UNION, performance may improve.

As someone else pointed out, if the engine and cpu are fully utilized processing select #1, then it
will slow the result for select #2.

Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<

[Jim Egan]

It's like shooting in the dark without the structures of the tables. Can
you post them? Need to know indexes, FK, PK, etc.

Why do you have C=C and H=H in your WHERE clause?
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm

[Rafey]

Here is the table structure. Looking forward to any suugestions.
Thanks,
Rafey.

CREATE TABLE [dbo].[AGCHLD] (
[AGNYM] [varchar] (15) NULL ,
[CCYSWIFT] [varchar] (3) NULL ,
[STATUS] [smallint] NULL ,
[MODIBY] [varchar] (10) NULL ,
[MODION] [datetime] NULL ,
[VERIBY] [varchar] (10) NULL ,
[VERION] [datetime] NULL ,
[GLCLDR] [float] NULL ,
[GLCLCR] [float] NULL ,
[GLCLDATE] [datetime] NULL ,
[STCLDR] [float] NULL ,
[STCLCR] [float] NULL ,
[STCLDATE] [datetime] NULL ,
[GLFLNYM] [varchar] (10) NULL ,
[STFLNYM] [varchar] (10) NULL ,
[AGNO] [smallint] NULL ,
[GLACCTNO] [varchar] (35) NULL ,
[STACCTNO] [varchar] (35) NULL ,
[GLFREQ] [smallint] NULL ,
[STFREQ] [smallint] NULL ,
[STCLDONE] [smallint] NULL ,
[GLCLDONE] [smallint] NULL ,
[LASTGLDATE] [datetime] NULL ,
[LASTSTDATE] [datetime] NULL ,
[DATASTATE] [varchar] (2) NULL ,
[GLREVCODE] [varchar] (10) NULL ,
[STREVCODE] [varchar] (10) NULL ,
[StDrSumOpen] [float] NULL ,
[GLDrSumOpen] [float] NULL ,
[LastGLCrClBal] [float] NULL ,
[LastSTCrClBal] [float] NULL ,
[StCrSumOpen] [float] NULL ,
[GLCrSumOpen] [float] NULL ,
[LastGLDrClBal] [float] NULL ,
[LastSTDrClBal] [float] NULL ,
[StDrCountOpen] [int] NULL ,
[GLDrCountOpen] [int] NULL ,
[StCrCountOpen] [int] NULL ,
[GLCrCountOpen] [int] NULL ,
[LastGlPostDate] [datetime] NULL ,
[LastStPostDate] [datetime] NULL ,
[CntrGlAcctNo] [varchar] (35) NULL ,
[CntrStAcctNo] [varchar] (35) NULL ,
[OfspEBy] [varchar] (10) NULL ,
[OfspEOn] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GL001] (
[POSTDATE] [datetime] NULL ,
[VALUEDATE] [datetime] NULL ,
[BRCODE] [varchar] (4) NULL ,
[TRCODE] [varchar] (4) NULL ,
[REFNO] [varchar] (16) NULL ,
[REFDATE] [datetime] NULL ,
[AGENTREF] [varchar] (50) NULL ,
[DR] [float] NULL ,
[CR] [float] NULL ,
[DRCR] [varchar] (2) NULL ,
[SUPPDETAIL] [varchar] (34) NULL ,
[REVCODE] [varchar] (3) NULL ,
[STATUS] [smallint] NULL ,
[XTRADATE] [datetime] NULL ,
[XTRAREF] [varchar] (200) NULL ,
[AGNO] [smallint] NULL ,
[ADDINFO] [text] NULL ,
[PAGENO] [varchar] (15) NULL ,
[ISIMPORTED] [smallint] NULL ,
[IMPENTON] [datetime] NULL ,
[IMPERR] [varchar] (200) NULL ,
[GLNO] [float] NULL ,
[RECSTATE] [varchar] (3) NULL ,
[GENTYPE] [varchar] (1) NULL ,
[MATCHNO] [float] NULL ,
[MATCHVERION] [datetime] NULL ,
[MATCHTYPE] [varchar] (5) NULL ,
[RLNYM] [varchar] (8) NULL ,
[MSTATUS] [smallint] NULL ,
[RuleEffNo] [int] NULL ,
[IsQuery] [smallint] NULL ,
[ChangeNo] [int] NULL ,
[ChargesMatch] [varchar] (2) NULL ,
[InTran] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[hGL001] (
[POSTDATE] [datetime] NULL ,
[VALUEDATE] [datetime] NULL ,
[BRCODE] [varchar] (4) NULL ,
[TRCODE] [varchar] (4) NULL ,
[REFNO] [varchar] (16) NULL ,
[REFDATE] [datetime] NULL ,
[AGENTREF] [varchar] (50) NULL ,
[DR] [float] NULL ,
[CR] [float] NULL ,
[DRCR] [varchar] (2) NULL ,
[SUPPDETAIL] [varchar] (34) NULL ,
[REVCODE] [varchar] (3) NULL ,
[STATUS] [smallint] NULL ,
[XTRADATE] [datetime] NULL ,
[XTRAREF] [varchar] (200) NULL ,
[AGNO] [smallint] NULL ,
[ADDINFO] [text] NULL ,
[PAGENO] [varchar] (15) NULL ,
[ISIMPORTED] [smallint] NULL ,
[IMPENTON] [datetime] NULL ,
[IMPERR] [varchar] (200) NULL ,
[GLNO] [float] NULL ,
[RECSTATE] [varchar] (3) NULL ,
[GENTYPE] [varchar] (1) NULL ,
[MATCHNO] [float] NULL ,
[MATCHVERION] [datetime] NULL ,
[MATCHTYPE] [varchar] (5) NULL ,
[RLNYM] [varchar] (8) NULL ,
[MSTATUS] [smallint] NULL ,
[RuleEffNo] [int] NULL ,
[IsQuery] [smallint] NULL ,
[ChangeNo] [int] NULL ,
[ChargesMatch] [varchar] (2) NULL ,
[InTran] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [AGCHILDPK] ON [dbo].[AGCHLD]([AGNYM], [CCYSWIFT]) ON
[PRIMARY]
GO

CREATE INDEX [GL001PK] ON [dbo].[GL001]([GLNO]) ON [PRIMARY]
GO

CREATE INDEX [GL001MATCHNO] ON [dbo].[GL001]([MATCHNO]) ON [PRIMARY]
GO

CREATE INDEX [GL001forced] ON [dbo].[GL001]([STATUS], [RECSTATE], [DR],
[CR]) ON [PRIMARY]
GO

Jim Egan <dba...@eganomics.com> wrote in message
news:MPG.13dd7149a...@199.93.177.77...

[Jim Egan]

I think you are in need of some indexes. Maybe even some Foreign Keys
too.

Both the agchld and gl001 tables need an index on the AGNO column. You
also need an index on the POSTDATE column. This will get you started.

SQL Anywhere is NOT slow. The reason your query was slow was that the
database engine was forced to do a table scan, likely on both tables.
Add the two indexes and at the worst you will only scan one of the
tables. At best, no scan at all.

[Rafey]

Thanks Jim for advice. How did you evaluated that I need index on AGNO and
POSTDATE. Secondly, where do I need foreign keys.

Thanks,
Rafey.

Jim Egan <dba...@eganomics.com> wrote in message

news:MPG.13e17513...@199.93.177.77...

[Jim Egan]

I looked at your queries and saw that you were joining the two tables on
those columns. I then looked at your DDL and didn't find anything that
created an index on the AGNO columns.

I don't know your structures or your data so I can't recommend foreign
keys. Any time there is a parent->child relationship between your tables
then there is a good chance that a Foreign Key is appropriate. I want to
say "required" but this isn't always true. Besides creating an index for
you, the Foreign Key will help ensure that your child data isn't orphaned
when someone deletes the parent.

The DDL is somewhat hard to read with all those [] characters. If you
use DBUNLOAD you should get a nice clean set of DDL.