Tips for SQL Server Performance


We should give high periority for SQL Server performance.

I have written 20 tips for turning the performance of SQL server.
Following are the tips


1) Try to use where clause for restrict result.

2)Use predecessor "dbo." for tables.

3)Use proper join ("INNER JOIN ,OUTER JOIN ")

4) Try to avoid "OR" condition use "UNION" over there.

5) Try to avoid "IN" Operation .

6) Try To avoid "NOT IN" operation

7) Try to avoid "DISTINCT".

8) Try to avoid "CROSS JOIN".

9) Try to avoid use of Temporary Table. but if needed then define pre structure for that.

9) Define PRIMARY Key & UNIQUE Key Constraint for each table.

10) Try to avoid "HAVING Clause"

11)Include "SET NOCOUNT" at the first of your store Procedure.

12) Try to avoid "CURSOR".

13) Use "UNION ALL" Instead Of "UNION".

14) Try to create INDEX.

15) Create Index On column which is frequently used in Where , order by & Join.

16) Try to create index on Integer Column.

17) try to avoid "SELECT * " instead of it use "SELECT columnname,"

18) Use Sp_ExecuteSQL instead of EXECUTE

19)Use Explicity Index "With( INDEX( INDEXNAME)) with table.

20) Maximize the thread.



I think it will usefull for you.

Thanks
Nathan


Comments

Author: Venkatesan Prabu .J13 Aug 2010 Member Level: Gold   Points : 1


Fantastic.. article.

Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/

Author: raathisaa15 Aug 2010 Member Level: Gold   Points : 0

Hi,

Excellent Tips.

Thanks.

Author: nidheesh08 Mar 2011 Member Level: Gold   Points : 1

Hi,

I think "SELECT * " will give you better performance than Selecting Specific Columns...

please correct me if am wrong

Thanks
Nidheesh

Author: Dotnet Developer-201505 Aug 2015 Member Level: Gold   Points : 0

Hi
Nathan

I have list objects records so i am using Cursor for insertion so how to use without cursor list object to post the database can you explain this?

Author: Nathan06 Aug 2015 Member Level: Gold   Points : 0

Can you post your Stored Procedure. So that I can try.

Author: Dotnet Developer-201506 Aug 2015 Member Level: Gold   Points : 0

Hi

I have list records for 20 rows in my c# so how to insert the records using stored procedure without cursor
can you share the code.

Author: Nathan06 Aug 2015 Member Level: Gold   Points : 2

In your case you have 20 rows in c# code.
So there is no need to talk about the cursor. The challenge is how to send the rows from C# to SQL

1. you can have SP for insert and call that the 20 times

2. you can create SQL insert query in c# for 20 records and execute it only one time.

I hope this will help you.

Author: Dotnet Developer-201506 Aug 2015 Member Level: Gold   Points : 3

Hello
Nathan

I have 1 lakh Records means I called SP 1 lakh Times?

I have 20 rows so we need using Cursor
for looping for insert records in your article asking Try to avoid "CURSOR".

So How can i Insert 20 rows call my stored procedure one time without cursor ?

My Question
How to insert Multiple rows to insert db without cursor but i dont need multiple call single insert query.

Author: Arun Skaria07 Aug 2015 Member Level: Silver   Points : 2

You can use xml for the saving the value. u can make the 5,000 records in one xml and send. it will be more fast.
y i split the xml? it is because there varchar(max) can hold around 4000 letters. since my xml is for 50,000 rows, it will cross that. so i split like that.. you can check for a better value according to number of columns and length of it

i dont have the code with me now. but i did that 5 years back. there were around 50,000 or 70,000 rows if am right

Author: Nathan07 Aug 2015 Member Level: Gold   Points : 4

I think you did not understand my point.

1. CURSOR is only for enumerate the record inside SP.
2. You have 1 lakh records in C# right?.
3. How will you send this C# Records into SQL Server?. Two way (SP and Dynamic Query)
4. What am I telling, Your case is "bulk insert", Create Dynamic SQL Query from C#, then execute only once.
5. Why you are going for SP and CURRSOR?.
For your case you do not need SP and CURRSOR.
6. According to your design pattern, If it is compulsory to create SP and call that for 1Lak. You can discuss with your team and change the desing pattern. Because it will be create you performance issue.

Author: Nathan18 Aug 2015 Member Level: Gold   Points : 0

You can try something like as following

INSERT INTO [dbo].[Table_2]
([Col1]
,[Col2])
( SELECT count(*) as Col1, hour
FROM [Test11].[dbo].[Table_1] )



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: