|
|
You're in the
sql server dts
group:dts quick to access 97 but slow to access 2003
sql server dts:
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?
Jet driver differences?
"Andrew Brill" <dragon@ReplyToNewsGroup.dragon2000.co.uk> wrote in message news:#fFzCtNxFHA.2792@tk2msftngp13.phx.gbl: [quoted text, click to view] > 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?
[quoted text, click to view]
"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.
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. [quoted text, click to view] "Andrew Brill" <dragon@ReplyToNewsGroup.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... >> 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. >
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. [quoted text, click to view] "Andrew Brill" <dragon@ReplyToNewsGroup.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@ReplyToNewsGroup.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... >>> 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. >> > >
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@ReplyToNewsGroup.dragon2000.co.uk> wrote in message news:OAF#vZQxFHA.1168@TK2MSFTNGP15.phx.gbl: [quoted text, click to view] > 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@ReplyToNewsGroup.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@ReplyToNewsGroup.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... > >>> 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. > >> > > > >
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. [quoted text, click to view] "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@ReplyToNewsGroup.dragon2000.co.uk> wrote in message > news:OAF#vZQxFHA.1168@TK2MSFTNGP15.phx.gbl: > >> 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@ReplyToNewsGroup.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@ReplyToNewsGroup.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... >> >>> 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. >> >> >> > >> > >
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@ReplyToNewsGroup.dragon2000.co.uk> wrote in message news:#Ho$DUZxFHA.3152@TK2MSFTNGP10.phx.gbl: [quoted text, click to view] > 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@ReplyToNewsGroup.dragon2000.co.uk> wrote in message > > news:OAF#vZQxFHA.1168@TK2MSFTNGP15.phx.gbl: > > > >> 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@ReplyToNewsGroup.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@ReplyToNewsGroup.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... > >> >>> 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. > >> >> > >> > > >> > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |