sql server - C# / SQL Efficiency Query -
i've got winforms application connects sql database , retrieves data, application needs check if data exists in sql database before performing action.
i've seen similar queries tend sql, i'd know of 2 options below, better in terms of overall performance. i've considered 2 approaches:
1.create 2 lists in c#, populate them , check if value first list exists in next list:
list<t> firstlist = new list<t>(); list<x> secondlist = new list<x>(); firstlist = firstlist.populate(); // sql stored procedure populate list secondlist = secondlist.populate(); // sql stored procedure populate list foreach(var item in firstlist) { if( (x => x.value == item.value) ) { //do action } }
2.create list in c# , method executes stored procedure check if value passed parameter exists in sql database. once list populated iterate through , execute new method returning boolean value:
list<t> firstlist = new list<t>(); firstlist = firstlist.populate(); // sql stored procedure populate list foreach(var item in firstlist) { bool exists = false; exists = firstlist.checkvalue(item.value); // sql stored procedure check if value exists if(exists) { //do action } }
i'm not sure whether it's better perform many sql queries , reduce memory usage or perform fewer queries , use memory. application run on small application server should have 32gb ram.
i'm open using technique if either of approaches aren't efficient.
i suggest more efficient run whole query on sql server. retrieving any data remote database , doing comparison locally never quicker doing comparison source of data held.
my suggestion write sproc comparison , returns rows exist in both databases.
possibly use like:
select * [server2name].[thedb].[theowner].[thetable]
or have @ linked tables in sql server
Comments
Post a Comment