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.
