|
|
Posted by Inna on 11/14/07 15:37
Hello,
I have a question
I created a SP that by using dynamic sql access SP in all databases
with certain name and get the output into a temp table and returns
it's contents.
The stored procedure in other databases is very simple couple of joins
and where clause for the parameter passed..
The problem is, there are a lot of activities happening on server, so
when the execution plan is flashed, it might take a minute to execute,
in the mean time the second execution will take 1 to 5 second.
here is the example:
--main procedure
CREATE procedure dbo.P_main
( @User varchar(20),
@Environment varchar(20)
)
as
SET NOCOUNT ON
Declare @Exec nvarchar(4000),@DBNAme varchar(100) , @err
int
Create Table #Return
(
DBName Varchar(100),
UserId varchar(100),
first_name varchar(32),
last_name varchar(32),
ProjectName Varchar(100),
Project_translated_name nvarchar(100) null,
Project_Language int
)
select @Environment = (case @Environment when 'PR' then 'DBPR
%'
when 'SI' then 'DBSI%'
when 'TR' then 'DBTR%'
when 'DV' then 'DBDV%_V%'
when 'SI' then 'DBSI%'
when 'IT' then 'DBIT%'
when 'PP' then 'DBPP%_V%'
else 'ERROR' end)
if @Environment = 'ERROR'
begin
select 'Wrong environment!!!'
return
end
DECLARE @TEMP_VAR TABLE (NAME SYSNAME)
INSERT INTO @TEMP_VAR
SELECT NAME
From Master.Dbo.Sysdatabases with (nolock)
where Name like @Environment
and mode = 0
and status<32
Declare DBCur Cursor fast_forward local For Select
T.name
FROM @TEMP_VAR AS T
order by T.Name FOR READ ONLY option(KEEPFIXED PLAN )
Open DBCur
Fetch DBCur into @DbName
While @@FEtch_Status<>-1
Begin
if 0=(select mode from master.dbo.Sysdatabases with (nolock)
where name = @DbName and status<32)
begin
Set @Exec = 'If exists (Select * From ' + @DBName +
'.dbo.Sysobjects with (nolock) where name =
''P_ALL_DB'' )
Insert into #Return
EXEC ' + @DBName + '.dbo.P_ALL_DB ' + ''''+ @User
+''''
exec sp_executesql @Exec
end
Fetch DBCur into @DbName
End
Close DBCur
Deallocate DBCur
Select DBName ,
UserId ,
first_name ,
last_name ,
ProjectName ,
Project_translated_name ,
Project_Language,
From #Return option(KEEPFIXED PLAN )
Drop table #Return
SET NOCOUNT OFF
go
--procedure in each database
CREATE PROCEDURE dbo.P_ALL_DB
(@Userid varchar(20))
AS
SET NOCOUNT ON
Select
DB_Name() as DBName,
USERID,
first_name,
last_name,
Project_Name ,
Project_Name Project_translated_name,
language as Project_Language
from table1 with (nolock )
join table2 with (nolock ) on .......
Where USERID = @Userid
SET NOCOUNT OFF
GO
Please tell me what I can do to improve this situation.
Thank you
Navigation:
[Reply to this message]
|