I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
Showing posts with label querying. Show all posts
Showing posts with label querying. Show all posts
Monday, March 26, 2012
ORDER BY is very slow when querying linked Server Oracle9i
I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal ?
code below
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEW
Hi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal ?
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>
|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
sql
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal ?
code below
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEW
Hi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal ?
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>
|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
sql
ORDER BY is very slow when querying linked Server Oracle9i
I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
> >I am creating 3 views from the linked server
> > and then I have a question at the bottom returning results.
> > If I exclude the ORDER BY from my question the query will take about 40
> > seconds,
> > with the ORDER BY it takes about an hour to finish.
> >
> > Is this normal '
> >
> > code below
> > --
> >
> > create view xxxyyy1 as
> > SELECT INSATSID, STATUS
> > FROM HACTAR..EKOP2.INSATS
> >
> > go
> >
> > create view xxxyyy2 as
> > SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> >
> > go
> >
> > create view xxxyyy3 as
> > select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> > 'INK905_STATUS'
> > from xxxyyy1 A left outer join xxxyyy2 B
> > on a.insatsid = b.insats
> > where a.status <> b.status
> > go
> >
> > SELECT
> > A.INSATSID,
> > A.STATUS_OLD,
> > A.STATUS_NEW,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > B.INSATSID,
> > B.STATUS,
> > B.INK905_INSATS,
> > B.INK905_STATUS
> > FROM HACTAR..EKOP2.INSATSLOG A
> > right outer join xxxyyy3 B
> > on A.INSATSID = B.INSATSID
> > WHERE A.ANDR_DATUM < 20060225
> > ORDER BY
> > A.INSATSID,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > A.STATUS_OLD,
> > A.STATUS_NEW
> >
>
>
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
> >I am creating 3 views from the linked server
> > and then I have a question at the bottom returning results.
> > If I exclude the ORDER BY from my question the query will take about 40
> > seconds,
> > with the ORDER BY it takes about an hour to finish.
> >
> > Is this normal '
> >
> > code below
> > --
> >
> > create view xxxyyy1 as
> > SELECT INSATSID, STATUS
> > FROM HACTAR..EKOP2.INSATS
> >
> > go
> >
> > create view xxxyyy2 as
> > SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> >
> > go
> >
> > create view xxxyyy3 as
> > select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> > 'INK905_STATUS'
> > from xxxyyy1 A left outer join xxxyyy2 B
> > on a.insatsid = b.insats
> > where a.status <> b.status
> > go
> >
> > SELECT
> > A.INSATSID,
> > A.STATUS_OLD,
> > A.STATUS_NEW,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > B.INSATSID,
> > B.STATUS,
> > B.INK905_INSATS,
> > B.INK905_STATUS
> > FROM HACTAR..EKOP2.INSATSLOG A
> > right outer join xxxyyy3 B
> > on A.INSATSID = B.INSATSID
> > WHERE A.ANDR_DATUM < 20060225
> > ORDER BY
> > A.INSATSID,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > A.STATUS_OLD,
> > A.STATUS_NEW
> >
>
>
Wednesday, March 7, 2012
Oracle Linked Server poor performance
I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
Any ideas would be appreciated
You don't specify how you are querying the Oracle database but I would guess you are using a 4 part name query against the Oracle data source. Try changing your query to use OpenQuery instead. That is also closer to how a pass-through query in Access would execute.
-Sue
|||Thanks I will give it a go. Is using th 4 part name a bad way to query?
<edit> WOW that is much better thanks very much for your help </edit>
|||In and of itself, the four part name isn't necessarily a bad thing but you can send the query over to the server to be executed on the remote data source and force more processing, data filtering on the server. So you can push more processing off to the server. Some people have a hard time with Openquery as the flip side is that you aren't sending transact sql - you need to send the sql syntax used by the remote data source, the other database platform. I've found with Oracle data sources, it's generally better to just use Openquery.
-Sue
|||Sue - thanks very much you have been very helpful. My query now runs in under 15 seconds now.
Subscribe to:
Posts (Atom)