How easy or difficult to write dynamic SQL ? TIP#110

This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation.

This seems very easy but we need to cast the  parameters in VARCHAR and sometimes we stuck in single code.

I am sure this happened with all of us. 

Let’s understand first a straight forward way which we (most of  us) are  using.

In example I am using person table of Adventureworks database and it is just a simple query  which provide person row according to primary key.

Dynamic_Exec

Now the above query is OK but it can be write in much better way with one of the SQL server in build stored procedure which is sp_executeSQL. This is one of the best way which have certain advantage which will discuss in next tip. Now see how we can write above query in much better way

sp_execute_SQL

DECLARE @BusinessEntityID   INT
DECLARE @DynamicSQL NVARCHAR(200)
DECLARE @Parameters NVARCHAR(100)

SET @BusinessEntityID = 1
SET @Parameters =N’@ParameterBusinessEntityID INT’

SET @DynamicSQL = N’SELECT * FROM [Person].[Person] WHERE BusinessEntityId = @ParameterBusinessEntityID ‘
EXECUTE sp_executesql @DynamicSQL, @Parameters,  @ParameterBusinessEntityID =@BusinessEntityID

If you see above query, you will find we are not using any type conversion the statement is clear.

This is one of the best way to write dynamic query which is not only increase your statement’s readability but also increase performance of your query with certain amount.

Which we will discuss in next tip.

I hope now you are eager to use this and replace all your old fashion dynamic queries.

Please do post your feedback.

Enjoy !!!

RJ !!!

Performance tips- How to clean buffer to test performance TIP#28

 

Dear Friends,

To test the performance of a stored procedure we run the stored procedure several time.

You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached.

Now , due to this cache problem we got a wrong assumption that the stored procedure is fast but in actual it it taking cache data.

To assure that when we run the stored procedure it will run on new set of data not from buffer data SQL Server provided a option which is called “DROPCLEANBUFFER”

You can run this command as shown in below fig

DROP_CLAN_BUFFER

Either way you can restart SQL Server services also Smile .

It is one of the effective command by which we can evaluate of stored procedure performance.

Enjoy !!

Thanks

Rajat Jaiswal

How to Improve query by reducing CXPACKET WAIT type with simple option?

 

Dear All,

I am sure you have faced this problem that when you running query like Aggregation , grouping etc then due to default setting the query uses all the CPU available to machine and this case is parallelism.

Now you are thinking its very good your query will be faster if it distributed amount the multiple processor.

but sometime this is not the case due to this distribution suppose one process is processing slowly then  overall your combine result have to wait this wait is “CXPACKET WAIT”

Now you are wondering how to handle this. so no worries Microsoft provided option to tweak this setting with MAXDOP (Maximum degree of Parallelism ) option.

You can change overall SQL Server setting or for particular query.

see below screen from where you can change MAXDOP settings.

MAXDOP

Now the default value is “0” . It means can use all the CPU.

Now this is not we generally prefer instead of this we can use MAXDOP option in query option.

But before doing this you just need to cross check whether you require it or not ?

if your CXPACKET wait type  if too much then you can use this option

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
           se.StudentId      
       FROM dbo.StudentEnrollment se WITH (NOLOCK)       
       INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                             AND s. YearId = @YearID
       WHERE sh.Statusid = @StatusActivateId 
       GROUP BY sh.StudentID
    OPTION (MAXDOP 1)

in the above query we used MAXDOP 1 which means query use only once CPU.

With this option the CXPACKET will reduce but it may be possible your query may take time so be careful.

But this is good option. It helped me to reduce the time.

Lets try if this can help you.

Enjoy  learning 🙂

Thanks & Best Regards,

Rajat Jaiswal

Improve Query Performance by just a small change “OPTIMZE FOR”

 

Dear All,

If your SQL statement is slow then sometimes just a little change help you a lot in my case it was helpful hope it will helpful in your problem also.

Suppose you want to fetch  data from two large tables  for a specific constant value. Let take example of this

suppose you have two table 1. Student table 2. student enrollment table both table have large amount of data.

Now you want to fetch max record of enrollment id student wise for  particular status in Student enrollment  table.

Then for this you need to write following query .

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
          se.StudentId      
      FROM dbo.StudentEnrollment se WITH (NOLOCK)       
      INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                            AND s. YearId = @YearID
      WHERE sh.Statusid = @StatusActivateId 
      GROUP BY sh.serializationid

Now if you find here the StatusActivateId  is fixed in this case so  we need to optimize this statement   here we used optimize for option 

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
           se.StudentId      
       FROM dbo.StudentEnrollment se WITH (NOLOCK)       
       INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                             AND s. YearId = @YearID
       WHERE sh.Statusid = @StatusActivateId 
       GROUP BY sh.serializationid
     OPTION (OPTIMIZE FOR (@StatusActivateId =21))

if you  compare above statements you will find there is only one difference in last line which is option (OPTIMIZE FOR)

As i shared earlier we know the fixed value so we explicitly added the variable name and value for which we need to optimization for the query.

In SQL Server 2008  this option ins more robust which taking any unknown option.

I hope it will help you some where . Just hit and try.

Learn & Share

Thanks

Rajat Jaiswal

LINQ tip to use proper for loop

Dear Friends,

As a programmer I would like to share some of the best tips which I found so far and this is one of the tip which I am going to share right away

suppose you have following  piece of code

string[] Names= namecollection.Split(GetDelimeter());

           List<string> NameList = new List<string>();
     

foreach (string name in Names)
          {
              if (!string.IsNullOrEmpty(name ))
              {
                  sb.Append(name .Trim() + ",");
                  NameList .Add(name .Trim());
              }
          }

Now you can write above  for each code  & if condition in following way

foreach (string Nam in Names.Where(Nam=> !string.IsNullOrEmpty(Nam)))
           {
               sb.Append(Nam .Trim() + ",");
             NameList .Add(Nam.Trim());
           }

It is much faster then a earlier code which shown

Hope you like this. Enjoy  Reading

Regards,

Rajat Jaiswal

How to boost performance of my ASP.NET Website ? Part -II

Dear Friends,
As we have shared earlier how to optimize image (part1) to improve site performance to continue with that topic. I have going to share one more useful topic how to minimize the CSS & JS used in your project.
If you remember Microsoft Ajax minifier (AjaxMinifier) which I have shared earlier in my post we can utilize that tool to

minimize size of the JS & CSS.

So earlier I have explained how to use command prompt to minimize the CSS & JS.
Today I am going to share how to minimize the all the JS & CSS in your project while doing rebuild it.
So here we go just follow below steps.
1)    Go to web solution and unload it  as show in below fig

Boost perfromance of website
2)    Once you have unload the project you will get below screen

Edit project file
3)    Edit the unload project file

Add minifier in project file
4)    Your file will be open in  IDE now add the below lines in project files

<Import Project="$(MSBuildExtensionsPath)\Microsoft\MicrosoftAjax\ajaxmin.tasks" />
<Target Name="AfterBuild">
<ItemGroup>
<JS Include="**\*.js" Exclude="**\*.min.js;" />
</ItemGroup>
<ItemGroup>
<CSS Include="**\*.css" Exclude="**\*.min.css" />
</ItemGroup>
<AjaxMin JsSourceFiles="@(JS)" JsSourceExtensionPattern="\.js$" JsTargetExtension=".min.js" CssSourceFiles="@(CSS)" CssSourceExtensionPattern="\.css$" CssTargetExtension=".min.css" />
</Target>


5)    Now reload the project and build the project. Now refresh the solution and click on show all files. You will some extra files for JavaScript (JS) & CSS file which have same name  and “min” word added in the file.
Like suppose earlier you have Site.css then now you will find site.min.css also.

minimized css & JS

So it is clear now.

Now we can use this compress “minimized” CSS or JS file.
In this way we can use minimized CSS & JS improve the website performance.

So enjoy the New Year with fast performance of the site.
Happy New Year
Thanks & Best Regards,
Rajat Jaiswal

How to boost performance of my ASP.NET Website ?

Dear Friends,

Why my website is slow?

I know this question come in your mind several times.  So I will try to help in boosting performance of your website with sharing some of the utility which helped me hope it will help you also.
As a developer I know the first basic problem is image optimization. So let us fix the image optimization issue first.  To resolve we are going to use image optimization extension.

To download it goes to Extension manager (Tool –> Extension Manager) and search image optimization. You will get following options as shown in below screen

ImageOptimizationExt

Now install the image optimization. It will ask to restart the visual studio. Now just right click the image folder of the project.  You will get image optimization menu as shown in below fig.

optimizationOption

When you run it you will find that the image in that particular folder is optimized and as a output you will get following screen.

output_of_Image_Optimization

As we already know if the size of the page content over the network is less then it will render fast on client machine. So image optimization help in reducing the network size.
I hope you like this post and it may helpful to you either today or may be in future.
I am going to share some more tips related this topic so stay connected
Enjoy.
Thanks & Best Regards,
Rajat jaiswal

How to resolve “AJAXControlToolKit required Ajax 4.0 script.”

Hello friends,

Recently my college got problem in AJAX file upload control which is “AJAXControlToolKit required Ajax4.0 script

see the error detail in below snap.

 So the reason behind this he is using AJAX script manager not AJAX toolkit script manager. Now you have question when to use script manager and when to use AJAX toolkit script manager so my suggestion is always use AJAX toolkit script manager because it is faster than script manager in many ways and if you try to find difference between AJAX script Manager & AJAX Toolkit script manager then you will find AJAX toolkit script manager is best option for enhancement of your web app performance also.

(If you have not downloaded AJAX toolkit Script Manager then you can download latest  AJAX toolkit and install on your machine)

 So Enjoy weekend.

 Happy Makar sakranti (Indian New Year)

Thanks & Best Regards,

 Rajat Jaiswal

How to check your SQL Server Performance using new Utility Performance Dashboard Reports ?

Hello friends,
I know after reading the post title you are very excited to know more, even I was excited too to know more for this topic which I like very much.
Microsoft Introduced Performance Dashboard Utility reports which help us in finding the cause of low performance of our SQL Server Database.
You can download the performance Dashboard utility reports setup from below link
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc
When you install this   installer you will find a new folder is created with Performance Dashboard name in tools folder of Microsoft SQL SERVER (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)
Now when you open this folder you will get many RDL files.
Next question is how to use it so here we go.
1) Firstly run the script  with name “setup.sql “ from Performance Dashboard folder (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)


2) Now  open our SQL Server Management studio
3) Open object explorer
4) Right click on the server select reports option  then select standard reports  as shown in below fig


5) Now select Performance_dashboard _main.rdl report
6) When you select this report you will find following screen

Great it means you are able to see performance dashboard report.
The performance dashboard report gives you following result
1) First graph of report shows System CPU utilization (With the help of this you can find how much CPU utilization done by SQL)
2) Just Below the CPU utilization graph you can see current Activity (CPU consumption time, wait time) We can see detail with two parameter either by User Session or User request (When you click on the header of the grid  you will get different report)
3) Below this we have miscellaneous information which will give basic information like number of database currently on server, active trace etc.
4) Waiting log just right side of CPU utilization  graph which shows different wait type for SQL
5) Below that one more tabular grid there which shows historical records of SQL according to different criteria like waits, IO Statics
6) Expensive SQL report with different parameters like BY CPU, BY Duration, BY Logical Reads, BY Logical Writes etc.
Checks out there are many more feature reports.
So with these different reports we can find where we are lacking. Where is the problem?

My personal feeling is the utility is good to finding the performance counter but it will not provide any suggestion and solution to improve performance.
So why are you waiting?
Just download and enjoy the performance dashboard.
Have fun with reports in weekends: D.
Thanks & Regards
Rajat Jaiswal

Nice utilities for Web Developer

Hello friends,

Today I come up with some useful list which help you in web development and good news is that its all free.
1) “FIREBUG” :-
Basically “FIREBUG” is add in for FireFox. It’s a great utility helps a lot to design web pages. As a developer I am mostly intrested in programming not designing.But this utility help me in solving designing issues. With the help of it you can manage designing ,CSS issues.
You can download this from https://addons.mozilla.org/en-US/firefox/addon/1843
2) Web Development Helper:-
This one is another useful utitly for web developer to improve performance. Its basically help you to determine which page taking how much time to response.It also help other area also like script finding. It has dom Inspector also. You can take help of this tool in various way.
You can download this from http://projects.nikhilk.net/WebDevHelper
3) Deep Zoom composer:- If you are working on Silverlight also then you can use Deep Zoom composer.it has very nice feature for images.
You can dowload this from http://www.microsoft.com/downloads/details.aspx?familyid=457b17b7-52bf-4bda-87a3-fa8a4673f8bf&displaylang=en

So just dowload & use this tools and improve your code.

Happy programming!

Thanks …
Your host
Rajat