| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-02-10, 15:16
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 1
|
|
|
SQL Function slow improvement
|
|
Hi,
I have funcion taking a lot of time to run.
Do you have any idea how to optimize it? Rewtire etc.
CREATE FUNCTION [OVERT09].[FN$GET_POPIS_FIELD]()
RETURNS @tab table (mt_id int,mt_typ_id int,p0 varchar(255),p1 varchar(255),p2 varchar(255),
p3 varchar(255),p4 varchar(255),p5 varchar(255),p6 varchar(255),p7 varchar(255),
p8 varchar(255),p9 varchar(255),p10 varchar(255),
dt_jednotka varchar(255))
--varchar(254)
AS
BEGIN
/*
PORADIE_STR varchar(34) => xxaabbccddeeffgghhiijj
xx = bar typ
aa = ktory MAT_aa je na prvej pozicii
bb = ktory MAT_bb je na druhej pozicii
.
.
.
jj = ktory MAT_jj je na sestnastej pozicii
*/
declare @mt_id int, @mt_typ_id int, @popis varchar(255),@dt_jednotka varchar(255)
declare @pole varchar(255), @i int
declare @dlzka int,@start_pola int,@end_pola int, @poradie int
declare @p0 varchar(255),@p1 varchar(255),@p2 varchar(255),@p3 varchar(255),@p4 varchar(255),
@p5 varchar(255),@p6 varchar(255),@p7 varchar(255),@p8 varchar(255),@p9 varchar(255),@p10 varchar(255)
declare cur cursor for select mt_id, mt_typ_id,popis,dt_jednotka
from overt09.vw$mt_mat_current_popis_lss
open cur
fetch next from cur into @mt_id,@mt_typ_id,@popis,@dt_jednotka
while (@@fetch_status=0)
begin
set @dlzka=len(@popis)
set @i=0
set @p0=''
set @p1=''
set @p2=''
set @p3=''
set @p4=''
set @p5=''
set @p6=''
set @p7=''
set @p8=''
set @p9=''
set @p10=''
set @poradie=0
set @popis=substring(@popis,0,len(@popis)-2)
set @i=3
set @pole=''
while (@dlzka>=@i)
begin
if (substring(@popis,@i,1)!=char(31))
begin
set @pole=@pole+substring(@popis,@i,1)
set @i=@i+1
end
else
begin
if (@poradie=0)
set @p0=@pole
if (@poradie=1)
set @p1=@pole
if (@poradie=2)
set @p2=@pole
if (@poradie=3)
set @p3=@pole
if (@poradie=4)
set @p4=@pole
if (@poradie=5)
set @p5=@pole
if (@poradie=6)
set @p6=@pole
if (@poradie=7)
set @p7=@pole
if (@poradie=8)
set @p8=@pole
if (@poradie=9)
set @p9=@pole
if (@poradie=10)
set @p10=@pole
set @poradie=@poradie+1
set @pole=''
set @i=@i+2
end
end
insert into @tab (mt_id,mt_typ_id,p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 ,dt_jednotka)
values (@mt_id,@mt_typ_id,@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7 ,@p8,@p9,@p10,@dt_jednotka)
fetch next from cur into @mt_id,@mt_typ_id,@popis,@dt_jednotka
end
close cur
deallocate cur
return --@tab
END
|
|
09-03-10, 11:07
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 1,160
|
|
Extracting a substring by copying part of a string character by character is not the fastest way to go when MSSQL provides pre-build functions for that. Nor is using a cursor the best way to go. You could try and see if this function is any faster.
I have no clue what a "popis" or "jednotka" mean.
Code:
DROP TABLE current_popis
GO
CREATE TABLE current_popis(
mt_id int,
mt_typ_id int,
popis VARCHAR(255),
dt_jednotka VARCHAR(255)
)
GO
INSERT INTO current_popis(mt_id, mt_typ_id, popis, dt_jednotka) VALUES
(1, 1, '', 'dt_jednotka'),
(2, 1, char(31) + ' ' + 'Popis1' + char(31) + ' ' + 'Popis2' + char(31) + ' ' + 'Popis3' + char(31) + ' ' + char(31) + ' ' + 'Popis5' + char(31) + ' ', 'dt_jednotka')
GO
DROP FUNCTION dbo.FN_GET_POPIS_FIELD
GO
CREATE FUNCTION dbo.FN_GET_POPIS_FIELD()
RETURNS @tab table (
mt_id int,
mt_typ_id int,
p0 varchar(255),
p1 varchar(255),
p2 varchar(255),
p3 varchar(255),
p4 varchar(255),
p5 varchar(255),
p6 varchar(255),
p7 varchar(255),
p8 varchar(255),
p9 varchar(255),
p10 varchar(255),
dt_jednotka varchar(255)
)
AS
BEGIN
declare @mt_id int, @mt_typ_id int, @popis varchar(255),@dt_jednotka varchar(255)
declare @pole varchar(255), @i int, @poradie int
declare @p0 varchar(255), @p1 varchar(255), @p2 varchar(255), @p3 varchar(255)
declare @p4 varchar(255), @p5 varchar(255), @p6 varchar(255), @p7 varchar(255)
declare @p8 varchar(255), @p9 varchar(255), @p10 varchar(255)
declare cur cursor for
select mt_id, mt_typ_id, popis, dt_jednotka
from current_popis
open cur
fetch next from cur into @mt_id, @mt_typ_id, @popis, @dt_jednotka
while (@@fetch_status=0)
begin
set @p0=''
set @p1=''
set @p2=''
set @p3=''
set @p4=''
set @p5=''
set @p6=''
set @p7=''
set @p8=''
set @p9=''
set @p10=''
set @poradie = 0
SELECT @i = charindex(char(31), @popis)
if @i > 0
set @popis = substring(@popis, @i+2, len(@popis)-@i+2)
else
set @popis = ''
while (len(@popis) > 0)
begin
SELECT @i = charindex(char(31), @popis)
if @i > 0
BEGIN
SET @pole = substring(@popis, 0, @i)
if (@poradie=0)
set @p0 = @pole
if (@poradie=1)
set @p1=@pole
if (@poradie=2)
set @p2=@pole
if (@poradie=3)
set @p3=@pole
if (@poradie=4)
set @p4=@pole
if (@poradie=5)
set @p5=@pole
if (@poradie=6)
set @p6=@pole
if (@poradie=7)
set @p7=@pole
if (@poradie=8)
set @p8=@pole
if (@poradie=9)
set @p9=@pole
if (@poradie=10)
set @p10=@pole
set @poradie = @poradie+1
set @popis = substring(@popis, @i+2, len(@popis)-@i+2)
END
else
set @popis = ''
END
insert into @tab (mt_id, mt_typ_id, p0, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, dt_jednotka)
values(@mt_id, @mt_typ_id, @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @dt_jednotka)
fetch next from cur into @mt_id, @mt_typ_id, @popis, @dt_jednotka
end
close cur
deallocate cur
return --@tab
END
GO
SELECT *
from dbo.FN_GET_POPIS_FIELD()
GO
__________________
With kind regards . . . . . SQL Server 2000/2005/2008/2008 R2 Earned beers: 13
Wim
Beware of bugs in the above code; I have only proved it correct, not tried it. -- Donald Knuth
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL Function slow improvement
