-1

I'm about to develop a database for a community. The database is MS SQL Server. Each user can create their own shop where they can advertise about their products.

So let's say I create a table to store every user's products. Then the product is linked to the user with an id.

I'm just a little concerned about how much data there can be in the table before it hurts performance when searching for the user's products.

Improve this question
5
  • 8
    There is no number of records past which you shouldn't go. Of course, as a table grows in size, performance will degrade, but not linearly (Assuming proper indexes exist), but a properly indexed table should be able to scale quite well. How many records do you anticipate? What would be acceptable response times? What kind of hardware are you hosting the SQL Server on?
    – Chad Mattox
    Jun 13, 2016 at 22:55
  • Not that many records. Around 3-4.000 records
    – Mikael Andersen
    Jun 14, 2016 at 4:05
  • This is an extremely broad question that has to do with everything from hardware (RAM, IO, CPU, network, etc.) to data (number of rows vs. amount and type of data on each row) to the types of DML operations used (ad-hoc, warehousing scenarios, mass inserts / exports, how many concurrent users, etc.). So we're forced to guess with very little information to go on here. But a few thousand records as you implied, that I'm assuming don't contain gigabytes of data each (?), would likely be more than fine with any hardware that can run the thing in the first place.
    – Kahn
    Jun 14, 2016 at 6:46
  • 2
    4000 rows is nothing. Not even 4 million are considered "big" these days
    – a_horse_with_no_name
    Jun 14, 2016 at 7:02
  • Even table scanning 4000 rows will be instantaneous. Unless there is huge amounts of LOB data on each row as Kahn suggests
    – Chad Mattox
    Jun 14, 2016 at 19:06

1 Answer 1

Reset to default
3

Performance issues can stem from a wide variety of issues - the size of the table is only one factor. Things such as indexes and the design of the query are just as, if not more important than, how well something performs. For example, take a look at the advice on this Stack Exchange question:

Slow Performance Inserting Few Rows Into Huge Table

Once your performance starts to degrade, you will start to look at the reason why, and then fix that problem. Is your table actually "too large?" Then maybe partitioning it will solve the issue. Maybe the query is bringing over unused columns and you don't need them all - rewrite the query and all may be better. In your case, a covering index on the users that includes the product will probably keep it from ever degrading any noticeable amount. There are a host of reasons why performance will be degraded over time.

Don't plan for any certain size - instead keep good practice in database design and query writing. That way when something comes up, it will be much easier to find that issue and fix it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.