Proper date time format to optimize query 2

[TheBugSlayer]

Good day all. I need some quick help optimizing the date time manipulation in this query. The query is the most time-consuming in a very long script...I want to take it out by saving the resultset in a temp table then join the rest of the query to the temp table. MyTable is a very dense table.

--SELECT CONVERT(VARCHAR(10), DATEADD(D, -1, GETDATE()), 101) --Yields date in format '12/12/2010'
--ReportDateTime is in format '20101212231715'
SELECT *  FROM dbo.MyTable WITH (NOLOCK)
WHERE SUBSTRING(ReportDateTime,5,2) + '/' 
      SUBSTRING(ReportDateTime,7,2) + '/' + 
      SUBSTRING(ReportDateTime,1,4) = CONVERT(VARCHAR(10), DATEADD(D, -1, GETDATE()), 101)

Thanks much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[bborissov]

SELECT *  
      FROM dbo.MyTable WITH (NOLOCK)
WHERE ReportDateTime >= dateadd(dd,datediff(dd,0,getdate()),-1) AND
      ReportDateTime < dateadd(dd,datediff(dd,0,getdate()),0)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Hey Boris, check out this blog:

http://blogs.msdn.com/b/psssql/arch...erformance-problems-in-sql-2005-and-2008.aspx

Apparently, the DateAdd/DateDiff code is a bit too complex for SQL Server to accurately estimate the number of rows. It's better to assign variables and use them in the query.

SET NOCOUNT ON
Declare @StartDate DateTime, 
        @EndDate DateTime

Select  @StartDate = dateadd(dd,datediff(dd,0,getdate()),-1),
        @EndDate = dateadd(dd,datediff(dd,0,getdate()),0)

SELECT *  
      FROM dbo.MyTable WITH (NOLOCK)
WHERE ReportDateTime >= @StartDate AND
      ReportDateTime < @EndDate

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

TheBugSlayer,

The biggest reason Boris's suggestion is better than your original code is because the query becomes [google]SQL Server sargable[/google]. If you don't know what this means, please take 10 minutes to learn about it.

Also note that his suggestion would only help if there is an index with the ReportDateTime as the first column in the index.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Thanks Boris. The date format in the DB is "MMDDYYYYHHMMSS". So conversion is still needed but we are going to keep it on the of the operator, not on the ReportDateTime column AND I will convert the min and max values prior and maybe index on ReportDateTime. So the final query is:

DECLARE @StartDate AS CHAR(8)
DECLARE @EndDate AS CHAR(8)
SELECT @StartDate = '20101212'
SELECT @EndDate = '20101213'
SELECT * FROM dbo.MyTable WITH (NOLOCK) 
WHERE ReportDateTime >= @StartDate
      AND ReportDateTime < @EndDate

Thanks for pointing me in the right direction.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Thanks George, I read about "sargable" in a previous post of yours.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

You' re on point George. I had posted before I read your suggestions but was already thinking along those lines. Thank you both.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[bborissov]

TheBugSlayer,
SQL Server didn't keep datetime in some format.
If I remember correctly SQL Server keeps datetime values as numeric where in interger part keeps days passed from 1900/01/01 and in decimal part keeps seconds passed since midnight.

Datetime Format is used only to show you results and to convert string variables to datetime.



Thanks George,
I didn't read this blog. I'll keep this in mind.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[TheBugSlayer]

I got you Boris, I was just trying to explain that the date and time is store in the database as text in the MMDDYYYYHHMMSS format.

Thanks again for all the help you provide.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)