What am I missing??? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

What am I missing???

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-24 : 15:34:34

I have the following code:
When I execute this, it seems to be going on for ever. When I remove the 'Having and Group by' condition in the end, it executes fine. Any suggections please.

SELECT T1.ENTITY, t2.EMPLNAME, T1.EMPLID, T1.FISCAL_YR, T1.ACCOUNT_NBR10, T1.POSITION_NBR, T1.JOBCLASS, EMPFTE, T1.FINAL_BGT_FTE, FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB
FROM openquery(SERV1, 'select ENTITY, EMPLID, FISCAL_YR, ACCOUNT_NBR10, POSITION_NBR, JOBCLASS, FINAL_BGT_FTE, FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM,ORGID, FINAL_BGT_BEG
FROM BUDGET.DBO.BDBPDCTB
WHERE EMPLID > 0
AND ORGID = ''TT''') as T1,
openquery(SERV2, 'select EMPLNAME, EFFECTIVE_DATE, ORGID, EMPLID, EMPFTE
FROM TECHRIS.DBO.TRBASCTB
WHERE ORGID = ''TT''
AND EMPLSTAT <> ''X''') as T2
WHERE T1.EMPLID = T2.EMPLID
AND T2.ORGID = T1.ORGID
AND T1.FISCAL_YR = '2004'
AND ENTITY = 'H'
AND T2.EFFECTIVE_DATE =
(SELECT MAX(T3.EFFECTIVE_DATE)
FROM openquery(SERV2, 'SELECT ORGID, EMPLID, ENTYCODE, EFFECTIVE_DATE
FROM TECHRIS.DBO.TRBASCTB') as T3
WHERE T3.ORGID = T2.ORGID
AND T3.EMPLID = T2.EMPLID
AND EFFECTIVE_DATE <= '09/01/2003')
AND EXISTS (SELECT *
FROM openquery(SERV1, 'SELECT ENTITY, EMPLID, FISCAL_YR, ACCOUNT_NBR10, POSITION_NBR, JOBCLASS, FINAL_BGT_FTE, FINAL_BGT_PCT, FINAL_BGT_SAL, FINAL_BGT_FTB, TERM,ORGID, FINAL_BGT_BEG
FROM BUDGET.DBO.BDBPDCTB
WHERE EMPLID > 0
AND ORGID = ''TT''') as T4,
openquery(SERV2, 'select EMPLNAME,EFFECTIVE_DATE,ORGID,EMPLID,EMPFTE
FROM TECHRIS.DBO.TRBASCTB
WHERE ORGID = ''TT''') as T5
WHERE T1.EMPLID = T4.EMPLID
AND T4.EMPLID = T5.EMPLID
AND T4.ORGID = T5.ORGID
AND T4.FISCAL_YR = '2004'
AND T5.EFFECTIVE_DATE =
(SELECT MAX(T6.EFFECTIVE_DATE)
FROM openquery(SERV2, 'SELECT ORGID, EMPLID, ENTYCODE, EFFECTIVE_DATE
FROM TECHRIS.DBO.TRBASCTB') as T6
WHERE T5.ORGID = T6.ORGID
AND T5.EMPLID = T6.EMPLID
AND EFFECTIVE_DATE <= '09/01/2003')
GROUP BY T4.EMPLID, T5.EMPFTE, T4.FINAL_BGT_PCT
HAVING (SUM((T4.FINAL_BGT_PCT)+.05) <> (T5.EMPFTE*100)))
ORDER BY 1,2

Edited by - sahu74 on 04/24/2003 16:57:02

Edited by - sahu74 on 04/24/2003 16:57:25

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-24 : 17:53:28

Instead of all the openqueries get the data int local temp tables so that you can see what is going on.
Otherwise put thye result into a temp table and run the group by/having on that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-25 : 10:31:20

Thank you NR.
I tried that but could you explain this...

What is the idea of having the subquery within 'EXISTS':

select T1.var1, var2, ....
from tableABC T1, table XYZ T2
where T1.var1 =T2.var1
and condition 2
and condition 3
and condition 4
and exists ( select T3.var1
from tableABC T3, table XYZ T4
where T1.var1 =T3.var1 --I donot know why?? that's the way the original query was written
and T3.var1 = T4.var1
and condition 2 --same as in main query
and condition 3 --same as in main query
and condition 5 --different from main query
group by T3.var1, T4.var3
having sum(var5)+.05 <> T4.var6*100)

quote:

Instead of all the openqueries get the data int local temp tables so that you can see what is going on.
Otherwise put thye result into a temp table and run the group by/having on that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 10:58:10

Honestly, when I encounter a query like this, I always take a step back, ignore the query completely, and then decide what it is trying to return and what it needs.

And then re-write from scratch.

The results are usually about 1/10 as long and 100 times faster.

Some advice about OPENQUERY():

It looks like you are trying to get the latest row per employee from the techris.dbo.trbasctb table (nice names!) which lives on another server, but you are doing all of the processing on wrong server. Instead of just using OPENQUERY() twice, returning all of the rows, and doing the joins, do all of that WITHIN the OPENQUERY() function.

That is, instead of:

SELECT blah blah FROM
OPENQUERY(server, 'select * from table1') A
INNER JOIN
OPENQUERY(server, 'select * from table2') B
ON A.ID = B.ID
WHERE blah blah
GROUP BY blah blah

You should do this:

SELECT * FROM
OPENQUERY(server, 'Select blah blah from table1 inner join table2 on a.id = b.id WHERE blah blah group by blah blah') A

Does that make sense? the seperate openquery's require ALL rows from both tables to be returned to the calling server, and then the calling server must do the joins and filters and group by's, and then the results are returned.

All in 1 OPENQUERY() means the callee server does all the work between the two tables locally and efficiently, and only returns back the necessary rows already grouped and filtered.

It looks like the SQL you posted could use a LOT of that kind of optimization.

- Jeff

sahu74
Posting Yak Master

100 Posts

Posted - 2003-04-25 : 11:25:39

Thank you Jeff for your suggestion. That was something new that I learnt. But I am accessing two different servers if you would notice (SERV1, SERV2). Do you have any suggestions?

Sahu.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 11:47:29

Sahu --

yeah, i'd noticed that ... i guess i was mostly talking about this part:

openquery(SERV2, 'select EMPLNAME,EFFECTIVE_DATE,ORGID,EMPLID,EMPFTE
FROM TECHRIS.DBO.TRBASCTB
WHERE ORGID = ''TT''') as T5
WHERE T1.EMPLID = T4.EMPLID
AND T4.EMPLID = T5.EMPLID
AND T4.ORGID = T5.ORGID
AND T4.FISCAL_YR = '2004'
AND T5.EFFECTIVE_DATE =
(SELECT MAX(T6.EFFECTIVE_DATE)
FROM openquery(SERV2, 'SELECT ORGID, EMPLID, ENTYCODE, EFFECTIVE_DATE
FROM TECHRIS.DBO.TRBASCTB') as T6
WHERE T5.ORGID = T6.ORGID
AND T5.EMPLID = T6.EMPLID
AND EFFECTIVE_DATE <= '09/01/2003')

That can definitely be consolidated ... as for server1 and server2, try to re-arrange you query so that you are only accessing each server once if possible.

Or, as Nigel suggested, as the first step of this process create a local temp table of the data you need from the other servers, and then do everything locally.

But I really think you need to re-examine what you are trying to return, and where everything lives (in plain but detailed english) and re-organize and re-write from scratch.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources