Hi Guys,

I have users (Campaign Managers) who can add templates, domains, users and links. Each entry in those tables has a campaign manager id in it.

I want to create a console so that they can see a count of the number of users, domains, templates and links they have added this week/month (might let them select a range) and the total added by everyone (all campaign managers) over that same time period.

What would you suggest is the best method?

• Create a stored procedure
• Create a view
• Use LLBLGen Code

Out of the first two I prefer the first method. I'd rather not use a stored procedure as I am trying to do everything with LLBLGen.

Question is:

1) Would I get better performance using the stored procedure? 2) How could I go about it in LLBLGen:

It is basically select Count(0) from templates where CampaignManager= 3 and date between start date and end date.

I could set up the query retrieve the collection and then get the count property but that seems a bit much.

Thanks,

John

Performance of 1) and 3) will be the same, with the view a bit slower I think, (as you'll have a select on a select). The query is not that hard using LLBLGen Pro code: you can use a dynamic list which constructs the counts. If you want to retrieve them separately you can also use teh GetScalar method.

I would create a covered index, that way all data in the where clause was in the index. This would ensure that stored proc or dynamic sql would hit the index leaf pages and not the data pages.

Also, ordering of fields would be important as well.

Assume that field 0 is a PK identity field with a clustered index. Also assume that you put a secondary index on the table with fields manager id, start date, end date.

Effectively, the data in the index page for the secondary index would look like this:

Row ID | Manager Id | Start Date | End Date 152 | 3 | 01/01/2004 | 01/31/2004 157 | 3 | 02/01/2004 | 02/31/2004 159 | 3 | 02/15/2004 | 02/31/2004 174 | 4 | 01/01/2004 | 01/31/2004 175 | 4 | 01/01/2004 | 01/10/2004 176 | 4 | 02/10/2004 | 06/30/2004

Since you always have 8k of data on a page, this index will be able to facilitate large amounts of data per page. It will also ensure that all values in the where clause are covered, and the query engine can get all the data from the same data page, which means less IO.

In reality, the physical data could be all over the real data pages which could cause the query to have excessive IO, but this index would bring it together for this one TSQL statement.

Indexed views really only speed things up with horizontally partitioned data. Since the data is unrelated, it would make no sense to create an indexed view.

Cool idea, Devildog!