I have a question about query performance: retrieving values in the query with a pivot or left join versus retrieving them on the row of an ItemDataBound in the grid:
To get my grid values, I can do a "select * from UserProfile where ..."
I have a several fields in a table with responses that I would use a PIVOT or left join to create other columns:
UserName Age City State Zip Haircolor
These are all in a responses table (these are examples, there are numerous responses); but none of these are in the UserProfile table. Each has its own row in the responses table.
As I said, I could use a pivot to create the query, or I could instead, when binding the grid, on the itembound go look up the data for that one row's user and display it in the grid at that time.
I'm thinking this will be better performance, because my grid will display maybe 100-500 results to a page, whereas, if I put this in a pivot, it will do it for every single record in the select clause, which could be maybe 5000 or 50,000 or whatever it gets to.
Also, in doing this, I might want to add to my main query some filtering, like:
select * from userprofile where exists (select 1 from ResponseTable where userprofile.userprofileid = ResponseTable .userprofileId and ResponseTypeId = 32)
So I *AM* going to be adding some subqueries to the actual query for filtering, but I don't need to really do this for some of the displayed fields such as, in my example, city, hair color (they won't be in the search filter). So entirely leaving out all the desired display results from the query and only using the exists clause on the search filters, plus adding the display data to the grid on load by doing queries at that time, seems to me would be the better performance.
I will probably look up about 6-10 values for each row that is displayed to the screen on the ItemDataBound; as opposed to putting PIVOT subqueries or even left joins on each of these 6-10 values:
For example, here's an actual query with only 2 of these values using a left join:
qaCity.textanswer as MyCity,
lvRace.ListValueName as MyRace
left join qaset on qaset.userprofileid = userprofile.userprofileid
left join qafield qaCity
on qaset.qasetid = qaCity.qasetid
and qaCity.questionvalueid = 34
left join qaList qaRace
on qaset.qasetid = qaRace.qasetid
and qaRace.questionvalueid = 32
left join ListValue lvRace
on lvRace.ListValueId = qaRace.ListValueId
where username <> ''
What do you think? What's the best performance method? 100 rows in the grid, or all 5000 rows in the query?