Simple DateDiff query takes too long to run 2

[iuianj07]

Hello guys,

I have a simple Job Tracking database wherein we track a number of SitusIDs and determine which phase/status is it going through. Our Client is a bank wherein they send us deals (PK in db is SitusIDs) and we track the status of each deals in the database.

To have a quick view of the table structure: (as it relates to my question)

tblJobTracking

SitusID -- PK
WeekNumber
DealName
UBSOriginator
Broker
City
State
Region
AssetType
PropertyCount
Priority
DocLinks


tblDealStatus

DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst
StatusChangeID -- FK to tblStatusChange
StatusDate
StatusHours
StatusComments


Now, I have a simple query that should calculate the time difference (in minutes) between when a deal has been received (Unassigned status/StatusChangeID = 1) and when it has been assigned out to someone (Assigned status/ StatusChangeID = 11)

the query is:

SELECT DISTINCT tblJobTracking.SitusID, tblJobTracking.DealName, (SELECT Max(StatusDate) FROM tblDealStatus WHERE StatusChangeID = 1 AND tblJobTracking.SitusID=tblDealStatus.SitusID) AS [Date Unassigned], (SELECT Min(StatusDate) FROM tblDealStatus WHERE StatusChangeID = 11 and tblJobTracking.SitusID = tblDealStatus.SitusID) AS [Date Assigned], DateDiff("n",(SELECT Max(StatusDate) FROM tblDealStatus WHERE StatusChangeID = 1 AND tblJobTracking.SitusID=tblDealStatus.SitusID),(SELECT Min(StatusDate) FROM tblDealStatus WHERE StatusChangeID = 11 and tblJobTracking.SitusID = tblDealStatus.SitusID)) AS Difference
FROM tblJobTracking INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID;

although I guess the query gives the correct results, it freezes the db and takes almost 10 minutes though for it to give the results when the query is being ran.

I was wondering if you could help me figure out what could have caused this or is there any way I could re-write the query to make it run faster.

Any help is greatly appreciated.

thanks

 

[MajP]

I am not that good with SQL, but I now that the Rushmore Query Optimization can be forced to do things in a certain order.

Suppose you have a million records and you do a date diff calculation but only 10 of those records are StatusChangeID = 1 and jobTrackingSitusID = tblDealStatus.SitusID.
Functions in sql are expensive. So you want to ensure you filter to ten then do the calculation, not do the calculation and then filter down to the 10 records.

There are ways to do your own query optimization, I am not that smart so I try it in pieces.

I would firt make two querys one that just returns max
WHERE StatusChangeID = 1 AND tblJobTracking.SitusID=tblDealStatus.SitusID

Then one that just returns min
WHERE StatusChangeID = 11 AND tblJobTracking.SitusID=tblDealStatus.SitusID

It appears you only need the one field max or min.

Now use those queries in your query above.

If that does not help calculate the datediff directly instead of a function. Functions are expensive. Dates are stored as whole days and fraction of a day. So if you subtract and multiply by minutes in a day you should get minutes.
(date2-date1)*24hrs/day*60mins/hr or (1440)

 

[dhookom]

I'm not sure why you have tblDealStatus in the main query. I think you could get rid of it as well as the DISTINCT. I would create these two totals/group by queries and then create a final query of them and tblJobTracking

qgrpSiteUnassigned

SELECT SitusID UnAssignSitusID, Max(StatusDate) as DateUnassigned
FROM tblDealStatus
WHERE StatusChangeID = 1
GROUP BY SitusID;

qgrpSiteAssigned

SELECT SitusID as AssignSitusID, Min(StatusDate) as DateAssigned
FROM tblDealStatus
WHERE StatusChangeID = 11 
GROUP BY SitusID;

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom and MajP,

Thank you both for your responses, I did what both of you suggested. to create seperate queries for Min and Max then create a final query to calculate DateDiff... and now the query runs faster

@MajP, you said on your post that "I am not that smart..." just want to say that I (and probably all of the other Tek Tipster) totally disagree... you've helped me (and others) so much with my previous problems..

both you and dhookom are geniuses

Thanks again