Get low stock from a C# dll database which generate million rows automatically

I have database (C#, dll) which contains only one table, generates million rows automatically.

I can only get one row at once. The table's columns are : int ItemID, string category, string model, string Brand).

Two items are same if they have same category, brand, model). I want to find out all types of items that are low in stock, where low is that there are two or less items of that type left.

This is the code I wrote for this, but it took 2 or 3 hours to execute, it is not give me correct results. How can I solve this?

   public DataTable getLowStock()
   {
       DataTable dt = new DataTable();
       object[,] arr=new object[1000000,4];
       int  index=0;
       int rows;
       rows = db.NumRows;
       int[] IdArray;
       IdArray = db.GetItemIDList(0, rows - 1);

       string Category, Brand, Model, Condition, Location, Notes;
       DateTime ReceivedDate, LastUpdated;
       int Weight;
       double PurchasePrice, SellingPrice;

       for (int a = 0; a < IdArray.Length; a++)
       {
           Console.WriteLine(a);

           db.GetItemRecord(IdArray[a], out Category, out Brand, out Model,
                            out ReceivedDate, out Weight, out Condition, out Location,
                            out PurchasePrice, out SellingPrice, out Notes, out LastUpdated);

           if (a == 0)
           {
               arr[0, 0] = Category;
               arr[0, 1] = Brand;
               arr[0, 2] = Model;
               arr[0, 3] =  1;
               index++;

           }
           else
           {

               for (int i = 0; i < index; i++)
               {

                   if ( ( arr[i,2].ToString()==Model)&& (arr[i,1].ToString()==Brand) &&   (arr[i,0].ToString()==Category ) )
                   {

                       arr[i, 3] = (Int32)arr[i, 3] + 1;
                   }
               }

               arr[index,0]=Category;
               arr[index,1]=Brand;
               arr[index,2]=Model;
               arr[index, 3] = 1;

               index++;
           }

       }

        dt.Columns.Add("Category", typeof(string));    
        dt.Columns.Add("Model", typeof(string));
        dt.Columns.Add("Brand", typeof(string));
        dt.Columns.Add("count", typeof(int));   

        for 开发者_Python百科(int i = 0; i < index; i++) {
           Console.WriteLine("i="+i);

       if((int)arr[i,3]<3){
           dt.Rows.Add(arr[i,0].ToString(),arr[i,2].ToString(),arr[i,1].ToString(),(int)arr[i,3]);
       }
       }
       return dt;
   }

Surely the best way to do this is with an actual database? Then you can do the query in SQL, which will execute about 1000 times faster than querying an in-memory datatable.

Instead of fetching the whole table I suggest you execute a sql aggregation and fetch only the aggregates:

SELECT category, model, brand, COUNT(*) 
FROM table 
GROUP BY category, model, brand
HAVING COUNT(*) >= 2

Then, your code is very slow for several reasons, but the main one is that it is quadratic. So 1 million squared makes one thousand billion operations, which is quite slow.

If you must do the aggregation yourself you have to use a fitting container, like a set or a hash table, not an array. This way you will perform in O(n) (with a good hash table) or O(n log n) ( with a set), instead of 0(n^2).