Solved

SQL - check on the stored procedure with extended date query

Posted on 2013-05-27
6
360 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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:
ScottPletcher 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now