SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP running slow
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  08:34:11  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  08:49:46  Show Profile  Visit gongxia649's Homepage  Reply with Quote
the tables has a max 18k records. so, i dont index them.




Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/30/2006 :  08:53:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  08:56:20  Show Profile  Visit gongxia649's Homepage  Reply with Quote
-- 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]





Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/30/2006 :  09:01:24  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  09:04:28  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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.



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/30/2006 :  09:06:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 08/30/2006 09:09:07
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/30/2006 :  09:07:41  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  09:17:39  Show Profile  Visit gongxia649's Homepage  Reply with Quote
god damnit..now he tells me its not this SP... its another sp. im working with donkeys...



Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/30/2006 :  09:49:18  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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?



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/30/2006 :  10:09:35  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/30/2006 :  11:55:21  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 08/30/2006 :  15:00:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000