Hello everyone,
I am having trouble making a decision on which direction to go.
I have several users with unique ID's and I am tracking visitor information, time, day, month, year, based on that ID and the month.
Plan A,
12 Select Statements (1 for each month) to get a monthly visitor count. The draw back here is that we could end up with 100 unique id's/accounts and if they are running reports it could amount to 1200 inquiries. Even further, this will be a monthly report, but I'd like weekly and daily reports eventually as well.
Plan B,
1 Select Statement and then use java/jsp to loop through the data getting the months total. The draw back I think might be at the end of the year. There could be around 10,000 records per ID, and to loop through them 12 times might take some time.
The data looks something like this.
ID - Day - Month - Year
1 - 15 - 3 - 2011
1 - 15 - 2 - 2011
2 - 15 - 9 - 2011
3 - 12 - 9 - 2011
2 - 12 - 1 - 2011
2 - 11 - 4 - 2011
1 - 10 - 5 - 2011
1 - 9 - 5 - 2011
3 - 5 - 9 - 2011
3 - 5 - 8 - 2011
Am I being overly paranoid about hitting the database 1200 times and grabbing small amounts of data each time? Tomcat/Struts (THE server framework) and MSSQL are on the same machine. So it doesn't have to go far to get the data.
Thanks for any input!
Tim
I am having trouble making a decision on which direction to go.
I have several users with unique ID's and I am tracking visitor information, time, day, month, year, based on that ID and the month.
Plan A,
12 Select Statements (1 for each month) to get a monthly visitor count. The draw back here is that we could end up with 100 unique id's/accounts and if they are running reports it could amount to 1200 inquiries. Even further, this will be a monthly report, but I'd like weekly and daily reports eventually as well.
Plan B,
1 Select Statement and then use java/jsp to loop through the data getting the months total. The draw back I think might be at the end of the year. There could be around 10,000 records per ID, and to loop through them 12 times might take some time.
The data looks something like this.
ID - Day - Month - Year
1 - 15 - 3 - 2011
1 - 15 - 2 - 2011
2 - 15 - 9 - 2011
3 - 12 - 9 - 2011
2 - 12 - 1 - 2011
2 - 11 - 4 - 2011
1 - 10 - 5 - 2011
1 - 9 - 5 - 2011
3 - 5 - 9 - 2011
3 - 5 - 8 - 2011
Am I being overly paranoid about hitting the database 1200 times and grabbing small amounts of data each time? Tomcat/Struts (THE server framework) and MSSQL are on the same machine. So it doesn't have to go far to get the data.
Thanks for any input!
Tim