DataTable Row Loop c# Performance Testing (Linq vs. Select vs. Parallel vs. For)

froot-loopsI still find myself using DataTables (from SQL Server, mySQL, etc.) on a regular basis. In an effort to see how some different methods of looping through the data performs and what method might be the fastest, I put together some small, fast tests, below are my test results.

[Just jump to the fastest method.]

datatable-row-tests

My sample data was comprised of about a 34,000  row datatable – running these tests on a i7, 32gb, VS2015 setup. The 34,000 was chosen because a box of Froot Loops contains about 1800 Froot Loops (I think), but 1800 was too small, so how about 18 boxes of Froot Loops?

Continue reading “DataTable Row Loop c# Performance Testing (Linq vs. Select vs. Parallel vs. For)”

DataTable Row Loop c# Performance Testing (Linq vs. Select vs. Parallel vs. For)

MongoDB – Linking Records / Documents Using MongoDBRef

If you’re using MongoDB, you know as a document database it doesn’t provide the “join” feature you’ve come to rely on in standard relation databases (sql server, mySql, postgres). In many instances, you’re not using Mongo as a replacement for your existing data structure / methods – but it would be nice to relate some documents (without embedding everything [potentially duplicating data] into a single document.

I am a big fan of MongoDB and the .net / c# driver. Below is a method I’ve found that works well for relating different documents using MongoDBRef. In my scenario – which I’m sure many have better methods – I’m using a property to keep a list of related documents – then a method to retrieve the related documents if needed.

I’m my below example, I’m creating trains, train cars, and passengers – then relating them. In short: I’m using IList<MongoDBRef> to store the list, and FetchDBRefAs to get the documents again.

This works really well and is flexible. I’ve tested this against many records and have found that if you’re looking to get thousands of records with their related thousands of records – you won’t experience the same performance as a standard relational db join. But for a few records (hundreds in my testing) it’s fast and efficient. I’m also using .AsParallel() on the query – this gained me about 10%+ performance.

One note: if you’re allowing Mongo to generated the document id on insertion, queuing up records in a bulk operation (ie: InitializeOrderedBulkOperation) you’ll soon realize there isn’t an id yet to use for the MongoDBRef.

Comment if you’ve found a better method you prefer for linking documents.

My example:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace mongoDBlinked
{
    public class passenger
    {
        public ObjectId Id { get; set; }

        public string Name { get; set; }
    }

    public class trainCar
    {
        public ObjectId Id { get; set; }

        public string carNo { get; set; }

        public string serial { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> passengers { get; set; }

        public IList<passenger> GetPassengers(MongoDatabase db)
        {
            if (passengers.Count == 0)
                return new List<passenger>();

            IList<passenger> tpass = new List<passenger>();
            foreach (var related in passengers)
            {
                tpass.Add(db.FetchDBRefAs<passenger>(related));
            }

            return tpass;
        }
    }

    public class train
    {
        public ObjectId Id { get; set; }

        public string trainNo { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> trainCars { get; set; }

        public IList<trainCar> relTrainCards { get; set; }

        public IList<trainCar> GetTraincars(MongoDatabase db)
        {
            if (trainCars.Count == 0)
            {
                return new List<trainCar>();
            }

            IList<trainCar> tcars = new List<trainCar>();
            foreach (var related in trainCars)
            {
                tcars.Add(db.FetchDBRefAs<trainCar>(related));
            }

            return tcars;
        }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            
            var server = new MongoClient("mongodb://localhost").GetServer();
            var database = server.GetDatabase("testmongodb");

            var collection = database.GetCollection<trainCar>("trainCars");
            var trainCollection = database.GetCollection<train>("trains");
            var peopleCollection = database.GetCollection<passenger>("passengers");
            

            //add some data
            for (int t = 0; t < 20; t++)
            {
                train newTrain = new train();
                newTrain.note = "nyc" + t.ToString();
                newTrain.trainNo = "345";
                newTrain.trainCars = new List<MongoDBRef>();

               

                //add some cars:

                for (int i = 0; i < 50; i++)
                {
                    trainCar tcar = new trainCar();

                    tcar.carNo = "0" + i.ToString();
                    tcar.note = "Needs new brakes";
                    tcar.serial = "1234";
                    tcar.passengers = new List<MongoDBRef>();
                    for (int p = 0; p < 3; p++)
                    {
                        passenger pass = new passenger();
                        pass.Name = "name" + p.ToString();
                        peopleCollection.Insert(pass);
                        tcar.passengers.Add(new MongoDBRef(peopleCollection.Name, pass.Id));
                    }

                    collection.Insert(tcar);
                 
                    newTrain.trainCars.Add(new MongoDBRef(collection.Name, tcar.Id));
                }

                

                trainCollection.Insert(newTrain);
            }
            Console.WriteLine("saved");
           

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            //get the data
            //asparallel gained me about 15% of qry time
            var query = from v in trainCollection.AsQueryable<train>().AsParallel()
                        select v;

            foreach (train aTrain in query)
            {
                Console.WriteLine("train: " + aTrain.note);
                foreach (trainCar tcar2 in aTrain.GetTraincars(database))
                {
                    if (tcar2 != null)
                    {
                        Console.WriteLine("car: " + tcar2.carNo);
                        foreach (passenger pass in tcar2.GetPassengers(database))
                        {
                            Console.WriteLine("car: " + tcar2.carNo + " pass: " + pass.Name);
                        }
                    }
                }

               
            }
            Console.WriteLine("trains loaded");

            stopWatch.Stop();           
            TimeSpan ts = stopWatch.Elapsed;

            
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);
            Console.WriteLine("RunTime " + elapsedTime);

            

            Console.ReadLine();
        }
    }
}
MongoDB – Linking Records / Documents Using MongoDBRef

Removing WordPress Spam Comments In Bulk

I’ve encountered a few WordPress blogs that had been bombarded with spam comments (some over 200k comments – taking several gigs of db space). Usually these comments accumulated over a few months or years (many using Akismet) – but if you’ve tried to delete thousands of comments through the WordPress admin, you might have noticed it taking very long and timing out.

Does your comments page look like this?
Does your comments page look like this?

 

Having these comments exist in your WordPress site only increases the size of the database, causing backups and migrations / upgrades to take longer. Unless you have plans to review thousands of comments (if so, you probably have too much time on your hands) you should be able to delete these in my opinion. The fastest option to remove the comments that I’ve found is to delete them from the database side (MySql).

Continue reading “Removing WordPress Spam Comments In Bulk”

Removing WordPress Spam Comments In Bulk

Changing RavenDB’s Port From 8080 – Windows Service

A few folks have asked how to change the port of which RavenDB is listening (defaults to 8080). It’s easily changed by editing a config file. Below are the steps if you’re running Raven as a Windows service.

  1. Launch Services and stop the RavenDB service. ravenservice
  2. Locate the “Raven.Server.exe.config” file from your install directory of RavenDB. (mine was in f:\ravendb\)
  3. Open this config file and change this line: <add key=”Raven/Port” value=”8080″/> to whatever available port you’d like (I did 8082)
  4. Save the config file.
  5. Start the service back up.

RavenDB should now be listening on your new port. You can test by going to http://localhost:8082  (or whatever hostname you’re using).

Changing RavenDB’s Port From 8080 – Windows Service

How To Install Redis on Windows and Get Started With C#

Dictionary_IconRedis is a key-value store that is fast, sits in memory and runs on many different platforms. Installing on Windows isn’t straightforward from the Redis site, so below are the steps I use to get going.

1. Download the latest .zip from: https://github.com/mythz/redis-windows/tree/master/downloads

2. Extract these files to a folder (I did f:\redis)

3. In a command prompt, run

redis-server.exe redis.windows.conf

(from your directory above).

Redis should now be running on port 6379. You can change this in redis.windows.conf  if you’d like.

4. Start a new Visual Studio Project (I’m using c# / .net 4.5).

5. In Package Manager Console (NuGet) let’s install the client, run:

Install-Package StackExchange.Redis

6. Include using StackExchange.Redis;

7. Now you can use the below to save and get values:

  //setup your connection
  ConnectionMultiplexer redisConn = ConnectionMultiplexer.Connect("localhost");

 //get your db
 IDatabase redDb = redisConn.GetDatabase();

 //save a key & val
 redDb.StringSet("testKey", "test val");

 //get a key & val
 Console.WriteLine(redDb.StringGet("testKey"));

This is just start of what Redis can do, find more at the official Redis site.

How To Install Redis on Windows and Get Started With C#