SP running slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP running slow

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 08:34:11

i have written a SP, but it eventually runs slower. i have to run this SP 500 times.

do you know what is causing that?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 08:43:37

Poor code.
Poor structure.
Poor indexing.
Bad statistics.
Poor architecture.
Dust in the server.

Difficult to say.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 08:49:46

the tables has a max 18k records. so, i dont index them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 08:53:06

Is it this [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70802[/url] stored procedure?

quote:
Originally posted by gongxia649

the tables has a max 18k records. so, i dont index them.

Index would be a great idea to speed up things. Eventually. It depends on the stored procedure.

Peter Larsson
Helsingborg, Sweden

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 08:56:20

-- Input parameter is the name of the town table for TRIO

-- drop proc usp_ImportTown_OnlyTrioFormat
create proc [dbo].[usp_ImportTown_OnlyTrioFormat] @tableName varchar(300) as
-- alter proc usp_importTown_OnlyTrioFormat @tableName varchar(300) as
set nocount on
begin

declare @sql varchar(200)

set @sql = 'insert into TempAddressParsingTable (id_voter, id_town, ad_num, ad_str1)
select id_voter, id_town, ad_num, ad_str1 from hava_import.dbo.' + quotename(@tableName)

truncate table TempAddressParsingTable
-- PRINT @SQL
exec (@sql)

EXEC dbo.usp_Trio_AddressParsing
-- exec dbo.usp_Trio_updateDCDE

declare @x varchar (500)
set @x = 'update hava_DCDE.dbo.' + quotename(@tableName) +
'set
ad_num = a.ad_num,
ad_str1 = a.ad_str1,
ad_num_suffix_a = a.ad_num_suffix_a,
ad_num_suffix_b = a.ad_num_suffix_b,
ad_unit = a.ad_unit
from hava_dev.dbo.TempAddressParsingTable a
inner join hava_DCDE.dbo.' + quotename(@tablename) + 'dcde
on dcde.id_town = a.id_town
and dcde.id_voter = a.id_voter'

-- print @sql
exec (@x)
end
--------------------------------------------------------------------------------

-- usp_ImportTown_OnlyTrioFormat '00485'
-- usp_ImportTown_OnlyTrioFormat '00513'

-- select * from TempAddressParsingTable
-- select* from HAVA_DCDE.dbo.[00513]

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 09:01:24

Is this 500 times for 500 tables?
Whic bit is slow? usp_Trio_AddressParsing or one of the statements you have posted?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 09:04:28

Is this 500 times for 500 tables? yes
Whic bit is slow? usp_Trio_AddressParsing or one of the statements you have posted? not sure, the guy who is supposed to run it told me it eventually get slower. i think he assumes is my SP.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 09:06:50

Last statement

declare @x varchar (500)
set @x = 'update hava_DCDE.dbo.' + quotename(@tableName) +
'set 
ad_num = a.ad_num,
ad_str1 = a.ad_str1,
ad_num_suffix_a = a.ad_num_suffix_a,
ad_num_suffix_b = a.ad_num_suffix_b,
ad_unit = a.ad_unit
from hava_dev.dbo.TempAddressParsingTable a
inner join hava_DCDE.dbo.' + quotename(@tablename) + 'dcde 
on dcde.id_town = a.id_town
and dcde.id_voter = a.id_voter'

should be

declare @x varchar (500)
set @x = 'update dcde set 
ad_num = a.ad_num,
ad_str1 = a.ad_str1,
ad_num_suffix_a = a.ad_num_suffix_a,
ad_num_suffix_b = a.ad_num_suffix_b,
ad_unit = a.ad_unit
from hava_dev.dbo.TempAddressParsingTable a
inner join hava_DCDE.dbo.' + quotename(@tablename) + 'dcde 
on dcde.id_town = a.id_town
and dcde.id_voter = a.id_voter'

since you have aliased one of the tables.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 09:07:41

There doesn't look to be anything there that should cause problems assuming the tables are quite small.
You should put some logging into the sp.
Log the tablename and start and end of each step and rowcount so you can see what's taking the time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 09:17:39

god damnit..now he tells me its not this SP... its another sp. im working with donkeys...

gongxia649
So Suave

344 Posts

Posted - 2006-08-30 : 09:49:18

quote:
Originally posted by nr

There doesn't look to be anything there that should cause problems assuming the tables are quite small.
You should put some logging into the sp.
Log the tablename and start and end of each step and rowcount so you can see what's taking the time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

how do you put logging into the sp?
how do you log the tablename?
why do we want to use rowcount? i know it returns the last rows affected. why is it useful?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 10:09:35

rowcount would be the number of rows affected by the queries i.e number of rows in the table

create a trace table

create table trace (id int identity, dte datetime default getdate(), entity1 varchar(30), entity2 varchar(30), data varchar(8000))
then the sp becomes this.
You can also add a @debug flag and only log when it's on.

set nocount on
begin

declare @sql varchar(200)

set @sql = 'insert into TempAddressParsingTable (id_voter, id_town, ad_num, ad_str1)
select id_voter, id_town, ad_num, ad_str1 from hava_import.dbo.' + quotename(@tableName)

truncate table TempAddressParsingTable
-- PRINT @SQL
insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'start sql1', @sql
exec (@sql)
insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'end sql1 ' + convert(varchar(20),@@rowcount), @sql

insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'start sp', 'usp_Trio_AddressParsing'
EXEC dbo.usp_Trio_AddressParsing
insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'end sp', 'usp_Trio_AddressParsing'
-- exec dbo.usp_Trio_updateDCDE

declare @x varchar (500)
set @x = 'update hava_DCDE.dbo.' + quotename(@tableName) +
'set
ad_num = a.ad_num,
ad_str1 = a.ad_str1,
ad_num_suffix_a = a.ad_num_suffix_a,
ad_num_suffix_b = a.ad_num_suffix_b,
ad_unit = a.ad_unit
from hava_dev.dbo.TempAddressParsingTable a
inner join hava_DCDE.dbo.' + quotename(@tablename) + 'dcde
on dcde.id_town = a.id_town
and dcde.id_voter = a.id_voter'

-- print @sql
insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'start sql2', @x
exec (@x)
insert trace (entity1, entity2, data) select 'usp_ImportTown_OnlyTrioFormat', 'end sql2 ' + convert(varchar(20),@@rowcount), @x
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 11:55:21

SELECT * FROM HAVA_IMPORT.dbo."00485"

Getting rid of that would help.
Put some tracing in this or run the profilker to see what is taking the time.
Not much is going to use indexes so combining the updates could help.

UPDATE dbo.Name_Pharse_Stg_Tbl3
SET NM_MID = REPLACE(REPLACE(NM_MID,'IV',''),'V','')
WHERE PATINDEX('%IV%',NM_MID) > 0
or PATINDEX('%V%',NM_MID) > 0

in fact this could be
UPDATE dbo.Name_Pharse_Stg_Tbl3
SET NM_MID = REPLACE(REPLACE(NM_MID,'IV',''),'V','')
WHERE PATINDEX('%V%',NM_MID) > 0

UPDATE dbo.Name_Pharse_Stg_Tbl3
SET NM_MID = REPLACE(NM_MID,'II','')
WHERE PATINDEX('%II%',NM_MID) > 0

UPDATE dbo.Name_Pharse_Stg_Tbl3
SET NM_MID = REPLACE(NM_MID,'III','')
WHERE PATINDEX('%III%',NM_MID) > 0

Are you sure running these in that order is what you want?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 15:00:00

quote:
Originally posted by gongxia649

2006-08-30 14:19:53.197 parsingName start sql 0 update jr
2006-08-30 14:19:53.230 parsingName end sql 0 update jr
2006-08-30 14:19:53.243 parsingName start sql 1 update (
2006-08-30 14:19:53.260 parsingName end sql 0 update (
2006-08-30 14:19:53.277 parsingName start sql 1 update )
2006-08-30 14:19:53.290 parsingName end sql 0 update )
2006-08-30 14:19:53.290 parsingName start sql 1 update ?
2006-08-30 14:19:53.323 parsingName end sql 0 update ?
2006-08-30 14:19:53.323 parsingName start sql 1 update jr
2006-08-30 14:19:53.353 parsingName end sql 0 update sr
2006-08-30 14:19:53.353 parsingName start sql 1 update sr
2006-08-30 14:19:53.387 parsingName end sql 2 update II
2006-08-30 14:19:53.387 parsingName start sql 1 update II
2006-08-30 14:19:53.417 parsingName end sql 0 update III
2006-08-30 14:19:53.417 parsingName start sql 1 update III
2006-08-30 14:19:53.447 parsingName end sql 2 update IV
2006-08-30 14:19:53.447 parsingName start sql 1 update IV
2006-08-30 14:19:53.480 parsingName end sql 25 update IV

seems to be ok.

Yes. Just under 300 ms. And runned 500 times, that is 2.5-3.0 minutes...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources