I have one table with 800 million rows. Per month an extra 12 million records are added, all a summary of the accounts. The table is also farily wide with 25 numeric(11,2) columns.
Im have very poor performance on this table. The queires Im doing it to compare the one month with another month and to subtract the one month's columns from the other.
What I did for testing was to extract the 2 months data into temp table and run the test from there which work well for testing.
Now for the real world
If I start quering the main table and compare the 2 months it takes about 5 minutes for one month to compare. However I need to test 24 months and for the 24 months each of them will have 10 months to test against.
Yesterday I created a seperate database and created tables for each month. This works well, but now Im sitting with table names with a yyyy_mm appended to the table name.
I would like to automate the queries for the test, but Im totally stuck
My questions are the following
1) Any ideas on how to speed up the main fact table? My clustered index is on the year first then the account number. Ive even tried non-clustered, but with no luck.
or if I cant get the index on the main table to speed up
2) is there a better way of joinging the seperate table on the new database into one, say maybe via a view, in order to use only one table name for the testing. I dont want to use dynamic SQL as my test query is already over 300 lines long.
Any ideas are welcome