SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Huge table.and poor performance.


Huge table.and poor performance.

Author
Message
chris.stuart
chris.stuart
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4026 Visits: 849
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)SSC Guru (835K reputation)

Group: General Forum Members
Points: 835592 Visits: 48503
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
ZZartin
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21998 Visits: 15923
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
TheSQLGuru
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113441 Visits: 8972
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
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4281 Visits: 888
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
Jeff Moden
SSC Guru
SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)

Group: General Forum Members
Points: 786185 Visits: 45917
The OP has left the building. BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90071 Visits: 13672
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search