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
 General SQL Server Forums
 New to SQL Server Programming
 script runs slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whitefreesia.bai
Starting Member

6 Posts

Posted - 07/25/2013 :  14:18:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 07/25/2013 :  14:28:43  Show Profile  Reply with Quote
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
Go to Top of Page

whitefreesia.bai
Starting Member

6 Posts

Posted - 07/25/2013 :  15:32:12  Show Profile  Reply with Quote
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

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.06 seconds. Powered By: Snitz Forums 2000