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.

RI

12 posts in Trigger Last posting was on 2006-03-03 17:40:48.0Z
John Smith Posted on 2006-02-28 16:44:07.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
Subject: RI
Date: Tue, 28 Feb 2006 10:44:07 -0600
Lines: 10
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 68.186.139.40
Message-ID: <44047d83@solutions.advantagedatabase.com>
X-Trace: 28 Feb 2006 09:42:43 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:187
Article PK: 1136254

I tested my app with and without RI. To add a record (19 fields) without
RI, the execution was instantaneous. Once RI for just this table was added,
the execution was a tad bit slower. Once I added all of the RI back to the
DD (for all of the tables), the execution was much slower (in the 5-6 second
range). Am I missing something? At this point, with the reduction in
performance, the application is simply not usable. TIA.

John


John Smith Posted on 2006-02-28 19:17:58.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <44047d83@solutions.advantagedatabase.com>
Subject: Re: RI
Date: Tue, 28 Feb 2006 13:17:58 -0600
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 68.186.139.40
Message-ID: <4404a193@solutions.advantagedatabase.com>
X-Trace: 28 Feb 2006 12:16:35 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:188
Article PK: 1136253

Update on this. I wrote an AEP to add a record and return an autoinc value.
In the AEP, if I close my TAdsTable and open it, I get around 2.5 seconds
per execution. If I leave the TAdsTable open, the first execution is 2.5
seconds, but subsequent executions are instantaneous. This lead me to
believe that the RI overhead is on the opening of the dataset. Is there
anything that can be done to make subsequent opens faster? Thanks.

John

"John Smith" <john.smith@aol.com> wrote in message
news:44047d83@solutions.advantagedatabase.com...
>I tested my app with and without RI. To add a record (19 fields) without
>RI, the execution was instantaneous. Once RI for just this table was
>added, the execution was a tad bit slower. Once I added all of the RI back
>to the DD (for all of the tables), the execution was much slower (in the
>5-6 second range). Am I missing something? At this point, with the
>reduction in performance, the application is simply not usable. TIA.
>
> John
>


Jeremy Mullin Posted on 2006-02-28 20:18:59.0Z
Date: Tue, 28 Feb 2006 20:18:59 +0000 (UTC)
Message-ID: <886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: RI
Newsgroups: Advantage.Trigger
References: <4404a193@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 198.102.102.44
X-Trace: 28 Feb 2006 13:15:44 -0700, 198.102.102.44
Lines: 36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:189
Article PK: 1136255

You could have the app keep one of the tables opened after doing an update
to it.

When you do the first update, ADS has to open all of the tables in the RI
graph that relate to the table being updated. If you then immediately close
that table, the entire graph will be closed, and the next time it will again
need to be opened.

J.D. Mullin
Advantage R&D

> Update on this. I wrote an AEP to add a record and return an autoinc
> value. In the AEP, if I close my TAdsTable and open it, I get around
> 2.5 seconds per execution. If I leave the TAdsTable open, the first
> execution is 2.5 seconds, but subsequent executions are instantaneous.
> This lead me to believe that the RI overhead is on the opening of the
> dataset. Is there anything that can be done to make subsequent opens
> faster? Thanks.
>
> John
>
> "John Smith" <john.smith@aol.com> wrote in message
> news:44047d83@solutions.advantagedatabase.com...
>
>> I tested my app with and without RI. To add a record (19 fields)
>> without RI, the execution was instantaneous. Once RI for just this
>> table was added, the execution was a tad bit slower. Once I added
>> all of the RI back to the DD (for all of the tables), the execution
>> was much slower (in the 5-6 second range). Am I missing something?
>> At this point, with the reduction in performance, the application is
>> simply not usable. TIA.
>>
>> John
>>


John Smith Posted on 2006-02-28 20:30:35.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <4404a193@solutions.advantagedatabase.com> <886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Tue, 28 Feb 2006 14:30:35 -0600
Lines: 55
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 68.186.139.40
Message-ID: <4404b298@solutions.advantagedatabase.com>
X-Trace: 28 Feb 2006 13:29:12 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:190
Article PK: 1136256

Thanks Jeremey. I wanted to see about further clarification.

I have a list screen for a table (TERMS.adt). The dataset on the list
screen is a tadstable. From the list screen, I click on a button to add a
record. The record is not added on the list screen. A new form is opened
that is the edit for the record. A tadsquery is used to create a new record
on the edit form. The list screen remains open in the background, thus
ADS has the table TERMS open. If I edit an existing record, the logic is
essentially the same with a new edit screen, but the record edit is
instantaneous. So inserting a record with a tadsquery is much slower than
editting a record with a tadsquery where RI is concerned in my case. In the
mean time, ADS has the table open. Hope this make sense.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
> You could have the app keep one of the tables opened after doing an update
> to it.
> When you do the first update, ADS has to open all of the tables in the RI
> graph that relate to the table being updated. If you then immediately
> close that table, the entire graph will be closed, and the next time it
> will again need to be opened.
>
> J.D. Mullin
> Advantage R&D
>
>> Update on this. I wrote an AEP to add a record and return an autoinc
>> value. In the AEP, if I close my TAdsTable and open it, I get around
>> 2.5 seconds per execution. If I leave the TAdsTable open, the first
>> execution is 2.5 seconds, but subsequent executions are instantaneous.
>> This lead me to believe that the RI overhead is on the opening of the
>> dataset. Is there anything that can be done to make subsequent opens
>> faster? Thanks.
>>
>> John
>>
>> "John Smith" <john.smith@aol.com> wrote in message
>> news:44047d83@solutions.advantagedatabase.com...
>>
>>> I tested my app with and without RI. To add a record (19 fields)
>>> without RI, the execution was instantaneous. Once RI for just this
>>> table was added, the execution was a tad bit slower. Once I added
>>> all of the RI back to the DD (for all of the tables), the execution
>>> was much slower (in the 5-6 second range). Am I missing something?
>>> At this point, with the reduction in performance, the application is
>>> simply not usable. TIA.
>>>
>>> John
>>>
>
>


