April 11, 2007 at 4:40 am
Hi All
I try to create a proc that selects data from a view and then populate a table based on certain criteria. when I execute the script I get results but when I create the proc it executes for 30mins and ongoing....
Below is the script:
CREATE PROCEDURE prc_DailyPerformance
AS
DECLARE @FROMDATE varchar(8)
DECLARE @TODATE datetime
DECLARE @BASEDATE datetime
Print ' Executing Date : This process execute on: '
select @ToDate = dateadd(hour, - DATEPART ( hour , getdate() ) , getdate())
print @ToDate
-----
Print ' Process Date : This run is for date: '
Select @BASEDATE = dateadd(d, -1, getdate())
Select @BASEDATE = dateadd(hour, - DATEPART ( hour , getdate() ) , @BASEDATE)
print @BASEDATE
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempTab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempTab]
GO
CREATE TABLE [dbo].[TempTab] (
[ComputerDNSName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ComputerName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PerformanceInstanceName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PerformanceObjectName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PerformanceCounterName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeSampled] [varchar] (85) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SampledValue] [float] NULL
) ON [PRIMARY]
GO
insert into [dbo].[TempTab]
SELECT CD.DNSName AS ComputerDNSName, CD.ComputerName_PK AS ComputerName, CDD.InstanceName_PK AS PerformanceInstanceName,
CDD.ObjectName_PK AS PerformanceObjectName, CDD.CounterName_PK AS PerformanceCounterName,
substring(convert(varchar,SNDF.DateTimeSampled,126),1,10) AS TimeSampled,
SNDF.SampleValue AS SampledValue
FROM dbo.SC_SampledNumericDataFact_View SNDF INNER JOIN
dbo.SC_ComputerDimension_View CD ON SNDF.Computer_FK = CD.SMC_InstanceID INNER JOIN
dbo.SC_CounterDetailDimension_View CDD ON SNDF.CounterDetail_FK = CDD.SMC_InstanceID
Select ComputerDNSName, ComputerName, PerformanceInstanceName, PerformanceObjectName, PerformanceCounterName,
TimeSampled, min(SampledValue) AS MINValue, max(SampledValue) AS MAXValue, avg(SampledValue) AS AVGValue, COUNT(SampledValue) AS COUNTValue, sum(SampledValue) as SUMValue,
stdev(SampledValue) AS STDEVValue
from [dbo].[TempTab]
GROUP BY ComputerDNSName, ComputerName, PerformanceInstanceName, PerformanceObjectName, PerformanceCounterName,
TimeSampled
truncate table [dbo].[TempTab2]
INSERT INTO [dbo].[TempTab2]
Select ComputerDNSName, ComputerName, PerformanceInstanceName, PerformanceObjectName, PerformanceCounterName,
TimeSampled, min(SampledValue) AS MINValue, max(SampledValue) AS MAXValue, avg(SampledValue) AS AVGValue, COUNT(SampledValue) AS COUNTValue, sum(SampledValue) as SUMValue,
stdev(SampledValue) AS STDEVValue
from [dbo].[TempTab]
GROUP BY ComputerDNSName, ComputerName, PerformanceInstanceName, PerformanceObjectName, PerformanceCounterName,
TimeSampled
Thank you in advance
Anche
April 12, 2007 at 5:40 am
Ok, a couple of things. First, all of the white space you have in your posted procedure syntax is making it hard to read. Second, have you run sp_helptext on your procedure and looked at the syntax? I think you want the entire piece of T-SQL you have posted to be the procedure, but you have a batch separator (GO) in the middle of it. Your procedure syntax is going to stop there. You also have this TempTab that is being dropped and created each time the procedure runs. In a procedure, it is a good practice to not alter schema as it makes dependencies hard to trace - I would also suspect you really want to use a temporary table #TempTab so you do not have a concurrency issue.
Once that is all dealt with, you need to run the script and then run the stored procedure and compare the execution plans. This will tell you where the performace difference is.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply