February 4, 2014 at 3:15 pm
Can someone tell me how to dynamically populate my
in these nested cursors to reflect the current month? eg... [Stats201402]
I'm also happy to get rid of the cursors if someone wants to show me an easier way. That said...this sproc has low overhead and will run in the middle of the night.
Thanks
USE [someDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AggregateUsage]
AS
DECLARE @start DATETIME
SELECT @start = MAX(SessionEndDate) FROM [StatsPerUserSession]
DECLARE @username VARCHAR(100), @sessionid VARCHAR(100), @type VARCHAR(10), @sesdate DATETIME, @in BIGINT, @out BIGINT, @sessiontime BIGINT
DECLARE crs_username CURSOR FOR
SELECT DISTINCT(Username)
FROM [Stats201402]
OPEN crs_username
FETCH NEXT FROM crs_username INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE crs_sessions CURSOR FOR
SELECT DISTINCT(Acct_session_id)
FROM [Stats201402]
WHERE username = @username
AND DATETIME > @start
OPEN crs_sessions
FETCH NEXT FROM crs_sessions INTO @sessionid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE crs_data CURSOR FOR
SELECT TOP 1 Acct_status_type, DATETIME, acct_session_time, acct_input_oct, acct_output_oct
FROM [Stats201402]
WHERE Acct_session_id = @sessionid
ORDER BY DATETIME DESC
OPEN crs_data
FETCH NEXT FROM crs_data INTO @type, @sesdate, @sessiontime, @in, @out
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [StatsPerUserSession] VALUES (@username, @type, @sesdate, @in, @out, @sessiontime)
FETCH NEXT FROM crs_data INTO @type, @sesdate, @sessiontime, @in, @out
END
CLOSE crs_data
DEALLOCATE crs_data
FETCH NEXT FROM crs_sessions INTO @sessionid
END
CLOSE crs_sessions
DEALLOCATE crs_sessions
FETCH NEXT FROM crs_username INTO @username
END
CLOSE crs_username
DEALLOCATE crs_username
February 4, 2014 at 3:18 pm
Will the table name always be formatted as "Stats" + yyyy + mm?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 3:21 pm
Yes...it will always have that format.
February 4, 2014 at 4:08 pm
You would need to change your multi-tiered cursors to be a dynamic statement and execute that statement.
The statement (to get the date pieces - converting to dynamic will be an exercise for you) would be like...
DECLARE @currdate DATE = GETDATE()
SELECT 'Stats' + CONVERT(VARCHAR(4),DATEPART(YEAR,@currdate))
+ Right('0' + LTRIM(RTRIM(CONVERT(CHAR(2),DATEPART(MONTH,@currdate)))),2)
Orrrr
You could create a "partitioned" view that queries the necessary tables that have been dated as you specified. Then you would only need to change the view to add/remove tables as the become available/obsolete.
Orrrr
You could create a partitioned table that slides the data for each month into a different partition and have your query only touch that one table. The movement between partitions would be automated.
Without a heavy duty look, it appears the cursors can be optimized at a minimum to a single cursor call. Most likely, this query can be converted to set-based too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2014 at 4:38 pm
Thanks Jason!
I tried doing that earlier. Unfortunately...the problem isn't simply creating a variable for the table name and using it in the cursor. The issue is with cursors in general. It appears they don't allow the table name to be a variable at all.
eg... This...
DECLARE crs_username CURSOR FOR
SELECT DISTINCT(Username)
FROM [Stats201402]
can't be This...
DECLARE crs_username CURSOR FOR
SELECT DISTINCT(Username)
FROM @tablename
I'll look into your other recommendations.
Meanwhile...if anyone else would like to chime in...feel free.
Thanks
February 4, 2014 at 4:41 pm
When the table is coming from a variable, the entire sql statement must be done via Dynamic SQL. It can be done, but you have to set your entire sql statement to a variable. Then you execute that dynamic sql by doing something like execute (@sqlstatement)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2014 at 8:10 pm
Here's what I came up with. It was actually much easier than I thought it would be.
DECLARE @query NVARCHAR(2000)
DECLARE @table NVARCHAR(20)
SET @table = (SELECT 'Stats'+''+[dbo].[fnFormatDate](GETDATE(), 'YYYYMM'))
SET @query = '
DECLARE @start DATETIME
SELECT @start = MAX(SessionEndDate) FROM StatsPerUserSession
DECLARE crs_username CURSOR FOR
SELECT DISTINCT(Username)
FROM '+ @table +'
OPEN crs_username
FETCH NEXT FROM crs_username INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE crs_sessions CURSOR FOR
SELECT DISTINCT(Acct_session_id)
FROM '+ @table +'
WHERE username = @username
AND DATETIME > @start
OPEN crs_sessions
FETCH NEXT FROM crs_sessions INTO @sessionid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE crs_data CURSOR FOR
SELECT TOP 1 Acct_status_type, DATETIME, acct_session_time, acct_input_oct, acct_output_oct
FROM '+ @table +'
WHERE Acct_session_id = @sessionid
ORDER BY DATETIME DESC
OPEN crs_data
FETCH NEXT FROM crs_data INTO @type, @sesdate, @sessiontime, @in, @out
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO StatsPerUserSession VALUES (@username, @type, @sesdate, @in, @out, @sessiontime)
FETCH NEXT FROM crs_data INTO @type, @sesdate, @sessiontime, @in, @out
END
CLOSE crs_data
DEALLOCATE crs_data
FETCH NEXT FROM crs_sessions INTO @sessionid
END
CLOSE crs_sessions
DEALLOCATE crs_sessions
FETCH NEXT FROM crs_username INTO @username
END
CLOSE crs_username
DEALLOCATE crs_username
'
--PRINT @query
EXEC (@query)
February 5, 2014 at 8:11 pm
brickpack (2/5/2014)
Here's what I came up with. It was actually much easier than I thought it would be.
Well done.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 6, 2014 at 2:07 am
It's not often that such a great example of when not to use cursors comes up. This appears to be a very straightforward query:
DECLARE @start DATETIME
SELECT @start = MAX(SessionEndDate) FROM [StatsPerUserSession]
INSERT INTO [StatsPerUserSession] -- column list here
SELECT
Username, Acct_status_type, [DATETIME], acct_input_oct, acct_output_oct, acct_session_time
FROM (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY Username, Acct_session_id ORDER BY [DATETIME] DESC),
Username, Acct_status_type, DATETIME, acct_input_oct, acct_output_oct, acct_session_time
FROM [Stats201402]
WHERE [DATETIME] > @start
) d
WHERE rn = 1
It's far less code than three or four nested cursors, it's less complicated, runs far faster using less resources - and it's easier to convert to dynamic SQL.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 6, 2014 at 7:19 am
Although I can't take credit for writing the cursors. I am happy to part of this historic teaching moment. 😛
Thanks Chris! This is exactly the kind of answer I was looking for. I'm constantly on a quest to create a "cursor-free" zone. I'll pass your query on to the developer.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply