Helping you solve those tough coding problems!
Learn some great web development and web design tips and techniques through recipes that clearly define the problem being solved with a working solution.
Popular Tags: cakephp | theory | optimization | javascript-development | asp-net | css | jquery-development | sql | ajax | c

Search:
Follow @endyourif rss Facebook LinkedIn

Improving the performance of slow Entity Framework queries Improving the performance of slow Entity Framework queries

Published on Apr 18, 2019 by Jamie Munro

I'm a big fan of Entity Framework. It makes working with databases very convenient. I've discussed previously how I use Entity Framework to implement the repository pattern. Of course with ease of development sometimes sacrifices performance. In today's article I'm going to explain my favorite approach to improve the performance of Entity Framework queries that are slow.

Read more >

Tags: SQL | Optimization | Entity Framework | entity framework

Preventing race conditions with sp_getapplock Preventing race conditions with sp_getapplock

Published on Apr 17, 2019 by Jamie Munro

I am a big proponent of storing calculated fields in the database opposed to calculating them each time they are displayed. They only change at specific times so you just need to cover those spots to calculate them. The danger with storing calculated fields is that concurrent calls have a chance incorrectly calculating the field. Languages such as C# offer the ability to lock objects to help with this. However, in a multi-server example, locking on a single server will not work. In this article I'm going to demonstrate how to use sp_getapplock to prevent race conditions.

Read more >

Tags: SQL | Entity Framework | ASP.NET | mssql | sp_getapplock

Delete duplicate rows with MySQL or MSSQL Delete duplicate rows with MySQL or MSSQL

Published on Feb 27, 2019 by Jamie Munro

The following article will provide a single SQL query that will delete rows that are duplicated in your table. Before you begin, be sure you've tracked down the bad code that is causing this; otherwise, you will need to run it multiple times!

To start, I will create a basic table structure that will help understand the SQL query:

Read more >

Tags: SQL | mysql | delete | mssql

Concatenate Multiple Rows From a Table Into One Field with MySQL Concatenate Multiple Rows From a Table Into One Field with MySQL

Published on Feb 17, 2019 by Jamie Munro

When you want to merge multiple rows from a table into one field separated by a specific character using MySQL.

By utilizing the existing GROUP_CONCAT function with MySQL, you can easily group multiple rows together into a single field result.

Read more >

Tags: SQL | mysql | GROUP_CONCAT

MySQL: OR versus UNION – which is faster? MySQL: OR versus UNION – which is faster?

Published on Mar 24, 2014 by Jamie Munro

I must say, this came as a shock to me, until I looked at the results and thought about it for a minute.  Before jumping in, let me explain how and where I encountered this.  I was recently working on a project called Deja Scene – The actor to actor movie database.  The purpose of this site is to find two actors who have starred in multiple movies together.

Because the nature of the algorithm an actor can be either on the left side of the connection or the right side.

Let’s look at an example, if I search for George Clooney, I will find that he has 372 Deja Scene connections.  Here was the original query I used to retrieve the list of his connections:



SELECT *

FROM  `actor2_actors`

WHERE actor1_id =1

OR actor2_id =1



Seems pretty straight forward; however, I was seeing really slow performance and I couldn’t figure out why.  I created an index on both the actor1_id and the actor2_id, so I assumed Mysql was properly indexing this.  Boy was I wrong.  Look at the results when I use the EXPLAIN function:

Read more >

Tags: SQL | Optimization | performance | mysql

Mysql: Insert or Update in One SQL Statement Mysql: Insert or Update in One SQL Statement

Published on Oct 3, 2012 by Jamie Munro

Regular Expressions with Mysql Regular Expressions with Mysql

Published on Oct 2, 2012 by Jamie Munro

Mysql Uptime Check Script Mysql Uptime Check Script

Published on Oct 1, 2012 by Jamie Munro

Renaming a Database in Mysql Renaming a Database in Mysql

Published on Sep 28, 2012 by Jamie Munro

The Best Way to Prevent SQL Injection The Best Way to Prevent SQL Injection

Published on Sep 19, 2012 by Jamie Munro

The Best Way to Prevent SQL Injection The Best Way to Prevent SQL Injection

Published on Sep 19, 2012 by Jamie Munro

Load Testing Mysql with mysqlslap Load Testing Mysql with mysqlslap

Published on Sep 17, 2012 by Jamie Munro

Update: It Actually Should Be LEPP Not LEMP or LAMP! Update: It Actually Should Be LEPP Not LEMP or LAMP!

Published on Sep 14, 2012 by Jamie Munro

Understanding group bys Understanding group bys

Published on Apr 18, 2009 by Jamie Munro

CakePHP Containable Statement Pitfalls CakePHP Containable Statement Pitfalls

Published on Mar 15, 2009 by Jamie Munro

CakePHP Containable Statement Pitfalls CakePHP Containable Statement Pitfalls

Published on Mar 15, 2009 by Jamie Munro

A practical CASE Statement Example A practical CASE Statement Example

Published on Mar 4, 2009 by Jamie Munro

A practical CASE Statement Example A practical CASE Statement Example

Published on Mar 4, 2009 by Jamie Munro

The Importance of Database Indexing The Importance of Database Indexing

Published on Feb 27, 2009 by Jamie Munro

The Importance of Database Indexing The Importance of Database Indexing

Published on Feb 27, 2009 by Jamie Munro

Dynamic COUNT() Versus Static COUNT() Dynamic COUNT() Versus Static COUNT()

Published on Feb 24, 2009 by Jamie Munro

Dynamic COUNT() Versus Static COUNT() Dynamic COUNT() Versus Static COUNT()

Published on Feb 24, 2009 by Jamie Munro

Why You Should Avoid ORDER BY RAND() OR NEWID() Why You Should Avoid ORDER BY RAND() OR NEWID()

Published on Feb 23, 2009 by Jamie Munro

Why You Should Avoid ORDER BY RAND() OR NEWID() Why You Should Avoid ORDER BY RAND() OR NEWID()

Published on Feb 23, 2009 by Jamie Munro

SQL Tips - Why LEFT JOINS are bad SQL Tips - Why LEFT JOINS are bad

Published on Feb 13, 2009 by Jamie Munro

SQL Tips - Why LEFT JOINS are bad SQL Tips - Why LEFT JOINS are bad

Published on Feb 13, 2009 by Jamie Munro