Performance optimization in sql.

Devinder Kumar

8y1.1k

I have 2 questions related sql performance.

1. we need to select 1 crore data than how we can improve performance of query.

2. We need to insert 1 crore data than how we can improve performance while inserting data in sql table.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Devinder Kumar8y ago
thanks all my friends for your instant reply.
Amit Kumar8y ago
Hi,
Follow the below link.

https://dzone.com/articles/7-simple-tips-to-boost-the-performance-of-your-sql

http://www.itprotoday.com/microsoft-sql-server/top-10-sql-server-performance-tuning-tips
Ravi Kiran Chanduri8y ago
Hey Dev,

The solution for your question is depends on the requirement.

While inserting:

If you are inserting into a table with actual data, you don't need to worried about it.

Statement: Insert into Table1(name, age sex) Values('Dev',20,M)

But incase of getting values from multiple tables to insert into you required table You have to follow few common peformance startegies. like....
Avoiding SubQueries,
Using proper innjer join and join condition with primary key columns
Avoiding loops as much as posible..

While retrieving :
same thing goes here too. its depend on you query .
fetching from a sib\ngle table with where clause of primary key condition . It will be fast.
where as you want to fetch from multiple joins you should follow above mentioned techniques.

for more detailes you can referance following link
https://technet.microsoft.com/en-us/library/ms172984(v=sql.110).aspx
Sankara Krishnan Venugopal8y ago
Hi Dev,

If you can avoid Looping and Cursor, you will see performance speed in your query.

Best way to go with Bulk insert in SQL SERVER.

1. Insert Record: insert into table1 select col_1, col_2, col_3 from table2
2. Select Records: Make sure, you are following below scenarios to improve performance.
     2.1 Only select the field that you need
     2.2 Remove irrelevant tables
     2.3 Avoid the Outer Joins
     2.4 Avoid the outer joins in where claues

I hope this helps.
Uttam Chaturvedi8y ago
Please follow below article

https://www.codeproject.com/Articles/35665/Top-steps-to-optimize-data-access-in-SQL-Serv