Click here to Skip to main content
15,922,155 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
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

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.
 
Share this answer
 
v2
Comments
Priyanka Tiwari001 7-May-15 2:01am    
Thanks a lot for your solutions and suggestions. I am working on various perspective of database as datatype and indexing. I am referring actual execution plan to get areas where improvements are required.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900