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


Working with large database tables


Working with large database tables

Author
Message
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15011 Visits: 18592
Jeff Moden (5/25/2014)
Eirikur Eiriksson (5/24/2014)
Must say that I'm slightly puzzled. I have read my post over and over but I cannot find neither any implicit nor explicit suggestions of ignoring good coding or working practices. And most certainly, if that is the impression left by my post, it is not the intention. Could someone help me out here if that is the case?
Cool


It was this... particularly the bolded part...



Start by getting something working, test it against the requirements and improve if needed. This way, you can hopefully avoid another pitfall which is premature optimization.


I've become quite sensitive to how such advice is worded because it's been my very unpleasant experience that "getting something working" frequently means making all character based columns NVARCHAR(4000), all integer columns NUMERIC(18,0), using While Loops and scalar functions, single row triggers, single row non-crud stored procedures, 6 or 8 levels of stored procedures, views of views, functions of functions, testing it against 10 rows (because the While Loops they know how to write take too long to build a couple million rows of test data), and then calling it "good enough". And, when you challenge someone on why they coded in such a fashion, they give you this big ol' s4it-eating grin and say "pre-optimization is the root of all evil". The quoted saying is correct... how they've applied it is not.

I've also found the words "test it against the requirements and improve if needed" can also be misconstrued because people are rarely, if ever, given either performance or scalability requirements and that opens up a whole world of excuses for crap code. Pinch

I didn't mean for you to take it personally. I just thought it needed some clarification that folks like you and me take for granted as to what the "something" to get working should be.


Thank you Jeff for the explanation, I do see your point. Whistling
Cool
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86869 Visits: 45262
Jeff Moden (5/25/2014)
Eirikur Eiriksson (5/24/2014)
Must say that I'm slightly puzzled. I have read my post over and over but I cannot find neither any implicit nor explicit suggestions of ignoring good coding or working practices. And most certainly, if that is the impression left by my post, it is not the intention. Could someone help me out here if that is the case?
Cool


It was this... particularly the bolded part...



Start by getting something working, test it against the requirements and improve if needed. This way, you can hopefully avoid another pitfall which is premature optimization.



Same here. It had the feeling of 'just get something working, no matter how bad, no matter how hard to maintain. It can always be fixed later'. In my experience, the 'fix it later' part never happens.

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


MMartin1
MMartin1
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2745 Visits: 2031
I have seen older systems store all data as characters. In your initial ETL to the SQL Server I would encourage proper conversions (make date fields DATE or DATETIME2(n) , ... numbers as perhaps DECIMAL(m,n), .. etc). Also if more real time reporting for the accounting data is required, consider transactional replication to the warehouse from the SQL Server source. You can take monthly snapshots per account and sum up the latest snapshot to the transactions within the current month so that real time reporting aggregates do not have to consider too many rows.

----------------------------------------------------
How to post forum questions to get the best help
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8571 Visits: 7660
HildaJ (5/23/2014)

So at any given point in time one of our customers may want to know what he/her balance might be.

This is relatively simple and a good use of triggers. Maintaining a current balance table is typically expected in any high transaction financial system. You need to know if a withdrawal/payment can be made before you even include the transaction to the main table. Real time calculation every time you want to check this can be painful.

In order to do this we would have to add up all the transactions to come up with a balance for this year or even as a whole.

Why would you ever want a balance for 'this year'? You want to know if another transaction can occur, no? If you're looking to do a kind of banking report, as to the balance as per a particular date, what I recommend you look at is running totals in SSRS. This way you can pull their transactions and let the report do the day to day valuations.

We're thinking about running a ETL twice a day to extract this aggregated data to a data warehouse which would contain the total balance for each customer.

Overkill. Proper setup and indexing means that you won't require this out of date system to handle immediate balance expectations. You do this when you're trying to do BI work on 'your spending for the year, by category' type stuff... or whatever (depends on the questions you want to ask). I wouldn't do this for balance reporting.


We have very little experience in dealing with database with tons of records, any sources that I can read and learn about would be greatly appreciated.

To echo the above, 10-20 million records isn't a lot anymore. Just need to get yourself straightened out for indexing, expected usage, query techniques, etc. so you can set it up right.

EDIT: Sorry, nasty necro here. I really should have looked at the dates on the OP.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
HildaJ
HildaJ
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 571
Evil Kraig,

Since my initial post we have developed an approach to those issue. We have started developing a data warehouse where we're extracting the needed transactions. We have not implemented this solution in production but we will in the following months.

Thank you for your post.
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