Click here to Skip to main content
15,066,193 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a query that brings back 1,000,000 rows. How can I take that and break that into smaller batches of 10,000. I have it set to fill a datatable called dt. However, i would like for it to fill a dataset with multiple tables of 10,000.

what I am wanting to do is go through each of these.

sqlconn1.Open()
        sqladaptor.SelectCommand = New SqlCommand("Select * from Profile, sqlconn1)
        sqladaptor.SelectCommand.CommandTimeout = False
        sqladaptor.SelectCommand.ExecuteNonQuery()
        sqladaptor.Fill(dt)
        sqlconn1.Close()
        sqlconn1.Dispose()
        sqladaptor.Dispose()


What I have tried:

I have been filling a datatable, I can set it to fill a single table in a dataset. but I need to dynamically create tables for the dataset. I figured batching these out may make it easier.
Posted
Updated 29-Aug-20 20:22pm

You don't. You would have to split your query into multiple queries, each returning it's own set of records.

Frankly, reading 1,000,000 into memory is a terrible idea for ANY reason. Multiply 1,000,000 by the number of bytes of data in each row and that's about how much memory you're going to need to keep it.

You cannot possibly show that many records to a user without severe performance and usability penalties.

You cannot get any performance benefit from loading all that data into memory for any kind of processing. That kind of processing should be left to the SQL Server, because that's what it was designed for.

Soooo, why would you need to do this?
   
v2
Comments
Member 11856456 29-Aug-20 23:29pm
   
is there a place to get an example on how to do this. I do not mind to change the design and code on what I am doing now. I figured the way I was doing it was not the best way but it was the only way I could figure out how to compare against the data I have.
Dave Kreskowiak 29-Aug-20 23:35pm
   
Do what? Why would you want to load 1 million records into memory?
Member 11856456 29-Aug-20 23:38pm
   
How do I "You would have to split your query into multiple queries, each returning it's own set of records" I have only done one query at a time. This could be a good learning experience for me. So instead of the 1,000,000 in memory how do I do your method?
Dave Kreskowiak 29-Aug-20 23:41pm
   
And that's exactly how you do it. One query at a time. Frankly, splitting the table up into multiple table in memory gives you nothing. You complicate any processing you're doing on the records.

Again, WHAT ARE YOU TRYING TO DO WITH ALL THAT DATA IN MEMORY?
Member 11856456 29-Aug-20 23:46pm
   
comparisons, it made sense to me to have everything available to make comparisons when a match is found I could update if new information was available.
Dave Kreskowiak 29-Aug-20 23:48pm
   
That should be done in the database itself, not in client-side code.

You have multiple problems attempting to do it memory. First, the storage requirements. Doing it in multiple tables of data just complicates things.

Second, speed. Once loaded, you lose all indexing which would have to be rebuilt to speed things up depending on what you mean by "comparisons".

If done correctly and the database is built and indexed correctly, the database engine will beat the piss out of anything you can do client-side.
Member 11856456 29-Aug-20 23:52pm
   
is there a good article where I can see how this is done? I googled single query to multiple queries in vb.net, or sql query into batches in vb.net. not much documentation on this.
Dave Kreskowiak 29-Aug-20 23:59pm
   
No. Nobody does this for very good reasons.

You're Googling for the wrong things. This is nothing more than a paging problem. Retrieving a block of records from whatever ID and returning the next X number of records.

SELECT column list
FROM table
ORDER BY column
OFFSET (@SkipRows) ROWS FETCH NEXT (@TakeRows) ROWS ONLY

Keep in mind that what you are trying to do is a dead-end. It's a bad solution that will perform poorly and will not scale well with an increase in the number of records.
Member 11856456 30-Aug-20 0:04am
   
I appreciate it. thank you!
To add to what Dave has - rightly said - what you are doing doesn't retrieve 1,000,000 records anyway - it tries to retrieve 2,000,000 instead!
VB
sqlconn1.Open()
sqladaptor.SelectCommand = New SqlCommand("Select * from Profile, sqlconn1)
sqladaptor.SelectCommand.CommandTimeout = False
sqladaptor.SelectCommand.ExecuteNonQuery()
sqladaptor.Fill(dt)
sqlconn1.Close()
sqlconn1.Dispose()
sqladaptor.Dispose()

This line:
sqladaptor.SelectCommand.ExecuteNonQuery()
Executes your SQL Command, which means that SQL prepares 1,000,000 rows for return, and returns the number of rows it found.
Then this line:
sqladaptor.Fill(dt)
Does it again, storing all 1,000,000 rows into a DataTable.

If you had tried to find a less efficient way to do anything, that would probably be it!

And please, don't use SELECT * FROM - always list the rows you want in the order you want them: SELECT Column1, Column2 FROM ... that reduces the amount of data you want to fetch to the minimum by only returning columns you are going to use - and images for example can take considerable space - and makes your code my "robust" in the event of database changes.

But the primary thing to take away from this is as Dave says: never try to present more than a hundred or so rows to a user: page it, search it, filter it - but think like a user for a moment: how are you going to find the row you are interested in out of 1,000,000 rows slapped on your screen? How long will it take you to scroll down that far?
Do it your way, and you users will hate you app, and by extension you.
   
Comments
Dave Kreskowiak 30-Aug-20 10:42am
   
I didn't even bother to look at the code because the idea of getting 1 million records was just insane, no matter how it was done.
OriginalGriff 30-Aug-20 11:15am
   
Doing it twice is a new level of lunacy! :laugh:
Member 11856456 30-Aug-20 11:48am
   
I see, it creates 2,000,000 because the same data in the database plus the datatable I have filled. I also should have explained that the datatable that is filled is only in memory and not being shown. its just to be compared against in the background when looking for matching data when someone imports an excel file. I will combine your information with what I received from Dave. Thank you both!
OriginalGriff 30-Aug-20 12:08pm
   
Doesn't matter, it's still using a silly amount of data, and that stresses SQL server, it's bandwidth, and your app.

Instead of reading it all, use SQL WHERE clauses to fetch just the data you need when you need it. That has the additional advantage of keeping up to date with changes made by other users of the same database.
Member 11856456 30-Aug-20 12:10pm
   
thank you, great information!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900