to much load on sql server

[aspnet98]

Hello,
I have an asp page that talks to my sql server 2000 db. I use it to create accounts on my custom system. I was looking for some ideas on how to re-write this code to put less load on my sql db.
Here is how it works:
1) asp form that captures username and password.
2) user clicks submit, I make the username a session variable and write to to a table. I have a template table that has 500 records. I update the template tables username field with the session variable (person who just joined) then append that template table into a master table that is linked to the user table (table i wrote to first). This process works great however the load on the sql server is high. I can't change the db design or anything about creating the 500 records in the master table but i was wondering if there was a better way to do this in asp code (array or something) that would put more strain on the web server verses the sql processes. Any ideas or methodolgies would be helpful. Like the template table may be a horrible way to do this or so inefficient but i am new to this and do not really no that...

best regards,
aspnet98

 

[Sheco]

Are you saying that you add 500 rows to the "master table" every time someone logs in?

 

[aspnet98]

no shecco, thanks for the quick post back.
Only when a cutomer creates an account. it happens once for each customer. then they use the 500 rows to update etc various different information.

creating accounts can happen 800 (new customers) in one day resulting in 400000 records in the master table.

that is the part that bogs the sql server down (i think because of the template i was telling you about).

 

[DotNetGnat]

i dont understand this...you have 500 rows for each customers that they update??? is that right...

or did you mean to say...you have 500 columns for each customer that they can/might update??

can you please explain your whole situation clearly...

-DNG

 

[aspnet98]

when a person create an account, the master table (5 columns) gets 500 records taged with the username field like mentioned above.

it is that simple. i just need a better way to do this based on the way i am doing it above with the temp table.

 

[ChrisHirst]

quite honestly I cannot see why you need 500 records updating per user,

I would suggest that you need a serious database redesign to normalise the tables and minimise the updates needed.

If you cannot change the DB structure/design there is little you can do to improve efficiency of queries.



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[aspnet98]

chris,
Each user has 500 days, like a large calendar. they record time in there each day. the only way to do this is have 1 record per day for every user...

 

[damber]

If they don't have anything against that day, why have it in there ? You don't need records in the db to generate a calendar - calendars are pretty much infinite in size, so it is more common to make the calendar rendition base itself on date increments (e.g. days in a date range) rather than database records.

e.g.:

5 days:
01/01/2005 - No Records
02/01/2005 - No Records
03/01/2005 - Job 1
- Job 2
04/01/2005 - No Records
05/01/2005 - Job 3

You would only need a table of CalendarEnries that is selected for the range of the dates currently in display and then whilst looping through the date increments (days/weeks etc) check the recordset for entries that day and move along as necessary. One loop for the increments in the date range and one recordset filtered for that date range.

So, from the info seen thus far, add a new record whenever the user records time - not 500 days in advance when the user creates their account. BTW.. what happens after 500 days ? Or if the user has more than 1 entry per day ? Does the user have to create a new account, or do they request more days ??

Maybe I've misunderstood the purpose of the design, but it seems overly complicated, and not very dynamic or necessary?

Hope that helps,

A smile is worth a thousand kind words. So smile, it's easy!

 

[tsdragon]

I'd agree with damber's assessment. There is no way I would ever design an application that requires adding 500 records to a database for each new user! Empty records are essentially wasted space. I think you need to look at your design again and see if you can't find a better way to handle this.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[ChrisHirst]

I'll third that.
That's the purpose of relational databases and normalised tables, To cut down on redundant data being stored.
By normalising, it wouldn't matter if the user had only 50 records or 5,000 in the database and your queries would certainly see a substantial speed increase.


Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[DotNetGnat]

I concur...

-DNG

 

[aspnet98]

guys and gals,
My users use every record of the 500. Your feedback is awesome and I do appreciate it but like I said in the first post I need to use every record for data for each person. Their is really none that are "empty". Any thoughts? In that scenario, what do you do?

 

[johnwm]

What happens when they have used their 500? Do you just add new records on the fly? Why not do that from the start, just adding one record at each log-in

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Sheco]

It sounds like the best thing you can do is to minimize the performance impact of your ASP. What the database does it itself is their problem... you just don't want to make it worse.

To this end I'd suggest pushing as much of this into a stored procedure as possible. Surely the person creating the account will not be inserting values for 500 rows so I must assume that all new accounts will initially have the same 500 rows.... If this is the case then the fastest execution will be via a stored procedure. You can use the SQL Server Query Analyzer program to help you tweak the performance of the stored procedure by turning on the menu option to show the execution path.

Try to make it so your ASP holds as little logic as possible so that all of these database operations run within SQL Svr compiled code.

 

[aspnet98]

thanks sheco. the template part is what i think i need to redesign...any ideas on that?

 

[Sheco]

I don't understand the question.

As the other guys were saying it is much cleaner to only create records for events that will actually need to be recorded than to create one for each and every day regardless.

But if that part is set in stone and you absolutely must create the records in the master table then to do this with minimal ASP overhead you should create a stored procedure. It could be a really simply one that just does something like: [tt]
CREATE PROCEDURE spNewUser
@UserName varchar(10),
@PassWord varchar(10)
AS
SET NOCOUNT ON

INSERT INTO MyMasterTable
SELECT
T.SomeField,
T.SomeOtherField,
Null As 'SomeEmptyField',
@UserName As 'USR',
@Password As 'PWD',
T.ThisOtherField
FROM TemplateTable T
[/tt]

Or you could add features like checking to make sure the user does not already exist or whatever...