| Author |
dts quick to access 97 but slow to access 2003
|
|
| Andrew Brill 2005-09-29, 7:24 am |
| There are 3 machines involved, the sql server, a file server holding the
access databases and a client installation of sql server.
We have dts packages designed to export data from the sql server to the
access databases. These were created while the databases were in 97 format
and were pretty quick. We have now upgraded these to 2003 format and its
taking literally hours to run the packages. Manages about 1000 rows per
fifteen seconds.
Has anyone any ideas why?
| |
| Allan Mitchell 2005-09-29, 7:24 am |
| Jet driver differences?
"Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in
message news:#fFzCtNxFHA.2792@tk2msftngp13.phx.gbl:
> There are 3 machines involved, the sql server, a file server holding the
> access databases and a client installation of sql server.
>
> We have dts packages designed to export data from the sql server to the
> access databases. These were created while the databases were in 97 format
> and were pretty quick. We have now upgraded these to 2003 format and its
> taking literally hours to run the packages. Manages about 1000 rows per
> fifteen seconds.
>
> Has anyone any ideas why?
| |
| Andrew Brill 2005-09-29, 9:24 am |
| "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OIG$ZcOxFHA.2932@TK2MSFTNGP10.phx.gbl...
> Jet driver differences?
>
Is there anything I can do about these differences?
I note that it's quick if I use ODBC to connect to the database rather than
setting Microsoft Access (with access 97 icon) as the Data Source. I'm not
too familiar with ODBC but it looks like this unfortunately requires me to
setup a DSN for every database file on every client machine.
| |
| Andrew Brill 2005-09-29, 11:23 am |
| I have noticed the network usage during this is excessive.
It's using 40% of the client and the file servers 100mbs connection network.
By my calculation, in the hour it runs for it could transfer 18Gb in that
time and it's only creating a 50Mb database.
The SQL Server itself is using a fraction of a percent - although it's page
file usage is 1gb and it only has 1gb of ram.
The CPU occupancy is nothing on the sql server, 30% on the file server and
60% on the client. That's a fair amount of processing over an hour too.
"Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in message
news:O1gwcDPxFHA.3860@TK2MSFTNGP09.phx.gbl...
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:OIG$ZcOxFHA.2932@TK2MSFTNGP10.phx.gbl...
>
> Is there anything I can do about these differences?
> I note that it's quick if I use ODBC to connect to the database rather
> than setting Microsoft Access (with access 97 icon) as the Data Source.
> I'm not too familiar with ODBC but it looks like this unfortunately
> requires me to setup a DSN for every database file on every client
> machine.
>
| |
| Andrew Brill 2005-09-29, 11:23 am |
| The problem seems to come from involving a 3rd machine. If the destination
file is on the sql server or the client computer then its fine. If the file
is on any other machine on the network then it slows to a crawl.
"Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in message
news:e2Em6VQxFHA.1284@tk2msftngp13.phx.gbl...
>I have noticed the network usage during this is excessive.
> It's using 40% of the client and the file servers 100mbs connection
> network. By my calculation, in the hour it runs for it could transfer 18Gb
> in that time and it's only creating a 50Mb database.
>
> The SQL Server itself is using a fraction of a percent - although it's
> page file usage is 1gb and it only has 1gb of ram.
>
> The CPU occupancy is nothing on the sql server, 30% on the file server and
> 60% on the client. That's a fair amount of processing over an hour too.
>
>
> "Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in message
> news:O1gwcDPxFHA.3860@TK2MSFTNGP09.phx.gbl...
>
>
| |
| Allan Mitchell 2005-09-29, 1:23 pm |
| So if you write back to the source server from where the file originates
then the perf is good. If you write out to A.N.Other server then the
perf goes through the floor?
Is ALL taffic to this "3rd" server slow? What about copying a file in
Windows Explorer?
Allan
"Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in
message news:OAF#vZQxFHA.1168@TK2MSFTNGP15.phx.gbl:
[color=darkred]
> The problem seems to come from involving a 3rd machine. If the destination
> file is on the sql server or the client computer then its fine. If the file
> is on any other machine on the network then it slows to a crawl.
>
>
>
> "Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in message
> news:e2Em6VQxFHA.1284@tk2msftngp13.phx.gbl...
| |
| Andrew Brill 2005-09-30, 3:23 am |
| Other access to the 3rd server is fine. And if I host the destination DB on
any other workstation the performance is lousy too. It seems to kill it if
I involve any 3 machines in the process but any two seem OK. I think I'm
just going to move the destination databases onto the SQL server - all the
paths in the packages use mapped drives so its easy to move from one machine
to another by changing the mapping.
"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OBrqsGSxFHA.3152@TK2MSFTNGP10.phx.gbl...
> So if you write back to the source server from where the file originates
> then the perf is good. If you write out to A.N.Other server then the perf
> goes through the floor?
>
> Is ALL taffic to this "3rd" server slow? What about copying a file in
> Windows Explorer?
>
> Allan
>
>
> "Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in message
> news:OAF#vZQxFHA.1168@TK2MSFTNGP15.phx.gbl:
>
>
| |
| Allan Mitchell 2005-09-30, 7:23 am |
| Does sound strange doesn't it? The "3 sites involved" should not be the
issue here from a DTS viewpoint but something is amiss. I think you are
probably taking the right approach.
--
----------------------------------------
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
"Andrew Brill" < dragon@ReplyToNewsGr
oup.dragon2000.co.uk> wrote in
message news:#Ho$DUZxFHA.3152@TK2MSFTNGP10.phx.gbl:
[color=darkred]
> Other access to the 3rd server is fine. And if I host the destination DB on
> any other workstation the performance is lousy too. It seems to kill it if
> I involve any 3 machines in the process but any two seem OK. I think I'm
> just going to move the destination databases onto the SQL server - all the
> paths in the packages use mapped drives so its easy to move from one machine
> to another by changing the mapping.
>
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:OBrqsGSxFHA.3152@TK2MSFTNGP10.phx.gbl...
|
|
|
|