Latency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Latency

Author

Topic

jhogan
Starting Member

3 Posts

Posted - 2007-06-18 : 19:33:19

We have been experiencing extreme latency when running DTS packages importing information from an Oracle database into our SQL database. The network gurus have been checking routers, switches, bandwidth etc. and have found no problems. The problem started about 3 months ago.

Today I ran a simple query using two different tools

I used the SQL Import wizard and the Crystal SQL Designer. I used the same computer and the same odbc connection to pull the data.

Crystal returned the data in 3 minutes, where the SQL Import took 1 hour and 18 minutes to pull 84,000 rows. My larger processes are taking 26-30 hours to complete.

Suggestions where to begin looking for a solution to this problem would be greatly appreciated. I am a drowning data analyst, not a dba, so please keep things as simple as possible.

Thanks,

Jennifer

SELECT

EPISODES.CLIENT_NUMBER,

EPISODES.OPENING_DATE,

EPISODES.CLOSING_DATE,

EPISODES.EPISODE_STATUS_FLAG,

EPISODES.REPORTING_UNIT

FROM

EPISODES EPISODES

WHERE

EPISODES.EPISODE_STATUS_FLAG = 'O'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-18 : 22:16:17

Perhaps the issue has nothing to do with how long it takes to get the data from the Oracle database. Maybe the slowness is due to how long it takes to insert the data into your SQL Server database.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 03:34:10

Are there significantly more rows to insert each time than there used to be 3 months ago?

Has the database recovery model changed from Simple to Full?

Is some DBA Shrinking the Logs (perhaps because the Oracle log is making them huge)? (In which case your import is going to have to re-extend them each time, and that's a slow, resource-hungry operation in SQL 2000)

Adjust the Batch size maybe?

Kristen

jhogan
Starting Member

3 Posts

Posted - 2007-06-19 : 18:01:52

I pull large extracts between the 1st and 10th of each month and the processes were running fine in March and in April they were dog slow. My largest process going from about 4 hours to 26 - 30 hours to complete.

Multiple changes were made during the time between my runs, including the VAX server that houses the Oracle database being moved from California to Texas. I believe that is why we have been chasing the external network for a solution, but I know we also had some space issues with the SQL server and I know that at least one database was deleted and I believe the tech also deleted log files.

If it is the log files having to be re-extended is that a process that is not as resource-hungry with SQL 2005? The reason I ask is we are setting up a new server and SQL 2005 is a possibility.

Thanks,
Jennifer

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 05:22:18

I believe it is not as resource hungry with SQL 2005. However, once the logs have been extended (i.e. for a monthly run) and provided that a) some DBA doesn't Shrink them

and b) next month's activity is not significantly bigger, then that log space will get reused.

Might be worth forcing a query against the Oracle database to "nowhere" to see if it is the network that is dog slow perhaps?

I imagine you could run a SELECT of the appropriate data into "working variables", so in effect each Row received would overwrite the previous rows value in the working variable. This would simulate the oracle and Network effort in acquiring the data, and leave the local "Insert" activity out of the equation.

If that takes 23 hours then your problem is with the Network, or maybe the Oracle server itself.

OTOH if that is "instant" then your problem is with the local Insert.

Kristen

jhogan
Starting Member

3 Posts

Posted - 2007-06-21 : 11:40:05

I presented your solution to our DBA and Network admin, but didn't get a response.

I would like to try the Select statement with working variables, but as a data analyst I spend more time looking at the data retrieved than writing the stored procedures and DTS packages. Could you help me with the syntax.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-06-21 : 11:54:45

A pass-through query to Oracle will look like this:


SELECT	ColA, ColB
FROM	OPENQUERY(MyOracleLinkedServer, 'SELECT ColA, ColB FROM OracleDatabase.MyTable')

and I expect you could do:


DECLARE	@StartTime datetime,
	@TempColA varchar(1000)		-- Use approrpriate datatype!!
SELECT	@StartTime = GetDate()
SELECT	@TempColA = ColA
FROM	OPENQUERY(MyOracleLinkedServer, 'SELECT ColA, ColB FROM OracleDatabase.MyTable')
SELECT	[TotalRows] = @@ROWCOUNT,
	[ElapsedTime] = DATEDIFF(Second, @StartTime, GetDate())

which should pull all the rows from Oracle, and stuff ColA for each one into @TempColA, and then print a Row Count of how many rows were received.

You'll need to know the name of the Linked Server (MyOracleLinkedServer). Assuming you have permission this may give you a clue:

EXEC sp_linkedservers

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources