I've been waiting over an hour already for this query, so I know I'm probably doing something wrong. Is there efficient way to tailor this query: ?
select RespondentID, MIN(SessionID) as 'SID'
from BIG_Sessions (nolock)
where RespondentID in (
1418283,
1419863,
1421188,
1422101,
1431384,
1435526,
1437284,
1441394,
/* etc etc THOUSANDS */
1579244 )
and EntryDate between
'07-11-2011' and '07-31-2012'
GROUP BY RespondentID
I kknow that my date range is pretty big, but I can't change that part (the dates are spread all over) .
Also, the reason for MIN(SessionID)
is because otherwise we get many SessionID's for each Respondent, and one suffices(it's taking MIN on an alphanumeric ID like ach2a23a-adhsdx123... and getting the first alphabetically)
Thanks
eg:
select RespondentID, MIN(SessionID) as 'SID'
from BIG_Sessions (nolock)
inner join RespondentsFilterTable
on BIG_SESSIONS.RespondentID = RespondentsFilterTable.RespondentID
where EntryDate between '07-11-2011' and '07-31-2012'
GROUP BY BIG_Sessions.RespondentID
You could add indexes to EntryDate and SessionID as well, but if you're adding to big_sessions frequently, this could be counter productive elsewhere
In general, you can can get hints of how performance of a query can be improved by studying the estimated (or if possible actual) execution plans.
If the smallest and largest ids in the IN statement are known beforehands and depending on how many ids are in the table then adding a respondedID > [smallest_known_id-1] AND respondedID < [largest_known_id+1]
prior to the IN statement would help limiting the problem
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With