Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kowani1
Starting Member
42 Posts |
 Posted - 2007-05-29 : 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 =7Please 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 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-29 : 23:07:23
|
| What's the problem? |
 |
|
|
kowani1
Starting Member
42 Posts |
Posted - 2007-05-29 : 23:27:58
|
| The issue here is that the query is taking forever to run.. Eventually timing out. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-05-30 : 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
491 Posts |
Posted - 2007-05-30 : 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 =7u r missing 2 aliases for the 2 last lines. |
|
|
|
|
|
|
|
|
Help with this SQL Query - 4 Part naming