Parallel execution of stored procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Parallel execution of stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-19 : 07:43:42

Superman writes "Hi,

I have written a stored procedure will does lot of work. It calls quite a few functions, executes several queries and creates and drops table during it's execution period. All this takes a couple of seconds to execute, which is not a problem at all. The real problem comes in when multiple users try to execute this stored procedure at the same time. In such a case the time factor multiplies. If it normally takes 2 seconds to execute then for 10 concurrent users it takes 20 seconds for each user. So each user has to wait for 20 seconds which would otherwise just take 2 seconds.

So is there any way to avoid the time multiplication?

Thanks,
Superman"

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-19 : 08:28:07

lets see some sample data, tadble ddl, query code and expected results. I suspect you are encountering some blocking.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

hash
Starting Member

2 Posts

Posted - 2005-09-20 : 01:19:29

Hi Sean,

Thanks for the response... I'll try to explain you the steps of the stored procedure.

There's a table called items which has got name, description, price etc fields. The stored procedure accepts keywords as parameter and searches in the items table. First it looks for the exact phrase and keeps the result in a table (named after the session id of the user). Then it looks for all the matching words and stores it in the same table. Afterwards it looks for any matching words and stores it in the same table again. So basically the table consists of 3 layers with each layer having different type of matched records.

The stored procedure also filters/sorts out the records based on criteria's like price (using a UDF), category (using a UDF), and count of words found (using a UDF).

The result of the temporary table is returned and the table is deleted.

I hope this helps. Do you still need some sample data?

Thanks,
Superman

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 01:25:57

you can modify the SP to not execute if there are still users executing it, more like "only one user can execute the sp at a time"

you can query current transaction in sysprocesses

or, modify your SP to use a table variable instead of a temporary table since you're basically doing a search/select

there shouldn't be any problem with parallel execution unless you're not doing select queries only?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 01:47:56

"First it looks for the exact phrase and keeps the result in a table (named after the session id of the user)"

Would be better [quicker and gets rid of competition for resources] to use a temporary table - particularly an @MyName table unless there are lots and lots of rows.

Make sure it is created with a Primary Key (even if very few rows will be stored)

Posting the code for the SProc, and maybe the DDL for the tables it uses, and some sample INSERT statements is likely to get people here having a go at improving it. Otherwise we're kinda guessing what the problems might be ...

Kristen

hash
Starting Member

2 Posts

Posted - 2005-09-20 : 03:29:49

Here's the SP

==================================================== ==================================================

CREATE PROC dbo.advance_search @strSearch1 varchar(1000),@sid varchar(100) ,@swhereClause varchar(1000),@ntype tinyint,@member_code int,@thirdlayer int

AS

declare @words varchar(100)
declare @wrdsplit varchar(100)
declare @w varchar(50)
declare @t1 varchar(50)
declare @t2 varchar(50)
declare @t3 varchar(50)
declare @qry1 varchar(1000)
declare @qry2 varchar(2000)
declare @qry varchar(1000)
declare @swhere varchar(2000)
declare @flds varchar(500)
declare @strflds varchar(500)
declare @result varchar(50)
declare @objName varchar(50)
declare @max_rec int
declare @recs int
declare @strSearch varchar(1000)

