October 12, 2010 at 4:04 pm
I have a table which has more than 100 thousand records with some fields like as mentioned below.
ID Account NumbStrategyLockStartDate LockEndDate
4015 4.4654E+15CSSA 3/12/2010 0:00 2/28/2011 0:00
4016 4.4654E+15CSSA 2/24/2010 0:00 4/30/2011 0:00
4017 4.4654E+15CSSA 2/11/2010 0:00 5/31/2013 0:00
4018 4.4654E+15CSSA 12/3/2009 0:00 1/31/2011 0:00
4019 4.4654E+15CSSA 1/13/2010 0:00 8/31/2010 0:00
4020 4.4654E+15CSSA 1/5/2009 0:00 3/31/2012 0:00
4021 4.4654E+15CSSA 11/30/2009 0:00 1/28/2014 0:00
4022 4.4654E+15CSSA 7/29/2009 0:00 6/30/2010 0:00
4023 4.4654E+15CSSA 4/16/2009 0:00 7/25/2011 0:00
4024 4.4654E+15CSSA 2/2/2010 0:00 5/31/2010 0:00
4025 4.4654E+15CSSA 9/9/2008 0:00 11/13/2012 0:00
Now basically If need to find out the records with dates other than month end date from LockEndDate column. Its a huge table basically. Is there any way i can find it out...
Any input - Welcome and thanks in advance...
October 12, 2010 at 4:38 pm
Mod this a little and it should get you where you're going. The trick is using the date manipulation functions and the datediff thresholds.
CREATE TABLE #tmp
( LockEndDate DATETIME)
INSERT INTO #tmp VALUES ( '2/28/2011')
INSERT INTO #tmp VALUES ( '2/24/2011')
INSERT INTO #tmp VALUES ( '1/31/2011')
INSERT INTO #tmp VALUES ( '5/12/2011')
INSERT INTO #tmp VALUES ( '10/31/2011')
INSERT INTO #tmp VALUES ( '10/30/2011')
GO
SELECT
*
FROM
#tmp
WHERE
DATEDIFF( mm, LockEndDate, DATEADD( dd, 1, LockEndDate)) = 1
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 12, 2010 at 6:37 pm
Thanks Craig...for your input. Well, I checked this and just wanted to discuss...basically the query u gave gives the month end records and what i need basically are the records apart from month end...So if I use "not in" function.....will it hamper the performance fo the query....
October 13, 2010 at 8:33 am
Don't forget Feb has variable month ends: 28 and 29. So the 28th is not a month end during leap years.
October 13, 2010 at 8:36 am
ravimodi (10/12/2010)
Thanks Craig...for your input. Well, I checked this and just wanted to discuss...basically the query u gave gives the month end records and what i need basically are the records apart from month end...So if I use "not in" function.....will it hamper the performance fo the query....
Yes it will affect the performance, because you're going to have to do two table scans instead of one: one for the main query and one for the subquery for the "not in". You can get the records you want by a simple change to the WHERE clause.
WHERE
DATEDIFF( mm, LockEndDate, DATEADD( dd, 1, LockEndDate)) <> 1
or if you recognize that the function will only ever return 0 or 1, because two consecutive days must either be in the same month or be the end and beginning of consecutive months, you can rewrite it as
WHERE
DATEDIFF( mm, LockEndDate, DATEADD( dd, 1, LockEndDate)) = 0
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 13, 2010 at 10:18 am
ravimodi (10/12/2010)
Thanks Craig...for your input. Well, I checked this and just wanted to discuss...basically the query u gave gives the month end records and what i need basically are the records apart from month end...So if I use "not in" function.....will it hamper the performance fo the query....
Just change the "=1" to "!=1".
homebrew01 (10/13/2010)
Don't forget Feb has variable month ends: 28 and 29. So the 28th is not a month end during leap years.
The DateAdd function handles this automatically. To test, add this row to Craig's test data:
INSERT INTO #tmp VALUES ( '2/29/2004')
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 10:41 am
As mentioned, make it =0 instead of =1. I meant to switch it when I copy/pasted the code and forgot, sorry.
As Wayne mentioned above, the leap year is handled by the date functions, which takes away most (if not all) of the pain of dealing with the dates. In a leap year 2/28 will not be a month end, for example, using the above code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 13, 2010 at 10:44 am
Thanks you Guys.......That Works pretty Great....with a significant improvement......
October 13, 2010 at 11:21 am
ravimodi (10/13/2010)
Thanks you Guys.......That Works pretty Great....with a significant improvement......
Thanks for the feedback... but it makes me curious. How significant is the improvement?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 3:55 pm
Well.....I did not tested the earlier code into the prod db yet....but for test db with like some 25000 records, the earlier code took roughly around 1 second, but the other fundtion merely took any time....it gave me records right after executing it.....So what i think is was much improvement....i didnt compared the execution plans yet...as i can make it out there was not need......
Thanks Wayne...
October 14, 2010 at 5:43 am
CREATE TABLE #tmp
( LockEndDate DATETIME)
INSERT INTO #tmp VALUES ( '2/28/2011')
INSERT INTO #tmp VALUES ( '2/24/2011')
INSERT INTO #tmp VALUES ( '1/31/2011')
INSERT INTO #tmp VALUES ( '5/12/2011')
INSERT INTO #tmp VALUES ( '10/31/2011')
INSERT INTO #tmp VALUES ( '10/30/2011')
GO
SELECT TOP 11000 -- sufficient for dates from 1900-01-31 into 2816-08-31
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2;
--===== Add a Primary Key to maximize performance
ALTER TABLE #Tally ADD
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100;
SELECT *
FROM #tmp tmp
WHEREnot exists (
select *
from #Tally t
where dateadd(day, -1, dateadd(month, t.n, 0)) = tmp.lockenddate
)
Restricting the number of dates by checking only for end dates that are actually useful in your usage scenario (other than the 1900-01-31 to 2816-08-31 used here) will help performance even more. But even using this range it should perform amazingly fast already. (tnx Jeff)
October 14, 2010 at 10:37 am
R.P., did you set up a 10k/20k row test for that algorithm? I'm curious to see how it compared to the simple inline code above.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 14, 2010 at 1:36 pm
I would think the easiest way to do this is add a day to the date and compare the month. Something like:
SELECT * FROM #tmp
WHERE DATEPART(month, LockEndDate) = DATEPART(month, DATEADD(day, 1, LockEndDate))
Todd Fifield
October 14, 2010 at 3:55 pm
Craig Farrell (10/14/2010)
R.P., did you set up a 10k/20k row test for that algorithm? I'm curious to see how it compared to the simple inline code above.
No, I hadn't. I just did (actually generated 1M rows of test data) and yours is about 1.5 times faster on simple io and time statistics already.
Here's my quick test:
USE TempDB
GO
select top 11000
identity(int, 1, 1) as n
into #Tally
from sys.syscolumns sc1,
sys.syscolumns sc2;
alter table #Tally add
primary key clustered (n);
CREATE TABLE dbo.TestData(
LockStartDate datetime not null,
LockEndDate datetime not null
)
go
-- Generate 1 milion test rows. LockEndDate randomly chosen to
-- be anywhere between today and 1000 days back. Just for fun
-- I also added a start date randomly between the LockEndDate
-- and 31 days before that.
insert dbo.TestData( LockStartDate, LockEndDate)
select top 1000000
dateadd(day, -(ABS(checksum(newid()))%31), x.LockEndDate) as lockStartDate,
x.LockEndDate
from #Tally t1,
#Tally t2
cross apply (
select
dateadd(day, -(ABS(checksum(newid()))%1000), dateadd(day, datediff(day, 0, getdate()), 0)) as LockEndDate
) x;
go
set statistics IO on
set statistics time on
SELECT
count(*)
FROM
dbo.TestData tmp
WHERE not exists (
select *
from #Tally t
where dateadd(day, -1, dateadd(month, t.n, 0)) = tmp.lockenddate
)
go
SELECT
count(*)
FROM
dbo.TestData tmp
WHERE
DATEDIFF( mm, LockEndDate, DATEADD( dd, 1, LockEndDate)) <> 1
set statistics IO off
set statistics time off
Results on my Intel Core 2 CPU 6600 @ 2.4GHz:
-----------
966881
(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Tally______________________________________________________________________________________________________________000000000011'. Scan count 3, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestData'. Scan count 3, logical reads 3096, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 421 ms, elapsed time = 225 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
-----------
966881
(1 row(s) affected)
Table 'TestData'. Scan count 1, logical reads 3096, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 330 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
October 14, 2010 at 4:14 pm
Rats. I had hoped the Sargable nature of your code vs. mine would have improved performance. Guess it would depend on the base data set though and how much index traversing had to occur.
EDIT: Wait, yours is 225ms and mine is 330ms. Mine is 1.5 times slower, unless I'm completely misreading that... 😛
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply