Databases
StackOverflow2013
Postsdbo
POASP.Net MVC - Data Design - Single Wide Record versus Many small record retrievals
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.Net MVC - Data Design - Single Wide Record versus Many small record retrievals
    primarykey
    data
    text
    <p>I am designing a Web application that we estimate may have about 1500 unique users per hour. (We have no stats for concurrent users.). I am using ASP.NET MVC3 with an Oracle 11g backend and all retrieval will be through packaged stored procedures, not inline SQL. The application is read-only.</p> <p>Table A has about 4 million records in it. Table B has about 4.5 million records. Table C has less than 200,000 records. There are two other tiny lookup tables that are also linked to table A.</p> <p>Tables B and C both have a 1 to 1 relationship to Table A - Tables A and B are required, C is not. Tables B and C contain many string columns (some up to 256 characters).</p> <p>A search will always return 0, 1, or 2 records from Table A, with its mate in table b and any related data in C and the lookup tables.</p> <p>My data access process would create a connection and command, execute the query, return a reader, load the appropriate object from that reader, close the connection, and dispose.</p> <p>My question is this.... Is it better (as performance goes) to return a single, wide record set all at once (using only one connection) or is it better to query one table right after the other (using one connection for each query), returning narrower records and joining them in the code?</p> <p>EDIT: Clarification - I will always need all the data I would bring over in either option. Both options will eventually result in the same amount of data displayed on the screen as was brought from the DB. But one would have a single connection getting all at once (but wider, so maybe slower?) and the other would have multiple connections, one right after the other, getting smaller amounts at a time. I don't know if the impact of the number of connections would influence the decision here.</p> <p>Also - I have the freedom to denormalize the table design, if I decide it's appropriate.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser158017
    1. USuser158017
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POASP.Net MVC - Data Design - Single Wide Record versus Many small record retrievals
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload