Last month data
-
I remember reading DATEDIFF is slow and wherever possible use DATADD.
I have to retrive data for previous month. Is there any elegant way of doing it without using DATEDIFF.
Now I use.
SELECT * FROM MyTable WHERE DATEDIFF(MONTH, MyColumn, GETDATE()) = 1
Regards,
gova -
Don't use functions on a column, check this out :
Select * from dbo.MyTable where DateCol between @StartDate and @EndDate
Just calculte those variables using the dateadd functions (or from the app) and it'll run much faster if the column is indexed.
-
...which, for claritys sake, could also look like this
Select *
from dbo.MyTable
where DateCol between DATEADD(MONTH, -1 , GETDATE()) and <A href="mailto
ATEDIFF@EndDate">GETDATE())SQL Server should evaluate your function only once. But check your query plan
You don't want it to do it for every row. (Like the DATEDIFF on you column would have to do). And, since we don't have function indexes in SQL Server as in Oracle, it would be a rather unpleasant thing...//Hans
-
"And, since we don't have function indexes in SQL Server as in Oracle, it would be a rather unpleasant thing..."
This isn't 100% accurate :
IF Object_id('Employes2') > 0
DROP TABLE Employes2
GO
IF object_id('fnEmployes2') > 0
DROP FUNCTION fnEmployes2
GO
CREATE FUNCTION [dbo].[fnEmployes2] (@DOB as datetime)
RETURNS SMALLINT
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(SMALLINT, RIGHT(CONVERT(CHAR(8),@DOB,112), 4))
END
GO
CREATE TABLE [Employes2] (
[id] [int] NOT NULL ,
[name] [varchar] (50) ,
[DOB] [datetime] NOT NULL ,
[DOB_MD] AS dbo.fnEmployes2 (DOB)
CONSTRAINT [PK_Employes2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Employes2_DOB ON dbo.Employes2 (DOB)
GO
INSERT INTO dbo.Employes2 (id, name, dob) Select id, cast(name as varchar(50)), CrDate from dbo.SysObjects
GO
--Select objectproperty(object_id('fnEmployes2'), 'IsDeterministic')
--1
GO
create nonclustered index IX_DayMonth_Of_Birth on dbo.Employes2 (DOB_MD)
GO
Select * from dbo.Employes2 where DOB_MD between 101 and 301
--163 row(s) affected
/*
|--Compute Scalar(DEFINE
[Employes2].[DOB_MD]=[dbo].[fnEmployes2](Convert([Employes2].[DOB]))))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[Ideal].[dbo].[Employes2]))|--Index Seek(OBJECT
[Ideal].[dbo].[Employes2].[IX_DayMonth_Of_Birth]), SEEK[Employes2].[DOB_MD] >= Convert([@1]) AND [Employes2].[DOB_MD] > 83% of the query)*/
GO
Select * from dbo.Employes2 where RIGHT(CONVERT(CHAR(6),DOB,12), 4) between '0101' AND '0301'
--163 row(s) affected
/*
|--Compute Scalar(DEFINE
[Employes2].[DOB_MD]=[dbo].[fnEmployes2](Convert([Employes2].[DOB]))))|--Clustered Index Scan(OBJECT
[Ideal].[dbo].[Employes2].[PK_Employes2]), WHEREright(Convert(Convert([Employes2].[DOB])), 4)>='0101' AND right(Convert(Convert([Employes2].[DOB])), 4)<='0301'))53.75% of the batch
*/
GO
DROP TABLE Employes2
GO
DROP FUNCTION fnEmployes2
GO
This shows how to index a cumputed column based on a function... but I don't know if it can do all the same things that Oracle offers.
-
Yeah, I guess you are right. Though it means creating a computed column and using it. One benefit in Oracle is that you can examine the queries executed and to tune them, you just add a function index with the function(s) mostly used...
Here you would have to recode applications or educate users about using the column to gain performance.
But I agree to call the SQL Server solution a function index
-
I still have to see a situation where I need to do this. I'm sure it's gonna happen, but maybe not this year
. There's almost always a way to write the query where you don't have to do gymnastics like this. -
But the Oracle way of optimizing performance without rewriting the client application can be useful. Though I havn't had any practical use for it yet either
-
Good, then I'm still gona go with Sql server
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply