views:

38

answers:

1

I have a table with a lot of rows. The structure is something like this: UserID | ItemID | Item Data...

Would I see any gains in query time if I separated this into a table per user, or per smaller group of users?

Queries are always single user getting/modifying a selection of items.

A: 

Do not make multiple tables.

Tell us what you think "lots of rows" is.

Do you have any indexes or constraints on the table?

How is the data inserted, updated and deleted?

Typically, I would want a unique index on UserID, ItemID, but without knowing more about your environment, it's hard to say.

Also, please give us information on the database platform and the data types you have chosen.

Cade Roux
lots of rows is in the high hundred thousands range. UserID, ItemID and the combination all have indexes (as well as some other commonly searched fields). This is on SQL Server 2000.
aepheus
High hundred thousands on decent hardware and proper indexes shouldn't be forcing you into looking at odd scenarios like per user tables yet, IMHO. But not a SQL Server 2000 guy.
rfusca
@aepheus What does the execution plan on a typical query look like - is it using the indexes?
Cade Roux