Tips for increase performance of SQL database

Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).

Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.

Don’t index anything else (yet).

Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.

Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.

Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.

Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.

Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.

Avoid NOT IN, instead use a left outer join - even though it’s often easier to visualize the NOT IN.

If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.

Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.

Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.

Avoid index and join hints.

When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.

QTP: using dictionary object

Set d = CreateObject(”Scripting.Dictionary”)
d.Add “a”, “Anil” ‘ Add some keys and items.
d.Add “b”, “Dhruvan Aaryan”
a = d.Items ‘ Get the items.
For i = 0 To d.Count -1 ‘ Iterate the array.
s = s & a(i) & “
” ‘ Create return string.
Next
Msgbox s

Exists Method
Returns true if a specified key exists in the Dictionary object, false if it does not.

If d.Exists(”c”) Then
Msgbox “Specified key exists.”
Else
Msgbox “Specified key doesn’t exist.”
End If

Types of Builds

Based on the software release lifecycle page, and my personal experience, here's how I'd characterize each phase of software development:


Pre-Alpha

The software is still under active development and not feature complete or ready for consumption by anyone other than software developers. There may be milestones during the pre-alpha which deliver specific sets of functionality, and nightly builds for other developers or users who are comfortable living on the absolute bleeding edge.


Alpha

The software is complete enough for internal testing. This is typically done by people other than the software engineers who wrote it, but still within the same organization or community that developed the software.


Beta

The software is complete enough for external testing -- that is, by groups outside the organization or community that developed the software. Beta software is usually feature complete, but may have known limitations or bugs. Betas are either closed (private) and limited to a specific set of users, or they can be open to the general public.


Release Candidate

The software is almost ready for final release. No feature development or enhancement of the software is undertaken; tightly scoped bug fixes are the only code you're allowed to write in this phase, and even then only for the most heinous and debilitating of bugs. One of the most experienced software developers I ever worked with characterized the release candidate development phase thusly: "does this bug kill small children?"


Gold Candidate
The software is finished -- and by finished, we mean there are no show-stopping, little-children-killing bugs in it. That we know of. There are probably numerous lower-prority bugs triaged into the next point release or service pack, as well.

QTP:disabling report filter

Reporter.ReportEvent micFail,"anil's steps","am failing this"
Reporter.Filter = rfDisableAll
disables report filter for subsequent comands

QTP:opening explorer

SystemUtil.Run "C:\Program Files\Internet Explorer\IEXPLORE.EXE"
SystemUtil.Run "IEXPLORE.EXE"

QTP:database connection

DATABASE connection
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DRIVER={Microsoft ODBC for Oracle};UID=;PWD="
objRecordset.CursorLocation = adUseClient
objRecordset.CursorType = adopenstatic
objRecordset.LockType = adlockoptimistic
ObjRecordset.Source="select field1,field2 from testTable"
ObjRecordset.ActiveConnection=ObjConnection
ObjRecordset.Open 'This will execute query
If ObjRecordset.recordcount>0 then
Field1 = ObjRecordset("Field1").Value
Field2 = ObjRecordset("Field2").Value
End if

QTP:Saving web page content at run time

Saving webpage
'Simulate a web query
Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHttp Is Nothing Then Set WinHttp = CreateObject("WinHttp.WinHttpRequest")
WinHttp.Open "GET", "http://www.google.com", False
WinHttp.Send
arrArray = WinHttp.ResponseBody
Set WinHttp = Nothing
On Error Resume Next
Set oADO = CreateObject("ADODB.Stream")
If oADO Is Nothing Then
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTextFile = oFSO.OpenTextFile("c:\google.htm", 2, True)
sData = ""
sBuffer = ""
For iCount = 0 to UBound(arrArray)
oTextFile.Write Chr(255 And Ascb(Midb(arrArray,iCount + 1, 1)))
Next
oTextFile.Close
Else
oADO.Type = 1
oADO.Open
oADO.Write arrArray
oADO.SaveToFile "c:\google.htm", 2
oADO.Close
End If
Set oADO = Nothing
Set oTextFile = Nothing
Set oFSO = Nothing

QTP: SetTOProperty GetToProperty

SetTOProperty
Set obj = Browser("name:=Sign in to Yahoo!").Page("title:=Sign in to Yahoo!").WebEdit("html id:=username")
msgbox obj.GetTOProperty("html id")
'Would retrieve the object html id from the test object description, whether it's in the OR or DP defined
'Now we set the name propertyobj.SetTOProperty "name", "login" 'And retrieve it
msgbox obj.GetTOProperty("name")

GetTOProperty
Msgbox Browser("Sign in to Yahoo!").Page("Sign in to Yahoo!").WebEdit("login").GetTOProperty("name")'Would retrieve the object name from the test object description, whether it's in the OR or DP defined.

QTP testing: load excel file

Open excel & read data
set ex= CreateObject("Excel.Application")
Set a=ex.Workbooks.open("D:\excel.xls")
Set b=a.worksheets("Sheet1")
dim login, pwd
for i=1 to 3
login=b.Cells(i,"A").value
pwd=b.Cells(i,"B").value
msgbox login
msgbox pwd