Re: High Throughput Database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

To add on to Tibor's response, first and high-order are the same thing. The reason I recommend UserId as the first column is that it is (presumably) used in all WHERE clauses and will effectively logically partition the table. Only data for the specified UserId will be touched.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Derek Hart" <derekmhart@xxxxxxxxx> wrote in message news:%23cHOOIo4JHA.1432@xxxxxxxxxxxxxxxxxxxxxxx
Can you tell me what you mean by the high-order column?

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:ehXmani4JHA.1712@xxxxxxxxxxxxxxxxxxxxxxx
I think 3 tables will provide the same performance as one set per user as long as UserID is the the high-order column of each index. Millions of rows isn't really that large nowadays with a properly indexed tables.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Derek Hart" <derekmhart@xxxxxxxxx> wrote in message news:eQsNAai4JHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
I have a database system that saves tons of data per user, in a web database system. Each user could have 10,000 - 20,000 records. This is going to be used potentially by thousands of users, so the numbers head into the millions of records very quickly. There are only 3 tables that need this kind of storage, so I was considering having a new set of these 3 tables made on the fly as each new user joins. I was considering even making a new database per user. But that I come back to using the 3 tables for everybody, because the indexing has been well tested with millions of records. Any creative ideas on this? 10,000 users would potentially create 30,000 tables, so that's seems like a bit of a nightmare. Perhaps go a different route and learn about load balancing, and how the sql server pooling will work. Please give me your thoughts, especially if you have created web sites and database connectivity at this level.





.

Relevant Pages

  • Re: WSE 3.0 Running under default ASPNet user
    ... UsernameTokenManager which needs to access a remote database. ... The web service needs to access a database on a remote server, ... use the UserID and Password held in the IIS config; ... this would be using the NetworkService account on Server 2003. ...
    (microsoft.public.dotnet.framework.webservices.enhancements)
  • Re: How do I number records in an Access Query?
    ... Adding indexes to PlyrID and StartGaming did the trick. ... "Shawn Bauer" wrote: ... our SMS Host database which is FoxPro it has indexes for all tables. ... I have a large database each record includes fields for UserID and TransDate. ...
    (microsoft.public.access.queries)
  • Re: Populating Default values of Forms using queries to Access
    ... I have an Access database that contains the ... |> results for the Userid that someone enters. ... |> What I want to be able to do is use the informaiton to set default values ... |> have the users send me an email anytime they want a to change their ...
    (microsoft.public.frontpage.client)
  • Re: Database Auditing and connection strings
    ... We have a need to be able to perform auditing on the database side ... As part of the requirement we must know the userid ... 1) Using a standard ID and password in SQL Server but providing ... This might at least give us an idea as to why connection pooling ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Dynamic "IN" criteria in query
    ... A method that might work is to create a table that contains the userID and ... or notthan insert a clause in the query "AND UserOfficeIDs=TRUE" ... Data for all offices is stored in the same database. ... "UserOfficeIDs" which returns a string of the comma-delimited ...
    (microsoft.public.access.queries)