How can i reprogram this? It's taking forever to run.

I need to rewrite a sp. I'm pasting the major part of the sp down here. Also
,
if possible, can you tell me which columns to index? Thank you.
DECLARE @.procAsset int,
@.procStartDate datetime,
@.procEndDate datetime
DECLARE assetdate_cur CURSOR FOR
SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)
)
+ '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
StartDate
FROM mapITAsset2ExecutablesUsage
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
OPEN assetdate_cur
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
--Delete old data for the day from the table
DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
@.procAsset AND RunDate = @.procStartDate
INSERT INTO mapITAsset2ExecutablesUsageSummary
(ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
RunDate)
SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
WHEN SUM(Secs) IS NULL THEN 0
ELSE SUM(Secs)
END, @.procStartDate
FROM
(
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
@.procEndDate
AND UsageEndDateTime >= @.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
@.procEndDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
UNION ALL
SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
FROM mapITAsset2ExecutablesUsage
WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
@.procEndDate AND UsageEndDateTime >= @.procStartDate
AND ITAssetObjectID = @.procAsset
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
) AS Totals
GROUP BY ExecutableRepositoryID, UserID
FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
END
CLOSE assetdate_cur
DEALLOCATE assetdate_cur
This sp actually summarizes thes data but it just takes forever to
run...(more than 10 hours)
Can someone give me a hint on how can i reprogram this? Thank youYou've got to get rid of the cursor, bottom line. Take out the subquery in
your INSERT INTO...SELECT statement. Put that data into a temp table and
link to that if you need to instead. If you post the DDL statements that
define your tables, I can help you better.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:E02337CF-71FF-4871-8F3C-60CDFC79C4B6@.microsoft.com...
>I need to rewrite a sp. I'm pasting the major part of the sp down here.
>Also,
> if possible, can you tell me which columns to index? Thank you.
>
> DECLARE @.procAsset int,
> @.procStartDate datetime,
> @.procEndDate datetime
> DECLARE assetdate_cur CURSOR FOR
> SELECT DISTINCT ITAssetObjectID, CAST(DATEPART(month, UsageStartDateTime)
> AS VARCHAR(2)) + '/' + CAST(DATEPART(day, UsageStartDateTime) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0'
> AS
> StartDate
> FROM mapITAsset2ExecutablesUsage
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
>
> OPEN assetdate_cur
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.procEndDate = DATEADD(day, 1, @.procStartDate)
> --Delete old data for the day from the table
> DELETE FROM mapITAsset2ExecutablesUsageSummary WHERE ITAssetObjectID =
> @.procAsset AND RunDate = @.procStartDate
> INSERT INTO mapITAsset2ExecutablesUsageSummary
> (ITAssetObjectID, ExecutableRepositoryID, UserID, RunCount, RunTime,
> RunDate)
> SELECT @.procAsset, ExecutableRepositoryID, UserID, SUM(Occur), CASE
> WHEN SUM(Secs) IS NULL THEN 0
> ELSE SUM(Secs)
> END, @.procStartDate
> FROM
> (
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*) AS
> Occur, SUM(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND (UsageEndDateTime < @.procEndDate OR UsageEndDateTime IS NULL)
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, UsageStartDateTime, @.procEndDate))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime >= @.procStartDate AND UsageStartDateTime <
> @.procEndDate
> AND UsageEndDateTime >= @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*), 24*60*60
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime >=
> @.procEndDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> UNION ALL
> SELECT ITAssetObjectID, ExecutableRepositoryID, UserID, COUNT(*),
> SUM(DATEDIFF(second, @.procStartDate, UsageEndDateTime))
> FROM mapITAsset2ExecutablesUsage
> WHERE UsageStartDateTime < @.procStartDate AND UsageEndDateTime <
> @.procEndDate AND UsageEndDateTime >= @.procStartDate
> AND ITAssetObjectID = @.procAsset
> GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID
> ) AS Totals
> GROUP BY ExecutableRepositoryID, UserID
>
> FETCH NEXT FROM assetdate_cur INTO @.procAsset, @.procStartDate
> END
> CLOSE assetdate_cur
> DEALLOCATE assetdate_cur
>
>
> This sp actually summarizes thes data but it just takes forever to
> run...(more than 10 hours)
> Can someone give me a hint on how can i reprogram this? Thank you|||Couple more things:
1. You may also want to consider having a clustered index on
UsageStartDateTime since you are doing a lot of range selection using
operators such as >, >=, <, and <=.
2. You may want to consider using T-SQL extended arguments like
READ_ONLY, OPTIMISTIC locking, etc for your cursor.|||I've acdtually used a temp table as a replacement for the cursor but it has
not given me the expected results. I've taken the initial query and stored i
t
in a temp table. I have then joined that temp table to the map... table and
done all my querying. But the group by stuff throws me off because when i do
selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate which
I
dont want to do.
SELECT DISTINCT ITAssetObjectID,
CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS StartDat
e
,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
'/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2))
+ '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS VARCHAR(4))
+ ' 0:0:0' AS EndDate
into ##SummarizeITAsset3
FROM mapITAsset2ExecutablesUsage (nolock)
WHERE IsSummarized = 0
ORDER BY ITAssetObjectID, StartDate
delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
##SummarizeITAsset3 SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
where mia2eus.RunDate = SITA.StartDate
SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*) AS
Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
into ##Totals
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
mia2eus.UsageStartDateTime < SITA.EndDate
AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime IS
NULL)
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
--2004
--union all
insert into ##Totals
SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second, UsageStartDateTime,
UsageEndDateTime)) AS Secs
FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
SITA
on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
mia2eus.UsageStartDateTime < SITA.EndDate
AND mia2eus.UsageEndDateTime >= SITA.EndDate
GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
.
.
.
.
I'm having problems with the above select into's and the insert into's. How
would you do those and the groupby's? Please help with that. Thank you.
The DDL's are as follows.
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[ProcessID] [int] NOT NULL ,
[UsageStartDateTime] [datetime] NOT NULL ,
[UsageEndDateTime] [datetime] NULL ,
[ServerUpdateTime] [datetime] NULL ,
[IsSummarized] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
[ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[ITAssetObjectID] [bigint] NOT NULL ,
[ExecutableRepositoryID] [int] NOT NULL ,
[UserID] [int] NULL ,
[RunCount] [int] NOT NULL ,
[RunTime] [int] NOT NULL ,
[RunDate] [datetime] NOT NULL
)
GO|||Sorry I don't have time to test this myself right now, but maybe what I have
will get you going in a better direction. I've tried to write things based
on your first procedure you posted:
--First, create a View that joins the mapITAsset2ExecutablesUsage table to
itself based on the ITAssetObjectID.
--This will filter out the IsSummarized records and give you a time
computation for each join result.
--I left out the parsing of the StartDate field since I couldn't tell why
you had that in there in the first place.
--I based the CASE statement on the way you calculated the EndDate field in
your cursor and your UNION statements. I'm sure you could clean it up
better.
CREATE VIEW vw_mapITAsset2ExecutablesUsage
AS
SELECT
M1.ITAssetObjectID,
M1.ExecutableRepositoryID,
M1.UserID,
M1.UsageStartDateTime,
CASE
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
(M2.UsageEndDateTime < DATEADD(day, 1, M1.UsageStartDateTime) OR
M2.UsageEndDateTime IS NULL) THEN DATEDIFF(second, M2.UsageStartDateTime,
M2.UsageEndDateTime)
WHEN M2.UsageStartDateTime >= M1.UsageStartDateTime AND
M2.UsageStartDateTime < DATEADD(day, 1, M1.UsageStartDateTime) AND
M2.UsageEndDateTime >= DATEADD(day, 1, M1.UsageStartDateTime) THEN
DATEDIFF(second, M2.UsageStartDateTime, DATEADD(day, 1,
M1.UsageStartDateTime))
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
>= DATEADD(day, 1, M1.UsageStartDateTime) THEN 86400
WHEN M2.UsageStartDateTime < M1.UsageStartDateTime AND M2.UsageEndDateTime
< DATEADD(day, 1, M1.UsageStartDateTime) AND M2.UsageEndDateTime >=
M1.UsageStartDateTime THEN DATEDIFF(second, M1.UsageStartDateTime,
M2.UsageEndDateTime)
END AS Secs
FROM mapITAsset2ExecutablesUsage M1
INNER JOIN mapITAsset2ExecutablesUsage M2 ON M1.ITAssetObjectID =
M2.ITAssetObjectID
WHERE M1.IsSummarized = 0 AND M2.IsSummarized = 0
---
--Next, your procedure to populate the mapITAsset2ExecutablesUsageSummary
table should be as simple as...
TRUNCATE TABLE mapITAsset2ExecutablesUsageSummary
INSERT INTO mapITAsset2ExecutablesUsageSummary (
ITAssetObjectID,
ExecutableRepositoryID,
UserID,
RunCount,
RunTime,
RunDate)
SELECT M.ITAssetObjectID,
M.ExecutableRepositoryID,
M.UserID,
COUNT(M.ITAssetObjectID),
SUM(Secs),
M.UsageStartDateTime
FROM vw_mapITAsset2ExecutablesUsage M
GROUP BY ITAssetObjectID, ExecutableRepositoryID, UserID,
M.UsageStartDateTime
--Here you get both the count on rows and the summary of the time in seconds
in the same statement.
--
Again, I may not have understood in the time I had your query needs, but I
hope this helps you out better. Keep in mind you can also add indexes to
the View to assist in your performance to the final query.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1052AF0E-B7D3-47B8-9DA9-69C6E04A4651@.microsoft.com...
> I've acdtually used a temp table as a replacement for the cursor but it
> has
> not given me the expected results. I've taken the initial query and stored
> it
> in a temp table. I have then joined that temp table to the map... table
> and
> done all my querying. But the group by stuff throws me off because when i
> do
> selects, I've to groupby ITAssetObjectID and @.procStartDate/StartDate
> which I
> dont want to do.
>
> SELECT DISTINCT ITAssetObjectID,
> CAST(DATEPART(month, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(day, UsageStartDateTime) AS VARCHAR(2)) + '/' +
> CAST(DATEPART(year, UsageStartDateTime)AS VARCHAR(4)) + ' 0:0:0' AS
> StartDate
> ,CAST(DATEPART(month, dateadd(day,1,UsageStartDateTime)) AS VARCHAR(2)) +
> '/' + CAST(DATEPART(day, dateadd(day,1,UsageStartDateTime)) AS
> VARCHAR(2))
> + '/' + CAST(DATEPART(year, dateadd(day,1,UsageStartDateTime))AS
> VARCHAR(4))
> + ' 0:0:0' AS EndDate
> into ##SummarizeITAsset3
> FROM mapITAsset2ExecutablesUsage (nolock)
> WHERE IsSummarized = 0
> ORDER BY ITAssetObjectID, StartDate
> delete mia2eus FROM mapITAsset2ExecutablesUsageSummary mia2eus join
> ##SummarizeITAsset3 SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> where mia2eus.RunDate = SITA.StartDate
>
>
> SELECT mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID, count(*)
> AS
> Occur, sum(DATEDIFF(second, UsageStartDateTime, UsageEndDateTime)) AS Secs
> into ##Totals
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate And
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND (mia2eus.UsageEndDateTime < SITA.EndDate OR mia2eus.UsageEndDateTime
> IS
> NULL)
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> --2004
> --union all
> insert into ##Totals
> SELECT mia2eus.ITAssetObjectID, mia2eus.ExecutableRepositoryID,
> mia2eus.UserID, COUNT(*) AS Occur, SUM(DATEDIFF(second,
> UsageStartDateTime,
> UsageEndDateTime)) AS Secs
> FROM mapITAsset2ExecutablesUsage mia2eus (nolock) join ##SummarizeITAsset3
> SITA
> on mia2eus.ITAssetObjectID = SITA.ITAssetObjectID
> WHERE mia2eus.UsageStartDateTime >= SITA.StartDate AND
> mia2eus.UsageStartDateTime < SITA.EndDate
> AND mia2eus.UsageEndDateTime >= SITA.EndDate
> GROUP BY mia2eus.ITAssetObjectID, ExecutableRepositoryID, UserID,processid
> .
> .
> .
> .
> I'm having problems with the above select into's and the insert into's.
> How
> would you do those and the groupby's? Please help with that. Thank you.
> The DDL's are as follows.
>
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsage] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [ProcessID] [int] NOT NULL ,
> [UsageStartDateTime] [datetime] NOT NULL ,
> [UsageEndDateTime] [datetime] NULL ,
> [ServerUpdateTime] [datetime] NULL ,
> [IsSummarized] [int] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[mapITAsset2ExecutablesUsageSummary] (
> [ID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ITAssetObjectID] [bigint] NOT NULL ,
> [ExecutableRepositoryID] [int] NOT NULL ,
> [UserID] [int] NULL ,
> [RunCount] [int] NOT NULL ,
> [RunTime] [int] NOT NULL ,
> [RunDate] [datetime] NOT NULL
> )
> GO
>|||Hey Random. I have put this in QA. There are a few problems I have with this
.
I'm thinking the view is correct but the group by clause is messing a lot of
things up. I'm receiving the correct format for almost everything.
-I need to remove the time field from the RunDate or make it 00:00:00
because everything for a day is to be summarized.
-The Runcount is somehow screwed. All the runcounts for a particular
ITAssetObjectID is the same regardless of its ExecutableRepositoryID, UserID
(I think I have found a fix as it's another column that it needs to count
on, it's not ITAssetObjectID, it's ProcessID.
This are the problems for now.|||Hey Random, The second point in my previous post is also a problem. Even
when I do a count(ProcessID) for the RunCount column it gives me the same
counts for every ITassetObjectId and ExecutableRepositoryID.
It's quite urgent for me to get this to work. Thank you for all your help...|||Tejas;
Is there any way you can email me some sample data? Maybe a lot of INSERT
statements for your mapITAsset2ExecutablesUsage table and an idea of what
the result data should look like in mapITAsset2ExecutablesUsageSummary?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DFA772AC-9CCA-46AB-A434-C1C06F8BB7E5@.microsoft.com...
> Hey Random. I have put this in QA. There are a few problems I have with
> this.
> I'm thinking the view is correct but the group by clause is messing a lot
> of
> things up. I'm receiving the correct format for almost everything.
> -I need to remove the time field from the RunDate or make it 00:00:00
> because everything for a day is to be summarized.
> -The Runcount is somehow screwed. All the runcounts for a particular
> ITAssetObjectID is the same regardless of its ExecutableRepositoryID,
> UserID
> (I think I have found a fix as it's another column that it needs to count
> on, it's not ITAssetObjectID, it's ProcessID.
> This are the problems for now.|||Yes, I can. Can you give me youe email address or email me at
parikht@.gmail.com? Thank you.