| Author |
 Topic  |
|
|
kowani1
Starting Member
42 Posts |
 Posted - 05/29/2007 : 23:03:39
|
Hi DBA's,
I have created a linked server; 'ORA9' using the MSDORA drivers to connect to an Oracle 9i Database. Now, I have used the 4 part query in the following and it takes forever; eventually timing out.
I know this is not a very efficient query, but because I am not good with optimising SQL queries, I cannot be able to make this query perform any faster. I know 'openqueries' are an option but don't know where to start.
Here is the query:
SELECT SAMPLES.SAMPLE_DESC,SAMPLES.QC, RESULTS.AVG_RES, RESULTS.AVG_UNITS
FROM
ORA9..DATA.JOBS JOBS, ORA9..DATA.SAMPLES SAMPLES,
ORA9..DATA.RESULTS RESULTS
WHERE SAMPLES.JOB_ID = JOBS.JOB_ID AND SAMPLES.QC = 'S' AND RESULTS.JOB_ID = SAMPLES.JOB_ID AND RESULTS.SAMPLE_ID = SAMPLES.SAMPLE_ID
AND RESULTS.TEST_ID = 23
AND GE_PATTERN_NO = 'DDH950' AND GE_BATCH_NO =7
Please help me optimise this query so it can run more efficiently and give me results.
PS. The tables are fairly small, and I know select * is not a best query method, but I want to use that for starters.
Thanks.
Jungle DBA |
Edited by - kowani1 on 05/29/2007 23:09:23
|
|
|
rmiao
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 05/29/2007 : 23:07:23
|
| What's the problem? |
 |
|
|
kowani1
Starting Member
42 Posts |
Posted - 05/29/2007 : 23:27:58
|
| The issue here is that the query is taking forever to run.. Eventually timing out. |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 05/30/2007 : 07:41:12
|
How fast does this execute direct on Oracle? If the raw query is poor there, it won't get any faster by running it across an odbc link from SQL.
The problem can be either a poorly structured query, a poorly optimised database, poor drivers, poor hardware or poor network cards.
Try to eliminate 1/more of these and you'll be in a better position to solve this. |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 05/30/2007 : 15:46:07
|
SELECT SAMPLES.SAMPLE_DESC,
SAMPLES.QC,
RESULTS.AVG_RES,
RESULTS.AVG_UNITS
FROM ORA9..DATA.JOBS JOBS,
ORA9..DATA.SAMPLES SAMPLES,
ORA9..DATA.RESULTS RESULTS
WHERE SAMPLES.JOB_ID = JOBS.JOB_ID
AND RESULTS.JOB_ID = SAMPLES.JOB_ID
AND RESULTS.SAMPLE_ID = SAMPLES.SAMPLE_ID
AND SAMPLES.QC = 'S'
AND RESULTS.TEST_ID = 23
AND GE_PATTERN_NO = 'DDH950'
AND GE_BATCH_NO =7
u r missing 2 aliases for the 2 last lines. |
|
|
| |
Topic |
|
Help with this SQL Query - 4 Part naming
