Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby

---

|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:[QUOTE]
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
>
> database, via
> submit the
> the same?
> 2000
> SERVERNAME
> SERVERNAME
> SERVERNAME|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

sql