StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
855350
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2009-05-12T23:12:24.313
FavoriteCount
0
LastActivityDate
2009-05-12T23:12:24.313
LastEditDate
LastEditorUserId
0
OwnerUserId
0
ParentId
855022
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
<h2>Generic</h2> <p>You say it is slow now and that can be many reasons for that other than the database like low memory, high CPU, disk fragmentation, network load, sockets problems etc etc. This should show up on a system monitor</p> <p>Try for instance Sysinternals (now MS) tool: <a href="http://live.sysinternals.com/procexp.exe" rel="nofollow noreferrer">http://live.sysinternals.com/procexp.exe</a></p> <p>But if that is all under control then back to the database.</p> <h2>Database index</h2> <p>One million records is not "that much" and should not be a problem. An 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 done so already.</p> <p>I tried to do a database model, is this right: <a href="http://www.freeimagehosting.net/image.php?a39cf99ae5.png" rel="nofollow noreferrer">http://www.freeimagehosting.net/image.php?a39cf99ae5.png</a></p> <h2>Table structure (?)</h2> <p>Page -> Field -> FieldControl -> ValueGroup -> Value</p> <p>The table structure looks like it may not be the optimal one but it is hard to say exactly when I don't know how the application works.</p> <blockquote> <p>Do all tables have the foreign keys of the table above ?</p> </blockquote> <h2>Is this somewhat similar to your code ?</h2> <p><strong>Pseudo code:</strong></p> <pre><code>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 </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POOptimization suggestions for sql server table
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. This table or related slice is empty.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COHere is what my structure actually looks like: http://freeimagehosting.net/uploads/95d4b3767e.gif Yes all tables have fk´s to the table above, and are indexed on their pk´s (Id) and by adding a non-clustered index to FieldValueGroupId in the Values table things went a LOT faster. I also noticed a bug in the paging of the gridviews which caused the query to return more than was really needed and once this has been optimized, i think the performance should be much better.
  singulars
  PostPostId
  PO
  UserUserId
  USCodeSpeaker

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.