Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Working with large database tables Expand / Collapse
Author
Message
Posted Sunday, May 25, 2014 3:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 2,533, Visits: 7,094
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?


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.

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.




Post #1574368
Posted Sunday, May 25, 2014 7:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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?


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 2008, MVP
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

Post #1574384
Posted Wednesday, July 16, 2014 6:29 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:42 PM
Points: 459, Visits: 1,065
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.
Post #1593343
Posted Wednesday, July 16, 2014 7:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1593352
Posted Thursday, July 17, 2014 7:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:22 AM
Points: 165, Visits: 496
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.

Post #1593576
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse