Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
255 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

821 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