Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
254 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Update trigger 5 18
T-SQL: Do I need CLUSTERED here? 13 45
SQL Server Connection String through a VPN 8 29
divide by zero error 23 16
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

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