SQLScript takes to long. - 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)

SQLScript takes to long.

Author

Topic

sqlpara
Starting Member

2 Posts

Posted - 2005-06-21 : 08:38:13

Hello people,

I need some help with this query. If I run this query it take more 45 minutes, I didn't try any longer.
But I try it on a other database and I get results in no time. This query looks in a couple of views and he compare some customers in other views.

So what im I doing wrong, I build a couple of indexes (on the objects that I use the most).

insert into TEMP_TABLE (Total,Type,time,Number)
select count (distinct pvn.ID) as Total, 'TYPE OF QUERY' as type getdate() as time, 13 as number
from PatViewG as PVG join PatViewN as PVN
on PVG.ID = PVN.ID
where PVN.ID not in (select ID from PatViewA) and
PVN.ID not in (select ID from PatViewAGN) and pvg.date > pvn.date and PVN.ID in (
select pvn.ID
from PatViewA as PVA join PatViewN as PVN
on PVA.ID = PVN.ID
where PVN.ID not in (select ID from PatViewG) and
PVN.ID not in (select ID from PatViewAGN)and pva.date < pvn.date)

I hope someone can help me out.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-21 : 09:31:54

Compare your database settings (red-gate.com) + query execution plans....the difference must be there somewhere.

sqlpara
Starting Member

2 Posts

Posted - 2005-06-21 : 10:02:48

Ok, thanks alot. I think that program might be usefull :).
And I'll lookup the execution plan.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-21 : 10:46:03

First off, that query won't even run....

Second of all, what's it suppose to be doing?



insert into TEMP_TABLE (Total,Type,time,Number)
select count (distinct pvn.ID) as Total
	, 'TYPE OF QUERY' as type
	,  getdate() as time
	, 13 as number
  FROM PatViewG as PVG 
  join PatViewN as PVN
    on PVG.ID = PVN.ID
 where PVN.ID not in (select ID from PatViewA) 
   and PVN.ID not in (select ID from PatViewAGN) 
   and pvg.date > pvn.date 
   and PVN.ID in (select pvn.ID 
		    from PatViewA as PVA 
		    join PatViewN as PVN on PVA.ID = PVN.ID
		   where PVN.ID not in (select ID from PatViewG) 
		     and PVN.ID not in (select ID from PatViewAGN)
		     and pva.date < pvn.date)

Why are you re-referemcing the same tables over and over, also yo umost likely will incur some scans...

Why don't you try and tell us what you're trying to accomplish?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources