Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
Medium Priority
?
271 Views
Last Modified: 2012-05-06
Hellow Experts.
I created sql procedure for showing absence of the workers. The procedure applies correctly, however she is very slow.
The problem is sticking in the dynamically generated updating inquiry. This moment is taking the most time up.
Is there way for increasing productivity?
Thank's for help.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_poza_biurem2] @d1 datetime, @d2 datetime, @iledni int
AS
BEGIN
	SET NOCOUNT ON; 
 
--drop table #urlopy;
--drop table ##urlopy2;
 
-- zmienne parametryzowane do raportu
declare @i int, @nowadata datetime
 
set @i = 0;
set @nowadata = @d1;
 
 
create table #urlopy
(
	id int,
	id_d int,
	naz varchar(100),
	dzien datetime,
	nieobecny int
)
 
--pobierz pracownikow
while @i < @iledni
begin
	set @i = @i + 1
	
	insert #urlopy 
		select l.id, 
			   p.id_dzialu, 
			   l.naz,	
			   @nowadata ,
			   (select distinct poza_biurem.powod from poza_biurem  
				left join urlopy on poza_biurem.id = urlopy.id_poza_biurem
				where 
				(poza_biurem.pracownik = l.id and 
				 aktywny = 'true') 
				and 
				(@nowadata >= pdn and @nowadata <= odn and urlopy.status <> 'odrzucony' 
			--	OR
			--	@d2 BETWEEN pdn and odn and urlopy.status <> 'odrzucony'
			--	OR
			--	pdn BETWEEN @d1 and @d2 and urlopy.status <> 'odrzucony'
			--	OR
			--	odn BETWEEN @d1 and @d2 and urlopy.status <> 'odrzucony'
				OR
				@nowadata >= pdn and @nowadata <= odn and (poza_biurem.powod = 5 or poza_biurem.powod = 6)))
			--	OR 
			--	@d2 BETWEEN pdn and odn and (poza_biurem.powod = 5 or poza_biurem.powod = 6)
			--	OR
			--	pdn BETWEEN @d1 and @d2 and (poza_biurem.powod = 5 or poza_biurem.powod = 6)
			--	OR
			--	odn BETWEEN @d1 and @d2 and (poza_biurem.powod = 5 or poza_biurem.powod = 6)))
	from 
		logins l join pracownicy p on l.id = p.idp
	where akt = 'T'
 
	set @nowadata = DATEADD(day,1,@nowadata);
 
 
end
 
update #urlopy set nieobecny = 0 where nieobecny is null;
 
 
--tabelka z dynamiczna liczba kolumn :)
declare @cols varchar(4000)
declare @strCreate nvarchar(max)
set @cols = 'id int, nazwisko varchar(100), '
set @i = 0;
set @nowadata = @d1;
while @i < @iledni
begin	
	set @cols = @cols + 'd' + right(convert(varchar(12), @nowadata, 112),4) + ' int'
	--print @cols;
	if @i < @iledni -1 set @cols = @cols + ', '
	set @i = @i + 1
	set @nowadata = DATEADD(day,1,@nowadata);	
end
set @strCreate = 'create table ##urlopy2(' + @cols + ')'
Exec sp_executesql @strCreate
--end of tabelka z dynamiczna liczba kolumn
 
--transponowanie macierzy
set @i = 0;
set @cols = '';
set @strcreate = '';
 
declare @naz varchar(max);
declare @nowadata2 varchar(max);
declare @id int;
 
declare kursor cursor for 
select distinct naz, id from #urlopy
OPEN kursor FETCH NEXT FROM kursor INTO @naz, @id
	WHILE @@FETCH_STATUS = 0 BEGIN	
 
