Solved

VB.net Select/Copy very large tables

Posted on 2015-01-23
5
131 Views
Last Modified: 2015-01-24
Hi

I have a VB.net Windows forms application where I have to count the records in SQL tables of 65 to 100 million records. I also have to pull data into a DataGridView.

Is there a class that I can use to make this faster? I have used the SQLBulkCopy class to upload data. I am looking
for something similar to pull data faster
0
Comment
Question by:murbro
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40566031
if you want to display 100M rows on the client, there will be no fast thing.
unless you want to "page" (and obviously filter) the data, so the end-user can reduce the number of records that need to be transferred from the server over the whole networkwork infrastructure and to be put into the end-users computer RAM. I see many times where a developer forgets that resources are limited, especially on normal end-user computers.
the lesser rows you bring to the user, the better (for EVERYbody)

you will need to clarify exactly what are the needs for this form, and how to reduce the data transfer between the machines.
just consider 100M rows, each how having 1KB size each (which can be considered a short row, actually).
total size of all those records would be around 100GB. how much RAM does your machine have? how much disk space does your machine have?

next point is that anything above 100 records (at a time) for a normal end-user is unusable, he will just be scrolling "all day".

note: there are exceptions, of course, but those are very rare.
0
 

Author Comment

by:murbro
ID: 40566045
Thanks Guy. What about counting records
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40566052
just have the total count of the full table with no filters?
there, you have some options, either slow and accurate, or fast and inaccurate, or not real-time:
http://blogs.msdn.com/b/martijnh/archive/2010/07/15/sql-server-how-to-quickly-retrieve-accurate-row-count-for-table.aspx
if this is a counting based on some fields, the above methods won't work at all.
depending on the needs (mainly on how real-time the information needs to be), an hourly job on the db that does the extraction to a staging table could do the job, and the user can look at that table which will give "instant and fast" results, but with a "timestamp" to tell him; this was the sitation like 12 minutes ago...
0
 

Author Closing Comment

by:murbro
ID: 40567915
Thanks very much
0
 

Author Comment

by:murbro
ID: 40567916
Great answer!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now