-----------
set @strSearch=Replace(@strSearch1,'''','''''')
set @objName='prod_mast_forsearch'
set @words='w_' + @sid
set @t1='t1_' + @sid
set @t2='t2_' + @sid
set @t3='t3_' + @sid
set @result='r_'+ @sid

set @max_rec=50
set @recs=0

set @flds='prod_code ,prod_name,dbo.trunc_desc(prod_desc_plain ,''' + @strSearch +''') as prod_desc_plain,dbo.search_get_price(prod_code,' + ltrim(rtrim(str(@member_code))) + ') as prod_cprice1,prod_cprice,prod_thumb,prod_unit,prod_shopcode,prod_collcode'

--print @flds
--set @flds='prod_code ,prod_name,prod_desc_plain'
set @strflds='prod_code+prod_name+isnull(prod_desc_plain,'''')+isnull(prod_keywords,'''')'
-------------------

-----------------------
if lower(right(@strSearch,1))='s'
set @qry1= 'dbo.occurs(''' + substring( @strSearch,1,len(@strSearch)-1) +''',' + @strflds +')'
else
set @qry1= 'dbo.occurs(''' + @strSearch +''',' + @strflds +')'
-----for exact phrase----------------------------------------
set @swhere=Replace(@qry1 ,'))','))>0 ')

if exists(select * from sysobjects where name = @t1 )
exec('drop table ' + @t1)

set @qry1='select ' + @flds + ',' + @qry1+ ' as cnt into ' + @t1 + ' from ' + @objName +' where ' + @swhere +' ' +@swhereClause
exec (@qry1 )

set @recs=@@rowcount

----------------------------------------------------------------------
-----for splitting keywords----------------------------------------
set @qry=''

exec('dbo.Split '''+ @strSearch + ''',''' + @sid +'''')
exec ('declare wcursor cursor for select w from ' + @words )
open wcursor
set @swhere=''
fetch next from wcursor into @w
while @@fetch_status=0
begin

if @qry<>''
set @qry=@qry + '+'
set @qry=@qry + 'dbo.occurs('''+ Replace(@w,'''','''''') + ''','+ @strflds + ')'
fetch next from wcursor into @w
end
close wcursor
deallocate wcursor
-----for all keywords----------------------------------------
set @swhere=Replace(@qry,'+dbo.', ' ¿and¿ dbo.')

set @swhere=Replace(@swhere,'))',' ))>0 ' )

if @ntype<>1
begin

set @qry2= 'select ' + @flds +',' + @qry + ' as cnt into ' + @t2 +' from ' + @objName + ' where ' + Replace(@swhere,'¿and¿', 'and') +' and prod_code not in(select prod_code from ' + @t1 +') ' +@swhereClause

end
else
begin
set @qry2= 'select ' + @flds +',' + @qry + ' as cnt into ' + @t2 +' from ' + @objName + ' where 1=2'
end
if exists(select * from sysobjects where name = @t2)
exec('drop table ' + @t2)
exec(@qry2)

set @recs=@recs+ @@rowcount
-----=------------------------------------------------------------------------

-----for any keywords----------------------------------------
set @swhere=Replace(@swhere,'¿and¿', 'or')

if exists(select * from sysobjects where name = @t3)
exec('drop table ' + @t3)
if @ntype=0 and @recs<@max_rec --and (@thirdlayer=0 or @thirdlayer=3)
begin
set @qry2= 'select top 100 ' + @flds +',' + @qry + ' as cnt into ' + @t3 +' from ' + @objName + ' where (' + @swhere +') and not (prod_code in (select prod_code from ' + @t1 +' ) or prod_code in(select prod_code from ' + @t2 +')) ' +@swhere
Clause
end
else
begin
set @qry2= 'select ' + @flds +',' + @qry + ' as cnt into ' + @t3 +' from ' + @objName + ' where 1=2'
end
exec(@qry2)

set @recs=@recs+@@rowcount
-------------------------------------------------------------------------
if exists(select * from sysobjects where name = @result)
exec('drop table ' + @result)

exec ('select * ,1 as layer into ' + @result + ' from ' + @t1 + ' union all select * ,2 from ' + @t2 + ' union select *,3 from ' + @t3 )

-------------------------------------------------------------------------
if exists(select * from sysobjects where name = @t1)
exec('drop table ' + @t1)
if exists(select * from sysobjects where name = @t2)
exec('drop table ' + @t2)
if exists(select * from sysobjects where name = @t3)
exec('drop table ' + @t3)
if exists(select * from sysobjects where name = @words)
exec('drop table ' + @words)
-------------------------------------------------------------------------

return

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 03:49:33

you may want to modify it NOT to use dynamic sql

also, i see the parallel execution thing, problem is you're creating permanent tables (real tables) then dropping them after done.

when other users do the same, you're referring to the same table, using the existing data plus additional ones that might be added by user2, prolonging search time for user1, user2 and any other succeeding users...

so my suggested to do list for you:
1. remove dynamic sql from your sp
2. either use a staging table for your search fields/keywords, with an additional field for user if you want that table to be reused (do not drop, just delete for userX entry), or better use table variables instead

HTH

--------------------
keeping it simple...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-20 : 05:01:10

...and IF possible DUMP the CURSOR....(evil, evil things.....

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources