Performance TIPS - II

Hi,

Let's continue with our Performance tips series - II.

Avoid Functions/Procedures with SQL statement using Group By clause:
Functions / Procedures should not use Group By clause in SQL statement. Although whenever we need to group the result we need to use GROUP by clause.

Avoid long table names: We must not use very long table names. We must limit the table name with X characters.

Avoid tables not involved in foreign keys: We must avoid refering tables which don't take part in foreign keys. Whenever we refer to these then we need to find a path to match data and get relevant or required data, so we must avoid referring to tables without foreign keys.

Avoid using functions in Select and Where clause: Whenever we use function in Select statement of Where clause it will be called for each & every row, which will result in a very slow query. Functions like ltrim(), substring() or any user function will result in low performance.

Avoid Functions / Procedures with queries on too many Tables: Avoid Functions / Procedures with queries on more than 4 Tables. Queries with more than 4 Tables is considered complex. Changing the threshold value requires Metric Assistant configuration update. This means that we should not join more than 4 queries but when we follow normalization and create multiple tables, its difficult to have only 4 tables in the query. We may have to refer to more than 4 tables to achieve required result.

Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction: Avoid Stored Procedures doing an Insert, Update or Delete and not managing a transaction (execute "begin tran" if @@trancount=0). We must always perform and INSERT, UPDATE or DELETE in a transaction, so that the data is always consistent.

Never use an undocumented feature: We must never use an undocumented feature of sql server because there is no notification when it will be removed from future version, which may cause big maintenance problem.\

Truncate Table statement: Always avoid using Truncate Table statement because when this command is used, the data is not logged. Because where clause can't be used with Truncate, it will delete all data. Unless BEGIN TRANS and ROLLBACK is used, nothing can be recovered. So be sure when to use Truncate table statement.

Avoid tables without clustered Index: We must always have clustered index except for table with very little data much data. Having clustered index always help retrieve result faster.

Avoid cursors: Whenever there is a scenario where we need to loop through data for processing, we just simply create a cursor without evaluating overall impact. Consider a scenario when we need to run nested loop using cursor, we need to maintain the state of the outer loop. There can be alternatives like CTE (Common Table Expressions) or Temp tables with Identity columns and then get the minimum and maximum values to run a loop. There can be other alternatives also.

Avoid cursors inside a loop: As its mentioned in the previous point that we must avoid cursors, but if we are using it make sure its not used in a loop.

Avoid using GOTO statement: As we already know we must always avoid GOTO statement but its true that we always encounter situation when we can't avoid using GOTO. In simple words, use it only when there is no alternative.

Avoid SQL queries not using the first column of a composite index in the
WHERE clause: Whenever we write query we must keep in mind the sequence in which composite index is created. We must use the fields in where clause as per the defined index sequence.

Select * from table: Whenever we write query we must avoid using "select *", we must refer to individual fields.

UNION ALL Vs UNION: When we need to combine 2 resultsets to generate 1 resultset and we know that both resultset are distinct we must use UNION ALL rather than UNION because UNION internally uses DISTINCT after the result is returned.


Do write your comments.

Regards

Performance TIPS - 1

Guys,

Today I would like to share some performance tips, which I got while we ran an analysis tool on our code. I will share this in parts, so here is part - 1.

Stored Procedure naming convention - prefix control: Names of Procedures should start with X. The prefix must not start with sp because sp carries an overhead. If we call a procedure with sp_, it will first search the MASTER database before searching the current database. By default, only members of the dbo_owner role and db_ddladmin role can create stored procedures. Members of the dbo_owner role can give other users the ability to create procedures using a GRANT statement.

Avoid Functions and Procedures doing an Insert, Update or Delete without including error management: Avoid stored procedures or functions to Insert, Update or Delete without error management. You should use @@error for SQL 2000 and BEGIN TRY and BEGIN CATCH for SQL 2005. You will have full control and can handle all exceptions (both application and system) easily and update user accordingly.


Avoid Stored Procedures not returning a status value: We must avoid using a stored procedure which does not return anything. There should be a RETURN status.


Avoid Functions / Procedures with a complex SELECT clause: Avoid Functions / Procedures with a SELECT clause returning more than 9 columns or with a ´SELECT *´ query. Such queries are considered complex. Changing threshold value requires a Metric Assistant configuration update. In practical scenario its generally not possible to restrict the result to 9 columns but we must try to follow wherever we can.

Avoid Functions / Procedures with SQL statement including Subqueries: Functions / Procedures should not use SQL statement including Subqueries (at least subqueries should be avoided). We generally use subqueries, we have to use subqueries, we can take output into a table variable then we can use join to avoid subquery.

Avoid using temporary Objects:Triggers, Views, Functions and Procedures should not use temporary Objects (except temporary Tables). We can use temp tables / table variables.

Watch for more tips later on. Do write your comments and let me know what else you want to know.

Regards,

Sudhir

Script to extract objects available on a Linked Server

Hi,

In a scenario where we need to find out all the objects of a LINKED Server and check which object is accessible to us, we need to write following query:

Declare @min int,@max int
Declare @sql varchar(100)
Declare @table_server varchar(100)
Declare @table_catalog varchar(100)
Declare @table_schema varchar(100)
Declare @tablen varchar(100)


Set @table_server = 'LINKEDSERVER' --Name of Linked Server (as per sys.servers table)
Set @table_catalog = 'DATABASENAME' --Database Name
Set @table_schema = 'dbo' --Database owner

--Now create a table variable with identity column to help loop through.

Declare @table table
(Id int identity(1,1),
table_cat varchar(100),
Table_schem varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100))

--Insert data into table variable
Insert into @table
exec sp_tables_ex --Use sp_tables_ex procedure
@table_server = 'LINKEDSERVER',
@table_catalog = 'DATABASENAME',
@table_schema = 'dbo'

select * from @table --Check if anything is populated
set nocount on

--As this populate, remove SYSTEM TABLES
Delete from @table where Table_Type = 'SYSTEM TABLE' 

--Extract Min & Max value to run loop on the table variable.
Select @min = min(id), @max=max(id) from @table

while (@min<=@max)
Begin

--Extract Table name to refer from @table table variable.
Select @tablen = Table_Name from @table where id = @min

--Prepare dynamic query
Set @sql = 'Select top 10 * from ' + @table_server + '.' + @table_catalog + '.' + @table_schema + '.' + @tablen

print @sql
begin try
--Execute dynamic query in TRY block to check for permission
exec(@sql)
end try
begin catch
--If come here then we know this query is not accessible.
print @sql + ' not worked'
End catch
set @min = @min + 1 --Increment loop
End

Let me know if you have any question.

Regards,

Happy SQL Coding

SQL SERVER: Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

SQL SERVER: Row Constructor, Declare - Initialization and Assignment features of SQL Server 2008

Date and Time data type in SQL Server 2008

Hi,

Today let's discuss about Date & Time data type introduced in SQL Server 2008.

We always had to do extra steps to remove time from Age field or DOB field because we don't need time. Also while comparing 2 dates, we need to remove Time so that we get correct data.

All this extra work or steps will not be required because SQL Server 2008 has introduced 4 new (DATE RELATED) data types :

DATE

TIME

DATETIME2

DATETIMEOFFSET

All these data types separate date and time, support larger date range, improved accuracy and support for timezone. DATE data type stores only Date, TIME data type stored only time. DATETIME2 data type is an improved version of Datetime data type, which is improved to provide better accuracy and a larger date range. DATETIMEOFFSET data type adds time zone component in the date.

Let's check the size of each data type:

Date: Storage in bytes = 3 Accuracy = 1 day
Time: Storage in bytes = 3-5 Accuracy = 100 nanoseconds
Datetime2: Storage in bytes = 6-8 Accuracy = 100 nanoseconds
DateTimeOffSet: Storage in bytes = 8-10 Accuracy = 100 nanoseconds

I would like to highlight one point in this:
The date format is 'YYYY-MM-DD' for all the data types but because date format is language dependent, it is possible when you change the language the format is also changed.

DATE Data Type

Date Only
01-01-0001 to 31-12-9999

TIME Data Type

Time Only, Variable Precision - 0 to 7 decimal places for seconds To 100 nanoseconds

DATETIME2 Data Type

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds, a datetime type w/ larger fractional seconds and year range than the existing DATETIME type

DATETIMEOFFSET

01-01-0001 to 31-12-9999
Variable Precision - to 100 nanoseconds
Time Zone Offset (From UTCTime) Preserved
Not Time Zone Aware - No Daylight Saving Time Support

The three new types that contain a time component (TIME, DATETIME2, and DATETIMEOFFSET) enable you to specify fractional seconds precision in parentheses following the type name. The default is 7, meaning 100 nanoseconds. If you need a fractional second accuracy of milliseconds, such as three for example, you must explicitly specify it: DATETIME2(3).

declare @e datetime2(3)
set @e=GETDATE()
select @e
------------------------------
2009-07-17 11:28:44.090
------------------------------

declare @d datetime2(5)
set @d=GETDATE()
select @d

------------------------------
2009-07-17 11:28:44.09000
------------------------------

declare @f datetime2(7)
set @f=GETDATE()
select @f

------------------------------
2009-07-17 11:28:44.0900000
------------------------------

The precision changes as per the specified size.

In the sys.columns view, the precision attribute describes the total number of characters in the default literal string representation of the value, and the scale describes the number of digits in the fractional part of the seconds. In INFORMATION_SCHEMA.COLUMNS, the DATETIME_PRECISION attribute describes the number of digits in the fractional part of the seconds.

Let's look at examples of these:

DECLARE
@d AS DATE = '2009-02-12',
@t AS TIME = '12:30:15.1234567',
@dt2 AS DATETIME2 = '2009-02-12 12:30:15.1234567',
@dto AS DATETIMEOFFSET = '2009-02-12 12:30:15.1234567 +02:00';

SELECT @d AS [@d], @t AS [@t], @dt2 AS [@dt2], @dto AS [@dto];

Here is the output:
@d = 2009-02-12
@t = 12:30:15.1234567
@dt2 = 2009-02-12 12:30:15.1234567
@dto = 2009-02-12 12:30:15.1234567 +02:00

Functions associated with Date data types:

To support the new date and time data types, SQL Server 2008 introduces new functions and enhanced existing functions. The new functions are SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET.

SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET return the current system date and time value.
SYSDATETIME returns the current date and time as a DATETIME2 value,
SYSUTCDATETIME returns the current date and time in UTC as a DATETIME2 value, and SYSDATETIMEOFFSET returns the current date and time along with the system time zone as a DATETIMEOFFSET value.

To get only the current date or only the current time, you can cast the value returned from the SYSUTCDATETIME function to DATE or TIME, as the following example shows:

SELECT
CAST(SYSDATETIME() AS DATE) AS [current_date],
CAST(SYSDATETIME() AS TIME) AS [current_time];

current_date current_time
2009-07-17 11:12:24.9059210

NOTE: When You convert a datetime datatype to DATE, SQL Server query optimizer still relies on index ordering to process the query more efficiently. This is contrary to the usual behavior, where conversion of a column to a different type prevents the optimizer from relying on index order. The new behavior means that the optimizer might consider an index seek for a query filter that has a conversion to DATE. For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:

USE AdventureWorks;
SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate
FROM Sales.CurrencyRate
WHERE CAST(CurrencyRateDate AS DATE) = '20040701';

FromCurrencyCode ToCurrencyCode EndOfDayRate
USD ARS 3.5501
USD AUD 1.7763
USD BRL 3.6949
USD CAD 1.5758
USD CNY 8.2872
USD EUR 0.9962
USD GBP 0.6343
USD JPY 120.586
USD MXN 10.255
USD SAR 3.7541
USD USD 1.00
USD VEB 1362.0302

The structure of the table is:
Column_name Type
ScaleCurrencyRateDate datetime


The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value.

SELECT SYSDATETIMEOFFSET()
2009-07-17 11:19:42.7380350 +08:00

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-16 22:19:42.7380350 -05:00

The TODATETIMEOFFSET function sets the time zone offset of an input date and time value. The main purpose of the TODATETIMEOFFSET function is to convert types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
2009-07-17 11:21:09.3356644 -05:00

This function is different from SWITCHOFFSET in several ways. First, it is not restricted to a DATETIMEOFFSET value as input; rather it accepts any date and time data type. Second, it does not try to adjust the time based on the time zone difference between the source value and the specified time zone but instead simply returns the input date and time value with the specified time zone as a DATETIMEOFFSET value.

SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');
2009-07-17 11:22:31.9018632 -05:00

DATEADD, DATEDIFF, DATENAME, and DATEPART functions are updated to add support for microseconds and nanoseconds. Many other functions are enhanced to support new date and time types, among them are, type conversion functions (CAST and CONVERT), set and aggregate functions (such as MAX, MIN), metadata functions (such as TYPEPROPERTY, COLUMNPROPERTY), and system functions (such as DATALENGTH, IS_DATE).

Hope this information is useful. Do let me know if you have any question.

Regards,

Happy SQL Coding