Great Ideas. Always Flowing.

We are not happy until you are happy. Client satisfaction guaranteed. Whatever your needs and requirements, we have the skills and resources for the job!

Recently Viewed...

SnowCovered Top Sellers

Frustrated over the lack of customization for your user's registration fields? Dynamically setup your DNN Portal with custom registration fields, layout, questions, and other core integration options......

Ultra Video Gallery is a brother product of Ultra Media Gallery, UVG allows you to upload videos in various format and automatically encode them to flv or H264 format, you also can add videos from internet or record live videos from your webcam.

Build high performance, completely customizable data-entry forms and views driven by your DNN and external databases. New built-in tools make it a snap to quickly create data entry forms, data views, and even database tables. Plus, add your own HTML, CSS, Javascript, SQL commands, stored procedures,

The most advanced DotNetNuke shopping cart on the planet. Easy to use e-Commerce, Secure Shopping Cart Software and SEO friendly. B2C / B2B Ecommerce Sites.

One stop solution for events calendar and events registration! FREE DOWNLOAD is available now!

Popular Tags

Tags

SQL Server Expressions- Key SQL Performance Situations to Watch Out For

Key SQL Performance Situations to Watch Out For - Part Two of Three

 

View Part One - SQL Server Blocking

 View Part Three - Full Table Scans

 

 

Expression = Expression

a2 b2 = c2.  Anyone remember what this is?  Pythagorean Theorem anyone?  It clearly states that in any right triangle, the square on the hypotenuse is equal to the sum of the squares on the other two sides.  So what?  What does it have to do with SQL Server or performance?  Look closely at the formula.  Let’s consider the letter “c” in the equation as a column in a table used in a where clause of a select statement. There is a component on the left side of the equal sign that requires calculation.  There is also a component on the right side of the equal sign that also requires a calculation.  What does this mean to performance in SQL Server?

 

When SQL Server has to perform calculations on both sides of a comparison operator within a Where clause, then this criteria cannot be considered when evaluating the usage of indexes.  SQL Server must try to use other criteria within the query to figure out which indexes to use, and if there are none, the query processor will scan all of the rows in the table, evaluating these expressions one row at a time, in order to determine which rows to process or return.

Let’s consider the following change to the Pythagorean Theorem formula. 

 

 c = \sqrt{a^2   b^2}. \, 

 

Notice that by using some simple algebra to solve for c, now we have a different scenario.  On the left side of the equal sign we now have a column with no calculations performed on it, while on the right side we have an expression.  The query optimizer can now consider indexes that include the “c” column, since it can evaluate the expression at compile time and not at run-time.

Here are some examples of poorly performing SQL statements that research shows to occur a great deal in applications.

 

SELECT A.*

FROM Customer as A

WHERE A.City ', ' A.State = @SearchField ;

 

SELECT A.*

FROM Customer as A

WHERE A.FirstName ' ' A.LastName  = @SearchField ;

 

SELECT A.*

FROM OrderDetail as A

INNER JOIN Product As B on (A.ProductID = B.ProductID)

WHERE (A.Qty * B.ItemPrice) >= 100.00 ;

 

In two of the three example queries, the SQL could be adjusted to solve for a column on one side of the comparison and have the potential to be evaluated by indexes if they are available.  There are expressions on both sides of the comparison, however, so these will perform full scans of the tables involved to find the criteria in question.

 

Here are examples of changes that could be made to these queries to allow them to work more optimally, should indexes be present on the columns involved.

 

SELECT A.*

FROM Customer as A

WHERE A.City  = @SearchFieldCity

AND   A.State = @SearchFieldState ;

 

SELECT A.*

FROM Customer as A

WHERE A.FirstName = @SearchFieldFirstName

AND   A.LastName  = @SearchFieldLastName ;

 

Why isn’t the third query able to be optimized in this way?  In the first two examples, the expression on the right of the comparison operator can be resolved at compile time.  With the third query, no matter how you manipulate the formula, there will be elements on both sides of the comparison operator that cannot be known until the query is executed.  Consider the following two examples of variations of the third query.

 

SELECT A.*

FROM OrderDetail as A

INNER JOIN Product As B on (A.ProductID = B.ProductID)

WHERE  A.Qty  >= 100.00 / B.ItemPrice ;

 

SELECT A.*

FROM OrderDetail as A

INNER JOIN Product As B on (A.ProductID = B.ProductID)

WHERE  B.ItemPrice >= 100.00 / A.Qty ;

 

As you can see, no matter how it is manipulated, there is a column on both sides of the equation and one of them must be involved with the calculation. 

Here is another example that includes the query plan of each case.

 

SELECT COUNT(*)

FROM dbo.TransactionTable1 AS A

WHERE  A.IntFlag 1 = 3;

 

 

Notice how the first thing that happens (you read these from right to left) is an Index Scan.  This is looping through an index, but it has to look at every node of the index to process the data for the query because of the way the WHERE clause is written with an Expression = Expression.  On the test machine, with the test example database, this yielded a cost of 2.78.  However, by making the subtle change below, the cost was reduced to 0.0032 on the same machine in the same database.

 

SELECT COUNT(*)

FROM dbo.TransactionTable1 AS A

WHERE  A.IntFlag = 3 – 1;

 

 

Notice how the first thing this query does (again, read from right to left) is an Index Seek.  This means that it can actually use the index as intended instead of having to scan through all of the data.  Also take note that the next activity processed by the query, Stream Aggregate, is drastically reduced in cost between the first query and the second query.  This is very common.  The estimated cost for the other activities of the query will go down when altering queries to quit scanning because the query optimizer tries to guess at how many rows will be processed by each step.  When an index is used, the query optimizer can use statistics about the data in the index to more accurately guess at how many rows will be processed.  There are cases however, as in the case of Filter tasks, or Bookmark Lookup tasks where the actual physical time will be saved performing these steps when and index is used.  This is because these types of activities must actually be performed for each row that is returned by the data gathering steps (Scans and Seeks) and therefore the performance of the query overall would be improved if they didn’t have to perform their activities on every row and instead only have to perform them on the necessary rows found by the Seek.

 

 

So what does all of this mean?  To summarize, the goal is to avoid the processing of every single row of a table when returning rows is a desirable thing.  However, having expressions on both sides of the comparison operator can cause this very thing to happen.  To optimize these situations, look for areas where simple algebra can be used to solve for one of the columns or where multiple parameters can be used instead of comparing a value to a calculation of columns.

 

 

View previous article on Performance Situations to Watch Out for  - SQL Server Blocking.

 

View next article on Performance Situations to Watch Out for  - SQL  Server Expressions.

 

 

 

Feedback Comments

Feedback

SharePoint Web Parts


All Data Springs Web Parts Support WSS 3.0, SharePoint 2007, and SharePoint 2010 Frameworks

Please select license option for each web part you wish to purchase. We highly recommend the SharePoint Bundle to get all Data Springs Web Parts for maximum value!

 

 

      
Cart


Data Springs Sharepoint Bundle

Best Value! The Bundle gives you all 5 web parts in one package at a very attractive price! Best Value! We think you will be very happy with the SharePoint bundle and great price discounts you will receive. With your purchase all of the web parts below will be included.
 
 
 
 

Random Image Web Part

With Random Image for Sharepoint 2007, you can select multiple images to display randomly when the web part loads...
 
 
 
 

Stock Quote Web Part

Giving your site visitors relevant information is critical. With the Data Springs Stock Web Part you can provide your users with up to date financial information
 
 
 
 

Dynamic Image Rotator Web Part

Who would have thought? Adobe Flash® with Sharepoint! The FIRST and ONLY image rotation web part for Sharepoint using Flash Technology from Adobe! The Dynamic Image Rotator displays selected images and then rotates between the images. Several extended and optional features allow you to select the time to rotate each image, fade between
 
 
 
 

SharePoint Charts Web Part

The MOSS Chart Web Part is a web part built by Data Springs for the purpose of rendering several chart types based on data from a SharePoint list on a MOSS 2007 or WSS 3.0 Site
 
 
 
 

Dynamic News Ticker Web Part

Provide current news items with a user-friendly news ticker for your Sharepoint Portal. With millions of web sites offering information you need a fun way to display information and the solution is Flash News Ticker....
 
 
 
 

Tailored Text Web Part

 Tailored Text Web Part allows you to add text/html to your web site that can be different for anonymous users, registered users,  and even individual users specifically.

 
 
 
 

Dynamic Views Web Part

Dynamic Views is an excellent tool to:
Personalization allows you to go the extra mile in communicating or connecting one to one with your clients. When it comes to technology and web site content, you now have the power to leverage this personalization directly with your users on your DotNetNuke® site

 
 
 
 

Dynamic Login Web Part

Your site content isn't vanilla, so why is your portal's login?

Add pizazz and functionality with Dynamic Login! Use custom templates, localization, redirection rules for various roles and many more features!
 
 
 
 


 
 

Join our mailing list...

Get current news and events the easy way
 
 
   
Subscribe Me

Recent Blogs...

 
Copyright 2005 - 2011 by Data Springs, Inc.