I am trying to fetch data from an Oracle database to SQL Server 2000
database.
The fetching does not take very long time (around 2 mins) if it's just
simple query eg:
select * from TABLE where DATE = MyDate

But when I try to put additional condition in the query:
select * from TABLE where DATE > to_date('2003-07-01
00:00:00','YYYY-MM-DD HH24:MI:SS')
The fetching on this takes around 30 mins.

The amount of data fetched is the same, but the loading takes almost
10 times longer.

Any idea what is wrong with this?

Regards, Erica.

Hello, Erica!

DTS should pass of this query to the Oracle provider and let it simply
return the results. I have heard that Oracle usage in DTS is at times slow
and people have reverted to Export to Text file | transfer | Import.

Check though that nothing in Oracle is getting stuck

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: But when I try to put additional condition in the query:
: select * from TABLE where DATE > to_date('2003-07-01
: 00:00:00','YYYY-MM-DD HH24:MI:SS')
: The fetching on this takes around 30 mins.

: The amount of data fetched is the same, but the loading takes almost
: 10 times longer.

: Any idea what is wrong with this?

---

Thanks for the reply.

I have experience this more than once, and it follows the same pattern.
As soon as I change the WHERE condition, the fetching is faster. So it
does not seem to be the Oracle connection problem =)

Any other opinion?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello, Erica!

I would be interested to see where the WHERE is being done. That said
though if it was done on the client it would pass the whole recordset back
which is what you do with an unrestricted load.

Have a look in Profiler to see if you can spot where the WHERE clause is
applied.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: I have experience this more than once, and it follows the same pattern.
: As soon as I change the WHERE condition, the fetching is faster. So it
: does not seem to be the Oracle connection problem =)

: Any other opinion?

--- AspNNTP 1.50 (ActionJackson.com)

Allan,

Can you please tell me how to run the Profiler. I am working only on SQL
and the Oracle database is accessible only by the account to run the
jobs, I can't access the Oracle database.

Is there anyway to run Profiler to Oracle from SQL DTS Packages? or just
from SQL Server 2000 ?

Regards, Erica.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You will not be able to see Oracle through Profiler but you should be able
to see SQL Server returning the records.

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Erica Santosaputri" <erica_santosa (AT) yahoo (DOT) com> wrote