Optimierung einer Procedure..

[masterdemo]

Hallo,

ich bin ganz frisch hier im Forum und habe auch direkt schon eine Frage...

Ich habe folgende Procedure ( MS SQL 2005 ) die ich mal zur Diskussion stellen möchte.
Es geht mir hier um prinzipielle "not to do´s"

Nicht erschrecken.... soll nur die bisherige Struktur verdeutlichen...

bin über jede Anregung froh...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







ALTER PROCEDURE [dbo].[sp_st_wT] @wt int AS

declare @1 int

declare @lfd_nr int
declare @position int
declare @last_lfd_nr int

declare @st1 int

DECLARE @aktive int

set @aktive=1



if @aktive=1

BEGIN
SET NOCOUNT ON

if @wt=1
BEGIN


select @last_lfd_nr=(select MAX(station.Lfd_Nr) from station
        Where (station.string30 NOT LIKE N''))




set @lfd_nr=1
WHILE @lfd_nr <= (@last_lfd_nr)
BEGIN
 select @position=(select number30 from station where lfd_nr=@lfd_nr)
UPDATE station 
  SET  -- QIDNr
   station.number30 = @position+20
        where station.Lfd_Nr=(@lfd_nr)     
                   
 SET @lfd_nr=@lfd_nr+1
END



END

ELSE

BEGIN
PRINT @wt
END



DECLARE @count_linie int

declare @rg varchar (20)
declare @rg_exists int
declare @rg_status int

declare @pk varchar (20)
declare @pk_exists int
declare @pk_status int

declare @ext varchar (20)
declare @ext_exists int
declare @ext_status int

declare @kue varchar (20)
declare @kue_exists int
declare @kue_status int

declare @sd varchar (20)
declare @sd_exists int
declare @sd_status int

declare @pp varchar (20)
declare @pp_exists int
declare @pp_status int

declare @rm varchar (20)
declare @rm_exists int
declare @rm_status int


select @pk=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30<=1800 and string30 not like N''))
select @ext=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=1820 and number30<=2800 and string30 not like N''))
select @sd=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=2820 and number30<=3800 and string30 not like N''))
select @kue=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=3810 and number30<=9100 and string30 not like N''))
select @pp=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=9100 and number30<=9900 and string30 not like N''))
select @rm=(select string30 from station where lfd_nr=(select MAX(lfd_nr) from station where number30>=10040 and number30<=16400 and string30 not like N''))


-----------------------------erfasse status-------------------------------------------------------------

select @pk_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@pk)
select @ext_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@ext)
select @kue_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@kue)
select @sd_status=(select auftrag_status  from ku_rohre_im_prozess where qidnr=@sd)
select @pp_status=(select auftrag_status from ku_rohre_im_prozess where qidnr=@pp)
select @rm_status=(select auftrag_status  from ku_rohre_im_prozess where qidnr=@rm)




select @pk_exists=(select lfd_nr from kl_20 where qidnr=@pk)
if @pk_exists not like N''
print @1  --"bereits erfasst"
ELSE 
begin
delete kl_10 where qidnr=@pk

if @pk not like N''
insert into kl_20 (qidnr,status) values (@pk,@pk_status)
ELSE
print @1  
end

------------ ext
select @ext_exists=(select lfd_nr from kl_30 where qidnr=@ext)
if @ext_exists not like N''
print @1  -- "bereits erfasst"
ELSE 
begin

delete kl_20 where qidnr=@ext
delete kl_30 where qidnr=@ext


if @ext not like N''
insert into kl_30 (qidnr,status) values (@ext,@ext_status)
ELSE
print @1  -- "leer"
end





------------ sd
select @sd_exists=(select lfd_nr from kl_40 where qidnr=@sd)
if @sd_exists not like N''
print @1  -- "bereits erfasst"
ELSE 
begin

delete kl_30 where qidnr=@sd
delete kl_40 where qidnr=@sd

if @sd not like N''
insert into kl_40 (qidnr,status) values (@sd,@sd_status)
ELSE
print @1  -- "leer"
end

------------ kue
select @kue_exists=(select lfd_nr from kl_50 where qidnr=@kue)
if @kue_exists not like N''
print @1  -- "bereits erfasst"
ELSE 
begin

delete kl_40 where qidnr=@kue
delete kl_50 where qidnr=@kue


if @kue not like N''
BEGIN
insert into kl_50 (qidnr,status) values (@kue,@kue_status)
--insert into kl_protokoll (qidnr,bemerk) values (@kue,'insert_ku')
END
ELSE
print @1  -- "kue_ leer"
end

--DEAKTIVIERT TAKT RM

------------ pp
select @pp_exists=(select lfd_nr from kl_60 where qidnr=@pp)
if @pp_exists not like N''
print @1  -- "bereits erfasst"
ELSE 
begin

delete kl_50 where qidnr=@pp
delete kl_60 where qidnr=@pp

if @pp not like N''
insert into kl_60 (qidnr,status) values (@pp,@pp_status)
ELSE
print @1  -- "leer"
end





---DEAKTIVIERT TAKTRM

------------ rm
select @rm_exists=(select lfd_nr from kl_70 where qidnr=@rm)
if @rm_exists not like N''
begin
delete kl_60 where qidnr=@rm
print @1  -- "bereits erfasst"
end
ELSE 
begin






if @rm not like N''

insert into kl_70 (qidnr,status) values (@rm,@rm_status)
ELSE
print @1  -- "leer"
end
SET NOCOUNT OFF
END 


ELSE


print @1  -- "DEAKTIVIERT"

Gruß MD

[Ralf Jansen]

Wo ist denn die Frage?

Entschuldige die folgende flapsige Kommentierung, aber durch deine offene Fragestellung hast du es einfach herausgefordert. Nein, Neulinge werden hier nicht geschont

Dinge die mir beim Überfliegen aufgefallen sind(kann nichts über die Funktionsweise sagen da ich nicht weiß was du vorhast)

a.) Auch bei T/SQL erhöht einrücken die Lesbarkeit

b.) Gleiches gilt für konsequente gROß/kLEin Schreibung
Ob und was groß bzw. klein geschrieben wird ist mit egal man sollte es nur konsequent durchhalten.

c.) Weder die Datenbank oder der Datenbankserver noch irgendein Leser vor dem Monitor hat Aversionen gegen sprechende Namen. 'sp_st_wT' werte ich als 'manuelles Obfuscating'.

d.) beginne den Namen nicht mit 'sp_'. Das sollte den systemeigenen Procedures vorbehalten bleiben. Beim Auflösen des Namens würde der SQLServer auch in der MasterDB nach dieser Prozedur suchen da ja die systemeigenen auch dort liegen könnten. Hilf im also indem du sie gleich anders benennst dann kann er sich das sparen.


Gruß
Ralf

[Bernd Schulz]

Hallo!

Is ja wirklich unleserlich formatiert.

Sieh Dir mal das hier an:
http://www.wangz.net/cgi-bin/pp/gsql.../sqlformat.tpl
Wenn Du Deine Proc dort mal formatieren läßt sieht das schon viel besser aus...

So auf den ersten Blick der erste If Block "IF @wt = 1" enthält ein Select mit like. like = Full Table Scan => Kein Index bei 10 Datensätzen interessiert das niemanden bei 10mio könnte der Anwender nervös werden...

Besagter Block macht doch einfach nur ein
update station set number30 = number30 + 20 where string30 like N
oder ?!?

Datenbankdesign: number30 enthält offensichtlich eine Kodierung. Ist nicht so mein Fall würde ich ändern...

Hm mal sehen... ich ab hier in unserer DB noch eine Procedure mit fast 1000 Zeilen. Die poste ich auch mal hier. Mal sehen was passiert .. Grins

So und nun gib uns doch mal einen winzig kleinen Tipp was das Ganze denn machen soll.

BYE BERND

[Andreas Kosch]

Hallo,

Es geht mir hier um prinzipielle "not to do´s"

eine Stored Procedure, die nur für eine bestimmte Datenbank gilt, sollte nicht mit dem Präfix sp_ beginnen. Dieser Präfix steht beim MS SQL Server für Special. Wenn eine Tabelle oder eine Stored Procedure im Namen mit der Zeichenkette sp_ beginnt, gelten besondere Regeln. Diese führen im Alltag im Worst Case dazu, dass sich der Entwickler selber ins Knie schießt. Um dies zu vermeiden, sollte man auf sp_ verzichten (... "defensiver Programmierstil"...).

