Huge table.and poor performance.

Question

Huge table.and poor performance.

chris.stuart

SSCarpal Tunnel

Points: 4965
More actions
February 13, 2018 at 6:47 am

#336097

Hi
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
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the queries that are slow, along with their actual execution plans? The ones against the full table that take ~5 min.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

chris.stuart - Tuesday, February 13, 2018 6:47 AM
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

What did you try for the non clustered index? If you are trying to compare month over month data I would imagine you would want at least the year + month in the index if not just the entire date.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I have run 500+ MILLION row (uncompressed) table queries on my LAPTOP, taking < 10 seconds each time.

My experience says that indexing is not even close to the main cause of your performance issues here. Talk to me about your server configuration (CPU, RAM and ESPECIALLY IO PERFORMANCE). Note I didn't say "your disks" or "the server's IO". I have a single disk in my my laptop, but I get over 2 GIGABYTES PER SECOND of sequential IO off of it. How much sequential IO can your server do per second, and at what latency?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Arsh SSCertifiable Points: 6354 More actions · Answer 4

Looking at the code would give a much better idea about what's happening . Hence please share the query here. Most of the times the real culprit turns out to be badly written code.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

The OP has left the building. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

My clustered index is on the year first then the account number.

Sounds like year, month and date are stored in separate columns.
If that's the truth, then yes, indexing is not the only cause of the bad performance.

_____________
Code for TallyGenerator