query too 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

query too slow

Author

Topic

catbollu
Starting Member

10 Posts

Posted - 2011-10-22 : 00:52:38

can anyone help me speed this query up. it is a report putting multiple records in one record. and right now it will take 22 days to finish



drop table #tempourfilenum
drop table #temp2
select distinct up.ourfile,up.editdate,up.subkey into #tempourfilenum from updates as up,deadline as dead where dead.ourfile = up.ourfile and dead.code ='skipvacc' and ltrim(rtrim(up.subkey))= 'DEB1' order by up.ourfile ,up.editdate
--select * from #tempourfilenum

--------------------------------------------------------------------

--drop table #mytemp
declare @ourfile char(30)
declare @ourdate char(30)
declare @theourfile char(1200)
declare @mycount int
declare @ourcount int
declare @ourcountmax int
declare @sql char(250)

--DECLARE @str varchar(4000)

select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum

While ( @theourfile is not null)
begin
	select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum
	drop table #temp2
	select rtrim(ltrim(ourfile))as ourfile, ltrim(rtrim(editdate))as editdate into #temp2 from #tempourfilenum where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) ORDER BY editdate DESC 
    set @sql = 'select * from #temp2'
	select @mycount=count(ourfile) from #temp2
	if @mycount >=1
		begin
 		--address2
   		select  top 1 @theourfile = rtrim(ltrim(ourfile)), @ourdate = ltrim(rtrim(editdate)) from #temp2
		

      		if (select count(orgvalue) from updates where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='ADD1' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
				update skipaddresses set add2=(select top 1 orgvalue from updates where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='ADD1' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=@theourfile


end
			else 
begin

print @theourfile + 'else add2'+ @ourdate
				update skipaddresses set add2 ='' where ltrim(rtrim(ourfile)) =ltrim(rtrim(@theourfile))

		 
end   			
			
			if (select count(orgvalue) from updates where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1')>0
            
begin

				update skipaddresses set city2 =(select top 1 orgvalue from updates 
							where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 

begin
print @theourfile + 'else city2'+ @ourdate
				update skipaddresses set city2 ='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
		
end

   			
			
			if (select count(orgvalue) from updates where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) = ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') >= 1
begin
					update skipaddresses set state2=(select top 1 orgvalue from updates 
							where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile + 'else state2'+ @ourdate
				update skipaddresses set state2 = '' where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile))
			
   	end		
			
			if (select count(orgvalue) from updates where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
					update skipaddresses set zip2=(select top 1 orgvalue from updates 
							where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile + 'else zip2'+ @ourdate
				update skipaddresses set zip2='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			
		
		delete from #temp2	where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate))=ltrim(rtrim(@ourdate))
		end
		--address 3
		select  top 1 @theourfile = rtrim(ltrim(ourfile)), @ourdate=ltrim(rtrim(editdate)) from #temp2



      		if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='add1' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
				update skipaddresses set add3=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='add1' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else add3'+ @ourdate
				update skipaddresses set add3 ='' where  ltrim(rtrim(ourfile)) =ltrim(rtrim(@theourfile))
end
			

   	
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
				update skipaddresses set city3=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else city3'+ @ourdate
				update skipaddresses set city3='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			


   			
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
					update skipaddresses set state3=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else state3'+ @ourdate
				update skipaddresses set state3='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
   			
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
					update skipaddresses set zip3=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile + 'else zip3' + @ourdate
				update skipaddresses set zip3='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			
			
		delete from #temp2 where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate))

		--address 4

select  top 1 @theourfile = rtrim(ltrim(ourfile)), @ourdate=ltrim(rtrim(editdate)) from #temp2



      		if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='add1' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
				update skipaddresses set add4=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='add1' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else add4'+ @ourdate
				update skipaddresses set add4 ='' where  ltrim(rtrim(ourfile)) =ltrim(rtrim(@theourfile))
end
			

   	
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
				update skipaddresses set city4=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='city' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else city4'+ @ourdate
				update skipaddresses set city4='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			


   			
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
					update skipaddresses set state4=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='state' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile+ 'else state4'+ @ourdate
				update skipaddresses set state4='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
   			
			
			if (select count(orgvalue) from updates where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') >0
begin
					update skipaddresses set zip4=(select top 1 orgvalue from updates 
							where  ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate)) and ltrim(rtrim(fieldname)) ='zip' and rtrim(ltrim(subkey))= 'DEB1') where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			else 
begin
print @theourfile + 'else zip4' + @ourdate
				update skipaddresses set zip4='' where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile))
end
			
		
		delete from #temp2 where ltrim(rtrim(ourfile))=ltrim(rtrim(@theourfile)) and ltrim(rtrim(editdate)) =ltrim(rtrim(@ourdate))

		
	
 delete from #tempourfilenum where ltrim(rtrim(ourfile)) = ltrim(rtrim(@theourfile))
select top 1 @theourfile=rtrim(ltrim(ourfile)) from #tempourfilenum
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 04:48:41

i'm not sure anybody will spend time looking into whole code and will come up with rewritten code. It would be better if you identify time taking part of query and then post it explaining what you're trying to achieve in which case we might be able to give you alternate suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-22 : 05:14:57

i can't sleep


update	sa
set	add2	= isnull(u.add2, ''),
	city2	= isnull(u.city2, ''),
	state2	= isnull(u.state2, ''),
        . . . 
from	(
		select 	distinct 
			up.ourfile,
			up.editdate,
			up.subkey 
		into 	#tempourfilenum 
		from 	updates as up
			inner join deadline as dead on	dead.ourfile = up.ourfile 
		where	dead.code 	= 'skipvacc' 
		and 	ltrim(rtrim(up.subkey))= 'DEB1' 
	) d
	inner join skipaddresses sa	on	d.ourfile	= sa.ourfile
	outer apply
	(
		select	add2	= max(case when u.fieldname = 'ADD1' then u.orgvalue end),
			city2	= max(case when u.fieldname = 'city' then u.orgvalue end),
			state2	= max(case when u.fieldname = 'state' then u.orgvalue end),
                        . . .
		from	updates u
		where	u.ourfile	= d.ourfile
		and	u.editdate	= d.editdate
		and	u.fieldname	in ( 'ADD1' , 'city', 'state', . . .) 
		and	u.subkey	= 'DEB1'
	) u

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 05:29:04

quote:

Originally posted by khtan

i can't sleep


update	sa
set	add2	= isnull(u.add2, ''),
	city2	= isnull(u.city2, ''),
	state2	= isnull(u.state2, ''),
        . . . 
from	(
		select 	distinct 
			up.ourfile,
			up.editdate,
			up.subkey 
		into 	#tempourfilenum 
		from 	updates as up
			inner join deadline as dead on	dead.ourfile = up.ourfile 
		where	dead.code 	= 'skipvacc' 
		and 	ltrim(rtrim(up.subkey))= 'DEB1' 
	) d
	inner join skipaddresses sa	on	d.ourfile	= sa.ourfile
	outer apply
	(
		select	add2	= max(case when u.fieldname = 'ADD1' then u.orgvalue end),
			city2	= max(case when u.fieldname = 'city' then u.orgvalue end),
			state2	= max(case when u.fieldname = 'state' then u.orgvalue end),
                        . . .
		from	updates u
		where	u.ourfile	= d.ourfile
		and	u.editdate	= d.editdate
		and	u.fieldname	in ( 'ADD1' , 'city', 'state', . . .) 
		and	u.subkey	= 'DEB1'
	) u

KH
[spoiler]Time is always against us[/spoiler]

wow
great patience

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

catbollu
Starting Member

10 Posts

Posted - 2011-10-23 : 15:19:47

OK so I understand what khtan is doing.
I have a table fields(name add,city state,zip add2,city2,state2,zip,add3,city3,zip3)
another table that has the changes to another tables address and contains (field,datedone, orgvalue, newvalue)
so I need those changed addresses in one record and I only need 4 of them. so I am trying to read the orvalues into the add2 for first found ad2 for second found etc...
so multiple records in on record. I am having problems figureing out how to utilize the code from khtan because I am not sure what it is doing. I am truely trying to learn better sql.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 20:43:31

quote:

		select 	distinct 
			up.ourfile,
			up.editdate,
			up.subkey 
		into 	#tempourfilenum 
		from 	updates as up
			inner join deadline as dead on	dead.ourfile = up.ourfile 
		where	dead.code 	= 'skipvacc' 
		and 	ltrim(rtrim(up.subkey))= 'DEB1'

this part is basically your cursor query

quote:

drop table #tempourfilenum
drop table #temp2
select distinct up.ourfile,up.editdate,up.subkey into #tempourfilenum from updates as up,deadline as dead where dead.ourfile = up.ourfile and dead.code ='skipvacc' and ltrim(rtrim(up.subkey))= 'DEB1' order by up.ourfile ,up.editdate
--select * from #tempourfilenum

quote:

	outer apply
	(
		select	add2	= max(case when u.fieldname = 'ADD1' then u.orgvalue end),
			city2	= max(case when u.fieldname = 'city' then u.orgvalue end),
			state2	= max(case when u.fieldname = 'state' then u.orgvalue end),
                        . . .
		from	updates u
		where	u.ourfile	= d.ourfile
		and	u.editdate	= d.editdate
		and	u.fieldname	in ( 'ADD1' , 'city', 'state', . . .) 
		and	u.subkey	= 'DEB1'
	) u

this part of the query retrieves the orgvalue value for each ourfile and editdate in query "A".

the SELECT portion "add2 = max(case when u.fieldname = 'ADD1' then u.orgvalue end)," will return one value of orgvalue per fieldname.

I din't include all the other fieldname that you required. Just add it to the SELECT part. For example : zip2. See those in red


update	sa
set	add2	= isnull(u.add2, ''),
	city2	= isnull(u.city2, ''),
	state2	= isnull(u.state2, ''),
	zip2	= isnull(u.zip2, ''),
        . . . 
from	(
		select 	distinct 
			up.ourfile,
			up.editdate,
			up.subkey 
		into 	#tempourfilenum 
		from 	updates as up
			inner join deadline as dead on	dead.ourfile = up.ourfile 
		where	dead.code 	= 'skipvacc' 
		and 	ltrim(rtrim(up.subkey))= 'DEB1' 
	) d
	inner join skipaddresses sa	on	d.ourfile	= sa.ourfile
	outer apply
	(
		select	add2	= max(case when u.fieldname = 'ADD1' then u.orgvalue end),
			city2	= max(case when u.fieldname = 'city' then u.orgvalue end),
			state2	= max(case when u.fieldname = 'state' then u.orgvalue end),
			zip2    = max(case when u.fieldname = 'zip' then u.orgvalue end),
                        . . .
		from	updates u
		where	u.ourfile	= d.ourfile
		and	u.editdate	= d.editdate
		and	u.fieldname	in ( 'ADD1' , 'city', 'state', 'zip', . . .) 
		and	u.subkey	= 'DEB1'
	) u

One last thing, try out the query see if it works as you required as I haven't and couldn't.

KH
[spoiler]Time is always against us[/spoiler]

catbollu
Starting Member

10 Posts

Posted - 2011-10-24 : 09:27:47

Thank you very much. I also thank you for taking the time to explain it too me. I won't get to put it to work till this afternoon and I will let you know how it goes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources