Query optimization

INDEX

• Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• If you create a composite (multi-column) index, the order of the columns in the key are very important
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
• Avoid Indexing Small Tables
• Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
• Don't accidentally add the same index twice on a table.
• Drop indexes that are not used by the Query Optimizer
• If a table has a clustered index and non-clustered indexes, then performance will be best optimized if the clustered index is based on a single column that is as narrow as possible.
• If you drop or change a clustered index, keep in mind that all of the non-clustered indexes also have to be rebuilt
• Generally, you probably won't want to add an index to a table under these conditions:
• If the index is not used by the query optimizer.
• If the table is small, most likely indexes will not be used.
• If the column values exhibit low selectivity, often less than 90%-95% for non-clustered indexes.
• If the column(s) to be indexed are very wide.
• If the column(s) are defined as TEXT, NTEXT, or IMAGE data types.
• If the table is rarely queried.
• Keep the "width" of your indexes as narrow as possible
• Non-clustered indexes are best for queries:
• That return few rows (including just one row) and where the index has good selectivity (generally above 95%).
• Where both the WHERE clause and the ORDER BY clause are both specified for the same column in a query. This way, the non-clustered index pulls double duty. It helps to speed up accessing the records, and it also speeds up the sorting of the records (because the returned data is already sorted).
• When the column or columns to be indexed are very wide. While wide indexes are never a good thing, if you have no choice, a non-clustered index will have overall less overhead than a clustered index on a wide index.
• If a column in a table is not at least 95% unique, then the query optimizer may not use a non-clustered index based on that column. Because of this, don't add non-clustered indexes to columns that aren't at least 95% unique. For example, a column with "yes" or "no" as the data won't be at least 95% unique
• If you have no choice but to use a composite index, keep the "width" of it as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
• Try to create indexes on columns that have integer values rather than character values
• If you need to join several tables very frequently, consider creating index on the joined columns.
• Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
• Use the INDEX hint to specify which index to use.
• The syntax is: INDEX (index_val [ ,...n ] )

CLUSTER INDEX

· Create cluster index if the column is incremental.

· As a rule of thumb, every table should have at least a clustered index

• When selecting a column to base your clustered index on, try to avoid columns that are frequently updated
• If your table needs a clustered index, be sure it is added to the table before you add any non-clustered indexes.
• When sorting a specific column often consider making that column a clustered index
• That retrieves small ranges of data (not large ranges). Clustered indexes perform better for large range queries.
• If possible, avoid adding a clustered index to a GUID column

TRIGGERS

· You can use INSTEAD OF triggers to perform enhance integrity checks on the data values

· Don't use a trigger to enforce referential integrity

WHERE

· Try to restrict the queries result set by using the WHERE clause.

· If you create a composite (multi-column) index, the orders of the columns in the key are very important

· If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

· Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.

· If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.

· If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.

· Avoid non-sargable in WHERE clauses, If possible rewrite them to sargable ones

· In the WHERE-clause use the least likely true AND expression first

· Avoid using OR in the WHERE-clause if not all columns have an index

· Always try to use a WHERE-clause in your query to narrow the results

· Avoid using NOT IN. Instead use EXIST, NOT EXIST, IN or LEFT OUTER

· JOIN with a check for a NULL condition

· Avoid using IN when EXISTS is also possible

· Avoid using IN when BETWEEN is also possible

· In case using IN try to order the list of values so that the most frequently found values are placed first

· Avoid using SUBSTRING in the WHERE-clause. If possible use LIKE instead

· In case of using HAVING try to minimize the amount of rows using a WHERE clause.

· Try to avoid the HAVING clause, whenever possible

· In case using LIKE on CHAR of VARCHAR columns quite often consider using the full-text search option

· Do not forget to close SQL Server cursor when its result set is not needed.

· Do not forget to de-allocate SQL Server cursor when the data structures comprising the cursor are not needed.

· Avoid using variables in a WHERE clause in case the query is located in a batch-file

· Try to avoid WHERE clauses that are non-sargable.

· When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient

· Try to avoid string concatenation in your Transact-SQL.

· If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written

· Don't group redundant columns.

· While grouping, keep the number of groupings as few as possible.

· While grouping, keep the number of rows returned by the query as small as possible.

· If you use LIKE in your WHERE clause, try to use one or more leading characters in the clause, if possible. For example, use:

LIKE 'm%'

Not:

LIKE '%m'

CURSORS

· If a cursor is unavoidable, use a WHILE loop instead.

· Try to avoid using SQL Server cursors, whenever possible

· Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

· Use FAST_FORWARD cursors, whenever possible

· Try to reduce the number of columns to process in the cursor.

· Try to reduce the number of records to process in the cursor.

· Use READ ONLY cursors, whenever possible, instead of updatable cursors

· Do not forget to close SQL Server cursor when its result set is not needed.

· Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed

· Do not forget to close SQL Server cursor when its result set is not needed

Here are some alternatives to using a cursor:

· Use WHILE LOOPS

· Use temp tables

· Use derived tables

· Use correlated sub-queries

· Use the CASE statement

· Perform multiple queries

STORED PROCEDURE

· Try to use views and stored procedures instead of heavy-duty queries

· Try to avoid using temporary tables inside your stored procedure.

· Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement

· Keep Transact-SQL transactions as short as possible within a stored procedure.

· If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure.

· If a stored procedure needs to return only a single value, and not a record set, consider returning the single value as an output statement.

· Call stored procedure using its fully qualified name.

TEMP TABLE/ TEM VARIABLES

· Use table variables instead of temp tables

· In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives:

· Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table.

· Instead of using temporary tables, consider using a derived table instead

· Consider using a correlated sub-query.

· Use a permanent table instead.

· Use a UNION statement to mimic a temp table.

· One legitimate reason you might want to consider using a temp table is to avoid having to use a cursor.

· If you have to use a temp table, do not create it from within a transaction. Create the temporary table before the transaction.

· Here are the steps when you use a temporary table:

o Lock tempdb database

o CREATE the temporary table (write activity)

o SELECT data & INSERT data (read & write activity)

o SELECT data from temporary table and permanent table(s) (read activity)

o DROP TABLE (write activity)

o Release the locks

ROW COUNT/TOP

· Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows

GENERAL

· Don't use ORDER BY if you don't really need it

· In case using GROUP BY without an aggregate function try using DISTINCT instead

· Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.

· Avoid using COUNT(*) to check the existence of a record. Instead use EXIST

· Rewrite Subqueries to Use JOIN

· If you know that a particular column will be subject to many sorts, consider adding a unique index to that column

· Avoid using FLOAT or REAL data types as primary key

· Be aware that the MIN() or MAX() functions can take advantage of appropriate indexes. If you find that you are using these functions often, and your current query is not taking advantage of current indexes to speed up these functions, consider adding appropriate indexes

· To make complex queries easier to analyze, consider breaking them down into their smaller constituent parts

· For static tables use a FILLFACTOR and PAD_INDEX of 100. For dynamic tables use a lower FILLFACTOR

· To identify additional indexes use the SS Profiler Create Trace Wizard and trace "Identify Scans of Large Tables

· Use cascading referential integrity constraints instead of triggers, whenever possible

· Try to avoid using the DISTINCT clause, whenever possible

· Use varchar/nvarchar columns instead of text/ntext columns whenever possible.

· If you need to delete all tables rows, consider using TRUNCATE TABLE instead of DELETE command

· Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.

· Try to use the best performing operator as possible

· Keep the width and/or number of sorted columns to the minimum

· Keep the number of rows to be sorted to a minimum

· If your SQL Server database is not configured to be case sensitive, you don't need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed

· Whenever SQL Server has to perform a sorting operation, additional resources have to be used to perform this task. Sorting often occurs when any of the following Transact-SQL statements are executed:

· ORDER BY

· GROUP BY

· SELECT DISTINCT

· UNION

UNION ALL/UNION

· Try to use UNION ALL statement instead of UNION, whenever possible.

· Avoid using UNION of two subsets from the same table. Instead use OR in the WHERE-clause

· Sometimes consider rewriting a query using a OR to multiple queries combined with a UNION ALL

Cognos: Removing '---------------------------------' from Dropdown

Place following code in end of C:\Program Files\cognos\c8\webcontent\prompting\prompting.js

window.onload=function updte()
{
if(document.forms.length>0)
{
var pstr11=document.getElementsByTagName('Select');

if(pstr11!=null && pstr11.length>0)
{
for(var i=0;i {
if(pstr11[i].type=='select-one')
{
if(pstr11[i]!=null && pstr11[i].name !=null)
{

/*
//Following code is used to remove blank line from Status DDLBs
if((pstr11[i].length>=1) && (pstr11[i].name=='_oLstChoicesparamStatus' || pstr11[i].name== '_oLstChoicesstatusparam'))
{
pstr11[i].remove(1);
}

else if((pstr11[i].length>=1) )
{
pstr11[i][1].text='';
}

*/

//Used to remove Blank line at index 1 From ALL DDLBs
if(pstr11[i].length>=1)
{
pstr11[i].remove(1);

}



}
}

}


}
}

};

Alter Table constraints/and disabling triggers

go
ALTER TABLE tableName NOCHECK CONSTRAINT IndexName
DISABLE TRIGGER trg_FORM_DEFINITION_COVERAGE_audit_ins ON FORM_DEFINITION_COVERAGE;
go

SQL SERVER: Convert database to MULTI/Single USER

ALTER DATABASE DBName SET MULTI_USER WITH NO_WAIT;
ALTER DATABASE DBName SET SINGLE_USER WITH NO_WAIT;

SQL Server Shrinking

DBCC SHRINKDATABASE (MWS_DEV02_VICTORS, TRUNCATEONLY);
DBCC SHRINKDATABASE ([VICTORS_BA]);
DBCC SHRINKFILE (MWS_DEV06_VICTORS_log, 10);
DBCC SHRINKFILE (VICTORS, 10);

ORACLE: How to Import

>imp userName_InWhich_BackUpTo_BeImported/password@orcl
Import file: ImportFileNameWithFullPath> fullPath\fileName.dmp
Enter insert buffer size (minimum is 8192) 30720> Press Enter
List contents of import file only (yes/no): no>
Ignore create error due to object existance (yes/no): no> Press Enter
Import grants (yes/no): yes> Press Enter
Import table date (yes/no): yes>Press Enter
Import entire export file (yes/no): no>
username:ExportFile_UserName
Import Terminated successfully with warnings.
-------------------------------------------------

ORACLE: Create Backup/Export db backup

>exp userName_FromToCopyDate/Password@Orcl
>Enter array Fetch buffer size:xxxx> PRESS ENTER
>Export file: EXPDAT.DMP> ENTER FILE NAME.DMP
>(2)U, or (3)T: (2)U> Press Enter to export full user Information or press 3 and enter for just export user tables
>Export grants : yes>press enter
Export table date (yes/no): yes >press enter
compress extents (yes/no): yes >press enter
Export terminated Successfully with warnings.

SQL Server Restore DB Backup(Advanced)

RESTORE FILELISTONLY
FROM DISK = 'H:\Backup\BPM_MWS_71_VICTORS.bak'

RESTORE DATABASE [BPM_MWS_71_VICTORS]
FROM DISK = 'H:\Backup\BPM_MWS_71_VICTORS.bak'
WITH REPLACE, MOVE 'BPM_MWS_71_VICTORS' TO 'G:\LFC\BPM_MWS_71_VICTORS.MDF',
MOVE 'BPM_MWS_71_VICTORS_log' TO 'G:\LFC\BPM_MWS_71_VICTORS_log.LDF'

SQL Server Restore DB Backup(Simple)

RESTORE DATABASE [DBName] FROM DISK = N'x:\location\FileName.Bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

SQL Server Database Backup

BACKUP DATABASE [DBName] TO DISK = N'x:\location\BackupFileName.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10