Angular News App

logo

I have a new Angular 2 Sample.  It reads data from some public feeds – specifically 680 News and Google News, and renders it via angular. Check it out here: Angular News App

Hardcore SQL Perfomance Optimization Tools

While attempting to optimize some stored procedures I was working on, I found an tool that its much better to do performance optimization on SQL.  This has helped me so much and I would gladly recommend this tool to anyone that wants to perform the review quickly and efficently.  This is my second review of a dbForge product, the first one being dbForge Schema Compare.  However, this below tool actually includes features to handle both Schema and Data compare as well, but that will not be the focus of my post.  I mainly am interested in this tool for the purpose of optimizing queries.  Coming from a background of using SSMS for almost 10 years, this blew me away.  The productivity improvements are so gigantic I can’t imagine working without this tool or a similar one.

dbForge Studio For SQL Server.  This tool is far superior in just about every way to SSMS (SQL Server Management Studio) when it comes to this task.  Overall it is a very refined and powerful tool that has a wide variety of uses.  I am going to focus on the query plan analyzer features in this post.

In a nutshell: Get it, now.

First of all, while writing your query, it will provide autocomplete and nicely capitalize your keywords.  I twill also auto generate the join for you and create nice short table aliases.  There are also built in snippets like CreateTable, CreateTempTable, CreateTrigger, etc.  When you mouse over the name of the table in this view, it will give you a popup with the column names and other useful information.

dbforge_autocomplete

Not only that, but you can actually edit the results if you like, directly here!  Compare this to having to open a “Edit top 200 results” window in SSMS where you get that ugly view that is had to write code in (it automatically adds brackets everywhere), to this one:

dbforge_results_editable

Talk about a breath of fresh air!  These two features alone make the whole process of working so much smoother and give you a much cleaner SQL result.  Beats having to paste your SQL here!  Also, if you have existing SQL, you can simply paste it and then right-click and pick “format code”.

Next, let us look at the query plan view:

dbforge_perf1

In this plan, we can see a visual display of the query plan with top operations highlighted in red to green by cost. You can see that its a nice clean display and shows you the information you need.  It allows you to visualize the query execution plan and pinpoint the issues.

There are several other screens in the “Profiler” mode which I will share with you:

In the next image, we can see the amount of CPU, I/O, and Operator / Subtree cost for each item in the plan:

dbforge_perf2

Here is a slightly more complex one below:

dbforge_perf3

If you have multiple executions, you can highlight two of them and see them compared:

dbforge_perf4

Comparing multiple query plans visually is quite a nice feature.  It allows you to take a look at both of them, with the Cost highlighted above.  If you run two queries in SSMS, you will also see the percent of time that each one took, but it doesnt allow you to stack them above each other like shown here.

In the following image, we can view and compare statistics on execution CPU, Memory, Disk.

dbforge_perf5

In the image above, you can see the difference in the number of rows, compile memory, compile time, cached plan size, etc.  You can also see the common options and what you have them set to.  It’s very handy that it colours them in red and green to quickly show you where you have improved.  Trying to do this in SSMS would take a lot longer and would lengthen the code->test->confirm cycle that optimizers do.

In the next image you can see the a different aspect of the results compared side by side in regards to actual execution time and CPU, and read/scan count on each of the tables.  This is very important as it will also tell you the cost of missing indexes.

dbforge_perf6

 

For all of these, you can rename the result name and give it a more meaningful name than “3:45:00 PM 7/1/2015”.

Other features which I have not got a chance to try yet but look very promising:

  • code analysis rules – look for errors and warnings.  You can use this to enforce certain standards in your code quality across the team if you like.
  • table designer – far superior table designer to SSMS.  You can switch back and forth from the visual editor to the T-SQL editor.  You can also preview ALTER table script and rebuild the table without losing data.
  • database diagram editor – Visualize database structure, track relations, print out diagrams, and edit objects directly. choose the syntax you like (IE or IDEF1X).  You can create logical contaniners to group tables together.  In the print dialog it gives some handy options such as displaying certian markup above the tables.    Also far far better than SSMS.
  • schema compare and sync – compare two servers and create a delta script based on the changes.  You can either execute or save the script it generates (patch script).  The sync scrypt can also include making  a backup first.
  • data compare – like schema compare, but you can actually check the data based on its key.  This can be helpful for verifying proper deployment was done.
  • t-sql debugger – similar to SSMS, you can step through your code line by line.
  • data export/import – from csv, excel, access, xml, etc.
  • data generator – to create test data, you can fill up tables with a bunch of values say dates, text, numbers, etc.
  • event profiler – profile events such as query locks and blocks for use in diagnosing issues or improving performance.  Profiling events does not cause as much load as running a full SQL Server profiler.
  • master/detail browser (i.e. Class and Students)
  • pivot table (visualizer)
  • create simple data reports

Overall, with a price that is very reasonable for the quality of product, $499 for the Professional edition, along with competitive discounts, I would highly recommend this product.  If you are not interested in some of the features such as Data Compare, Schema Compare,  Reporting, etc, you can get a more basic version that will still do the job and not hurt your bottom line.

Disclaimer: I will receive a free license for writing this review.  Note, license or not, I would still 100% stand behind this product, especially because I used the trial and loved it, and the prices are very reasonable for the quality of product you receive.

Here it is again: DBForge studio for SQL Server (https://www.devart.com/dbforge/sql/studio/)

 

dbForge Schema Compare for SQL Server Review

I have recently tried dbForge Schema Compare for SQL Server .   As the name says, obviously, this tool is used to compare db schemas.  Although this is now built into Visual Studio, the fact is that specialized tools are still often much better.    Out of the multiple products I used, I found this product to be the best bang for the buck.  And they have some promotional discounts you can apply such as upgrading from a competing product, etc.

This application is quite nice.  First of all, it is very competitively priced so if you are on a tight budget you will love the amount of features you get for the price.  For $150 this comes up to half the price of some of the other competing products.

You can compare scripts, databases, or db snapshots.

It allows you to filter by schema (for example only dbo.*) and to map by schema (ie from source to destination).

Then it allows you to pick what type of changes are important to you.  For example

  • ignore case
  • ignore comments
  • ignore QUOTED IDENTIFIER adn ANSI_NULLS
  • ignore white space
  • ignore WITH option order
  • ignore signatures

dbforge2

The next step allows you to filter by Object type.  It even groups object type by SQL Server Instance (i.e. Tables,Views, etc. for SQL SERVER 2000+,  Partition Functions, Assemblies, etc for 2005+ and so on) – screenshot below only shows upto 2005, but 2008 objects are supported as well.
dbforge3

Here is the list:

  • Application Role
  • Assembly
  • Asymmetric Key
  • Broker Priority
  • Certificate
  • Contract
  • DDL Trigger
  • Default
  • Event Notification
  • Full Text Catalog
  • Full Text Stoplist
  • Function
  • Message Type
  • Partition Function
  • Partition Scheme
  • Procedure
  • Queue
  • Remote Service Binding
  • Role
  • Route
  • Rule
  • Schema
  • Search Property List
  • Sequence
  • Service
  • Symmetric Key
  • Synonym
  • Table
  • User
  • User Defined Type
  • View
  • XML Schema Collection

The next step will run the compare, and show you the DIFFERENT, and the EQUAL changes and let you choose what you like.

The final step you can execute or save the script for later.  It will include options for backing up your database first.  It also allows you to put the entire thing in a transaction to make sure no partial changes go through.

The summary page is also really nice.

Price?
Standard edition is $149.95

Pro edition is $229.95

Trial is available for 30 days.

If you want a more rudimentary quick and dirty free tool, check out SQL Admin Studio

The complementary tool is dbForge Data Compare for SQL Server, which I will review in another post.

Programmer Competency Matrix

This is a very well written matrix of programming skills required to be at the top of your game.

Here’s the first table

Computer Science
2n (Level 0) n2 (Level 1) n (Level 2) log(n) (Level 3)
data structures Doesn’t know the difference between Array and LinkedList Able to explain and use Arrays, LinkedLists, Dictionaries etc in practical programming tasks Knows space and time tradeoffs of the basic data structures, Arrays vs LinkedLists, Able to explain how hashtables can be implemented and can handle collisions, Priority queues and ways to implement them etc. Knowledge of advanced data structures like B-trees, binomial and fibonacci heaps, AVL/Red Black trees, Splay Trees, Skip Lists, tries etc.
algorithms Unable to find the average of numbers in an array (It’s hard to believe but I’ve interviewed such candidates) Basic sorting, searching and data structure traversal and retrieval algorithms Tree, Graph, simple greedy and divide and conquer algorithms, is able to understand the relevance of the levels of this matrix. Able to recognize and code dynamic programming solutions, good knowledge of graph algorithms, good knowledge of numerical computation algorithms, able to identify NP problems etc.
systems programming Doesn’t know what a compiler, linker or interpreter is Basic understanding of compilers, linker and interpreters. Understands what assembly code is and how things work at the hardware level. Some knowledge of virtual memory and paging. Understands kernel mode vs. user mode, multi-threading, synchronization primitives and how they’re implemented, able to read assembly code. Understands how networks work, understanding of network protocols and socket level programming. Understands the entire programming stack, hardware (CPU + Memory + Cache + Interrupts + microcode), binary code, assembly, static and dynamic linking, compilation, interpretation, JIT compilation, garbage collection, heap, stack, memory addressing…

 

Take a look here for the rest of it.

Only one [FromBody] parameter allowed with Web API

Please see Encosia’s article on Using jQuery to POST [FromBody] parameters to Web API.

In summary, create a view object with all the properties you want and post it that way.

Screen Calipers

Sometimes you need to measure pixels on the screen. Screen Calipers is a great way to do it. I use version 1.0 as its free of charge and does the job.

Iconico Screen Calipers (download v1.0)

Excellent Color Picker and Sampler

Color Selector 3.0 is an amazing and free, open source tool that allows you to sample colors and gives you the data in a variety of formats. Did I mention its open source?

Color Selector 3

CSS Resets

A handy trick that makes the browsers more consistent – CSS Resets

Async Call Stack in Chrome

If you are working with asynchronous javascript calls, you might want to TURN THIS ON – NOW!

How to turn on Async Call Stack in Google Chrome. This makes it much easier to debug async calls as you will be able to see where the call came from.

Self Executing Anonymous Functions

This is an essential skill when you want to limit scope in JavaScript.

Take a look at this excellent read on Self-Executing Anonymous Functions. It allows you to create “private” variables and functions in JavaScript. Really cool.

Optimization WordPress Plugins & Solutions by W3 EDGE