Jeremy Mullin Posted on 2006-03-01 17:49:08.0Z
Date: Wed, 1 Mar 2006 17:49:08 +0000 (UTC)
Message-ID: <886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: RI
Newsgroups: Advantage.Trigger
References: <4404b298@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 198.102.102.44
X-Trace: 1 Mar 2006 10:45:53 -0700, 198.102.102.44
Lines: 63
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:193
Article PK: 1136258

Can you have the TAdsQuery that is used for inserts live on the list form
(as opposed to creating a new one each time) and pass it to the new insert
form instance? That would keep the tables cached on the server and should
solve your problem.

J.D. Mullin
Advantage R&D

> Thanks Jeremey. I wanted to see about further clarification.
>
> I have a list screen for a table (TERMS.adt). The dataset on the list
> screen is a tadstable. From the list screen, I click on a button to
> add a record. The record is not added on the list screen. A new form
> is opened that is the edit for the record. A tadsquery is used to
> create a new record on the edit form. The list screen remains open
> in the background, thus ADS has the table TERMS open. If I edit an
> existing record, the logic is essentially the same with a new edit
> screen, but the record edit is instantaneous. So inserting a record
> with a tadsquery is much slower than editting a record with a
> tadsquery where RI is concerned in my case. In the mean time, ADS has
> the table open. Hope this make sense.
>
> John
>
> "Jeremy Mullin" <no@email.com> wrote in message
> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>
>> You could have the app keep one of the tables opened after doing an
>> update
>> to it.
>> When you do the first update, ADS has to open all of the tables in
>> the RI
>> graph that relate to the table being updated. If you then immediately
>> close that table, the entire graph will be closed, and the next time
>> it
>> will again need to be opened.
>> J.D. Mullin
>> Advantage R&D
>>> Update on this. I wrote an AEP to add a record and return an
>>> autoinc value. In the AEP, if I close my TAdsTable and open it, I
>>> get around 2.5 seconds per execution. If I leave the TAdsTable
>>> open, the first execution is 2.5 seconds, but subsequent executions
>>> are instantaneous. This lead me to believe that the RI overhead is
>>> on the opening of the dataset. Is there anything that can be done
>>> to make subsequent opens faster? Thanks.
>>>
>>> John
>>>
>>> "John Smith" <john.smith@aol.com> wrote in message
>>> news:44047d83@solutions.advantagedatabase.com...
>>>
>>>> I tested my app with and without RI. To add a record (19 fields)
>>>> without RI, the execution was instantaneous. Once RI for just this
>>>> table was added, the execution was a tad bit slower. Once I added
>>>> all of the RI back to the DD (for all of the tables), the execution
>>>> was much slower (in the 5-6 second range). Am I missing something?
>>>> At this point, with the reduction in performance, the application
>>>> is simply not usable. TIA.
>>>>
>>>> John
>>>>


John Smith Posted on 2006-03-02 15:55:37.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <4404b298@solutions.advantagedatabase.com> <886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Thu, 2 Mar 2006 09:55:37 -0600
Lines: 93
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
NNTP-Posting-Host: 68.186.139.40
Message-ID: <440714f3@solutions.advantagedatabase.com>
X-Trace: 2 Mar 2006 08:53:23 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:194
Article PK: 1136260

This really won't work for me as I allow users to have multiple edit screens
open at a time. Configuration I am using is this:

XP
Delphi 6
Advantage versions - 8.0.0.4
ADS is on the same machine application runs on.

Insert is slow.
Edit is fast.
Posting after an insert or edit is slow. I don't understand this one. With
the TAdsQuery open, shouldn't the RI graph be available.
On the 'TERMS.adt' table, there are only 2 RI constraints, and the table
only has 19 fields.

