Solved

How to increase efficiency of the t-sql procedure ?

Posted on 2009-04-08
6
252 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now