--wstawiamy pracownika	
	set @strcreate = 'insert into ##urlopy2 (id, nazwisko)'
	set @strcreate = @strcreate + ' values ('''+ convert(varchar(5), @id) + ''',''' + @naz + ''')';
	
	Exec sp_executesql @strCreate 
--updatujemy dane
	set @nowadata = @d1;	
	set @i = 0;
 
	set @strcreate = 'update ##urlopy2 set';
 
	while @i < @iledni
	begin
		set @strcreate = @strcreate +  ' d' + right(convert(varchar(12), @nowadata, 112),4) + ' =';	
		set @strcreate = @strcreate + '(select nieobecny from #urlopy where id='''+convert(varchar(5),@id)+''' and convert(varchar(12),dzien,112)='''+ convert(varchar(12),@nowadata,112) +''')';		
 
		--set @strcreate = @strcreate + '1';		
 
		if @i < @iledni - 1 set @strcreate = @strcreate + ', '
		set @i = @i + 1;
		set @nowadata = DATEADD(day,1,@nowadata);	
	end
 
	set @strcreate = @strcreate + ' where id = '''+convert(varchar(5),@id)+'''';		
	exec sp_executesql @strCreate --- <<<< ---- This moment is taking the most time up.
 
	
	print @strcreate;
 
FETCH NEXT FROM kursor INTO @naz, @id
END
 
CLOSE kursor;
DEALLOCATE kursor;
 
select * from ##urlopy2 order by nazwisko;
 
-- sprzatamy
drop table #urlopy;
drop table ##urlopy2;
END
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Open in new window

0
Comment
Question by:ASkEPA
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24097376
you need to get rid of the cursors in order to improve efficiency.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 24097988
Comment #1:
Look at the following lines:

declare @naz varchar(max);
declare @nowadata2 varchar(max);
In table #urlopy, you declare the column "naz" as VarChar(100).  You then read that into the @naz variable and later insert it into a column 'nazwisko' which you have also defined as a VarChar(100).  Perhaps you might want to make all of those declarations the same?
Comment #2:
Try changing the following line:

set @cols = 'id int, nazwisko varchar(100), '
 to the following line:

set @cols = 'id int PRIMARY KEY, nazwisko varchar(100), '
 
Comment #3:
Also, you might try building your INSERT statement for ##urlopy2 so that it inserts the values you are later updating the table with.  That would let you touch that table only once instead of twice, as you currently are.  If you do it right, you can drop the cursor and make it a straight forward insert from your #urlopy table.
0
 

Author Comment

by:ASkEPA
ID: 24104763
Hellow.
I rebuilt the program according to your signs. Unfortunately the productivity is still low. I made a profit about 1 second. What else can i do ?
I would like to resign from the cursor but I am not reliable in what way I can do it.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_poza_biurem3] @d1 datetime, @d2 datetime, @iledni int
AS
BEGIN
	SET NOCOUNT ON; 
 
--drop table #urlopy;
--drop table ##urlopy2;
 
-- zmienne parametryzowane do raportu
declare @i int, @nowadata datetime
 
set @i = 0;
set @nowadata = @d1;
 
 
create table #urlopy
(
	id int,
	id_d int,
	naz varchar(100),
	dzien datetime,
	nieobecny int
)
 
--pobierz pracownikow
while @i < @iledni
begin
	set @i = @i + 1
	
	insert #urlopy 
		select l.id, 
			   p.id_dzialu, 
			   l.naz,	
			   @nowadata ,
			   (select distinct poza_biurem.powod from poza_biurem  
				left join urlopy on poza_biurem.id = urlopy.id_poza_biurem
				where 
				(poza_biurem.pracownik = l.id and 
				 aktywny = 'true') 
				and 
				(@nowadata >= pdn and @nowadata <= odn and urlopy.status <> 'odrzucony' 			
				OR
				@nowadata >= pdn and @nowadata <= odn and (poza_biurem.powod = 5 or poza_biurem.powod = 6)))		
	from 
		logins l join pracownicy p on l.id = p.idp
	where akt = 'T'
 
	set @nowadata = DATEADD(day,1,@nowadata);
 
 
end
 
update #urlopy set nieobecny = 0 where nieobecny is null;
 
 
--tabelka z dynamiczna liczba kolumn :)
declare @cols varchar(4000)
declare @strCreate nvarchar(max)
set @cols = 'id int PRIMARY KEY, nazwisko varchar(100), '
set @i = 0;
set @nowadata = @d1;
while @i < @iledni
begin	
	set @cols = @cols + 'd' + right(convert(varchar(12), @nowadata, 112),4) + ' int'
	--print @cols;
	if @i < @iledni -1 set @cols = @cols + ', '
	set @i = @i + 1
	set @nowadata = DATEADD(day,1,@nowadata);	
end
set @strCreate = 'create table ##urlopy2(' + @cols + ')'
Exec sp_executesql @strCreate
--end of tabelka z dynamiczna liczba kolumn
 
--transponowanie macierzy
set @i = 0;
set @cols = '';
set @strcreate = '';
declare @naz varchar(100);
declare @nowadata2 varchar(100);
declare @id int;
 
 
declare kursor cursor for 
select distinct naz, id from #urlopy
OPEN kursor FETCH NEXT FROM kursor INTO @naz, @id
	WHILE @@FETCH_STATUS = 0 BEGIN	
 
	set @i = 0;
	set @strcreate = 'insert into ##urlopy2 select '
	set @strcreate = @strcreate + convert(varchar(5), @id) + ', ''' + @naz + ''', ';
	set @nowadata = @d1;
	while @i < @iledni
	begin
		set @strcreate = @strcreate + '(select nieobecny from #urlopy where id='''+convert(varchar(5),@id)+''' and convert(varchar(12),dzien,112)='''+ convert(varchar(12),@nowadata,112) +''')';		
		if @i < @iledni - 1 set @strcreate = @strcreate + ', '
		set @i = @i + 1;
		set @nowadata = DATEADD(day,1,@nowadata);	
	end	
	print @strcreate;
	Exec sp_executesql @strCreate
FETCH NEXT FROM kursor INTO @naz, @id
END
CLOSE kursor;
DEALLOCATE kursor;
 
	select * from ##urlopy2 order by nazwisko;
 
-- sprzatamy
drop table #urlopy;
drop table ##urlopy2;
END
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:ASkEPA
ID: 24104947
I managed to resign from the cursor.
Now everything is acting very quickly.
Thank you very much for the help.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_poza_biurem4] @d1 datetime, @d2 datetime, @iledni int
AS
BEGIN
	SET NOCOUNT ON; 
 
--drop table #urlopy;
--drop table ##urlopy2;
 
-- zmienne parametryzowane do raportu
declare @i int, @nowadata datetime
 
set @i = 0;
set @nowadata = @d1;
 
 
create table #urlopy
(
	id int,
	id_d int,
	naz varchar(100),
	dzien datetime,
	nieobecny int
)
 
--pobierz pracownikow
while @i < @iledni
begin
	set @i = @i + 1
	
	insert #urlopy 
		select l.id, 
			   p.id_dzialu, 
			   l.naz,	
			   @nowadata ,
			   (select distinct poza_biurem.powod from poza_biurem  
				left join urlopy on poza_biurem.id = urlopy.id_poza_biurem
				where 
				(poza_biurem.pracownik = l.id and 
				 aktywny = 'true') 
				and 
				(@nowadata >= pdn and @nowadata <= odn and urlopy.status <> 'odrzucony' 			
				OR
				@nowadata >= pdn and @nowadata <= odn and (poza_biurem.powod = 5 or poza_biurem.powod = 6)))		
	from 
		logins l join pracownicy p on l.id = p.idp
	where akt = 'T'
 
	set @nowadata = DATEADD(day,1,@nowadata);
 
 
end
 
update #urlopy set nieobecny = 0 where nieobecny is null;
 
 
--tabelka z dynamiczna liczba kolumn :)
declare @cols varchar(4000)
declare @strCreate nvarchar(max)
set @cols = 'id int PRIMARY KEY, nazwisko varchar(100), '
set @i = 0;
set @nowadata = @d1;
while @i < @iledni
begin	
	set @cols = @cols + 'd' + right(convert(varchar(12), @nowadata, 112),4) + ' int'
	--print @cols;
	if @i < @iledni -1 set @cols = @cols + ', '
	set @i = @i + 1
	set @nowadata = DATEADD(day,1,@nowadata);	
end
set @strCreate = 'create table ##urlopy2(' + @cols + ')'
Exec sp_executesql @strCreate
--end of tabelka z dynamiczna liczba kolumn
 
--transponowanie macierzy
set @i = 0;
set @cols = '';
set @strcreate = '';
declare @naz varchar(100);
declare @nowadata2 varchar(100);
declare @id int;
 
set @nowadata = @d1;
 
 
set @strcreate = 'insert into ##urlopy2 select distinct u.id, u.naz, '
 
while @i < @iledni
begin
	set @strcreate = @strcreate + '(select u' + convert(varchar(5),@i) + '.nieobecny from #urlopy u' + convert(varchar(5),@i) + ' where u' + convert(varchar(5),@i) + '.id = u.id and convert(varchar(12),dzien,112) ='+ convert(varchar(12),@nowadata,112) + ')'
	if @i < @iledni - 1 set @strcreate = @strcreate + ', '
		set @i = @i + 1;
		set @nowadata = DATEADD(day,1,@nowadata);	
end
 
 
set @strcreate = @strcreate + ' from #urlopy u'
			
Exec sp_executesql @strCreate
 
select * from #urlopy order by naz;
 
select * from ##urlopy2 order by nazwisko;
 
-- sprzatamy
drop table #urlopy;
drop table ##urlopy2;
END
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24107731
>>I managed to resign from the cursor.
Now everything is acting very quickly. <<
I told you to get rid of the cursor in comment 24097376. Just wonder why no points for me?
0
 

Author Comment

by:ASkEPA
ID: 24113647
Since the reply of the 8080_diver user contained more information and more helpful for me.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question