C# / SQL Efficiency Query

Question

I've got a Winforms application that connects to a SQL database and retrieves data, the application then needs to check if the data exists in another SQL database before performing an action.

I've seen similar queries but most tend to be for SQL, what I'd like to know is which of the two options below, is better in terms of overall performance. I've considered two approaches:

1.Create two lists in C#, populate them and then check if the value from the first list exists in the next list:

List<T> firstList = new List<T>();
List<X> secondList = new List<X>();

firstList = firstList.populate(); // SQL Stored Procedure to populate list
secondList = secondList.populate(); // SQL Stored Procedure to populate list 

foreach(var item in firstList)
{
  if( (x => x.value == item.value) )
  {
     //do some action
  }
}

2.Create a list in C# and a method that executes a Stored Procedure to check if a value passed as a parameter exists in the SQL database. Once the list is populated I would iterate through it and execute the new method returning a boolean value:

List<T> firstList = new List<T>();

firstList = firstList.populate();  // SQL Stored Procedure to populate list

foreach(var item in firstList)
{
  bool exists = false;
  exists = firstList.checkValue(item.value);  // SQL Stored Procedure to check if value exists
  if(exists)
  {
     //do some action
  }
}

I'm not sure whether it's better to perform many SQL queries and reduce memory usage or perform fewer queries and use memory. This application will run on a small application server which should have 32Gb RAM.

I'm also open to using another technique if either of my approaches aren't efficient.

Do you really mean the data is in 2 separate *databases* or 2 separate *tables*? — Neil, Aug 15 '17 at 08:26
Do you have any indices on the relevant fields? If you do, then a SQL solution will be more efficient. — Jonathan Willcock, Aug 15 '17 at 08:37
If you are unable to solve this through sql I would suggest to parallelize the the loop. — Stefan, Aug 15 '17 at 08:41
Thanks @Stefan I'll research this if I'm unsuccessful with the SQL approach — Daniel, Aug 15 '17 at 08:45
@JonathanWillcock yes there are indexes, I'll try Neil's approach before looking at Stefan's suggestion — Daniel, Aug 15 '17 at 08:46

Neil · Accepted Answer · 2017-08-15T08:37:23.660

4

I would suggest it would be more efficient to run the whole query on the SQL server. Retrieving any data from a remote database and doing a comparison locally would never be quicker than doing that comparison where the source of the data is held.

My suggestion would be to write an SPROC that does the comparison and returns only the rows that exist in both databases.

Possibly use something like:

SELECT
    *
FROM
    [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]

Or have a look at linked tables in Sql Server

edited Aug 15 '17 at 08:37

answered Aug 15 '17 at 08:32

Neil

11,059
3
31
56

That sounds reasonable, how would you handle the querying of a second database from within SQL? I can't assume that the databases are running on the same server either - didn't think to mention that, sorry. – Daniel Aug 15 '17 at 08:33
Look at linked servers - see https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine – FJT Aug 15 '17 at 08:36
This answer https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server gives some examples. – Neil Aug 15 '17 at 08:36
Thanks Neil, I'll go this route then, I do prefer working with data in SQL so that suits me best. I hope it's not a huge task to set up! – Daniel Aug 15 '17 at 08:43

C# / SQL Efficiency Query

1 Answers1