Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

looping through a dataset firing off count queries

hello,

  I'm moving an application from asp to asp.net. I populate a dataset from a stored procedure. I have to fire off a count against a different table passed on a value from a particular row for each row displayed.
  So in asp it's like
  while not rsClubs.eof
    select count(id) from users
    where code = rsClubs("code")
  wend.
  To populate the count column.
 
  I can't use a datagrid because I have no access to the values it's just populated automagically. I tried writing a stored proc that used a cursor to populate the fields in a temp table but it takes 9 minutes to run so that's not an option. Please, please, please, anyone who has an idea about how to do this please tell me.

Thanks,
Bryan

bryan murtha
Thursday, November 13, 2003

There are a variety of solutions for something like this.  It’s hard to declare one the absolute best without knowing the specifics of the app.  Some ideas that come in mind in rough order of preference:

1. Add the user count subquery to the clubs query using something like this (hopefully the forum formatting doesn't screw this up too bad):

  select c.*,
    (select count(*) from users u
        where u.code = c.code) as usercount
    from clubs c

2. Store the clubs list in a DataSet.  Add and populate a user count column before binding.

3. Use DataGrid’s ItemDataBound event.  This gives you access to the data as each item is “automagically” bound.  You could do the subquery for each item in here.  ItemDataBound is the key to the DataGrid universe when you can’t get something done an easier way.

SomeBody
Thursday, November 13, 2003

Yes, programmatically "massage" the the DataSet before it gets data-bound: either add a column to the DataSet:
  dataSet.Tables["MyTable"].Columns.Add("MyCount",typeof(int));
...and assign values to it in a loop, and then databind it to the grid, or:

Or use calculated/aggregate columns, you add a column as above but use a third parameter which is an expression - check out the "Parent/Child Relation Referencing" section
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.asp This relies in you having set up DataRelations between the tables in your DataSet.

Duncan Smart
Friday, November 14, 2003

Don't know the specifics of your schema, but I would imagine that SomeBody's option #1 would be the most performant assuming you've created the the appropriate indexes.

Rick Childress
Friday, November 14, 2003

I would be wary of using a nested select to determine counts.  Of course it depends on the application, but I've seen horrible performance degradation result from doing just that.

Another solution from which I've derived much better performance is defining a "count" view with the primary key and the count, and then joining that view against the base table that contains the meta-data you need:

create view club_user_ct
as
select c.id, count(*) as count
from clubs , users u
where c.id = u.code
go

And then issue your "rsClubs" query as:

select c.*, cut.count
from clubs c, club_user_ct cut
where c.id = cut.id

About three weeks ago I optimized a query that used nested selects into this format and was able to reduce a 1.5 minute query to a few seconds.

John Hart
Thursday, November 20, 2003

*  Recent Topics

*  Fog Creek Home