Essential viewing for web developers

Every now and again I come across some amazing videos on YouTube that I think are relevant to all web developers. One author (video blogger? – not sure what they are called) who is particularly brilliant is Tom Scott. He wonderfully articulates how web technologies and their exploits happen, and how to ensure that you don’t fall in to the same traps as many systems/sites out there.

One of Tom’s most brilliant videos is his explanation of how a self-re-tweeting tweet worked and how to ensure that you don’t have the same issues on your text boxes when writing their contents back out to the screen.

Essential viewing videos for development teams:

Tom Scott – Cracking Websites with Cross Site Scripting – Computerphile

Tom Scott – Hacking Websites with SQL Injection – Computerphile

Dr Mike Pound – Advanced SQL Injection

Tom Scott – Cross Site Request Forgery – Computerphile

Tom Scott – How Not To Store Passwords

Tom Scott – Hashing Algorithms and Security

 

If you are a developer, and have any suggestions of videos that should be added to this list, please add a comment with the URL!

SQL Server check table fragmentation level

If you are using SQL Server, and discover that what should be a simple select query, or particularly multiple select queries that are unioned together with a sort are running slowly, then the issue is your table indexes. This is particularly the case when using Date and DateTime fields for sorting.

If you are trying to sort a table or multiple tables by Date, then it is a good idea to add an index on the Date column, it makes things significantly faster.

Here is the SQL Query to check all tables in your database and the levels of fragmentation of each:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

SQL Server – Increasing query performance for large reports

Its always a balance when creating reports, you want to produce an amazing report, but you dont want to cripple your server and make it unresponsive for other users whilst it is produced.

After a lot of research, we found that adding:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

as the first line in a stored procedure greatly improves performance for other users as the stored procedure then doesn’t lock tables for reading when it is producing the report.

increase-chart-11As a real-world example, we wrote a system that could produce an annual statistical report that had to go through 1.1million records 18 times per row, and for around 40,000 rows, so a staggering 792 billion reads on the database. We optimised the query, but it still took around 40 seconds to produce, and during that time all the CPUs in the database server were at 100% and disk IO went through the roof, and the system performance suffered dramatically. Other users were left with the system appearing to hang for up to a minute whilst one user created the report.

By adding the transaction level statement we not only decreased the query time to under 30 seconds, but it also allowed all other users to continue to use the system and not notice any performance hang in the system as their queries were still be executed as needed since the tables were not locked.

SQL Server Distinct – Make it Case Sensitive

By default SQL Server DISTINCT is NOT case sensitive. This means that if you had the following data in a table:

FirstNames
Richard
Adam
James
Adam
Ryan
RICHARD
Richard

and queried this table with “SELECT DISTINCT FirstNames FROM table” you would get:

FirstNames
Richard
Adam
James
Ryan

However, there are occasions where you want the case taken into account. Change the query to include a COLLATE function with case sensitive (notice the cs) and it then does this.

SELECT DISTINCT FirstNames COLLATE sql_latin1_general_cp1_cs_as As [FirstNames] FROM table

now returns:

FirstNames
Richard
Adam
James
Ryan
RICHARD

 

Its really handy to know and remember, especially if you are getting errors when trying to select a value from a list and the distinct name isn’t there because the exact same text is in the field with a slightly different case.