|
Paweł Mrozik wrote: SQL Server Compact be a good choice
Yes.
Paweł Mrozik wrote: use from within Visual Studio
Why would you do that?
|
|
|
|
|
Thank you very much. SQL Server Compact it is then.
|
|
|
|
|
I have an application needs to retrieve large dataset from the database (5 million could be go up to 20 millions records) then use it for stats calculation (such as range, mode, stddev, variance ...). The application has memory spike when loading large dataset and store the records into LIST collections. sometimes the application crashes due to memory usage.
Is there any alternatives ? use any other technologies, such as NOSQL.
|
|
|
|
|
Once you retrieve the dataset in code, the database no longer matters.
You should consider setting up a configurable report and only pulling in the data needed for the report. I doubt that you are reporting on all 5 Million records at any given time.
Additionally, you can retrieve the data grouped and sorted from the database.
<jl>W
|
|
|
|
|
Tricky since we don't know what you need to report, but pulling 5-20 million records into memory seems a bad idea in any case. let the server do the work. There are numerous options available to you to speed up things.
You could calculate statistics "continuously" in the background so when you pull the report (or stats) you have a much less work on getting it out. Calculating counts, sums, averages etc... is pretty though on resources and time when using large datasets. If you analyze your queries you can probably optimize all this, but remember that aggregate functions render any index on that column useless.
Another option would be to write a server side module that receives a "task" and notifies the user when it's done. You could write it in such a way that it consumes less memory, but it will probably take longer. You could optimize this maybe by using several threads.
Both solutions have the downfall that when many users launch these calculations, the server comes to a standstill. Your best bet is to probably divide all this in bits and pieces that calculate seperately.
If this is a real production database and the calculations are often en significant I'd recommend a new database optimized for reporting and go from there. (Database structures and setup can be different depending on the usage of the database)
Just to give you some ideas. I have rarely seen "real-time" statistics on large datasets, rather they have a delay (eg statistics on my database are quite simple, but due to the size only calculated once a day)
hope this helps...
|
|
|
|
|
What database are you using? If MSSQL or Oracle, you could write stored procedures or views to do the statistical calculations server side, and just return the results. This is likely to be much more efficient than pulling all of the data client side and then calculating
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
As all the others have stated, you are using the wrong tool for the job. You are using the CLIENT, used to display stuff, to do the work of a SERVER, used to crunch stuff. Use the database to crunch the data the client needs to display.
Most LOB developers work with volumes like these and never do the crunching on the client. We have a policy that we never send more than 2k records to the client. There are exceptions but the business has to sign in blood to get them accepted.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: We have a policy that we never send more than 2k records to the client.
I like the fact you have a setup a real limit for this. 
Out of curiousity, you do look at the record size too right? eg. I send way more than 2k records to my client website, but they are merely timestamp/value pairs. If I would need to send several megabyte blobs I would limit that severely 
|
|
|
|
|
V. wrote: you do look at the record size too right
No, all our data is text and none will amount to a lot per record so we don't go past the record count. I did catch one guy trying to pass a list of 1k records with attached documents up to 20mb EACH. Nailed that rather quickly. You want an attachment you get it one at a time, here have a list of file names!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I suggest you to download the data first, instead of keeping such a huge data in in-memory.
You can download the data to the client and store it in MSAccess or .TXT. Later use your code to do the calculation on the data.
|
|
|
|
|
Hi,
does anyone has a good tutorial on pagination , I searched the internet no tutorial walking.
Thank you
|
|
|
|
|
|
its not working I already tried
|
|
|
|
|
It would have helped if you had stated that in the original question. What does "not work" mean? Does it not compile, does it throw an exception?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
there errors when connecting and following precedents and next BUTTONS do not work properly
|
|
|
|
|
We know that you can see your code, but you have to understand that we can't. We cannot see your Visual Studio output window or your debugger. Can you see why we cannot help you here? We can see no code and no exceptions.
|
|
|
|
|
..and the other articles produce the same error? Or a different one?
You can try and solve the error, ask the original author for help (there's a forum at the bottom of the article) or find some other example.
I'd still recommend dropping paging completely.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have no other solution I have too much data I need to do pagination
|
|
|
|
|
That does not mean that other solutions do not exist; paging is usually not done in a WinForm, even if there's a lot of data. We don't have a "pagelimit" as a book has, the complete concept of having a "page" should remain on the web, not in Forms.
Having a page is half an anti-pattern - most people will only use the first page at all times.
Large datasets can be loaded quickly by virtualizing the grid, and there's filtering to limit the dataset fetched.
Still, if you want pagination, you'd have to find a working component, or adapt one of the existing ones to your needs.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Virtualization is a very good solution for me but there is no tutorial to explain and I am beginner so it's hard for me 
|
|
|
|
|
|
Golden Upvote for Patience Above and Beyond ... awarded 
« I am putting myself to the fullest possible use which is all, I think, that any conscious entity can ever hope to do » HAL (Heuristically programmed ALgorithmic computer) in "2001, A Space Odyssey"
|
|
|
|
|
this totoriel is not well explained it is very difficult
|
|
|
|
|
|
Yup, you're right; it is complexer than simply binding. It takes more time to write, and it takes some extra time to change the structure to something that loads on demand - but then the grid will load and display fast, even scrolling the grid with the mouse will be rather quick.
I've never tried the code from the HowTo that you linked, but I did complete the Walkthrough. To me, the walkthrough looks like it is simpeler. The idea is easier to follow if you only implement a readonly-datagrid, meaning you'd only implement the <a href="http://msdn.microsoft.com/nl-nl/library/windows/apps/system.windows.forms.datagridview.cellvaluen">CellValueNeeded</a>[<a href="http://msdn.microsoft.com/nl-nl/library/windows/apps/system.windows.forms.datagridview.cellvaluen" target="_blank" title="New Window">^</a>]-event. Not the push, no delete, only the stuff to display the data.
I always make mistakes when implementing those together, so I always start with a readonly-grid. One part to load the data into a List (of object-arrays), executed on a separate thread, and the CellValueNeeded event that gets the data from that list and displays is. If you have 10 items in the list, you set the RowCount value of the grid to ten, and the grid will ask a value for each visible item.
Another reason I like the approach is because it allows me to display a ProgressBar that shows how much is loaded - no white forms for five seconds while databinding, but a nice progressbar with a Cancel button.
There's other alternatives though; one I read here that sounds as a logical and quick solution is to fetch up to a maximum. I think the most common solution is to add another filter, to force the user to think about specifying more precisely which data he/she needs.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
