It’s quite common where I need to perform an UPDATE statement with SQL but it is not a garden variety single table update statement. Instead I need to join data from another table, whether it is to get data from the second table or use it in my WHERE clause.
Category: Database
-
MSSQL Running an extra AND statement
This had me baffled and shook me to the core as it went against everything I understand about an AND statement. I was using a standard CASE as I have done hundreds, if not thousands, of times that contained two AND statements. The first part of the statement returned false but, MSSQL still proceeded to execute the second statement. This may not seem like a big deal, but in this particular case I had an error producing data type mismatch problem. When the first part of the CASE was true the second part compared the data as a bigint whereas the actual value was an nvarchar, so it errored.
Let’s take a look at an example.
-
Improving the performance of slow Entity Framework queries
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.
-
Delete duplicate rows with MySQL or MSSQL
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:
-
MySQL: OR versus UNION – which is faster?
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:
[code]
SELECT *
FROM `actor2_actors`
WHERE actor1_id =1
OR actor2_id =1
[/code]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: