Query Optimization Document

Sr.No

Topic

1

Between

2

TempTable

3

Use Storeprocedure

4

Cursors

5

Use of Count() Function

6

Sub Query

7

Filter

8

Alias

9

Table Join

10 11

Null value Not Exist in place of Not in

12

OR

13

Change Cursor to Loop

Comments if two condition in use like lessthan and Greaterthan then use between clause in the query for Optimization if more data will be process than user Temporary table for get data in Temporary table and use that Temp Table for below Processing this is most useful tips for Query speed up Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavyduty queries text. Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations. Use Table's Primary key in Count(pk_id) in place of Count(*) below Query will give same output as count rows for table SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TableName') AND indid < 2 if only one field is needed then use SubQuery not use join for one field Use proper Sequence of the Filter in the where condition it is very important for select Query example:Write bigfilter first which reduce the Reocrds for other Filters Use Table Alias for the use of more than one table that will affect the speed of Query if you join Two table than Write more rows table first in the joining that will work fast in the Query Don’t Compare Null values in the where condition it will affect the speed of Query Use Not exitst in place of not in it very beneficial for Query. in where condition replace not exitst in place of not in Avoid OR Conditional operator use IN or UNION inplace of or Show Below Example

if cursors will take time then use that logic for the your Store procedurs. ---below is cursor declare @query varchar(100), @dbname sysname declare BadCursor Cursor for select name from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb') open BadCursor fetch next from BadCursor into @dbname while @@fetch_status = 0 begin select @query = 'dbcc checkdb(' + quotename(@dbname) + ')' exec(@query) fetch next from BadCursor into @dbname end close BadCursor deallocate BadCursor go -- how to change to loop declare @query varchar(100), @dbname sysname declare @dblist table (dbname sysname) insert into @dblist(dbname) select name from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb') while (select count(*) from @dblist) > 0 begin select top 1 @dbname = dbname from @dblist select @query = 'dbcc checkdb(' + quotename(@dbname) + ')' exec(@query) delete from @dblist where dbname = @dbname end go just get your data in the Any Temp table or table variable after that delete rows from that at end of loop.

Sr.No 111 127 138 146 191 248 266 268 275 279 287 292 341 351 392

Name of Procedure NHS_SP_GET_MedicationDataBetWeenTime NHS_SP_GETACTIVERORDERLISTold NHS_SP_Get_OrderRecordsForPhysician NHS_SP_GETADDMISSIONORDERRECORDS NHS_GET_ResidentProviderDischargeSummaryData NHS_RPT_Discharge_CMS802Report NHS_SP_RPT_GETACTIVERORDERLIST_ALL NHS_RPT_UnitMontlyTreatMentSheetReport NHS_RPT_SP_GETORDERHISTORYForBatchPrint NHS_SP_LAST24HourReacpResidentList NHS_SP_Get_NurseChemicalAssignedMedications NHS_SP_Get_NurseResidentBowelMedications NHS_SP_RPT_GETACTIVERORDERLIST NHS_SP_GetMedicationUnitInfo NHS_SP_GETORDERHISTORY

Status In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use In Use

Optimization Apply DONE DONE DONE DONE DONE DONE DONE DONE DONE DONE DONE DONE Done Done DONE

Change Yes Yes Yes Yes Yes YES YES YES YES YES YES YES YES YES YES

Detail

Sec Before

4

3

Sec After

1

1

select * from information_Schema.routines where specific_name not like '%Insert_tbl%' and specific_name not like '%update_tbl%' and specific_name not like '%Readall_tbl%' and specific_name not like '%Delete_tbl%' and ROUTINE_type ='PROCEDURE' and specific_name not like '%dt_%' and specific_name like 'NHS%' and ROUTINE_DEFINITION like '%NHS_SP_GetEmployeeUserID%'

P_GetEmployeeUserID%'