| Author |
Topic |
|
nemohm
Yak Posting Veteran
69 Posts |
 Posted - 2004-01-21 : 10:45:28
|
| 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-21 : 12:17:39
|
| The performance problems could be caused by lack of indexes on the Oracle side. You need to investigate the queries on the Oracle side first.Tara |
 |
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2004-01-21 : 12:51:49
|
| Same all queries executed directly in Oralce take less than a second to return result.Indexes in Oracle are on place, but are they used via linked servers queries? Dobromir |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-21 : 12:53:00
|
| What does the execution plan show in Query Analyzer?Tara |
|
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2004-01-21 : 18:16:34
|
| The execution plan shows no difference between queries.Dobby |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-21 : 18:26:46
|
| But what does it show? Table scans?Tara |
|
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2004-01-22 : 09:42:23
|
| The execution plan shows: Row count 1 on the shortest query, and full table scans on others.My understanding is that indexes are used only (but always) with the query on 'APCEPKS', but never with any other queries.What makes queries to differ?Is there a way to force a query to use a particular index?Thx,Dobby |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-01-22 : 11:30:22
|
| I wonder if the table you're hitting in Oracle might be partitioned. That might explain the difference. The table containing 'APCEPKS' may have an index on the right column but perhaps not on the other tables in the partition. Just a guess.Mike"oh, that monkey is going to pay" |
|
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2004-01-22 : 11:59:01
|
| The table in Oracle is not partitioned.Dobby |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 12:26:19
|
| The value that you are using the query does not affect which index it selects to use. It's the WHERE clause and MAX in your case that affects it but not the value of SERVERNAME. So is there an index on ID and SERVERNAME?Tara |
|
|
|
nemohm
Yak Posting Veteran
69 Posts |
Posted - 2004-01-22 : 14:37:09
|
| ID, SERVERNAME IS a PK;SERVERNAME, ID is an index.Dobby |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-22 : 14:40:37
|
| Have you tried using an index hint? I've never done it with linked servers though. How many rows are we talking about being returned for each query? Do the first two queries return a lot more data than the third? If so, then the difference in performance would be due to the number of rows being returned.Tara |
|
|
|
|
Performance question