0

My app written in C# and loads over 1 milion rows from database. For some reason, I have to perform this initial loading and then I'll search inside the loaded rows instead of querying SQL and every single column has large data( about 500 characters each row) and each column has different type . For search, I have to check the equality of data from selected record with the 3 specific columns from other records.

I think using Binary Search Tree can do it fast because now , it takes minutes using nested for loops(2 loops). If I can put the data in the correct structure, making HashCode of 3 columns will create unique integer data and put that in the BST.

I do not know any structure suits this hypothesis. The main question is, is this hypothesis correct? Or just dreamed?

Little Elite
  • 65
  • 13
  • 1
    A simple lookup created at startup should do the job - see LINQ ToLookup – Sir Rufo May 27 '18 at 06:12
  • So the result indexing is smart ? if some of column are text so indexing still stays as the order that it can be searched? – Little Elite May 27 '18 at 07:36
  • 1
    `For some reason` Can you talk us through why you want to do it in memory rather than in the database? – mjwills May 27 '18 at 09:19
  • You may not be using the same database that is loaded into the SQL Server. You should not include the mdf file in your connection string which is a common mistake. You should just connect to the instance of the SQL Server database that you see when you open SQL Server Management Studio (SSMS). – jdweng May 27 '18 at 09:45

1 Answers1

1

Indexing that data in memory is a good idea. It seems you are accessing based on equality e.g. row.SomeKey == mySearchKey. Hash tables can do that in O(1). Shove the data into a Dictionary or use ToLookup. Performance will be excellent (on the order of 10 million accesses per second for one CPU core).

If you need access to ranges there's a framework class for that as well that internally uses a red black tree. It's SortedSet/SortedDictionary.

usr
  • 168,620
  • 35
  • 240
  • 369