?
Solved

SQL - check on the stored procedure with extended date query

Posted on 2013-05-27
6
Medium Priority
?
398 Views
Last Modified: 2013-07-08
Hi

I have inherited stored procedure below.  
The WHERE part seems a bit overly complicated for what is actually needed with each DatePart split out....
I wonder if there's reason why query has been written as is and not simpler?

INHERITED STORED PROCEDURE


parameters
@date datetime,
@PIE2IID as smallint
....

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where DATEPART (D , tblTimeStamp.timeStamp ) =  DATEPART (D , @date)AND
        DATEPART (M , tblTimeStamp.timeStamp ) =  DATEPART (M , @date)AND
        DATEPART (YYYY , tblTimeStamp.timeStamp ) =  DATEPART (YYYY , @date)  AND
        DATEPART (HH , timeStamp ) =  DATEPART (HH , @date)AND
        DATEPART (MI , timeStamp ) =  DATEPART (MI , @date)
      AND tblInputs.PIE2_I_ID = @PIE2IID ;


SUGGESTED REVISED VERSION

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where tblTimeStamp.timeStamp = @date
      AND tblInputs.PIE2_O_ID = @@PIE2IID ;
0
Comment
Question by:rwallacej
6 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 668 total points
ID: 39199507
I think you don't want to compare the seconds part. If so, you can have the WHERE condition like this.

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where CONVERT(varchar(16),tblInputs.PIE2_O_ID,120) = CONVERT(varchar(16),@date,120)
      AND tblInputs.PIE2_O_ID = @@PIE2IID ;
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 664 total points
ID: 39199788
The reason for that where clause is trying to get rid of the SS.mmm part of the date, which is the seconds and milliseconds. You can do that like this:

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where
		DATEDIFF(SECOND,@date,tblTimeStamp) = 0
		AND tblInputs.PIE2_I_ID = @PIE2IID;

Open in new window


As an observation, in any of the cases the SQL engine will not be able to use any index on the tblTimeStamp column because is wrapped in function(s). However if you don't have to many dates for one timeStampKey and PIE2_I_ID combination you should be fine.
0
 

Author Comment

by:rwallacej
ID: 39199809
Thanks for helps, I'll look more in depth tomor.

There is only one date per timestampKey (I.e. each day has its own key, so for 1 year there are 365 rows in tblTimeStamp) timestampkey is UID in this tblTimeStamp
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39201389
Actually I just realized that the query should look at the minutes difference and not seconds :

SELECT   tblInputs.value
FROM     tblInputs INNER JOIN
         tblTimeStamp ON tblInputs.timeStampKey = tblTimeStamp.timeStampKey
where
		DATEDIFF(MINUTE,@date,tblTimeStamp) = 0
		AND tblInputs.PIE2_I_ID = @PIE2IID;

Open in new window


what you want is to ignore the part after minutes so the date time value should be the same to the minute. That is what the original code does.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 668 total points
ID: 39201849
If any function or computation is done to a table column, SQL cannot do any keyed index lookup on that column.  Therefore, it's best to avoid using functions/computations on the table column if reasonably possible.

For date/time comparisons, it's best to use ">=" and "<" rather than = or between.

Given all of that, and adding aliases to make the query easier to read/maintain, the final query should be:


SELECT   i.value
FROM     tblInputs i INNER JOIN
         tblTimeStamp ts ON i.timeStampKey = ts.timeStampKey
                                                 --strip seconds and subseconds from @date
WHERE ts.timeStamp >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date), 0)
                                             --strip seconds and subseconds from @date and add 1 minute
      AND ts.timeStamp < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @date) + 1, 0)
      AND ts.PIE2_O_ID = @PIE2IID ;
0
 

Author Closing Comment

by:rwallacej
ID: 39307864
split points, thanks all for help
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question