Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
858 views
in Technique[技术] by (71.8m points)

sql server - SQL Table Size And Query Performance

We have a number of items coming in from a web service; each item containing an unknown number of properties. We are storing them in a database with the following Schema.

Items
- ItemID
- ItemName

Properties
- PropertyID
- PropertyName
- PropertyValue
- PropertyValueType
- TransmitTime
- ItemID [fk]

The properties table is growing quite large since it stores the properties for each item, each time the web service is called. My question is this: at what point should we stop adding new records to the Properties table, and archive older Property records according to their transmit time? When does the properties table become too large, and take too long to query? Is there a rule of thumb?

Thanks.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

There is no rule of thumb

Some thoughts:

  • define "large" (we have 160 million rows tables)
  • do you have a problem now? if no, don't fix it
  • have you run profiler or some of the whizzy dmvs to find out bottlenecks (missing indexes etc)
  • if you need the data to be in hand, then you can't archive it
  • you could partition the table though

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

56.8k users

...