How will you increase the performance to retrieve certain record from the table ? [Resolved]

Posted by Kasani007 under Sql Server on 7/17/2014 | Points: 10 | Views : 431 | Status : [Member] | Replies : 3
If we have a table with lacs of records. If we want to retrieve a record form the table. How will you increase the performance to retrieve certain record ?




Responses

Posted by: Bandi on: 7/17/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
To improve the query performance,

1. Make sure that every table in your database has a primary key.

This will ensure that every table has a clustered index created (and hence, the corresponding pages of the table are physically sorted in the disk according to the primary key field). So, any data retrieval operation from the table using the primary key, or any sorting operation on the primary key field or any range of primary key values specified in the where clause will retrieve data from the table very fast.

2. Create non-clustered indexes on columns which are:

* Frequently used in the search criteria
* Used to join other tables
* Used as foreign key fields
* Used in the ORDER BY clause

Ex: CREATE NON CLUSTERED INDEX NCLIX_OrderDetails_ProductID ON dbo.OrderDetails(ProductID)

3. Rewrite Queries to improve performance.
Refer 'Some TSQL Best Practices' section in the below link
http://www.codeproject.com/Articles/35665/Top-steps-to-optimize-data-access-in-SQL-Serv

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ajay.Kalol on: 7/17/2014 [Member] Starter | Points: 50

Up
0
Down

Resolved
Some More suggestion :

-- You should retrieve only needed records to display. means Use Paging to get Records
-- Apply necessary Indexes
-- Avoid Subqueries
-- Check your query which execution plan or SQL profiler


Ajay
ajaypatelfromsanthal.blogspot.in

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 7/17/2014 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Create a Primary key (It automatically create Clustered Index) on the column that is being used to filter the records from the database table. If creating primary key is not possible, create Non-clustered index.

Read this article, it may help http://www.dotnetfunda.com/articles/show/177/a-guide-to-sql-server-indexes

Indexes can be created by right clicking the field name in Design view of the table and selecting on Indexes/Keys ..., then click Add button and select the columns and type etc from the details boxes.

Hope this helps.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response