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 |
|
NguyenL71
Posting Yak Master
228 Posts |
 Posted - 2009-01-07 : 13:30:34
|
| Hi,When I run the step#1 below without using NOT EXISTS clause the performance adequate butwhen I add NOT EXISTS clause into the query is taking 5 - 10 min to return results back even tblM_Process_Details table has only a few thousand rows. Is there another way to convert using NOT EXISTs to optimize search by mean re-write the queries below but return the same results. I appreciate for you help or suggestion.--step #1 SELECT DISTINCT CTRLSEG.PROCESSID, 627 FROM dbo.elig_834_ControlSegments CTRLSEG (NOLOCK) INNER JOIN dbo.elig_834_MEMBER MEM (NOLOCK) ON CTRLSEG.PROCESSID = MEM.PROCESSID INNER JOIN QNXT_LOG_LNK_SVR.QNXT_LOG.dbo.tblM_PROCESS_INFO PROCINFO (NOLOCK) ON CTRLSEG.PROCESSID = PROCINFO.PROCESS_ID AND PROCINFO.APP_ID = 627 WHERE ISNULL(PROCINFO.FILE_IND,'') NOT LIKE 'C' AND PROCINFO.STATUS = 4 --AND NOT EXISTS (SELECT * FROM dbo.tblM_Process_Details (NOLOCK) WHERE PROCESSID = 'PFL09005000894') --step #2 Very slow response because of using NOT EXISTS clause. SELECT DISTINCT CTRLSEG.PROCESSID, 627 FROM dbo.elig_834_ControlSegments CTRLSEG (NOLOCK) INNER JOIN dbo.elig_834_MEMBER MEM (NOLOCK) ON CTRLSEG.PROCESSID = MEM.PROCESSID INNER JOIN QNXT_LOG_LNK_SVR.QNXT_LOG.dbo.tblM_PROCESS_INFO PROCINFO (NOLOCK) ON CTRLSEG.PROCESSID = PROCINFO.PROCESS_ID AND PROCINFO.APP_ID = 627 WHERE ISNULL(PROCINFO.FILE_IND,'') NOT LIKE 'C' AND PROCINFO.STATUS = 4 AND NOT EXISTS (SELECT * FROM dbo.tblM_Process_Details (NOLOCK) WHERE PROCESSID = 'PFL09005000894') |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 13:59:11
|
| What are you using not exists there for ? |
 |
|
|
NguyenL71
Posting Yak Master
228 Posts |
Posted - 2009-01-07 : 14:41:20
|
quote:
Originally posted by sakets_2000
What are you using not exists there for ?
I realize that after I have posted to the group. The queries come from developers that why i didn't exam the syntax completely. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 14:45:06
|
| no problem. Also, I think you can't use any hints when joining to a table on a remote server. Likewise, You might want to correct on "NOT LIKE 'C'" bit. Add a wild character somewhere there. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 14:45:51
|
| And let us know the full query when you have it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 14:58:17
|
WHERE ISNULL(PROCINFO.FILE_IND,'') NOT LIKE 'C'WHERE (PROCINFO.FILE_IND IS NULL OR PROCINFO.FILE_IND <> 'C')
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Optimize Queries?