Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
256 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
Format Output of Select Statement 2 38
SQL Lag Function DateDiff 2 26
Access PS SQLSERVER from powershell 1 30
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

752 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