Board index » delphi » MS SQL 7, ADO & Delphi 5

MS SQL 7, ADO & Delphi 5

Is there a formula or could someone share with me any insights on the
resources required by a Server and its workstations when running a Delphi
app using ADO.  This app is pushing between 4000-6000 records while updating
3 or 4 support files (counters and statistical fields).  It's running
anywhere from 6-15 user (tyically 12+).  It's experiencing server timeouts
on transactions which get progressively worse as users increase until no one
can effective produce work.

Any help, guidance or reference source would be most appreciated.

Thanks

Ted

 

Re:MS SQL 7, ADO & Delphi 5


no magic formulas, but if you can give us some more info, we may be able to
better pinpoint potential sources of problems.

4000-6000 records - in what period of time? Pushing? Is that a Read -
update? How big is a transaction (in rows and KB of data)

I would expect a high end single processor (500mHz+) or decent dual
processor machine to handle this load, given that it wasn't memory starved.

Suggestions: Keep transactions short. Use stored procedures. Return scalar
output parameters instead of rowsets when possible.
Use the SQL Profiler tool to see if your DB is well indexed.

Re:MS SQL 7, ADO & Delphi 5


What edition of SQL Server are you using? It sounds like you might be using
the desktop edition which increasingly throttles back performance for any
more than 5 concurrent connections.

Dave

Quote
"Ted Hale" <t...@srs-inc.net> wrote in message news:3a9a6560$1_1@dnews...
> Is there a formula or could someone share with me any insights on the
> resources required by a Server and its workstations when running a Delphi
> app using ADO.  This app is pushing between 4000-6000 records while
updating
> 3 or 4 support files (counters and statistical fields).  It's running
> anywhere from 6-15 user (tyically 12+).  It's experiencing server timeouts
> on transactions which get progressively worse as users increase until no
one
> can effective produce work.

> Any help, guidance or reference source would be most appreciated.

> Thanks

> Ted

Re:MS SQL 7, ADO & Delphi 5


It's a full licensed version of SQL 7 Server (SP2), MDAC 2.6.  I'm not in
control of the server or software (running NT 4 (SP5).

What would the criteria be for "memory starved"?

Thanks for any help.

Ted

Quote
"David Johnson" <davidsjohnson(at)earthlink(dot)net> wrote in message

news:97e0ht$onh7@bornews.inprise.com...
Quote
> What edition of SQL Server are you using? It sounds like you might be
using
> the desktop edition which increasingly throttles back performance for any
> more than 5 concurrent connections.

> Dave

> "Ted Hale" <t...@srs-inc.net> wrote in message news:3a9a6560$1_1@dnews...
> > Is there a formula or could someone share with me any insights on the
> > resources required by a Server and its workstations when running a
Delphi
> > app using ADO.  This app is pushing between 4000-6000 records while
> updating
> > 3 or 4 support files (counters and statistical fields).  It's running
> > anywhere from 6-15 user (tyically 12+).  It's experiencing server
timeouts
> > on transactions which get progressively worse as users increase until no
> one
> > can effective produce work.

> > Any help, guidance or reference source would be most appreciated.

> > Thanks

> > Ted

Re:MS SQL 7, ADO & Delphi 5


The 4000-6000 record are processed at a fairly constant rate for about 6 or
7 hours.  The procedures that occur at post time (I've intercepted the post
event on the main table) vary and include posting to other files.  Moved
most everything I could to stored procedures.  The app is now at about 4MB
(I hadn't taken notice of it's size 'till now).  The main table has about 35
columns not all of which get data.  The support files that get updated are
the result of queries and only the relevant rows are retrieved and updated.

The truely odd thing is all was working well.  I then added a couple of
QuickReports, though no changes to the posting routines) posted the new
executable and all hell broke loose.  If I reload an older executable all is
fine again.

Thanks for any help.

Ted

Quote
"Justin Pitts" <jpi...@cnstores.com> wrote in message

news:3a9a688b_2@dnews...
Quote
> no magic formulas, but if you can give us some more info, we may be able
to
> better pinpoint potential sources of problems.

> 4000-6000 records - in what period of time? Pushing? Is that a Read -
> update? How big is a transaction (in rows and KB of data)

> I would expect a high end single processor (500mHz+) or decent dual
> processor machine to handle this load, given that it wasn't memory
starved.

> Suggestions: Keep transactions short. Use stored procedures. Return scalar
> output parameters instead of rowsets when possible.
> Use the SQL Profiler tool to see if your DB is well indexed.

Re:MS SQL 7, ADO & Delphi 5


From your message it is not clear how you manage your transactions and
database connections. I think it may help to use a separate readonly
database connection for generating reports and not the read/write connection
you use for updating. Otherwise you might lock a lot of resources. If you
use MTS or COM+ transactions, it is better to generate reports with a
component that does not support transactions. You will get a serializable
connection when a MTS or COM+ transaction is envolved.

Martijn Houtman

Quote
"Ted Hale" <t...@srs-inc.net> wrote in message news:3a9af793$1_2@dnews...
> The 4000-6000 record are processed at a fairly constant rate for about 6
or
> 7 hours.  The procedures that occur at post time (I've intercepted the
post
> event on the main table) vary and include posting to other files.  Moved
> most everything I could to stored procedures.  The app is now at about 4MB
> (I hadn't taken notice of it's size 'till now).  The main table has about
35
> columns not all of which get data.  The support files that get updated are
> the result of queries and only the relevant rows are retrieved and
updated.

> The truely odd thing is all was working well.  I then added a couple of
> QuickReports, though no changes to the posting routines) posted the new
> executable and all hell broke loose.  If I reload an older executable all
is
> fine again.

> Thanks for any help.

> Ted

> "Justin Pitts" <jpi...@cnstores.com> wrote in message
> news:3a9a688b_2@dnews...
> > no magic formulas, but if you can give us some more info, we may be able
> to
> > better pinpoint potential sources of problems.

> > 4000-6000 records - in what period of time? Pushing? Is that a Read -
> > update? How big is a transaction (in rows and KB of data)

> > I would expect a high end single processor (500mHz+) or decent dual
> > processor machine to handle this load, given that it wasn't memory
> starved.

> > Suggestions: Keep transactions short. Use stored procedures. Return
scalar
> > output parameters instead of rowsets when possible.
> > Use the SQL Profiler tool to see if your DB is well indexed.

Other Threads