Board index » delphi » ADO & MS SQL Server

ADO & MS SQL Server

I have an app using an SQL Database (not under my control) via ADO.  The
amount of users can be anywhere from 5 to 15 and the database is at around
200,000 records.

About a 2 months ago the users were experiencing server timeouts which
quickly got progressively worse.  At the time the server was a little short
on memory so the support staff doubled it.  The problem disappeared
immediately.  Now it has returned.  Started one day after 2 hours of work
with 10+ users.  A shutdown and restart of the server cured it and it didn't
return until the next day.  The next day it was worse, barely 5 users could
start working and would experience the same symptoms.  This time however the
problem remains even after a server shutdown and restart.  I tried applied
ADO Update 2, but it made no difference.

The activity they seem to having the most trouble with is posting records.
Queries for reference purposes, even intensive work fine.

They're using MS SQL 7.0 and MDAC 2.6.  I'm using Delphi 5 Ent. with ADO
Express Update 2.  Is there something I'm missing?  Since it was working and
now it's not, and there were no dramtic program changes I'm clueless.  Are
SQL indexes reliable?  How often should they be maintained?  Are there any
other things I can have them try?

They're pointing fingers at me, which is fine if I'm doing something wrong,
but no can figure out the problem to start.

Thanks for any pointers or help.

Ted

 

Re:ADO & MS SQL Server


Is you App Clint/Server o N-Tier?

Do you use TADOTables or TADODataset?

Do you get ALL records from the database?
Is best get small recordset.

"Ted Hale" <t...@srs-inc.net> escribi en el mensaje
news:3a96afd4_2@dnews...

Quote
> I have an app using an SQL Database (not under my control) via ADO.  The
> amount of users can be anywhere from 5 to 15 and the database is at around
> 200,000 records.

> About a 2 months ago the users were experiencing server timeouts which
> quickly got progressively worse.  At the time the server was a little
short
> on memory so the support staff doubled it.  The problem disappeared
> immediately.  Now it has returned.  Started one day after 2 hours of work
> with 10+ users.  A shutdown and restart of the server cured it and it
didn't
> return until the next day.  The next day it was worse, barely 5 users
could
> start working and would experience the same symptoms.  This time however
the
> problem remains even after a server shutdown and restart.  I tried applied
> ADO Update 2, but it made no difference.

> The activity they seem to having the most trouble with is posting records.
> Queries for reference purposes, even intensive work fine.

> They're using MS SQL 7.0 and MDAC 2.6.  I'm using Delphi 5 Ent. with ADO
> Express Update 2.  Is there something I'm missing?  Since it was working
and
> now it's not, and there were no dramtic program changes I'm clueless.  Are
> SQL indexes reliable?  How often should they be maintained?  Are there any
> other things I can have them try?

> They're pointing fingers at me, which is fine if I'm doing something
wrong,
> but no can figure out the problem to start.

> Thanks for any pointers or help.

> Ted

Re:ADO & MS SQL Server


Thanks for taking time to respond.

Primarily I use TADOQueries and some TADOStoredProcs though there is a rare
instance of TADOTable (though this is closed by the time the posting routine
is called.  Most of the queries and Stored Procs return a narrow set of
data, often just one record!  There are only a couple of instances where I
need the entire field roster so mostly they are subsets, specified as
"clUseClient" and "ctStatic".

Thanks again.

Ted

Quote
"Mario Alejandro" <ma...@usa.net> wrote in message news:3a96c6dc_1@dnews...
> Is you App Clint/Server o N-Tier?

> Do you use TADOTables or TADODataset?

> Do you get ALL records from the database?
> Is best get small recordset.

> "Ted Hale" <t...@srs-inc.net> escribi en el mensaje
> news:3a96afd4_2@dnews...
> > I have an app using an SQL Database (not under my control) via ADO.  The
> > amount of users can be anywhere from 5 to 15 and the database is at
around
> > 200,000 records.

> > About a 2 months ago the users were experiencing server timeouts which
> > quickly got progressively worse.  At the time the server was a little
> short
> > on memory so the support staff doubled it.  The problem disappeared
> > immediately.  Now it has returned.  Started one day after 2 hours of
work
> > with 10+ users.  A shutdown and restart of the server cured it and it
> didn't
> > return until the next day.  The next day it was worse, barely 5 users
> could
> > start working and would experience the same symptoms.  This time however
> the
> > problem remains even after a server shutdown and restart.  I tried
applied
> > ADO Update 2, but it made no difference.

> > The activity they seem to having the most trouble with is posting
records.
> > Queries for reference purposes, even intensive work fine.

> > They're using MS SQL 7.0 and MDAC 2.6.  I'm using Delphi 5 Ent. with ADO
> > Express Update 2.  Is there something I'm missing?  Since it was working
> and
> > now it's not, and there were no dramtic program changes I'm clueless.
Are
> > SQL indexes reliable?  How often should they be maintained?  Are there
any
> > other things I can have them try?

> > They're pointing fingers at me, which is fine if I'm doing something
> wrong,
> > but no can figure out the problem to start.

> > Thanks for any pointers or help.

> > Ted

Re:ADO & MS SQL Server


Ted,

What is your MarshallOptions set to?

What service pack are they running on SQL server?

Do you have your queries prepared? If so then try them unprepared as ADO
doesn't seem to prepare them correctly.

Are you performing your updates within a transaction? Are you performing
them via a stored proc?

Jeff

Re:ADO & MS SQL Server


MarshallOptions are "moMarshallAll"
SQL 7 w/ SP 2
Queries are prepared (I'll try it the other way around)
Updates are within the transaction

Thanks for any help.

Ted

Quote
"Jeff Wright" <jeffwri...@nospam.bigpond.com> wrote in message

news:3a9a31b2_2@dnews...
Quote
> Ted,

> What is your MarshallOptions set to?

> What service pack are they running on SQL server?

> Do you have your queries prepared? If so then try them unprepared as ADO
> doesn't seem to prepare them correctly.

> Are you performing your updates within a transaction? Are you performing
> them via a stored proc?

> Jeff

Re:ADO & MS SQL Server


Quote
> Do you have your queries prepared?
>... ADO doesn't seem to prepare them correctly.

What do you mean?

--
Vassil Nazarov
IT Manager, Sofia Cable SA

Other Threads