I have a website with thousands of members. When they log in to the website there are certain questions displayed to them. These questions need to be dynamic in terms of them only displaying questions that are relevant to each logged in user. When we create the questions, we assign a select statement to each one defining who should see it. Currently we have to loop through all of the questions, execute the custom query and see if the current session ID is amongst the result set, otherwise don't show the question on the page. The site is coded in ASP.NET however I'd like to get my head around the SQL to optimise this process so that's the focus for this question.
Tables look like this...
UserID Name Age Gender City
12345 Joe 32 Male London
54321 Bill 44 Male Paris
45678 Mary 24 Female Madrid
QuestionID QText QRY
10 Do you travel on London Underground? Select UserID from UsersTable where City = 'London'
11 How often do you use the Paris Metro? Select UserID from UsersTable where City = 'Paris'
12 How often do you use the Metro Ligero? Select UserID from UsersTable where City = 'Madrid'
Current user is logged in to account and session ID is present.
The syntax of this may be wrong, but within the loop that checks each question in the QuestionsTable, we have something that essentially works like this...
select QuestionID from QuestionTable where SessionID in (EXEC QuestionTable.QRY)
This works, but takes way too long to run so I'm looking to optimise this process in some way.