SQL query optimization i sql server 2005 [modified]

[ashok techxygen]

Hi all, how to use query optimization in sql server 2005 for an efficient query. :)

Ash

modified on Friday, March 28, 2008 1:48 AM

[John_Adams]

Hi Ash, Here are some of the methods that most of the DBA's use to optimise the speed of MS SWL Server queries: -Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynaset to "not shown". -Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields. -Use primary keys or unique indexes wherever possible. -Use numeric rather than text primary keys. -Use non blank unique fields. -Avoid the use of IIf() function in queries. -Avoid domain aggregate functions such as Dlookup(). -Make careful use of Between and Equal to, rather than > or < speeds up queries. -Use fixed column headings in Crosstab queries. -For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties). -Use Make table queries for running reports on static data. These are called snapshot reports. -Use Count (*) rather than Count(Column). -When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest - the "many" may have markedly fewer records. -Short table and field names run faster than long names. -Normalise tables - join strategies execute more quickly on smaller tables. -Denormalise tables - reduce the number of joins. Get the balance right between normalisation and denormalisation by experiment. -Avoid the use of Distinct Row queries - Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All. You may visit the following link for more details about queyr optimization techniques. http://blogs.msdn.com/QueryOptTeam/[^] Hope this helps :).

Regards, John Adams ComponentOne LLC

[Gandalf_TheWhite]

That was a good answer!! covered lots of things. thanks for sharing

Believe Yourself™