ADO.NET SQL Query Join?

Question

I am writing a little test app which connects to a SQL Server database which has 2 tables in it. Table 1 is a list of data with a non unique key which points to multiple rows in table 2. At the moment what I do is traverse each record in Table 1 and then get the relevant records from Table 2 with a separate query. Table 2 has over 26 million records in it and this process takes a very long time. The data in Table 1 could have just a few hundred records in it or anything up to about 1 million.

Is there a way of speeding up this data access? Perhaps using a table join to get all the data in one query? Or anything else?

Sorry I cannot post the database tables or current code as I am under NDA as it is very sensitive data. The current code is sort of irrelevant here anyway as I am looking for a totally new (and better) way of doing this.

Please note I am using ADO within .NET with C#.

EDIT: I am okay doing the JOIN query but more asking if this will be more efficient with ADO? I know it would be more efficient in general but not sure if ADO can handle this. Also looking for some example C# code to do this. Thanks.

It's very simple; you can use the inner join operator between both tables. On the other hand, worse case scenario is you try to load 100 * 26 million rows into memory. How many rows do you expect the query to return in total (how many rows do you actually get from Table2?) — dash
– dash, Commented Feb 21, 2012 at 9:11
There will probably be around 8-10 rows in Table 2 per record in Table 1. — Jonnster
– Jonnster, Commented Feb 21, 2012 at 9:32

AdaTheDev · Accepted Answer · 2012-02-21 09:10:00Z

3

You can get all the data in one go with a JOIN like this:

SELECT t1.NonUniqueKey, t2.*
FROM Table1 t1
    JOIN Table2 t2 ON t1.NonUniqueKey = t2.NonUniqueKey

You'll want to consider putting an index on NonUniqueKey, but this should be better for you than executing n queries, one per row in Table1

answered Feb 21, 2012 at 9:10

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonnster Over a year ago

Thanks, that's great. Totally my fault for not explaining better, but I am okay with the join query but more looking how to use this from ADO.NET in as efficient way as possible. Would just executing this query with the SqlDataReader be the fastest way?

AdaTheDev Over a year ago

SqlDataReader may be more efficient than using a SqlDataAdapter to Fill a DataSet/DataTable, though how much difference - you'd need to test out tbh. Under the hood, the SqlDataAdapter would use a SqlDataReader. In the grand scheme of things, the query itself and the amount of data to transfer across the network will likely be the biggest pinch points.

Collectives™ on Stack Overflow

ADO.NET SQL Query Join?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related