HI,
I am using below query .. Before I added the Bold lines query was given results in just 40 sec .. but after I added that bold lines part query is taking more than 4 mins to complete it... 
I have run it for just 1 week data which suppose to be 11 records.. still it taking that much time to execute... Can you please check on this query and please let me know what could be the problem...
SELECT v.AccountNumber,q.VisitID ,v.[Name],q.Query,
De.Name as 'Entered Site'
,q2.Response as 'Entered Date'
,q4.Response as 'Issue Notified'
,De2.Name as 'Necessity Name'
,De3.Name as 'Observation'
FROM BarVisits v JOIN VisitQueries q ON v.VisitID=q.VisitID
Inner join VisitQueries q2 ON (q2.VisitID = q.VisitID and q2.QueryID = 'CLVDATE1')
Inner join VisitQueries q4 ON (q4.VisitID = q.VisitID and q4.QueryID = 'CLVSNOT1')
Inner join NCWorkTdRepeatMult np on (np.VisitID = q.VisitID and np.QueryID = 'CLVNECN1' and np.RowUpdateDateTime in (select Top(1)npw.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw where np.VisitID = npw.VisitID and npw.QueryID = 'CLVNECN1' order by npw.RowUpdateDateTime ASC))
Inner join NCWorkTdRepeatMult np1 on (np1.VisitID = q.VisitID and np1.QueryID = 'CLVOBSIT1' and np1.RowUpdateDateTime in (select Top(1)npw1.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw1 where np1.VisitID = npw1.VisitID and npw1.QueryID = 'CLVOBSIT1' order by npw1.RowUpdateDateTime ASC))
Left outer join DVisitResponseElements De on (De.CodeID = q.Response and De.GroupResponseID = 'CLVSITE')
Left outer join DVisitResponseElements De2 on (De2.CodeID = np.Response and De2.GroupResponseID = 'CLVNECN1')
Left outer join DVisitResponseElements De3 on (De3.CodeID = np1.Response and De3.GroupResponseID = 'CLVOBSIT1')
WHERE q.QueryID = 'CLVSITE' AND
q.[DateTime] BETWEEN @BeginDate and @EndDate
What does the execution plan show?
Why do you have this twice:
(select Top(1)npw.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw where np.VisitID = npw.VisitID and npw.QueryID = 'CLVNECN1' order by npw.RowUpdateDateTime ASC)
instead of adding another join clause? Also, do you get full optimization? (in the execution plan, right-click on the first select and choose Properties. In the properties window, you'll find Optimization Level)
are the join predicates indexed?
Hi,
I have Two different queries 'CLVNECN1' and 'CLVOBSIT1' need to find the 1st responses of these two queries ... As they have more responses we are pulling first response by selecting first RowUpdateDateTime .
that's why I used query like below. Not sure whether I am following right or wrong as I am new to SQL please suggest me and modify query please.
Inner join NCWorkTdRepeatMult np on (np.VisitID = q.VisitID and np.QueryID = 'CLVNECN1' and np.RowUpdateDateTime in (select Top(1)npw.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw where np.VisitID = npw.VisitID and npw.QueryID = 'CLVNECN1' order by npw.RowUpdateDateTime ASC))
From above I get the np.Response for QueryID = 'CLVNECN1'
From below I get the np1.Response for QueryID = 'CLVOBSIT1'
Inner join NCWorkTdRepeatMult np1 on (np1.VisitID = q.VisitID and np1.QueryID = 'CLVOBSIT1' and np1.RowUpdateDateTime in (select Top(1)npw1.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw1 where np1.VisitID = npw1.VisitID and npw1.QueryID = 'CLVOBSIT1' order by npw1.RowUpdateDateTime ASC))
Can you please help me to simplify the query .
Thanks,
Archana
e.g.
SELECT v.AccountNumber,
q.VisitID,
v.[Name],
q.Query,
De.Name AS 'Entered Site',
q2.Response AS 'Entered Date',
q4.Response AS 'Issue Notified',
De2.Name AS 'Necessity Name',
De3.Name AS 'Observation'
FROM BarVisits v
JOIN VisitQueries q
ON v.VisitID = q.VisitID
JOIN VisitQueries q2
ON (
q2.VisitID = q.VisitID
AND q2.QueryID = 'CLVDATE1'
)
JOIN VisitQueries q4
ON (
q4.VisitID = q.VisitID
AND q4.QueryID = 'CLVSNOT1'
)
JOIN NCWorkTdRepeatMult np
ON np.VisitID = q.VisitID
AND np.QueryID = 'CLVNECN1'
JOIN NurPocWorkTdRepeatResultMult npw1
ON np.VisitID = npw.VisitID
AND npw.QueryID = 'CLVNECN1'
AND np.RowUpdateDateTime = npw1.RowUpdateDateTime
JOIN NCWorkTdRepeatMult np1
ON np1.VisitID = q.VisitID
AND np1.QueryID = 'CLVOBSIT1'
JOIN NurPocWorkTdRepeatResultMult npw2
ON np2.VisitID = npw2.VisitID
AND npw2.QueryID = 'CLVNECN1'
AND np2.RowUpdateDateTime = npw2.RowUpdateDateTime
LEFT JOIN DVisitResponseElements De
ON (
De.CodeID = q.Response
AND De.GroupResponseID = 'CLVSITE'
)
LEFT JOIN DVisitResponseElements De2
ON (
De2.CodeID = np.Response
AND De2.GroupResponseID = 'CLVNECN1'
)
LEFT JOIN DVisitResponseElements De3
ON (
De3.CodeID = np1.Response
AND De3.GroupResponseID = 'CLVOBSIT1'
)
WHERE q.QueryID = 'CLVSITE'
AND q.[DateTime]
BETWEEN @BeginDate AND @EndDate;
Thank you gbritton but Your query giving all the responses of QueryID 'CLVNECN1' and 'CLVOBSIT1' . but I need only first response entered in table which can be captured by pulling old RowUpdateDateTime ..
That's why I used
select Top(1)npw.RowUpdateDateTime from NurPocWorkTdRepeatResultMult npw where np.VisitID = npw.VisitID and npw.QueryID = 'CLDRSGSIT1' order by npw.RowUpdateDateTime ASC
But you didn't included this part in query so because of that I got all the responses for each visitID ..
Can you please let me know how can I achieve this logic to get only 1st response for each VisitID for QueryID 'CLVNECN1' and 'CLVOBSIT1'
Thank you so much for looking in to this... Really appreciated for your help...
Thanks...
Aha, so we'll convert those joins to cross applys:
SELECT v.AccountNumber,
q.VisitID,
v.[Name],
q.Query,
De.Name AS 'Entered Site',
q2.Response AS 'Entered Date',
q4.Response AS 'Issue Notified',
De2.Name AS 'Necessity Name',
De3.Name AS 'Observation'
FROM BarVisits v
JOIN VisitQueries q
ON v.VisitID = q.VisitID
JOIN VisitQueries q2
ON (
q2.VisitID = q.VisitID
AND q2.QueryID = 'CLVDATE1'
)
JOIN VisitQueries q4
ON (
q4.VisitID = q.VisitID
AND q4.QueryID = 'CLVSNOT1'
)
JOIN NCWorkTdRepeatMult np
ON np.VisitID = q.VisitID
AND np.QueryID = 'CLVNECN1'
CROSS APPLY
(
SELECT TOP (1)
npw.RowUpdateDateTime
FROM NurPocWorkTdRepeatResultMult npw
WHERE np.VisitID = npw.VisitID
AND npw.QueryID = 'CLDRSGSIT1'
ORDER BY npw.RowUpdateDateTime ASC
) npw
JOIN NCWorkTdRepeatMult np1
ON np1.VisitID = q.VisitID
AND np1.QueryID = 'CLVOBSIT1'
CROSS APPLY
(
SELECT TOP (1)
npw1.RowUpdateDateTime
FROM NurPocWorkTdRepeatResultMult npw1
WHERE np1.VisitID = npw1.VisitID
AND npw1.QueryID = 'CLVOBSIT1'
ORDER BY npw1.RowUpdateDateTime ASC
) npw1
LEFT JOIN DVisitResponseElements De
ON (
De.CodeID = q.Response
AND De.GroupResponseID = 'CLVSITE'
)
LEFT JOIN DVisitResponseElements De2
ON (
De2.CodeID = np.Response
AND De2.GroupResponseID = 'CLVNECN1'
)
LEFT JOIN DVisitResponseElements De3
ON (
De3.CodeID = np1.Response
AND De3.GroupResponseID = 'CLVOBSIT1'
)
WHERE q.QueryID = 'CLVSITE'
AND q.[DateTime]
BETWEEN @BeginDate AND @EndDate;
Query is working but it's still taking long time to pull the results..
Any help will be appreciated...