| Author |
 Topic  |
|
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 08/30/2006 : 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
United Kingdom
12543 Posts |
Posted - 08/30/2006 : 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
Azores
344 Posts |
Posted - 08/30/2006 : 08:49:46
|
the tables has a max 18k records. so, i dont index them.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/30/2006 : 08:53:06
|
Is it this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70802 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 |
Edited by - SwePeso on 08/30/2006 08:54:31 |
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/30/2006 : 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
United Kingdom
12543 Posts |
Posted - 08/30/2006 : 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
Azores
344 Posts |
Posted - 08/30/2006 : 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
Sweden
29156 Posts |
Posted - 08/30/2006 : 09:06:50
|
Last statementdeclare @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 bedeclare @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 |
Edited by - SwePeso on 08/30/2006 09:09:07 |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 08/30/2006 : 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
Azores
344 Posts |
Posted - 08/30/2006 : 09:17:39
|
god damnit..now he tells me its not this SP... its another sp. im working with donkeys...
|
|
|
|
gongxia649
So Suave
Azores
344 Posts |
Posted - 08/30/2006 : 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
United Kingdom
12543 Posts |
Posted - 08/30/2006 : 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
United Kingdom
12543 Posts |
Posted - 08/30/2006 : 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
Sweden
29156 Posts |
Posted - 08/30/2006 : 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 |
Edited by - SwePeso on 08/30/2006 15:00:47 |
|
|
| |
Topic |
|
SP running slow