Oddity to this all, occassionally the insert, edit, and post are
instantaneous. This is with the same list screen and edit screens.
Sometimes, the speed is excellent, running the exact same app. I have yet
to figure out what causes it to speed up. When first running the
application, the speed is always slow as described. But for some reason, it
will all of a sudden get faster. All that I do is just edit, change, post,
or insert, post. Then sometimes the speed is incredible. I don't know what
to think at this point.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com...
> Can you have the TAdsQuery that is used for inserts live on the list form
> (as opposed to creating a new one each time) and pass it to the new insert
> form instance? That would keep the tables cached on the server and should
> solve your problem.
>
> J.D. Mullin
> Advantage R&D
>
>> Thanks Jeremey. I wanted to see about further clarification.
>>
>> I have a list screen for a table (TERMS.adt). The dataset on the list
>> screen is a tadstable. From the list screen, I click on a button to
>> add a record. The record is not added on the list screen. A new form
>> is opened that is the edit for the record. A tadsquery is used to
>> create a new record on the edit form. The list screen remains open
>> in the background, thus ADS has the table TERMS open. If I edit an
>> existing record, the logic is essentially the same with a new edit
>> screen, but the record edit is instantaneous. So inserting a record
>> with a tadsquery is much slower than editting a record with a
>> tadsquery where RI is concerned in my case. In the mean time, ADS has
>> the table open. Hope this make sense.
>>
>> John
>>
>> "Jeremy Mullin" <no@email.com> wrote in message
>> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>>
>>> You could have the app keep one of the tables opened after doing an
>>> update
>>> to it.
>>> When you do the first update, ADS has to open all of the tables in
>>> the RI
>>> graph that relate to the table being updated. If you then immediately
>>> close that table, the entire graph will be closed, and the next time
>>> it
>>> will again need to be opened.
>>> J.D. Mullin
>>> Advantage R&D
>>>> Update on this. I wrote an AEP to add a record and return an
>>>> autoinc value. In the AEP, if I close my TAdsTable and open it, I
>>>> get around 2.5 seconds per execution. If I leave the TAdsTable
>>>> open, the first execution is 2.5 seconds, but subsequent executions
>>>> are instantaneous. This lead me to believe that the RI overhead is
>>>> on the opening of the dataset. Is there anything that can be done
>>>> to make subsequent opens faster? Thanks.
>>>>
>>>> John
>>>>
>>>> "John Smith" <john.smith@aol.com> wrote in message
>>>> news:44047d83@solutions.advantagedatabase.com...
>>>>
>>>>> I tested my app with and without RI. To add a record (19 fields)
>>>>> without RI, the execution was instantaneous. Once RI for just this
>>>>> table was added, the execution was a tad bit slower. Once I added
>>>>> all of the RI back to the DD (for all of the tables), the execution
>>>>> was much slower (in the 5-6 second range). Am I missing something?
>>>>> At this point, with the reduction in performance, the application
>>>>> is simply not usable. TIA.
>>>>>
>>>>> John
>>>>>
>
>


Jeremy Mullin Posted on 2006-03-02 18:16:09.0Z
Date: Thu, 2 Mar 2006 18:16:09 +0000 (UTC)
Message-ID: <886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: RI
Newsgroups: Advantage.Trigger
References: <440714f3@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 198.102.102.44
X-Trace: 2 Mar 2006 11:12:53 -0700, 198.102.102.44
Lines: 110
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:195
Article PK: 1136261

You may need to start trying to narrow down the problem.

For example, try the edits and posts inside data architect and see if you
get the same performance. This can help you determine if the issue is in
the database or in the application (for example, some events that are firing
that you have not considered, etc.).

Try determining if it is a specific table where you see the slowdown.
If so, find out how many tables are related to this one via RI rules, that
significantly affects the work the server has to do (it affects the number
of key lookups it must perform to determine if the operation is legal).

Out of curiosity, how many tables are in this database? 1600 seems like a
lot of RI rules, so I was wondering how many tables are involved.

J.D. Mullin
Advantage R&D

> This really won't work for me as I allow users to have multiple edit
> screens open at a time. Configuration I am using is this:
>
> XP
> Delphi 6
> Advantage versions - 8.0.0.4
> ADS is on the same machine application runs on.
> Insert is slow.
> Edit is fast.
> Posting after an insert or edit is slow. I don't understand this one.
> With
> the TAdsQuery open, shouldn't the RI graph be available.
> On the 'TERMS.adt' table, there are only 2 RI constraints, and the
> table
> only has 19 fields.
> Oddity to this all, occassionally the insert, edit, and post are
> instantaneous. This is with the same list screen and edit screens.
> Sometimes, the speed is excellent, running the exact same app. I have
> yet to figure out what causes it to speed up. When first running the
> application, the speed is always slow as described. But for some
> reason, it will all of a sudden get faster. All that I do is just
> edit, change, post, or insert, post. Then sometimes the speed is
> incredible. I don't know what to think at this point.
>
> John
>
> "Jeremy Mullin" <no@email.com> wrote in message
> news:886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com...
>
>> Can you have the TAdsQuery that is used for inserts live on the list
>> form (as opposed to creating a new one each time) and pass it to the
>> new insert form instance? That would keep the tables cached on the
>> server and should solve your problem.
>>
>> J.D. Mullin
>> Advantage R&D
>>> Thanks Jeremey. I wanted to see about further clarification.
>>>
>>> I have a list screen for a table (TERMS.adt). The dataset on the
>>> list screen is a tadstable. From the list screen, I click on a
>>> button to add a record. The record is not added on the list screen.
>>> A new form is opened that is the edit for the record. A tadsquery
>>> is used to create a new record on the edit form. The list screen
>>> remains open in the background, thus ADS has the table TERMS open.
>>> If I edit an existing record, the logic is essentially the same with
>>> a new edit screen, but the record edit is instantaneous. So
>>> inserting a record with a tadsquery is much slower than editting a
>>> record with a tadsquery where RI is concerned in my case. In the
>>> mean time, ADS has the table open. Hope this make sense.
>>>
>>> John
>>>
>>> "Jeremy Mullin" <no@email.com> wrote in message
>>> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>>>> You could have the app keep one of the tables opened after doing an
>>>> update
>>>> to it.
>>>> When you do the first update, ADS has to open all of the tables in
>>>> the RI
>>>> graph that relate to the table being updated. If you then
>>>> immediately
>>>> close that table, the entire graph will be closed, and the next
>>>> time
>>>> it
>>>> will again need to be opened.
>>>> J.D. Mullin
>>>> Advantage R&D
>>>>> Update on this. I wrote an AEP to add a record and return an
>>>>> autoinc value. In the AEP, if I close my TAdsTable and open it, I
>>>>> get around 2.5 seconds per execution. If I leave the TAdsTable
>>>>> open, the first execution is 2.5 seconds, but subsequent
>>>>> executions are instantaneous. This lead me to believe that the RI
>>>>> overhead is on the opening of the dataset. Is there anything that
>>>>> can be done to make subsequent opens faster? Thanks.
>>>>>
>>>>> John
>>>>>
>>>>> "John Smith" <john.smith@aol.com> wrote in message
>>>>> news:44047d83@solutions.advantagedatabase.com...
>>>>>
>>>>>> I tested my app with and without RI. To add a record (19 fields)
>>>>>> without RI, the execution was instantaneous. Once RI for just
>>>>>> this table was added, the execution was a tad bit slower. Once I
>>>>>> added all of the RI back to the DD (for all of the tables), the
>>>>>> execution was much slower (in the 5-6 second range). Am I
>>>>>> missing something? At this point, with the reduction in
>>>>>> performance, the application is simply not usable. TIA.
>>>>>>
>>>>>> John
>>>>>>


John Smith Posted on 2006-03-02 18:44:29.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <440714f3@solutions.advantagedatabase.com> <886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Thu, 2 Mar 2006 12:44:29 -0600
Lines: 133
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
NNTP-Posting-Host: 68.186.139.40
Message-ID: <44073cbf@solutions.advantagedatabase.com>
X-Trace: 2 Mar 2006 11:43:11 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:196
Article PK: 1136262

There are 300 tables. The same happens when I do the first insert in ARC32.
It's slow. However, subsequent inserts are fast. I still can't understand
why the post would be slow if the RI graph is already open on that dataset.
I am going to do it be process of elimination, adding RIs for one table at a
time to try to determine if it's one of my RIs. Also, I did notice that I
can make an RI on the same table with a different field. I removed that,
and it did not help though. Is there any way to keep the RI graph open and
pass it to each new dataset? With just one RI to a parent table, with no
RIs on the respective parent table to other tables, I get the same slowness.
If I remove the one RI, leaving 1500+ RIs in the DB, none which are
affecting the table I am appending to, the speed is fast. This just doesn't
seem right to me. As far as the application, it runs great without the RIs,
not speed issues. When I add the RIs, just doing a simple insert with my
TERMS.adt maxes out my CPU (AMD 64 1.8 GHZ, 1GB RAM). That is with just one
RI. I am led to believe it has something to do with the shere number of RIs
I have in the DB. Thanks.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com...
> You may need to start trying to narrow down the problem.
> For example, try the edits and posts inside data architect and see if you
> get the same performance. This can help you determine if the issue is in
> the database or in the application (for example, some events that are
> firing that you have not considered, etc.).
>
> Try determining if it is a specific table where you see the slowdown. If
> so, find out how many tables are related to this one via RI rules, that
> significantly affects the work the server has to do (it affects the number
> of key lookups it must perform to determine if the operation is legal).
>
> Out of curiosity, how many tables are in this database? 1600 seems like a
> lot of RI rules, so I was wondering how many tables are involved.
>
> J.D. Mullin
> Advantage R&D
>
>> This really won't work for me as I allow users to have multiple edit
>> screens open at a time. Configuration I am using is this:
>>
>> XP
>> Delphi 6
>> Advantage versions - 8.0.0.4
>> ADS is on the same machine application runs on.
>> Insert is slow.
>> Edit is fast.
>> Posting after an insert or edit is slow. I don't understand this one.
>> With
>> the TAdsQuery open, shouldn't the RI graph be available.
>> On the 'TERMS.adt' table, there are only 2 RI constraints, and the
>> table
>> only has 19 fields.
>> Oddity to this all, occassionally the insert, edit, and post are
>> instantaneous. This is with the same list screen and edit screens.
>> Sometimes, the speed is excellent, running the exact same app. I have
>> yet to figure out what causes it to speed up. When first running the
>> application, the speed is always slow as described. But for some
>> reason, it will all of a sudden get faster. All that I do is just
>> edit, change, post, or insert, post. Then sometimes the speed is
>> incredible. I don't know what to think at this point.
>>
>> John
>>
>> "Jeremy Mullin" <no@email.com> wrote in message
>> news:886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com...
>>
>>> Can you have the TAdsQuery that is used for inserts live on the list
>>> form (as opposed to creating a new one each time) and pass it to the
>>> new insert form instance? That would keep the tables cached on the
>>> server and should solve your problem.
>>>
>>> J.D. Mullin
>>> Advantage R&D
>>>> Thanks Jeremey. I wanted to see about further clarification.
>>>>
>>>> I have a list screen for a table (TERMS.adt). The dataset on the
>>>> list screen is a tadstable. From the list screen, I click on a
>>>> button to add a record. The record is not added on the list screen.
>>>> A new form is opened that is the edit for the record. A tadsquery
>>>> is used to create a new record on the edit form. The list screen
>>>> remains open in the background, thus ADS has the table TERMS open.
>>>> If I edit an existing record, the logic is essentially the same with
>>>> a new edit screen, but the record edit is instantaneous. So
>>>> inserting a record with a tadsquery is much slower than editting a
>>>> record with a tadsquery where RI is concerned in my case. In the
>>>> mean time, ADS has the table open. Hope this make sense.
>>>>
>>>> John
>>>>
>>>> "Jeremy Mullin" <no@email.com> wrote in message
>>>> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>>>>> You could have the app keep one of the tables opened after doing an
>>>>> update
>>>>> to it.
>>>>> When you do the first update, ADS has to open all of the tables in
>>>>> the RI
>>>>> graph that relate to the table being updated. If you then
>>>>> immediately
>>>>> close that table, the entire graph will be closed, and the next
>>>>> time
>>>>> it
>>>>> will again need to be opened.
>>>>> J.D. Mullin
>>>>> Advantage R&D
>>>>>> Update on this. I wrote an AEP to add a record and return an
>>>>>> autoinc value. In the AEP, if I close my TAdsTable and open it, I
>>>>>> get around 2.5 seconds per execution. If I leave the TAdsTable
>>>>>> open, the first execution is 2.5 seconds, but subsequent
>>>>>> executions are instantaneous. This lead me to believe that the RI
>>>>>> overhead is on the opening of the dataset. Is there anything that
>>>>>> can be done to make subsequent opens faster? Thanks.
>>>>>>
>>>>>> John
>>>>>>
>>>>>> "John Smith" <john.smith@aol.com> wrote in message
>>>>>> news:44047d83@solutions.advantagedatabase.com...
>>>>>>
>>>>>>> I tested my app with and without RI. To add a record (19 fields)
>>>>>>> without RI, the execution was instantaneous. Once RI for just
>>>>>>> this table was added, the execution was a tad bit slower. Once I
>>>>>>> added all of the RI back to the DD (for all of the tables), the
>>>>>>> execution was much slower (in the 5-6 second range). Am I
>>>>>>> missing something? At this point, with the reduction in
>>>>>>> performance, the application is simply not usable. TIA.
>>>>>>>
>>>>>>> John
>>>>>>>
>
>


