AWS RDS SQL Server – Using Memory Optimized Objects in your DB

If you’re using Amazon Web Services RDS for SQL Server (btw, it works great) and want to use Memory-Optimized Objects, it’s easy to alter your DB to allow this. Below is a little T-SQL. The key to this is the directory “D:\rdsdbdata\“, the default where RDS is storing your data.

-- Add new file group 
-- Add the new file
ALTER DATABASE [sampleDB] ADD FILE ( NAME = N'Mem_Opt_Data', FILENAME = N'D:\rdsdbdata\DATA\Mem_Opt_Data.ndf') TO FILEGROUP [MOD_FG]

Things that you’ll need to swap out w/ your own info is “sampleDB” and “MOD_FG”.

Now your DB is all ready for some Memory-Optimized Objects.

Want to create a table that’s memory optimized, just use this:

-- Sample table create

These Memory-Optimized Objects do offer a significant speed improvement but don’t go crazy w/ them.

AWS RDS SQL Server – Using Memory Optimized Objects in your DB

Fixing WordPress Error: Maximum execution time of 30 seconds exceeded

If you have a WordPress install that is giving you this error:

Fatal error: Maximum execution time of 30 seconds exceeded in ...\wp-db.php on line ####

…It’s because PHP is timing out. The default timeout in PHP is 30 seconds, see here for details.

The fix:

The method I use for this is simple:

  1. Open your wp-config.php file in an editor (notepad, etc).
  2. Add this line to the top: set_time_limit(300);

The “300” can be any about of time (it’s seconds) you’d like.

The top of your file should like something like this:



PS: You should only need to change this if you’re working on some kind of import /export or a one time process. If your blog requires this to run normally, their might be bad code or a server issue.

Fixing WordPress Error: Maximum execution time of 30 seconds exceeded

Increase IIS Logs to SQL Log Parser Import Performance w/ transactionRowCount

A few years ago I showed how to use Microsoft’s Log Parser tool to take IIS log files and import into a SQL database.

From Microsoft: Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

In short, using something like this to take IIS logs and dump into a new SQL table:

C:\Program Files (x86)\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

But, if you’re importing tons of records, it might seem to take a while. BUT: you can use the option “transactionRowCount” to gain some performance. The transactionRowCount option determines how many rows are included in each transaction. By default, transactionRowCount is 1, so after every row, the transaction is committed. If you set it to “-1” it will include everything in 1 large transaction.

Test Results

Below a did a few tests. My test included 36,000,000 rows. Continue reading “Increase IIS Logs to SQL Log Parser Import Performance w/ transactionRowCount”

Increase IIS Logs to SQL Log Parser Import Performance w/ transactionRowCount

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.]


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)

            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)

            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();
                        tcar.passengers.Add(new MongoDBRef(peopleCollection.Name, pass.Id));

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



            Stopwatch stopWatch = new Stopwatch();

            //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");

            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);


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:

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

3. In a command prompt, run


(from your directory above).

Redis should now be running on port 6379. You can change this in  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

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#