Solved

VB.net Select/Copy very large tables

Posted on 2015-01-23
5
146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

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 143

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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