How to improve queries performance with large amount of data in database.

Question

4.00/5 (1 vote)

See more:

SQL-Server-2012

Dear all,

I am working in a online application where tables having 1 million data for one year and I am facing performance problem in various queries. For upcoming years also table data will increase as well as performance issue. Then what should I do to solve these problems?
Should I separate database year wise or do indexing year wise on tables or there are some more options available to improve performance?

Any response is highly appreciable.

Thanks in advance!!!

Posted 26-Apr-15 20:22pm

Priyanka Tiwari001

Add a Solution

Comments

Tomas Takac 27-Apr-15 3:06am

This is not a good question. What do you mean performance is bad? 1 million rows per year isn't that much. You need to examine your queries and indexes. And of course if the table keeps growing then a suitable partitioning strategy will help. But first of all you need to know what's happening in the database, otherwise you don't know what you need to optimize.

Herman<T>.Instance 28-Apr-15 4:38am

It all depends on how your db-scheme is set up. How your index strategy is set up. Last couple of years I owned my own company to solve these problems. I saw mostly the same issues.
Have you done a profiler session on your DB. That should tell you which queries are troublesome. There is a free Express Profiler program on the internet to download, install and use!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Georg Scholz · Answer 1 · 2015-04-29T20:12:00

There are many different options to improve the performance of a database, but first, it necessary to analyze your problem and to understand why your database is slow.

Therefore, as a first step, you can try this:

a) Take some query which is very slow

b) Run the query in SQL Management Studio, and use these options:
Query -> Include Actual Execution Plan ( Ctrl+M)
Query -> Include Client Statistics (Shift+Alt+S)

Be sure to use the ACTUAL Execution Plan, NOT the ESTIMATED Execution Plan!

The Actual Execution Plan gives you a good guide to the core of your performance bottlenecks.

You can then take further steps, e.g. either improve your data structures (indexes, data types, general setup of tables), or you can improve the quality of your SQL statements.

Perhaps you can post a Execution Plan here.