0

I have a sp which has to select data from 8 tables and each select query has a lot of 'where clauses' and each table has thousands of rows of data.

Now, the requirement is to increase performance of this sp. Below mentioned approaches are suggested and respectively my questions for them are mentioned below:

  1. create few other tables which will store frequently asked data (eg : for current year), so use these table for current year data which is asked 50 percent of time. and use original tables for years other than current year. As this will limit the data ,thus we are expecting increase in performance. So my question is : will this result in substantial improvement in performance? because anyhow where clauses also limit the data to be selected. Or how does reducing the data provided to sql relates to performance
  2. create partitions on existing table based on years. I have no idea how these partition works. So if anyone can throw some light on this, that would be appreciated.
Onki
  • 1,879
  • 6
  • 38
  • 58
  • 1
    mysql and sql server are different beasts,edit your tags. – Mihai Jan 16 '15 at 11:50
  • You need to introduce ETL in the picture and daily jobs to schedule them. – SouravA Jan 16 '15 at 12:36
  • @Sourav_Agasti : thanks for your opinion, But this is my constraint to work with this sp and existing db only. I cannot introduce ETL just for this requirement – Onki Jan 16 '15 at 12:52
  • Make sure the tables are properly indexed (check with `desc`). This will make a huge difference. Also see if anything can be cached. – ethrbunny Jan 16 '15 at 13:00

1 Answers1

0

Option 1 is essentially useless because you will have to write a huge amount of code to decide which tables to use.

Option 2: PARTITIONing rarely improves performance over what you can get from INDEXes.

So, Option 3: Let's see a typical query and the SHOW CREATE TABLEs. Then let's see if we can optimize the query. It may be as simple as putting the datetime at the end of some of the existing indexes.

Rick James
  • 135,179
  • 13
  • 127
  • 222