John Smith Posted on 2006-03-03 05:03:27.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <440714f3@solutions.advantagedatabase.com> <886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Thu, 2 Mar 2006 23:03:27 -0600
Lines: 125
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
NNTP-Posting-Host: 68.186.139.40
Message-ID: <4407cdd1@solutions.advantagedatabase.com>
X-Trace: 2 Mar 2006 22:02:09 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:197
Article PK: 1136263

I removed all RIs for testing. Speed was great. I then created RIs on only
two tables, the one I was editting and the table that had the RI
relationship (the parent) with the editted table. The speed was still
great. I then added over 500 RIs, which I think is conservative, and the
speed immediately decreased. Now in my ADD, I have 569 RIs, and the I can
already tell the speed slow down. I have not timed the speed, but I will.
It would appear the speed is relative to the # of RIs in the ADD. If a
table only has one RI object, and an ADD has say 1000 RIs object, why would
the speed be noticeably slower than if only the one RI existed.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com...
> You may need to start trying to narrow down the problem.
> For example, try the edits and posts inside data architect and see if you
> get the same performance. This can help you determine if the issue is in
> the database or in the application (for example, some events that are
> firing that you have not considered, etc.).
>
> Try determining if it is a specific table where you see the slowdown. If
> so, find out how many tables are related to this one via RI rules, that
> significantly affects the work the server has to do (it affects the number
> of key lookups it must perform to determine if the operation is legal).
>
> Out of curiosity, how many tables are in this database? 1600 seems like a
> lot of RI rules, so I was wondering how many tables are involved.
>
> J.D. Mullin
> Advantage R&D
>
>> This really won't work for me as I allow users to have multiple edit
>> screens open at a time. Configuration I am using is this:
>>
>> XP
>> Delphi 6
>> Advantage versions - 8.0.0.4
>> ADS is on the same machine application runs on.
>> Insert is slow.
>> Edit is fast.
>> Posting after an insert or edit is slow. I don't understand this one.
>> With
>> the TAdsQuery open, shouldn't the RI graph be available.
>> On the 'TERMS.adt' table, there are only 2 RI constraints, and the
>> table
>> only has 19 fields.
>> Oddity to this all, occassionally the insert, edit, and post are
>> instantaneous. This is with the same list screen and edit screens.
>> Sometimes, the speed is excellent, running the exact same app. I have
>> yet to figure out what causes it to speed up. When first running the
>> application, the speed is always slow as described. But for some
>> reason, it will all of a sudden get faster. All that I do is just
>> edit, change, post, or insert, post. Then sometimes the speed is
>> incredible. I don't know what to think at this point.
>>
>> John
>>
>> "Jeremy Mullin" <no@email.com> wrote in message
>> news:886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com...
>>
>>> Can you have the TAdsQuery that is used for inserts live on the list
>>> form (as opposed to creating a new one each time) and pass it to the
>>> new insert form instance? That would keep the tables cached on the
>>> server and should solve your problem.
>>>
>>> J.D. Mullin
>>> Advantage R&D
>>>> Thanks Jeremey. I wanted to see about further clarification.
>>>>
>>>> I have a list screen for a table (TERMS.adt). The dataset on the
>>>> list screen is a tadstable. From the list screen, I click on a
>>>> button to add a record. The record is not added on the list screen.
>>>> A new form is opened that is the edit for the record. A tadsquery
>>>> is used to create a new record on the edit form. The list screen
>>>> remains open in the background, thus ADS has the table TERMS open.
>>>> If I edit an existing record, the logic is essentially the same with
>>>> a new edit screen, but the record edit is instantaneous. So
>>>> inserting a record with a tadsquery is much slower than editting a
>>>> record with a tadsquery where RI is concerned in my case. In the
>>>> mean time, ADS has the table open. Hope this make sense.
>>>>
>>>> John
>>>>
>>>> "Jeremy Mullin" <no@email.com> wrote in message
>>>> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>>>>> You could have the app keep one of the tables opened after doing an
>>>>> update
>>>>> to it.
>>>>> When you do the first update, ADS has to open all of the tables in
>>>>> the RI
>>>>> graph that relate to the table being updated. If you then
>>>>> immediately
>>>>> close that table, the entire graph will be closed, and the next
>>>>> time
>>>>> it
>>>>> will again need to be opened.
>>>>> J.D. Mullin
>>>>> Advantage R&D
>>>>>> Update on this. I wrote an AEP to add a record and return an
>>>>>> autoinc value. In the AEP, if I close my TAdsTable and open it, I
>>>>>> get around 2.5 seconds per execution. If I leave the TAdsTable
>>>>>> open, the first execution is 2.5 seconds, but subsequent
>>>>>> executions are instantaneous. This lead me to believe that the RI
>>>>>> overhead is on the opening of the dataset. Is there anything that
>>>>>> can be done to make subsequent opens faster? Thanks.
>>>>>>
>>>>>> John
>>>>>>
>>>>>> "John Smith" <john.smith@aol.com> wrote in message
>>>>>> news:44047d83@solutions.advantagedatabase.com...
>>>>>>
>>>>>>> I tested my app with and without RI. To add a record (19 fields)
>>>>>>> without RI, the execution was instantaneous. Once RI for just
>>>>>>> this table was added, the execution was a tad bit slower. Once I
>>>>>>> added all of the RI back to the DD (for all of the tables), the
>>>>>>> execution was much slower (in the 5-6 second range). Am I
>>>>>>> missing something? At this point, with the reduction in
>>>>>>> performance, the application is simply not usable. TIA.
>>>>>>>
>>>>>>> John
>>>>>>>
>
>


