I'm beginning to watch the free 30 minute training videos on Brent Ozar's site (now Brent Ozar PLF).
Todays was entitled Let’s talk about joins by Jeremiah Peschka and served as a revision as to how sql processes joins. Obviously wqtch it yourself, I'm placing the following notes here to remind myself what they are all about...
Nested Loop Join
Reads every row from each table
Fastest for small tables
Performance governed by number of rows, gets slower as row counts increase.
Merge Join
Both tables must be sorted (by the join key) for this to occur.
Performance governed by number of pages retrieved.
To optimise, look at indexes, memory & disk speed.
Hash Joins
Use Hash functions
Hash function applied to join key (turning variable length keys to fixed)
Internally SQL sorts works on small ‘buckets’ of data when comparing data for joins.
Best for large tables, tempdb is used though if memory spills to disk.
Friday, 30 March 2012
Monday, 19 March 2012
Video Notes : How to succeed in database development without really trying
How to succeed in database development without really trying is another video put together by Jeremiah Peschka. Here are my notes -
Database Developer -
‘A database developer recognizes that the database is more than an implementation detail’
Thinking about Data
Specializations
Decide if you want to be a Generalist or a Specialist?
Some people specialise in a Language, Tool, Feature
Database Specialiszations
Core Skills
Hints - How do you get there?
Recommended Books
Database Developer -
‘A database developer recognizes that the database is more than an implementation detail’
Thinking about Data
- Sets not rows – Perform operations on sets, not row by row
- Think Like you dress : in layers - use views, procedures functions to separate logic & functionality from data
- Normalize – Use a balance –' Normalise till it hurts, denoramalise till it works'
Specializations
Decide if you want to be a Generalist or a Specialist?
Some people specialise in a Language, Tool, Feature
Database Specialiszations
- Performance Tuning – Read execution plans, what is going on under covers, turn into actionable results . I/O , memory, indexing etc
- Reporting – Reporting queries different for volumes
- Object Relational Modelling – Optimizing frameworks, ORM Tools - Nhibernate etc
- Modeling – Planning
Core Skills
- Be able to spot patterns – e.g. row rather than set based processing
- Be lazy – make sure your sql is updating least number of rows possible, db doing least work possible. Use referential integrity, foreign keys, use constraints for data integrity, calculated columns Make sure business logic is in ORM.
- Think about the long term – Data choices for storage/
- Understand normalization – Join decisions
- ETL Basics – Transferring & Processing data
- Architecture – Application & Data access patterns
Hints - How do you get there?
- Practice
- Consult DBAs!
- Reading
Recommended Books
- Joe Celko's Thinking in sets
- SQL and Relational Theory: How to Write Accurate SQL Code
- Pro SQL Server 2008 Relational Database Design and Implementation
- Refactoring Databases: Evolutionary Database Design
- Grant Fitchley's SQL Server 2008 Query Performance Tuning Distilled
- Grant Fitchley's Dissecting SQL Server Execution Plans
Friday, 16 March 2012
LINK : SQL 2012 - New Certification Info
James Serra is first past the post with an easily digestable piece on the SQL 2012 certifications.
I'm happy to see there is an upgrade path ...
SQL Server 2012 : New Certification Info
Friday, 2 March 2012
Not what you want to see...
It's bad enough when your client is on SQL 2000, without this error too!
(No, I never did get to the bottom of who 'tampered' or why it was like this)
Subscribe to:
Posts (Atom)