Solved

SQL - check on the stored procedure with extended date query

Posted on 2013-05-27
6
363 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 40

Assisted Solution

by:Sharath
Sharath earned 167 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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 166 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 26

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 69

Accepted Solution

by:
Scott Pletcher earned 167 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 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