Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, May 11, 2012

Database Performance Considerations

While designing your database, keep performance in mind. You can't really tune performance later when your database is in production.
  1. Normalize and De normalize Tables.
  2. Create Table Columns with maximum row size.(If number of columns larger than row size, split table into multiple tables with relations one to one). For column with extended size like binary, ntext, and...etc We prefer to put in separate table.
  3. Create a primary key on each table. Each primary key is clustered index.
  4. Add calculated columns which you need to avoid complicated queries.
  5. Create an index on any column that is a foreign key.
  6. Create indexes for your query, Indexes will eliminate rows scanning and locking problems:
    • Plan your indexes. Indexes enhance performance but increasing them cause drawback. Choose indexes make big filtration of your records.
    • Create simple index (indexes have only one column) for Where condition.
    • For sort create composite indexes(indexes have multiple columns).
  7. Create Indexed View if Possible.
  8. Use owner qualify of your objects (as much as possible) when you reference them in TSQL. Use owner.table instead of just table. 
  9. Use set nocount on at the top of each stored procedure and set nocount off at the bottom. 
  10. If needn't locking your database table, you take a chance on a dirty read? Ways of dirty work:
    • Use the NOLOCK hint.
    • Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
  11. Return the columns and the rows you need.use select column1, coulmn2,.. instead of select *
  12. Use transactions when appropriate, decrease transaction time.
  13. Avoid temp tables as much as you can.
  14. Indexes will not work with Functions for example, where LTRIM(Name) = 'Products'.
  15. Avoid using OR in your query as much as possible (Cause unexpected plan), Use join instead of it.
  16. Avoid negative query NOT IN (Use outer join),<> (Indexes will not work with Negative query). Same thing when you use like as like '%val'.
  17. If you use dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC). 
  18. Reduce the number of round trips to the server.

SQL Server Naming Conventions

  1. Databases, Files, and File Paths 

    •  The database name should accurately reflect the database content and function. All database names must be prefixed with the originating component's acronym--e.g., CBC_Medicare_Compare or EDG_Database_Tracking. Use both upper and lower case letters as appropriate. Do not use spaces in the name. 
    • File names must match the database name. 
    • If the database is being developed off site from CMS's central office, please create your databases in the default Microsoft directory structure. E.g.,
      C:\Program Files\Microsoft SQL Server\MSSQL\data\OIS_Personnel_Master_Data.MDF 
  2. Tables and Views 

    •  Table names should accurately reflect the table's content and function. Do not use spaces in the name. 
    • View names follow the same conventions as table names, but should be prefixed with the literal 'VW'. E.g.,
      vw1999NewRegulations 
  3. Columns

    The standards below are applicable to all column names:
    • Each column name must be unique within its table.
    • Each column name must be derived from the business name identified during the business/data analysis process. For more information on deriving column names from business names, see Creating Physical Names for Elements and Columns in the Data Administration standards. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name and an abbreviated physical name. 
    • Do not use reserved or key words as object names. 
    In addition, if the data is going to be brought in-house to interact with other CMS computer systems, the following standards are applicable: 
    • The name can have a maximum of 18 characters. 
    • The name must include acceptable class and modifying words as specified in CMS's Data Administration standards. 
  4. Indexes

    Indexes are named to indicate the table they are attached to and the purpose of the
    index.
    • Primary keys have a suffix of '_PK'.
    • Foreign keys have a suffix of '_FKx' where x is a number that is incrementally assigned.
    • Clustered indexes have a suffix of '_IDX'.
    • All other indexes have a suffix of '_NDXx' where x is incrementally assigned.
    Only one suffix per index may be appended. The application of the appropriate suffix should follow the following hierarchy: primary key, clustered index, foreign key, other index. E.g., an index that is both a primary key and clustered should have a suffix of '_PK'.  It is good practice to index columns that are frequently used in a query's selection criteria. 
  5. Stored Procedure

    • System level stored procedures are named using a prefix 'SP__' (two underscores) and a description of what the stored procedure does. 
    • All application level and user defined stored procedures are prefixed with the constant 'USP' with a description of what the stored procedure does. E.g., UspGetLastModifiedDate 
  6. Triggers

    Triggers are named to indicate the table they are for and the type of trigger. The
    purpose of the trigger is identified in the prefix to the name. All triggers should be
    prefixed with the letter 'T', a letter(s) designating the type, an underscore, and the
    table name. The type should be designated as 'I' = insert, 'U' = update, 'D' = delete.
    E.g., ti_Orders (Insert trigger)
  7. Variables

    Variable identifiers for datatypes should consist of two parts: 
    • The base, which describes the content of the variable; 
    • The prefix, which describes the datatype of the variable 
    Correct prefixes for each datatype are shown in the table below. 

    Datatype Prefix Example
    Char chr @chrFirstName
    Varchar chv @chvActivity
    Nchar chrn @chrnLastName
    Nvarchar chvn @chvnLastName
    Text txt @txtNote
    Ntext txtn @txtnComment
    Datetime dtm @dtmTargetDate
    Smalldatetime dts @dtsCompletedDate
    Tinyint iny @inyActivityID
    Smallint ins @insEquipmentTypeID
    Integer int @intAsset
    Bigint inb @inbGTIN
    Numeric or Decimal dec @decProfit
    Real rea @reaVelocity
    Float flt @fltLength
    Smallmoney mns @mnsCost
    Money mny @mnyPrice
    Binary bin @binPath
    Varbinary biv @bivContract
    Image img @imgLogo
    Bit bit @bitOperational
    Timestamp tsp @tspOrderID
    Uniqueidentifier guid @guidPrice
    sql_variant var @varInventory
    Cursor cur @curInventory
    Table tbl @tblLease

Friday, January 16, 2009

LINQ to SQL Serialization

Problem:
  • LINQ to SQL classes do not support binary serialization. Although I can manually modify them to meet my needs, it is a very time-consuming job, and difficult to maintain if the table is changed in the future.
  • LINQ to SQL classes cannot be serialized by XML serializer if there is a relationship between tables.

These articles give us work around:
http://www.west-wind.com/WebLog/posts/147218.aspx
http://www.codeproject.com/KB/linq/linqsqlserialization.aspx

Tuesday, July 29, 2008

SQL 2005 to delete lots of data in batches

If you want to delete lots of data (millions), Running one query causes the TransactionLog to grow with huge size.

You have 2 solutions for this problem:
1. If you can your data is offline:
  • Copy the rows you want to keep to a temporary table
  • Drop the original table
  • Rename the temporary table to original name
  • Reinstate any indexes

2. Second approach is to delete the rows in a loop. Delete a modest number each time round the loop. Keep looping until no more rows exist to delete.

  • You will need to either backup the TLog frequently during this process (to stop it extending to a vast size), or change the RECOVERY MODEL to SIMPLE whilst this is running, and back to FULL again after it finished.
  • If this batch works 24 hours/7 days you should also put a WAIT for 5 seconds or so inside the loop so that during each iteration other connected users get "their chance"

SQL Script to delete rows in loop:(needs local variables declaring)

SELECT @intRowsToDelete = COUNT(*) -- Number of rows to be deleted FROM dbo.MyTable WHERE ... MyDeleteCriteria ...
WHILE @intRowCount > 0 AND @intErrNo = 0 AND @intLoops > 0

BEGIN
SELECT @dtLoop = GetDate()
SELECT @strSQL =
SET ROWCOUNT @DEL_ROWCOUNT -- number of delete rows / iteration
DELETE D FROM dbo.MyTable AS D WHERE ... MyDeleteCriteria ...
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
SET ROWCOUNT 0 -- Reset batch size to "all"
SELECT @intRowsToDelete = @intRowsToDelete - @intRowCount,
@intLoops = @intLoops - 1
-- Debugging usage only:
PRINT 'Deleted: ' + CONVERT(varchar(20), @intRowCount)
+ ', Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtLoop, GetDate()))
+ ' seconds,' + ' remaining to delete=' + CONVERT(varchar(20), @intRowsToDelete)
+ ', Loops left=' + CONVERT(varchar(20), @intLoops)
WAITFOR DELAY '000:00:05' -- 5 seconds for other users to gain access
END

Tuesday, May 27, 2008

Sort ascending or descending dynamics

You 2 Ways to sort ascending or descending dynamics depends on variable:
First way:
SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]
order by CAST([Last_Update_Dt] AS INT)*-1

SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]order by [Last_Update_Dt] DESC
This technique of multiplying either 1(ASC) or -1(DESC), can be used in many queries, and of course is not limited to datetimes.
Second way
DECLARE @SortOrder int
SET @SortOrder = 1
SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]
ORDER BY
CASE
WHEN @SortOrder = 1 THEN (RANK() OVER (ORDER BY [Last_Update_Dt] ASC))
WHEN @SortOrder = 2 THEN (RANK() OVER (ORDER BY [Last_Update_Dt] DESC))
END

Article below gives you ideas to create dynamic query Sorting and Where Clause without conactenate string:
http://www.sqlteam.com/article/dynamic-order-by