[Thomas Rith]

Hallo masterdemo,

ich hab mir einfach mal den Spaß gemacht, das ganze zu formatieren und nicht benötigtes über Bord zu werfen. Kann leider nicht testen, ob noch alles geht, aber rausgekommen ist folgender Code:

alter procedure [dbo].[sp_st_wt] 
	@wt int 
as
begin
	declare @rg varchar (20)
	declare @rg_status int
	
	declare @pk varchar (20)
	declare @pk_status int
	
	declare @ext varchar (20)
	declare @ext_status int
	
	declare @kue varchar (20)
	declare @kue_status int
	
	declare @sd varchar (20)
	declare @sd_status int
	
	declare @pp varchar (20)
	declare @pp_status int
	
	declare @rm varchar (20)
	declare @rm_status int

	SET NOCOUNT ON
	
	if @wt=1
	begin
		update 
			station 
	  	set	
	   		number30 = number30 + 20
	        where 
			len(lfd_nr) > 0
	end
	else
	begin
		print @wt
	end
	
	set @pk	=(select top 1 string30 from station where number30 <= 1800 order by lfd_nr desc)
	set @ext=(select top 1 string30 from station where number30 between 1820 and 2800 order by lfd_nr desc) 
	set @sd	=(select top 1 string30 from station where number30 between 2820 and 3800 order by lfd_nr desc) 
	set @kue=(select top 1 string30 from station where number30 between 3810 and 9100 order by lfd_nr desc) 
	set @pp	=(select top 1 string30 from station where number30 between 9100 and 9900 order by lfd_nr desc) 
	set @rm	=(select top 1 string30 from station where number30 between 10040 and 16400 order by lfd_nr desc) 
	
	-----------------------------erfasse status-------------------------------------------------------------
	set @pk_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@pk)
	set @ext_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@ext)
	set @kue_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@kue)
	set @sd_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@sd)
	set @pp_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@pp)
	set @rm_status	= (select auftrag_status from ku_rohre_im_prozess where qidnr=@rm)
	
	if not exists (select lfd_nr from kl_20 where qidnr=@pk and len(lfd_nr) > 0)
	begin
		delete from kl_10 where qidnr = @pk
	
		if len(@pk) > 0
			insert into kl_20 (qidnr, status) values (@pk, @pk_status)
	end
	
	------------ ext
	if not exists (select lfd_nr from kl_30 where qidnr=@ext and len(lfd_nr) > 0)
	begin
		delete from kl_20 where qidnr=@ext
		delete from kl_30 where qidnr=@ext
	
		if len(@ext) > 0
			insert into kl_30 (qidnr,status) values (@ext,@ext_status)
	end
	
	------------ sd
	if not exists (select lfd_nr from kl_40 where qidnr=@sd and len(lfd_nr) > 0)
	begin
		delete from kl_30 where qidnr=@sd
		delete from kl_40 where qidnr=@sd
	
		if len(@sd) > 0
			insert into kl_40 (qidnr,status) values (@sd,@sd_status)
	end
	
	------------ kue
	if not exists (select lfd_nr from kl_50 where qidnr=@kue and len(lfd_nr) > 0)
	begin
		delete from kl_40 where qidnr=@kue
		delete from kl_50 where qidnr=@kue
			
		if len(@kue) > 0
			insert into kl_50 (qidnr,status) values (@kue,@kue_status)
	end
	
	--deaktiviert takt rm pp
	if not exists (select lfd_nr from kl_60 where qidnr=@pp and len(lfd_nr) > 0)
	begin
		delete from kl_50 where qidnr=@pp
		delete from kl_60 where qidnr=@pp
		
		if len(@pp) > 0
			insert into kl_60 (qidnr,status) values (@pp,@pp_status)
	end
		
	-- deaktiviert taktrm rm
	if exists (select lfd_nr from kl_70 where qidnr=@rm and len(lfd_nr) > 0)
		delete from kl_60 where qidnr=@rm
	else if len(@rm) > 0
		insert into kl_70 (qidnr,status) values (@rm,@rm_status)

	SET NOCOUNT OFF

	return

end

Ach ja und sprechendere Namen vor allem bei dem Procedure-Namen wäre toll...


Gruß
Thomas