Advice on accessing several tables and displaying data...

[SAD]

Hi

I am working on a website which will allow user's to access their own databases to view additional statistics which are not shown on the main website.

On the initial login if two additional tables do not exist then these need to be added - one of these tables will hold all the information which the user will be able to see once they have successfully logged into the website and the other is to keep a tab on whether the other table needs to be refreshed or not.

At most I would need to access 5 tables for the varies queries I need to run on the user's database. And I will probably need to add further queries in the future as the user decides on additional information they wish to see so need to create something which is fairly easy to add to and adjust.

I was wondering if stored procedures would be a good idea for this? But then read if not accessing more than 5 tables then procedures are not the best approach. As these statistics need to be refreshed every time the user logs in I need to use the mose efficient way to do this without affecting the processing time.

Does anyone have any pointers or good websites/examples I can look at.

Look forward to any advise you can give me to get me started

 

[cjard]

why are you looking to create tables upon every login?

 

[SAD]

Hi

I have been advised to create two tables which are in addition to our clients usual tables. They will only be created once, if they do not already exist, which will be on the user's initial login to the website. Once they have been created they will then be updated each time the user logs in so that the statistics the user is looking at will be up to date.

Is this the approach you would take, or would you think the user should select which stats to 'refresh'?

As I am really new to all this should I avoid looking at creating procedures and just stick to loading all the tables I need into a dataset, then renaming the tables so I know which ones are which and then running SQL queries on them to create the stats I need?

Thanks very much for any advise you can give me

 

[Stonkie]

What I understand is that you have 2 tables per user... This is nothing near efficient. Finding the right table and stuff could even be longer then actually reading the data. The way to do it is to simply add a column for the client the information refers to and passing the client's Id in parameter. That way, the information can be cached efficiently, it takes less overhead space in the database, accessing tables by name is much faster and client id's can be indexed for even further optimization. Not adding that you can change the settings of the database without having to go through each table...

Now, you can use stored procedures (I hear that's a little more efficient, but I never noticed a difference...). I have not used them much though as I'd rather just send my parametrized SQL query directly so I can change it from within my app. The key point in this is that you want to do as much processing as possible for your stats on the database's side. That means any SUM or COUNT that you can, as long as it avoids you loading the whole stuff in your dataset and calculating averages in code. The dataset is very inefficient for large amounts of data and will drag you down on the amount of memory it uses.

the other is to keep a tab on whether the other table needs to be refreshed or not.

What do you mean? Could you tell us what are the columns and datatypes for the tables?

 

[SAD]

Hi Stonkie

Thanks for responding.

I will use SQL queries to get the information I need straight from the users database. Have looked again and actually think I only need to look at one table - then I will need to add any statistics returned from my SQL queries into an additional table as this table is the one to be displayed in a datagrid to the user.

I need to investigate this a bit more but if you have any additional comments/advise for me please feel free!

Thanks again