Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

635 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