|
Home > Archive > MS SQL Server > February 2006 > ORDER BY is very slow when querying linked Server Oracle9i
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| Uri Dimant 2006-02-28, 8:23 pm |
| 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@microso
ft.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@microso
ft.com...
>
>
>
|
|
|
|
|