Re: High Throughput Database

You mean physically the first column. When I use a Create Table command, it is simply the first column?

I mean the first column of the indexes. It doesn't have to be the first in the table.

--
Hope this helps.

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

"Derek Hart" <derekmhart@xxxxxxxxx> wrote in message news:%239BlNcw4JHA.140@xxxxxxxxxxxxxxxxxxxxxxx

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:O%23vBOzq4JHA.2656@xxxxxxxxxxxxxxxxxxxxxxx
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: Database recovery with data file only
    ... Basically using that command breaks your business logic as there's no ... "Dan Guzman" wrote in message ... >> database Backup as well as ... >>> SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: High Throughput Database
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... I mean the first column of the indexes. ... SQL Server MVP ... was considering even making a new database per user. ...
    (microsoft.public.sqlserver.programming)
  • Re: High Throughput Database
    ... First column. ... Tibor Karaszi, SQL Server MVP ... "Dan Guzman" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: sa not starting in master-db
    ... in the nt-authentication for my default connection setting. ... "Dan Guzman" wrote in message ... > Is the sa database context set incorrectly when connecting from any ... > SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Re: User Mappings to TempDB
    ... "Dan Guzman" wrote: ... > databases, including tempdb, contain the user. ... > in a new database, you'll need to drop the user after database creation. ... > SQL Server MVP ...
    (microsoft.public.sqlserver.security)