Solved

VB.net Select/Copy very large tables

Posted on 2015-01-23
5
135 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 87
Find results from sql within a time span 11 29
T-SQL:  Collapsing 9 22
SQL Query assistance 16 20
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

786 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