Jeremy Mullin Posted on 2006-03-03 17:40:48.0Z
Date: Fri, 3 Mar 2006 17:40:48 +0000 (UTC)
Message-ID: <886edc15fa938c80cc611b4cd8e@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: RI
Newsgroups: Advantage.Trigger
References: <4407cdd1@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=utf-8; format=flowed
X-Newsreader: JetBrains Omea Reader 928.2
NNTP-Posting-Host: 198.102.102.44
X-Trace: 3 Mar 2006 10:37:33 -0700, 198.102.102.44
Lines: 184
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.44
Xref: solutions.advantagedatabase.com Advantage.Trigger:198
Article PK: 1136265

I'll quote a section from Cary Jensen's Advantage Database Server book (ISBN
0-07-223084-3):

"
Referential integrity definitions are valuable, but they tend to get overused.
Most
database applications contain many related tables. But just because two tables
contain
related data does not mean that there is a parent/child relationship that
needs to be
managed through referential integrity.

There are two primary problems caused by the overuse of referential integrity
definitions. The first is that few related tables require the tight controls
imposed by
referential integrity. Referential integrity restricts your ability to delete
and change
parent table records, as well as prohibiting the insertion of child records
for which
there is no parent. When applied to the wrong tables, a user???s attempt to
add, edit,
or delete data may be rejected, even when that operation is legitimate.
The second problem is related to resources. Whenever a table involved in a
referential integrity definition is written to, the associated tables and
their auto-open
indexes are opened. This happens even if you are making a change only to
the one
table you opened.

Extensive use of referential integrity may require you to configure ADS to
use
additional work areas to handle the increases in table usage, and can also
make it
more difficult to obtain an exclusive lock on a table. (A table opened as
a result of
a referential integrity definition cannot be opened exclusively by another
user.)
The solution is to use referential integrity sparingly. Most applications
have only
one or two pairs of tables whose related data is so critical to the application
that
referential integrity definitions are required. For many other related but
nonessential
tables, the data integrity provided by referential integrity is not worth
the restrictions
and increased resource usage they produce
"

J.D. Mullin
Advantage R&D

> I removed all RIs for testing. Speed was great. I then created RIs
> on only two tables, the one I was editting and the table that had the
> RI relationship (the parent) with the editted table. The speed was
> still great. I then added over 500 RIs, which I think is
> conservative, and the speed immediately decreased. Now in my ADD, I
> have 569 RIs, and the I can already tell the speed slow down. I have
> not timed the speed, but I will. It would appear the speed is relative
> to the # of RIs in the ADD. If a table only has one RI object, and an
> ADD has say 1000 RIs object, why would the speed be noticeably slower
> than if only the one RI existed.
>
> John
>
> "Jeremy Mullin" <no@email.com> wrote in message
> news:886edc15f9408c80c01d75c0725@devzone.advantagedatabase.com...
>
>> You may need to start trying to narrow down the problem.
>> For example, try the edits and posts inside data architect and see if
>> you
>> get the same performance. This can help you determine if the issue is
>> in
>> the database or in the application (for example, some events that are
>> firing that you have not considered, etc.).
>> Try determining if it is a specific table where you see the slowdown.
>> If so, find out how many tables are related to this one via RI rules,
>> that significantly affects the work the server has to do (it affects
>> the number of key lookups it must perform to determine if the
>> operation is legal).
>>
>> Out of curiosity, how many tables are in this database? 1600 seems
>> like a lot of RI rules, so I was wondering how many tables are
>> involved.
>>
>> J.D. Mullin
>> Advantage R&D
>>> This really won't work for me as I allow users to have multiple edit
>>> screens open at a time. Configuration I am using is this:
>>>
>>> XP
>>> Delphi 6
>>> Advantage versions - 8.0.0.4
>>> ADS is on the same machine application runs on.
>>> Insert is slow.
>>> Edit is fast.
>>> Posting after an insert or edit is slow. I don't understand this
>>> one.
>>> With
>>> the TAdsQuery open, shouldn't the RI graph be available.
>>> On the 'TERMS.adt' table, there are only 2 RI constraints, and the
>>> table
>>> only has 19 fields.
>>> Oddity to this all, occassionally the insert, edit, and post are
>>> instantaneous. This is with the same list screen and edit screens.
>>> Sometimes, the speed is excellent, running the exact same app. I
>>> have
>>> yet to figure out what causes it to speed up. When first running
>>> the
>>> application, the speed is always slow as described. But for some
>>> reason, it will all of a sudden get faster. All that I do is just
>>> edit, change, post, or insert, post. Then sometimes the speed is
>>> incredible. I don't know what to think at this point.
>>> John
>>>
>>> "Jeremy Mullin" <no@email.com> wrote in message
>>> news:886edc15f71f8c80b34e72192e6@devzone.advantagedatabase.com...
>>>> Can you have the TAdsQuery that is used for inserts live on the
>>>> list form (as opposed to creating a new one each time) and pass it
>>>> to the new insert form instance? That would keep the tables cached
>>>> on the server and should solve your problem.
>>>>
>>>> J.D. Mullin
>>>> Advantage R&D
>>>>> Thanks Jeremey. I wanted to see about further clarification.
>>>>>
>>>>> I have a list screen for a table (TERMS.adt). The dataset on the
>>>>> list screen is a tadstable. From the list screen, I click on a
>>>>> button to add a record. The record is not added on the list
>>>>> screen. A new form is opened that is the edit for the record. A
>>>>> tadsquery is used to create a new record on the edit form. The
>>>>> list screen remains open in the background, thus ADS has the table
>>>>> TERMS open. If I edit an existing record, the logic is essentially
>>>>> the same with a new edit screen, but the record edit is
>>>>> instantaneous. So inserting a record with a tadsquery is much
>>>>> slower than editting a record with a tadsquery where RI is
>>>>> concerned in my case. In the mean time, ADS has the table open.
>>>>> Hope this make sense.
>>>>>
>>>>> John
>>>>>
>>>>> "Jeremy Mullin" <no@email.com> wrote in message
>>>>> news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
>>>>>> You could have the app keep one of the tables opened after doing
>>>>>> an
>>>>>> update
>>>>>> to it.
>>>>>> When you do the first update, ADS has to open all of the tables
>>>>>> in
>>>>>> the RI
>>>>>> graph that relate to the table being updated. If you then
>>>>>> immediately
>>>>>> close that table, the entire graph will be closed, and the next
>>>>>> time
>>>>>> it
>>>>>> will again need to be opened.
>>>>>> J.D. Mullin
>>>>>> Advantage R&D
>>>>>>> Update on this. I wrote an AEP to add a record and return an
>>>>>>> autoinc value. In the AEP, if I close my TAdsTable and open it,
>>>>>>> I get around 2.5 seconds per execution. If I leave the
>>>>>>> TAdsTable open, the first execution is 2.5 seconds, but
>>>>>>> subsequent executions are instantaneous. This lead me to believe
>>>>>>> that the RI overhead is on the opening of the dataset. Is there
>>>>>>> anything that can be done to make subsequent opens faster?
>>>>>>> Thanks.
>>>>>>>
>>>>>>> John
>>>>>>>
>>>>>>> "John Smith" <john.smith@aol.com> wrote in message
>>>>>>> news:44047d83@solutions.advantagedatabase.com...
>>>>>>>
>>>>>>>> I tested my app with and without RI. To add a record (19
>>>>>>>> fields) without RI, the execution was instantaneous. Once RI
>>>>>>>> for just this table was added, the execution was a tad bit
>>>>>>>> slower. Once I added all of the RI back to the DD (for all of
>>>>>>>> the tables), the execution was much slower (in the 5-6 second
>>>>>>>> range). Am I missing something? At this point, with the
>>>>>>>> reduction in performance, the application is simply not usable.
>>>>>>>> TIA.
>>>>>>>>
>>>>>>>> John
>>>>>>>>


