Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I guess you are dealing with some custom build Data Warehouse solution, that means huge amounts of data. Whatever you try to do, you shouldn't be loading all the data from database to application in order to calculate some numbers in staging table.</p> <p>The best thing you can do is to calculate whatever you need before you put data to Dwh_staging_table, so the problem is solved before it happens. If this it not possible and you already loaded data to database, you should do all the processing in place, in database (e.g. using hated Stored Procedures).</p> <p>In general, when you are dealing with huge amounts of data, moving the data around is your biggest enemy. Try to solve all your problems at the place where the data are now, without unnecessary transfer.</p> <p>If you want to anyway load the data back to c# code (what I don't advice), try to do everything without materialising all the data in memory. Create repository function which returns IEnumerable, which will be internally using yield return, so the whole collection of data is never materialised.</p> <p>And if you still insist on materializing data in some collection (what I don't advice even more), look at some collections which are not using sequential blocks of memory. Using collections like array, List or DataSet will result in higher chance of out of memmory exception. Try to use something like LinkedList or even better some chunked LinkedList of arrays (almost like paging which was suggested in other post).</p> <p><strong>EDIT:</strong> From what you said</p> <blockquote> <p>i have some missing values in the table i want to fill some columns afterwards using the avg techninque</p> </blockquote> <p>it sounds to me like something what should be possible just by one UPDATE statement of the staging table in database. Not sure what exactly you want (e.g. I want to set to AvgMetric averaged value of Metric column grouped over Category column). In that case it would look like:</p> <pre><code>WITH t AS ( SELECT st.[Category] ,st.[AvgMetric] ,AVG(st.[Metric]) OVER (PARTITION BY [st.Category] AS [CalculatedAvgMetric] FROM [Dwh_staging_table] st ) UPDATE t SET [AvgMetric] = [CalculatedAvgMetric] </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POException 'System.OutOfMemoryException' in system.dll
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTiny
    1. USTiny
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POException 'System.OutOfMemoryException' in system.dll
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COpaging is it related with sql query or c# i am new to this concept.
      singulars
      1. PO
      1. USuser1056466
    2. COWhat HaurkurHaf suggested is paging on SQL query level using row_number() (http://beyondrelational.com/modules/2/blogs/28/posts/10434/sql-server-server-side-paging-with-rownumber-function.aspx). The advantage is you'll load just small chunks of data to memory. Disadvantage is that you'll execute multiple queries against database. What I was suggesting is to open connection and keep it open while processing all the data in stream. The use of LinkedList (or LinkedList of Lists) was if you want to put everything in memory.
      singulars
      1. PO
      1. USTiny
    3. COTHanks man that linked helped me ....:)
      singulars
      1. PO
      1. USuser1056466
 

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