February 22, 2010 at 4:43 pm
Hey guys,
From a performance, I've read on here plenty of times to avoid Cursors and While Loops, but I was wondering if there is a better way in this case.
Say I have a Function on a Production DB (where I can't view the code behind it). The only Input parameter for it is a date (last day of month).
If I want to insert the values of that Function into a Tempdb table (I create a Dynamic Pivot off it hence avoiding table variables) for each Month End for, say, 6 months (it varies); is there a qucker way than creating the table, and then having a While Loop go through six iterations of inserting the Functions values into it (as well as changing the date each time)?
Happy to provide the code on request but figured this was more a "Yes there is a better way, it's X" or "No", kind of question and that I'd write it from there.
Thanks!
February 22, 2010 at 5:37 pm
This will get rid of the function and the need to change the date each time, but it does use a WHILE loop, I have not attempted it to use a Tally table, just not enough time for me to do so
DECLARE @m INT
SET @m = 0
WHILE @m < 13
BEGIN
select dateadd(dd,-1,dateadd(mm, datediff(mm, 0, GETDATE()) + @m, 0)) AS 'End of month'
END
For just about anything you want to do with dates visit:
For using a Tally table see Jeff Moden's article at:
February 22, 2010 at 5:53 pm
Sorry Bitbucket, I'm updating the Date as part of the While Loop (for 0 to @Months int) - e.g.
set @StartDate = dateadd(d,-1,dateadd(m,1,(dateadd(d,1,@StartDate)))) It increases the Month that is fed into the Function by 1 and inserts the results into a table (which all works, if a little slowly).
I'm just wondering if the While Loop's the fastest way for something like this. Thanks for flicking me the code though!
February 22, 2010 at 6:22 pm
You don't mention if:
1. What the function returns (datatype), and
2. Whether this is a table-valued function, or a scalar-valued function.
So, assuming that this is a table-valued function that returns a date, then this avoids cursors and while loops:
DECLARE @Date datetime
SET @Date = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
CREATE TABLE #temp (MyDate datetime)
;WITH TALLY (N) AS
(
SELECT TOP 366 ROW_NUMBER() OVER (ORDER BY object_id)
FROM master.sys.columns
)
, DATES (N, MyDate) AS
(
SELECT N, DateAdd(d, -1*N, @Date)
FROM TALLY
UNION
SELECT 0, @Date
)
INSERT INTO #temp (MyDate)
SELECT f.ReturnField
FROM dbo.MyFunction(d.MyDate) f
CROSS JOIN DATES d
If it's a scalar-valued function, change the insert to:
INSERT INTO #temp (MyDate)
SELECT dbo.MyFunction(d.MyDate)
FROM DATES d
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2010 at 7:52 pm
Sorry, the Function produces Fund Manager Information, e.g. Manager Name, Portfolios, Securities, Net Asset Values etc. It's table-based as you can therefore imagine. It's probably best if I include the main code for this section to show what i've done and give an idea of the columns:
USE tempdb
GO
IF OBJECT_ID('dbo.MWH_Monthly') IS NOT NULL
DROP TABLE dbo.MWH_Monthly
declare @StartDate as datetime
declare @MonthstoRun as int
set @StartDate = '2009-01-31' -- ********* INPUTS
set @MonthstoRun = 7 -- ********* INPUTS
declare @Month as int
set @Month = 0
CREATE TABLE dbo.MWH_Monthly
(
MonthEnd datetime, [Portfolio Code] nvarchar(255), [Manager] nvarchar(255), [Asset Class] nvarchar(255),[Security Code] nvarchar(255), [Security Name] nvarchar(255),NAV decimal(18,4))
while @Month < @MonthstoRun
begin
insert into dbo.MWH_Monthly
select -- Column Names
MonthEnd, [Portfolio Code], [Manager], [Asset Class],[Security Code], [Security Name],NAV
FROM [db_name].dbo.[fn_BrowseManagerWarehouseDetails] (
@StartDate
,''
,'')
where PfolioOrder = 'AAAA'
set @Month = @Month + 1
set @StartDate = dateadd(d,-1,dateadd(m,1,(dateadd(d,1,@StartDate))))
--break
--else
continue
--end
end
The data from the Function would be pretty sensitive so I would have to dummy that data up, hence not providing it earlier (as I'm happy just to hear a method if there's one faster than requiring anyone to write me any code).
To reiterate, can someone can confirm whether the While Loop would/would not perform the best performance for this kind of task. For 1402 rows (7 months worth of data), the query takes 13 seconds, and that's before I turn this whole thing into a Dynamic Pivot (have already done that, just mentioning it in terms of adding to the length of this process).
February 23, 2010 at 1:56 am
Nugby (2/22/2010)
Sorry, the Function produces Fund Manager Information, e.g. Manager Name, Portfolios, Securities, Net Asset Values etc. It's table-based as you can therefore imagine....
To reiterate, can someone can confirm whether the While Loop would/would not perform the best performance for this kind of task. For 1402 rows (7 months worth of data), the query takes 13 seconds, and that's before I turn this whole thing into a Dynamic Pivot (have already done that, just mentioning it in terms of adding to the length of this process).
It increases the Month that is fed into the Function by 1 and inserts the results into a table (which all works, if a little slowly).
A While Loop is going to do everything Row-By-Row. SQL works best as a set.
In my earlier post, change the #Temp table to the layout of what the function returns, and then insert into that.
DECLARE @Date datetime
SET @Date = DateAdd(mm, DateDiff(mm, 0, GetDate()), 0) -- Beginning of this month
CREATE TABLE #temp (Col1 datetime, Col2 float)
;WITH TALLY (N) AS
(
SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY object_id)
FROM master.sys.columns
)
, DATES (N, MyDate) AS
(
SELECT N, DateAdd(m, N, @Date)
FROM TALLY
UNION
SELECT 0, @Date
)
INSERT INTO #temp (Col1, Col2)
SELECT f.Col1, f.Col2
FROM dbo.MyFunction(d.MyDate) f
CROSS JOIN DATES d
Why don't you test this out and see if it performs better?
Edit: Changed to use next month versus previous month.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 23, 2010 at 3:48 am
Thanks Wayne, will use that Tally Table for the Month Ends and see if it improves it tomorrow morning (just checking this before heading off to bed at the moment)!
February 23, 2010 at 4:00 pm
Hi Wayne,
I'm nearly there (for some reason, it took me ages to realise the correct way to get the correct month ends I needed in "dateadd(d,-1,dateadd(m,N,(dateadd(d,1,@StartDate))))". I just wasn't thinking to use "N" in there!).
However much I play around with it though, I keep getting that simplest of all errors "The multi-part identifier "d.MyDate" could not be bound.". Have tried to use "Dates d", I created a temp table to pass it in, but I can't seem to make that part work and passing it in to the Function is the one parto f your code that I can't really test properly to deduce my error.
declare @MonthsToRun int
declare @StartDate datetime
set @MonthstoRun = 7
set @StartDate = '2009-01-31'
CREATE TABLE dbo.MWH_Monthly
(
MonthEnd datetime, [Portfolio Code] nvarchar(255), [Manager] nvarchar(255), [Asset Class] nvarchar(255),[Security Code] nvarchar(255), [Security Name] nvarchar(255),NAV decimal(18,4))
create table #temp (N int, MyDate datetime)
;WITH TALLY (N) AS
(
SELECT TOP(@MonthsToRun-1) ROW_NUMBER() OVER (ORDER BY object_id)
FROM master.sys.columns
)
, DATES (N, MyDate) AS
(
SELECT N, dateadd(d,-1,dateadd(m,N,(dateadd(d,1,@StartDate))))
FROM TALLY
UNION
SELECT 0, @StartDate
)
insert into #temp(N, MyDate)
select N, MyDate from Dates
--select * from dates
INSERT INTO dbo.MWHMonthly (MonthEnd, [Portfolio Code], [Manager], [Asset Class],[Security Code], [Security Name],NAV)
select
--d.MyDate
f.MonthEnd, -- Added in Edit
f.[Portfolio Code], f.[Manager], f.[Asset Class],f.[Security Code], f.[Security Name],f.NAV
FROM [dbo].[fn_BrowseManagerWarehouseDetails] (
d.MyDate,'','') f -- forgot to mention, there's two other parameters that are always blank for my purposes
CROSS JOIN #temp d -- Couldn't get "Dates d" working either
select * from dbo.MWHMonthly
DROP TABLE dbo.MWH_Monthly
drop table #temp
Can you see what I've done wrong? I suspect it's simpler than a lot of the stuff here, but it's not coming to me.
** Edit, realised I should insert MonthEnd from the Function into MWHMonthly table rather than d.MyDate, though it doesn't solve the problem
February 23, 2010 at 5:00 pm
Nugby,
I'm afraid that I gave you some bad advice.
From BOL:
TABLE
Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed to table-valued functions.
I'm very sorry about this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 23, 2010 at 5:09 pm
That's alright. My original question had been about determining whether there was a better way to do this and it would appear that there isn't (barring converting the While Loop into some kind of "For each MyDate in Dates" to remove the @StartDate = dateadd etc" done in each iteration), so it looks as though my question was answered.
I also got to extend my understand of Tally tables beyond making a 1 to 10 table. I may be able to use that Month End Dates for X months in the future, so definitely got some benefits out of the exercise.
Thanks Wayne!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply