stored procedure take more time to generate outpu

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

Transact-SQL (2000)

stored procedure take more time to generate outpu

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-04-12 : 07:44:14

Hi
I have write a store procedure given below execute fine but take more time(30 second).In This i do two task
1:-If a table exists then Drop It.
2:-next i copy a Table's records into another table.

Note:-In procedure Dynamic sql query used because Table name come from
parameter.

Give me some sugession to increase speed.procedure is:-
---------------------------------------------------------------------
create procedure record
@user_id varchar(20)=null,@frtime varchar(100)=null,@totime varchar(100)=null
as
set nocount on
declare @yes varchar(10),@query nvarchar(4000)
set @yes='yes'
IF OBJECT_ID(@user_id) IS NOT NULL
begin
declare @qrydroptable nvarchar(100)
set @qrydroptable='DROP table ' + @user_id
exec(@qrydroptable)
end
select @query='select distinct tempaddplace.* into'+' '+ @user_id +' '+ ' from JOURNEY_REGT_DET
inner join assign_vehicle_access on JOURNEY_REGT_DET.vehicle_id=assign_vehicle_access.vehicle_id inner
join tempaddplace on tempaddplace.Jrid=JOURNEY_REGT_DET.JOURNEY_ID where assign_vehicle_access.Assign='''+@yes+''' and
tempaddplace.Timerecorded >=convert(datetime,'''+@frtime+''',103) and tempaddplace.Timerecorded <=
convert(datetime,'''+@totime+''',103) order by Timerecorded asc'
exec(@query)

---------------------------------------------------------------
exec by this:-

exec record 'USR012','10/04/2007 16:20:34.000','12/04/2007 16:20:34.000'

Ranjeet Kumar Singh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-12 : 09:04:49

To increase speed, I suggest a better database design. Your stored procedures should never accept database objects as parameters, and you should not be creating and dropping tables dynamically. Your tables should be constants that do not change except when you redesign your application. It is a poor design to have 1 table per UserID or something like that; all of your data should be in 1 table with the userID value as a key in that table.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-12 : 10:48:49

For more information on Dynamic SQL, refer
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources