Sunday, November 29, 2009

Parameter Sniffing

Sometimes maybe you find out your SP (Store Procedure) doesn't response in reasonable time despite Select statement, which you used in that SP, has good performance and result is shown you immediately. What's wrong with your SP?
There are different answers for that problem but when I faced on this problem, I realized it's strongly related to Parameter Sniffing.

You can solve Parameter Sniffing easily with follow this example:
If you have a SP such as GetOrderForCustomers that has Parameter Sniffing issue:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
Select * from orders
Where customerid = @CustID

To solve problem you should change your SP similar:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
Declare @LocCustID Varchar (20)
Set @LocCustID = @CustID
Select * from orders
Where customerid = @LocCustID

Good luck.

Wednesday, November 25, 2009

How to Speed up SQL Server Management Studio in SQL Server 2005?

Have you ever launched SQL Server Management Studio (SSMS) and had to wait over 60 seconds before you could even log in? I have - all too often! It's annoying - and thankfully there's an easy fix.
  1. Launch Internet Explorer
  2. Browse to Tools -> Internet options -> Advanced tab
  3. Uncheck the check box under Security called "Check for publisher's certificate revocation"
Now you'll be able to launch SSMS and it will be fast. BTW - this works particularly well for offline workstations/laptops. When you are connected to the internet and SSMS checks for this info (every 15 friggin' seconds) then it's fine - but when you aren't online, it has to wait for a timeout!

Monday, November 23, 2009

Increase your VS compile speed

To increase your VS compile speed:
  • Install Microsoft hotfix 935225.
  • Install Microsoft hotfix 947315.
  • Use a true multicore processor (ie. an Intel Core Duo 2; not a Pentium 4 HT).
  • Use 3 parallel builds. In Visual Studio 2005, you will find the option in Tools > Options... > Projects and Solutions > Build and Run > maximum number of parallel project builds.
  • Defragment your hard drive regularly.
  • Disable virtual memory.

Sunday, November 15, 2009

Visual studio IDE

Visual studio is a pretty awesome IDE, but sometimes you just wish it would go faster.

Here's a list. All of these can be accessed on Tools->Options menu:
  1. Disable F1. (Environment->Keyboard) This is probably the best advice that I found somewhere.
  2. Disable "Animate environment tools" (Environment->General).
  3. Disable Start Page (Environment->Startup).
  4. Disable "Track Active Item in Solution Explorer" (Projects and Solutions).
  5. Disable Navigation Bar (Text Editor->C#). I think this is available for every language.
  6. Set "AutoToolboxPopulate" to false (Windows Forms Designer).
  7. You can set the Code view as the default view when viewing Windows Forms. Just right-click on the cs file and select "Open With...".
  8. Open Visual Studio using the command line (devenv) rather than using the Start menu. I don't know why but I notice it loads faster.
  9. Turn off Track Changes. (Text Editor->Track changes)
Basically I just customized John Lam's settings. It's very minimal.

And also you can use ScottGu's Blog tips.

Tuesday, October 27, 2009

Hi all my friends,
I want to start to writing in my weblog again but I want to write about Programming.
Maybe I'll have some mistake in my sentences such as grammatical mistake, because I'm a beginner in English then I want you to help me to improve and correct my mistakes.

SQL Server Query: Fast with Literal but Slow with Variable

Often when there is a drastic difference from run to run of a query I find that it is often one of 5 issues. STATISTICS  - Statistics a...