Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Unanswered: 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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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/2012
    Wim

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •