Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Monday, March 16, 2015

Troubleshooting SQL index performance on varchar columns @ LosTechies

I was looking today into optimizing queries against a 450M row table that has an index ( not the primary key of course ) which is a string. I was checking around on the internet for some best practices and came across this interesting article:


So as a heads-up: be sure that your string type indexes ( if at all possible ) are type varchar - and that your ORM writes a query that does not require SQL Server to do a data type conversion on the field.

Apparently EntityFunctions.AsNonUnicode() enforces this:


Or - just use the ColumnType attribute on the appropriate column, which keeps things nice and clean:


Monday, March 24, 2014

Entity Framework: type inheritance

We're using EF 6 on our projects, which allowed us to make all calls async. Also, we're using both Table-Per-Type (TPT) and a Table-Per-Hierarchy (TPH) within the datamodel for one of the applications.

In order to get the TPH going in Entity Framework, you need to make use of filtering in de model. It works like a charm and is very powerful. It's perfectly explained here:


And also in this pluralsight course: http://pluralsight.com/training/Courses/TableOfContents/ef-customization

Tuesday, April 30, 2013

Understanding SQL Server statistics

I'm trying to tune a database using the excellent SQL Server Tuning advisor - which I will blog about later. However, one of its recommendations was to create statistics. Honestly, I didn't know what they were, but this post helped me out.

Turned out that SQL Server is on 'create statistics automatically', so it wasn't relevant for the performance, but it's interesting stuff.





Wednesday, April 24, 2013

Difference between NO LOCK and READPAST in SQL Server

I learned the difference today between NO LOCK and READPAST thanks to Tim Chapman. I suspect we have an SSIS package that locks the table for a significant amount of time - and I think we're going to solve the problem using READPAST. Check out Tim's post to read up on the difference between NO LOCK and READPAST.