How do DB driven sites work with huge amount of data?

[ReportingAnalyst]

Hi,

How do DB driven sites work with huge amounts of data? For instance, I go to Amazon to logon. It will definitely check my userid and password vs. a database which in turn can have 100,000s of records. So how does it get that checking so fast? What kind of ASP object could they be using to do this work so fast?

I am trying to build an org structure for a person with 400 subordinates, and it takes 32 seconds(which is very long!!!).

Should I cache the entire recordset into a Application variable? Update this Application variables in probably 3 times in a day. So that way the person with most subs will see his cached result set instead of sending a request to the DB everytime he logs in.

Any inputs.

Thanks.

 

[Chopstik]

Do *NOT* cache everyting into an application variable. You think you have a performance issue now? Wait until you try that maneuver...

You say you're trying to build an org structure (organisational structure) and it takes 32 seconds to run. Is it your query that returns your recordset that takes 32 seconds or what? You've offered no code samples nor a detailed description of what you're attempting to do so it would be hard to offer any sort of useful advice. It could be as simple as re-writing your SQL code or as difficult as changing your database or re-writing your application. It is impossible to tell without more information. Provide more info on what you're doing (and what you hope to do) and perhaps someone will be able to offer more assistance.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Sheco]

In the Amazon example, there is a lot more account information that what is needed to verify a login. Most of that information is not needed until you actually order something and prepare to have it shipped.

So if I was looking for max database speed for a login, I might consider making a special table with only 2 fields: username and password. Unique key index on the username and a stored procedure to do the query to precompile the SQL.

If you needed something even faster you could forget the database altogether and write a little server program that holds all the valid user/pass combos sorted in memory and some QuickSort/HeapSort affair to verify.

 

[ReportingAnalyst]

So in my case, where I am building an orgstructure, do I have to re-write a temp table to store the org-structure...

That is something I have to think again.

 

[Sheco]

Well you probalby dont have millions of rows....

Is there an index on the field(s) searched when making the ord structure?

 

[ReportingAnalyst]

No I do not have millions

I have only 35K records.

When I see the design of the table, I see only columns of data type int, varchar, bit so on and so forth.

I did not think that index, checks are something which I can see the design of the table using the enterprise manager. Is there any other way I can see it?

Thanks.

 

[Sheco]

if it is MS SQL Server enterprise manager then right-click the table, choose All Tasks on the popup menu, then choose Mangle Indexes.

 

[ReportingAnalyst]

I went to the SQL Query Analyzer. There in the user tables, I can see 1 index for this table.

iAMP_AllMySubordinates_Postings

which is, I am assuming since I saw 2 checkboxes checked, for the columns:

Unique_Identifier
Member_UniqueIdenfier

Neither of which are primary keys.

 

[Sheco]

For the purposes of speeding up searches for an org structure you might want an index on the field that holds the supervisor ID