John Smith Posted on 2006-02-28 20:57:23.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <4404a193@solutions.advantagedatabase.com> <886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Tue, 28 Feb 2006 14:57:23 -0600
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 68.186.139.40
Message-ID: <4404b8e0@solutions.advantagedatabase.com>
X-Trace: 28 Feb 2006 13:56:00 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:192
Article PK: 1136257

Jeremy,

Sorry to keep double posting. I wanted to mention my ADD has 300
tables, and when I add the RI, almost 1600 RI objects.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
> You could have the app keep one of the tables opened after doing an update
> to it.
> When you do the first update, ADS has to open all of the tables in the RI
> graph that relate to the table being updated. If you then immediately
> close that table, the entire graph will be closed, and the next time it
> will again need to be opened.
>
> J.D. Mullin
> Advantage R&D
>
>> Update on this. I wrote an AEP to add a record and return an autoinc
>> value. In the AEP, if I close my TAdsTable and open it, I get around
>> 2.5 seconds per execution. If I leave the TAdsTable open, the first
>> execution is 2.5 seconds, but subsequent executions are instantaneous.
>> This lead me to believe that the RI overhead is on the opening of the
>> dataset. Is there anything that can be done to make subsequent opens
>> faster? Thanks.
>>
>> John
>>
>> "John Smith" <john.smith@aol.com> wrote in message
>> news:44047d83@solutions.advantagedatabase.com...
>>
>>> I tested my app with and without RI. To add a record (19 fields)
>>> without RI, the execution was instantaneous. Once RI for just this
>>> table was added, the execution was a tad bit slower. Once I added
>>> all of the RI back to the DD (for all of the tables), the execution
>>> was much slower (in the 5-6 second range). Am I missing something?
>>> At this point, with the reduction in performance, the application is
>>> simply not usable. TIA.
>>>
>>> John
>>>
>
>


John Smith Posted on 2006-02-28 20:55:35.0Z
From: "John Smith" <john.smith@aol.com>
Newsgroups: Advantage.Trigger
References: <4404a193@solutions.advantagedatabase.com> <886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com>
Subject: Re: RI
Date: Tue, 28 Feb 2006 14:55:35 -0600
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 68.186.139.40
Message-ID: <4404b873@solutions.advantagedatabase.com>
X-Trace: 28 Feb 2006 13:54:11 -0700, 68.186.139.40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.186.139.40
Xref: solutions.advantagedatabase.com Advantage.Trigger:191
Article PK: 1136259

Jeremy

I wanted to clarify my app a little. I display a list screen with all
the records from TERMS.adt using a TAdsTable. Each record has an autoinc
value. When clicking on the 'Add' button, a new screen is displayed which
is the edit screen for the new record using a TAdsQuery. The way the record
is created is like this. When adding, a blank record is created in
TERMS.adt. The record is created using TAdsQuery.ExecSQL. This is what
takes so much time. The autoinc value is returned, then an 'Edit' is done
on this blank record using the autoinc value to locate the record. When
clicking on 'Edit', the autoinc value from that record is used by the same
query to return an existing record. In both cases, it's essentially an
edit. just with an 'Add', it's a blank record. Creating that blank record
initially is what takes the time. Hope this helps. Thanks again.

John

"Jeremy Mullin" <no@email.com> wrote in message
news:886edc15f6658c80a80abac3fc2@devzone.advantagedatabase.com...
> You could have the app keep one of the tables opened after doing an update
> to it.
> When you do the first update, ADS has to open all of the tables in the RI
> graph that relate to the table being updated. If you then immediately
> close that table, the entire graph will be closed, and the next time it
> will again need to be opened.
>
> J.D. Mullin
> Advantage R&D
>
>> Update on this. I wrote an AEP to add a record and return an autoinc
>> value. In the AEP, if I close my TAdsTable and open it, I get around
>> 2.5 seconds per execution. If I leave the TAdsTable open, the first
>> execution is 2.5 seconds, but subsequent executions are instantaneous.
>> This lead me to believe that the RI overhead is on the opening of the
>> dataset. Is there anything that can be done to make subsequent opens
>> faster? Thanks.
>>
>> John
>>
>> "John Smith" <john.smith@aol.com> wrote in message
>> news:44047d83@solutions.advantagedatabase.com...
>>
>>> I tested my app with and without RI. To add a record (19 fields)
>>> without RI, the execution was instantaneous. Once RI for just this
>>> table was added, the execution was a tad bit slower. Once I added
>>> all of the RI back to the DD (for all of the tables), the execution
>>> was much slower (in the 5-6 second range). Am I missing something?
>>> At this point, with the reduction in performance, the application is
>>> simply not usable. TIA.
>>>
>>> John
>>>
>
>