Import Slow
-
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'
-
What does your DTS Package look like? How are you importing the data? Are you querying across a linked server or using a Transform Data Task?
Please describe every step of the process or export it to a file and send it to me so I can look at it directly.
If you don't feel you can adequately describe the DTS Package and can't send it to me, can you take a screen shot of the various pieces? The properties view of every connection object and every task being performed.
Also, what is the schema of the data being selected from the Oracle database? What is the schema of the where the data is being put in SQL Server?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
That does sound strange. As Robert mentioned, I'd ask for more details on what you're doing in the wizard. I might even try a simple DTS package, with 2 connections and the task between them.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
My DTS package is about as simple as I could make it:
Open the import wizard, select other (odbc data source) and select the database from the list, enter my user name and password. (The database is in my System DNS Data Sources list and is the same source as I used for the crystal query)
I then select the local test database as the destination; select use a query to specify the data to transfer. I did not change any of the properties of any of the fields being copied.
Any suggestions?
Thanks,
Jennifer
-
Jennifer, try using the OLE connector with the Oracle connection and see if that makes a difference. Thanks.
Chris
-
DTS is slow, period. Especially when you pull data out of Oracle. DTS has to extract data into ASCII format meaning you are querying Oracle.
May be another approach should be taken like:
Check how long it takes to run only the query remotely from machine where DTS is running.
May be try using bcp instead which is faster. You could try using fastreader wisdomforce to extract data faster.
-
I pull more than a half-million rows from Oracle to SQL using a linked server and an INSERT/SELECT in less than 2 minutes... sure you want to stick with DTS?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
It might be extremely enlightening to know how many records are in the SQL Server table that is the target for this data. I've often found that people forget that as a table grows in size the time required to insert/update/delete rows increases. Is it possible months back your table was small and now it's very large?
If so you should consider dropping any indexes on the table prior to importing additional data and then recreating the indexes after the import is complete.
Oh and when DTS is used appropriately, it can be quite fast, as fast or faster in some cases than using linked servers.
-
Have you tried to run the query directly in Oracle? Not saying that it is or isn't the problem, but if the query is slow, doesn't matter what else you do, your wasting your time.
-
Jeff,
I'm also using linked server w/ INSERT/SELECT stored procedures to extract a couple of dozen Oracle tables to SQL Server overnight but I am getting horrible performance (~7hrs for half a million rows but only 20 minutes through TOAD)
I am using SQL Server 2000 on Windows Server 2003 Standard Edition connecting to Oracle 9.2.0.5. The linked server is using the Microsoft OLE DB Provider for Oracle w/ Data Access, RPC, RPC Out, Use Remote Collation all enabled and Connection Timeout = 0 and Query Timeout = 0.
This has caused me a ridiculous amount of aggravation and any help would be extremely appreciated.
Tai.
-
Do you have triggers on the destination table? Have you tried importing the SAME data into a blank table, or one with no validation, or indexes? Sometimes it's not the select that is the issue; more often it's the insert part that has the problem.
Your primary key and/or its fill factor may have a lot to do with it as well. If the inserts are causing a large amount of page splits, you may be causing a lot more activity than meets the eye. Perhaps look at putting the fill factor down, or use a primary key that will put the inserts all at the end of the file (versus something that forces a lot of page splits.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
"20 minutes through Toad" doesn't use linked servers... it uses direct connections. Are you trying to join Oracle tables though the linked servers? That's probably not going to work so hot... I've found that the best thing to do is to copy the data from Oracle into local SQL Server tables and do the joins from there.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
All -
Select <> Insert. You're comparing apples to oranges in that a select is a simple data retrieval operation limited only by the time for your source database server to generate the results and then chunk them back to you across the wire whereas an import into the target database has a whole lot more overhead... at this point I'd be looking into what is going on at the target server.
The first things I would look into:
(1) database recovery model, what mode is your database running in during the load? Is it possible to put the database into bulk-logged mode during your load?
(2) DTS, check things like batch size, tablock, keep nulls, etc. Ideally you want to be using a bulk operation to load this data. Use a batch size that allows for the target server to commit as frequently as necessary to reduce writes to the database log.
(3) Data types/conversions - how compatible are the data types between the source and target servers, any conversions going on behind the scenes?
(3) Triggers or other operations on the target table.
(4) Server performance, what does the target server "look like" while your inserts are running? CPU, disk activity, what else is going on at the same time, etc.?
Joe
-
Thanks for the reply Jeff.
I only put the comment about TOAD as a comparison to what I was getting through linked servers in SQL Server. We have no joins, just simple select statements some with date filters and some without.
We actually import data from Oracle using both stored procedures and DTS (I haven't even started to look at our DTS performance yet). The code in our stored procedures (executed though scheduled overnight jobs) look like this although sometimes we use OPENQUERY due to time stamp issues between SQL & Oracle:
------------------
insert into #BLM
select
BLM_BATCH_NO,
BLM_SEQ_NO,
BLM_MOVEMENT_ID,
BLM_CORRECTED_IND,
BLM_BLI_ITEM_NO_FROM,
BLM_FROM_AMOUNT_BF,
BLM_FROM_AMOUNT,
BLM_FROM_AMOUNT_MINOR,
BLM_BLI_ITEM_NO_TO,
BLM_TO_AMOUNT_BF,
BLM_TO_AMOUNT,
BLM_TO_AMOUNT_MINOR,
BLM_PMO_MONTH,
BLM_PMO_YEAR,
BLM_WOFF_REASON
from ORACLE..SENATOR.BLM_LEDGER_MOVEMENT
------------------
I have tested the same above code in Query Analyser without the insert, just the select and the performance difference is insignificant.
Thanks for any advice.
-
>>Performance differens is insignificant...
Is that good or bad?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply