How do I split a query into multiple tables using a dataset

Question

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 29-Aug-20 17:03pm

Member 11856456

Updated 29-Aug-20 20:22pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2020-08-29T20:19:00

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.

Dave Kreskowiak · Accepted Answer · 2020-08-29T17:25:00

Solution 1

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?

Posted 29-Aug-20 17:25pm

Dave Kreskowiak

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!