Have Geek Brain

Sql server table optimization recommendations

I have a table that contains user input that needs to be optimized. I have some ideas on how to solve this problem, but I would really appreciate your input on this. The table that needs optimization is called Value in the structure below. All the tables below have integer primary keys called Id.
Specifications: Ms Sql Server 2008, Linq2Sql, asp.net site, C #.

The current structure looks like this:
Page -> Field -> FieldControl -> ValueGroup -> Value

Page

Pages are a container for one or more fields.

Field

A field is a container for one or more FieldControls, such as a text field or a dropdown menu.
Relationship: PageId

FieldControl

If the field is of type "TextBox", then one FieldControl is created for the field. If the field is of type "DropDown", then for the field containing the option text, one FieldControl parameter is created for each dropdown list.
Relationship: FieldId

ValueGroup

Each time the user fills in fields within the page and saves them, a new ValueGroup (Id) is created to track the user's input related to that save. When the user wants to look at a previously filled out form, the value group is used to load values ​​into the FieldControls of the previously filled instance.
Relationship: No

Value

Actual input of FieldControl. If the user typed "Hello" into the TextBox, then "Hello" will be stored in a row in this table, and then as a reference to which FieldControl "Hello" was entered. ValueGroup binds to values ​​to group them to keep track of which save / instance type they belong to, as described in ValueGroup.
Relationships: ValueGroupId, FieldControlId

Problem

If 100,000 pages are full, each containing 10 text fields, we get 100,000 * 10 entries in the value table, which means we quickly reached a million entries, which makes it very slow as it is now. The user can create as many different pages with as many fields as they like, and all of these values ​​are stored in a value table. The way I am using this data is either displaying a paginated gridview that displays all records for one Pagetype, or when viewing a specific instance of the page (values ​​grouped by ValueGroupId).

Some ideas I have:
Good indexing should be very important when optimizing a value table.
Should I add the foreign key directly to the page from the value, resulting in indexing (Id, PageId, ValueGroup), allowing the gridview to retrieve values ​​that are only relevant to one page?

Should I be looking for a table splitting, and if so, how would you recommend me to do this? I thought there was pagination, so getting chunks of values ​​specific to a specific page would be wise in this case? What the script / schema would look like is something like where pages can be created / deleted by users at any time.

PS. There should be a badge in this forum for all the people who finished reading this long post and I hope I get it :)

0


a source to share


3 answers


Just to close this post. Correct indexing solves all performance problems.



+1


a source


This may be a little off topic, but why? Is this data available for access in real time or for post-processing? Could you pack the data into one line and then unpack it later?



0


a source


Generic

You say it is slow right now and there could be many reasons for this besides the database like low memory, high cpu, disk fragmentation, network load, socket problems, etc. etc. This should show up on the system monitor

Try for example the Sysinternals tool (now MS): http://live.sysinternals.com/procexp.exe

But if it's all under control, go back to the database.

Database Index

One million records is not "many" and shouldn't be a problem. The index should do the trick if you don't have any indexes right now. You should probably set indexes on all tables if you haven't already.

I tried to do the database model, this is correct: http://www.freeimagehosting.net/image.php?a39cf99ae5.png

Table structure (?)

Page -> Field -> FieldControl -> ValueGroup -> Value

The table structure looks like it might not be optimal, but it's hard to tell when I don't know how the application works.

Do all tables have foreign keys the table above?

Is this somewhat similar to your code?

Pseudocode:

1. Get page info. Gives key "page-id"
2. Get all "Field":s marked with that "page-id".
   Gives keys "field-id" & "fieldcontrol-id"
3. Loop trough all fields-id:s and get the FieldControl for each one
4. Loop trough all fields-id:s and get all ValueGroup:s.
   Gives a list of "valuegroup-id":s keys
5. Loop trough all ValueGroup:s and get all fields
0


a source






More articles:


All Articles