SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help with this SQL Query - 4 Part naming
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kowani1
Starting Member

42 Posts

Posted - 05/29/2007 :  23:03:39  Show Profile  Visit kowani1's Homepage  Click to see kowani1's MSN Messenger address  Reply with Quote
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  Show Profile  Reply with Quote
What's the problem?
Go to Top of Page

kowani1
Starting Member

42 Posts

Posted - 05/29/2007 :  23:27:58  Show Profile  Visit kowani1's Homepage  Click to see kowani1's MSN Messenger address  Reply with Quote
The issue here is that the query is taking forever to run.. Eventually timing out.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 05/30/2007 :  07:41:12  Show Profile  Reply with Quote
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.
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 05/30/2007 :  15:46:07  Show Profile  Visit funketekun's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000