script runs slow - 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

General SQL Server Forums

New to SQL Server Programming

script runs slow

Author

Topic

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-07-25 : 14:18:14

Hi,
I have a script file like this.

with a1 as (

select *
from openquery(servername, '
select id, date, class
from sss
')

),

with a2 as (

select id, count(*) as cnt
from a1
group by id

),

with a_sel as (

select a1.*
from a1 inner join a2 on a1.id = a2.id
where a2.cnt = 1

)

---- main sql -----
select id, count(*) as cnt
from a_sel
group by id

I can get the output of a_sel within 30sec, but it has been more than 30 minutes and I haven't got anything. Where could be the problem?

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-07-25 : 14:28:43

wouldn't that be the same thing as this?


select *
from openquery(servername, '
select id, 1 as cnt
from <database>..sss
group by id
having count(*) = 1
')

Be One with the Optimizer
TG

whitefreesia.bai
Starting Member

6 Posts

Posted - 2013-07-25 : 15:32:12

Thank, TG.

Yes, that would be the same thing. My real code is not like this. I need to join the data from the db2 server with the data on the SQL server. I can get the two data very quickly if I run the code separately, but when I try to join them, it took forever. That's why I wrote this test code. It has been 1hr40min and the script is still running. Do you think there might be a problem in the code or in the server?
Thanks a lot!

quote:
Originally posted by TG

wouldn't that be the same thing as this?

select *
from openquery(servername, '
select id, 1 as cnt
from <database>..sss
group by id
having count(*) = 1
')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources