Fixing a slow, brute force set of SQL calls

I've got a huge inefficiency in my code that I'm trying to fix. I'm coding in VB.NET using ASP.NET and an MSSQL 2000 server.

I'm working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record's fields in the temporary table.

Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I'm dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.

Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?

Thanks in advance for whatever help you can give,
-StarwizMy suggestions would be to use this sort of SQL command:

DELETE
FROM
 myTable
INNER JOIN
 #myTempTable ON #myTempTable.column1 = myTable.column1 <etc>

Terri|||Wouldn't this delete the records from the temporary table, too?|||No, the FROM clause specifies the table to use for the DELETE statement. Only the records from myTable matching the JOIN condition will be deleted.

Terri|||Okay then...but I can't even run it to see, since I get an error:

With the statement:
Delete from PPC inner join PPCTemp on PPC.searchengine = PPCTemp.searchengine and PPC.[date] = PPCTemp.[date] and PPC.keyword = PPCTemp.keyword

I get the error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'inner'.

Any ideas?|||That's because I gave you the wrong syntax and somewhat incorrect information ;-) The table name also has to follow the DELETE statement, and the table name following the DELETE is the one from which records are deleted:

Delete PPC from PPC inner join PPCTemp on PPC.searchengine = PPCTemp.searchengine and PPC.[date] = PPCTemp.[date] and PPC.keyword = PPCTemp.keyword

You should check out the DELETE topic in SQL Server Books Online for more background information on this topic.

Terri|||With the right syntax, it works great (lol)...a million times faster, too!

Thanks a lot.

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.
Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegr oups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent run
s? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>

First run of reports is SLOW

Hello all
I realize that the first time you run a report from RS it's slow because it
has to load everything up. Is there some way around this? Can I setup a
report to refresh itself every so often?
Anyway to turn this off? I hate the 30 second wait time to view the first
report...That is what I do, the official work around didn't work for me (IIS
configuration). If you have a report autorefresh every 5 minutes that will
keep everything ready to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> Hello all
> I realize that the first time you run a report from RS it's slow because
> it
> has to load everything up. Is there some way around this? Can I setup a
> report to refresh itself every so often?
> Anyway to turn this off? I hate the 30 second wait time to view the first
> report...|||Link for official work around?
"Bruce L-C [MVP]" wrote:
> That is what I do, the official work around didn't work for me (IIS
> configuration). If you have a report autorefresh every 5 minutes that will
> keep everything ready to go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> > Hello all
> >
> > I realize that the first time you run a report from RS it's slow because
> > it
> > has to load everything up. Is there some way around this? Can I setup a
> > report to refresh itself every so often?
> >
> > Anyway to turn this off? I hate the 30 second wait time to view the first
> > report...
>
>|||I guess it is not "official" it is just less of a hack. I.e. adjust certain
IIS settings etc. Below is the posting from someone else describing this,
but I just have a report on my box that refreshes every 5 minutes (set this
via report properties I believe).
>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:459E7222-BA43-436A-8980-96934C6FAC02@.microsoft.com...
> Link for official work around?
>
> "Bruce L-C [MVP]" wrote:
>> That is what I do, the official work around didn't work for me (IIS
>> configuration). If you have a report autorefresh every 5 minutes that
>> will
>> keep everything ready to go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
>> > Hello all
>> >
>> > I realize that the first time you run a report from RS it's slow
>> > because
>> > it
>> > has to load everything up. Is there some way around this? Can I setup
>> > a
>> > report to refresh itself every so often?
>> >
>> > Anyway to turn this off? I hate the 30 second wait time to view the
>> > first
>> > report...
>>|||Thanks Bruce. If the "hack" doesn't work I will just setup a report and have
it refresh every few minutes or so.
"Bruce L-C [MVP]" wrote:
> I guess it is not "official" it is just less of a hack. I.e. adjust certain
> IIS settings etc. Below is the posting from someone else describing this,
> but I just have a report on my box that refreshes every 5 minutes (set this
> via report properties I believe).
> >>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:459E7222-BA43-436A-8980-96934C6FAC02@.microsoft.com...
> > Link for official work around?
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> That is what I do, the official work around didn't work for me (IIS
> >> configuration). If you have a report autorefresh every 5 minutes that
> >> will
> >> keep everything ready to go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John" <John@.discussions.microsoft.com> wrote in message
> >> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> >> > Hello all
> >> >
> >> > I realize that the first time you run a report from RS it's slow
> >> > because
> >> > it
> >> > has to load everything up. Is there some way around this? Can I setup
> >> > a
> >> > report to refresh itself every so often?
> >> >
> >> > Anyway to turn this off? I hate the 30 second wait time to view the
> >> > first
> >> > report...
> >>
> >>
> >>
>
>|||I wouldn't call adjusting the properties of the app pool a "hack", but
really, "configuration".|||The reason I called it less of a hack is because the configuration of itself
doesn't do it. The last step was creating a subscription that ran every day.
My solution is a total hack but it is easy to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"octavius@.gmail.com" <Octavius@.gmail.com> wrote in message
news:1126656232.440168.308260@.o13g2000cwo.googlegroups.com...
>I wouldn't call adjusting the properties of the app pool a "hack", but
> really, "configuration".
>

First Hit to SQL Reporting Services - Slow Response Time

The first time each day that a user accesses SQL Reporting Services 2005, it
takes up to 3 minutes to load the SQL Reporting web interface. After the
first hit, it loads much faster. Is there a way to speed up the intial load?This is an IIS issue. After a certain time it unloads RS. Note that as far
as IIS is concerned RS is just another asp.net application.
Go to IIS Manager, under Applocation Pools node, right click
"DefaultAppPool" in which the Reporting Server work process is running,
select properties. On "Performace" tag, you will see, by default, the app
pool will shut down if > being idle for 20 min. You can extend this time to
8x60min 480min, so that the app pool will not shut down for a regular
working day. However, the first report reader of the day, will hit the
delay. You may schedule a dummy report at beginning of a work day for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chili86@.discussions.microsoft.com"
<Chili86@.discussions.microsoft.com@.discussions.microsoft.com> wrote in
message news:26DF80C2-7E46-4A7F-AEA1-57E36697D155@.microsoft.com...
> The first time each day that a user accesses SQL Reporting Services 2005,
> it
> takes up to 3 minutes to load the SQL Reporting web interface. After the
> first hit, it loads much faster. Is there a way to speed up the intial
> load?

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1.Downloaded and Installed but not using the MS Office interop stuff (Client)
2.Downloaded and Installed MDAC 2.8(Client)
3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
Jim
Please refer to original. Sent duplicate in error. Sorry
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000. Since then the following has occurred.
> 1.Downloaded and Installed but not using the MS Office interop stuff (Client)
> 2.Downloaded and Installed MDAC 2.8(Client)
> 3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB and
> it also takes 15 seconds before displaying the table objects. However, if I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes 15
> seconds to display the tables. Once the tables are displayed, access to data
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If I
> restart my application after 15 seconds, then it takes 15 seconds to connect.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click the
> nodes etc. However, if I right click on the database (or any of the standard
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000.
Jim
PLease Refer to my original. Sent Duplicate in error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1.Downloaded and Installed but not using the MS Office interop stuff (Client)
2.Downloaded and Installed MDAC 2.8(Client)
3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
Jim
PLEASE REFER TO MY ORIGINAL. I submitted 4. The first 3 came back with
Posting Error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000. Since then the following has occurred.
> 1.Downloaded and Installed but not using the MS Office interop stuff (Client)
> 2.Downloaded and Installed MDAC 2.8(Client)
> 3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB and
> it also takes 15 seconds before displaying the table objects. However, if I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes 15
> seconds to display the tables. Once the tables are displayed, access to data
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If I
> restart my application after 15 seconds, then it takes 15 seconds to connect.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click the
> nodes etc. However, if I right click on the database (or any of the standard
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if
I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server
.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1. Downloaded and Installed but not using the MS Office interop stuff (Clien
t)
2. Downloaded and Installed MDAC 2.8 (Client)
3. Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect
.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
JimPLEASE REFER TO MY ORIGINAL. I submitted 4. The first 3 came back with
Posting Error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater i
f I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The dela
y
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. Howeve
r
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Serv
er.
> I have not always had this problem. Unfortunately, I can’t say what I di
d
> that caused it, but the problem did not exist when I was running VS.Net 20
03
> and the Server was running MSDE 2000. Since then the following has occurre
d.
> 1. Downloaded and Installed but not using the MS Office interop stuff (Cli
ent)
> 2. Downloaded and Installed MDAC 2.8 (Client)
> 3. Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB a
nd
> it also takes 15 seconds before displaying the table objects. However, if
I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes
15
> seconds to display the tables. Once the tables are displayed, access to da
ta
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If
I
> restart my application after 15 seconds, then it takes 15 seconds to conne
ct.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click t
he
> nodes etc. However, if I right click on the database (or any of the standa
rd
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if
I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server
.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000.
--
JimPLease Refer to my original. Sent Duplicate in error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater i
f I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The dela
y
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. Howeve
r
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Serv
er.
> I have not always had this problem. Unfortunately, I can’t say what I di
d
> that caused it, but the problem did not exist when I was running VS.Net 20
03
> and the Server was running MSDE 2000.
> --
> Jim