Using SQL Server for Latitude & Longitude Calculations

If you’re like me, you’ve probably stored latitude and longitude data in decimal columns for a long time. I know SQL Server has a geography type as of a few years ago, but I never really played with it much. Anyway, I finally tried it, and so far it’s great – and no individual decimal columns – and I can do calculations right in SQL.

To get started, use the geography type in a table like this?:

CREATE TABLE [dbo].[MattressStoreLocations](	
[Mattress Store Name] [nvarchar](50) NULL,
[Location] [geography] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Continue reading “Using SQL Server for Latitude & Longitude Calculations”
Using SQL Server for Latitude & Longitude Calculations

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

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

SQL Server / Renaming Databases With Active Connections

hello-my-name-isI believe there are many methods and opinions that exist on the best practice to rename a SQL Server DB. Below I’ll quickly give you the method I use to rename a database. In the below example, I rename two DB’s – a common scenario if you’re swapping your production database with a different version:

-- #1 change first db name
use master
-- set db to single user
ALTER DATABASE myProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myProdDB MODIFY NAME = [myOldProdDB]
-- set back to multi user
ALTER DATABASE myOldProdDB SET MULTI_USER

-- #2 change second db name
use master
-- set db to single user
ALTER DATABASE myNewProdDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
-- do the rename
ALTER DATABASE myNewProdDB MODIFY NAME = [myProdDB]
-- set back to multi user
ALTER DATABASE myProdDB SET MULTI_USER

Note: this does not change the database or log file names, just the database name.

SQL Server / Renaming Databases With Active Connections

RavenDB Document Database – Super Quickstart Guide

Raven-info0RavenDB is among the many available document databases on the market today (mongo, redis, etc.). If you’re looking to try this db and see if it’s right for your project, below is a super quick guide to start using using RavenDB on Windows with asp.net. Below are my 10 simple steps to start using. You can skip to the bottom for entire “source” code.  Continue reading “RavenDB Document Database – Super Quickstart Guide”

RavenDB Document Database – Super Quickstart Guide

c# DataTable Copy / Filter / Sort & Clone

datatableIf you’re like me (I hope not too much like me) you probably find yourself using DataTables to hold lots of data for fast, flexible in memory usage. I get asked often, “how can I copy a DataTable?” or even more often, “how can I copy a DataTable, but change the sort or modify the rows”. Look no further. Below you’ll find my thoughts:

 

Example one (a simple copy with a sort difference):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();
    secondTable = table.Copy();
    secondTable.DefaultView.Sort = "someOtherColumn";

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();

Example two (creating a copy of the table but allowing a filter, sort or other criteria):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();

    //clone to get columns - NO data is copied
    secondTable = table.Clone();
    //loop through rows and import based on filter
    foreach (DataRow dr in table.Select("someColumn = 'value'","someColumnToSort")) {

    secondTable.ImportRow(dr);

    }

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();
c# DataTable Copy / Filter / Sort & Clone