an accumulation of data that is too large and complex for processing by traditional database management tools
obese data (noun)
an accumulation of data that is much larger and more complex than necessary
I made the second one up. After seeing lots of examples of obese databases.
In short, obese databases' effective size is much smaller than their actual size, e.g., a database that consumes 1TB of space, but has only 1GB of data. Further, the schema / architecture / model / framework / product / system / process is more complex than necessary.
Databases that have these problems:
they are slow
they could be a fraction of the size that they are
their growth rate is higher than it should be (GBs/day rather than Kbs/day)
their operations (backup, batch load, maintenance) take much longer than necessary
they are inflexible, e.g., not easily adaptable to solve a new problem, write a new report, add a new feature, etc.
All these problems above are leading people to think that they have big data, when they really have obese data.
Obese data might be viewed as a "database anti-pattern".
Things that contribute to obese data in the context of databases:
data types that are larger than they need to be
bigint that could be int
nchar that could be char
guid that could be int
datetime2 that could be date
char that could be varchar
char(50) that could be char(2)
unused columns
duplicate indexes
indexes that are never used
full-factorial indexing :-)
an index for every single column in the table
an index for every two-column combination in the table
etc.
data that should be archived
leftover staging data
redundant data
large numbers of NULL values
inappropriate schema
not "normalized" enough
too "normalized"
missing or inappropriate maintenance operations
hourly dbcc dbreindex to fillfactor 100% on a write-heavy table
A short video covering the IN operator in the WHERE clause within a SQL SELECT statement.
For beginners. Simple WHERE clauses using IN.
Here is the SQL that goes with the demo.
-- Database by Doug
-- Douglas Kline
-- 1/20/2016
-- Using the IN operator
USE northwind
-- refresher
-- here's a simple SELECT statement with a simple WHERE clause
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE UnitPrice > 10
ORDER BY ProductID
-- recall that the portion in the WHERE clause is a logical expression
-- the logical expression is evaluated for each record
-- records that evaluate (Unitprice > 10) as True (1) are included
-- records that evaluate (Unitprice > 10) as False (0) are not included
-- here's a simple IN example
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID IN (2, 4)
ORDER BY ProductID
-- all records that have a CategoryID of either 2 or 4 are included in the results
-- so the above could be equivalently rephrased as
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID = 2
OR CategoryID = 4
ORDER BY ProductID
-- but this can be inconvenient and verbose if there are lots of desired values, like this:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
ORDER BY ProductID
-- which would be more succinctly phrased as
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID IN (2, 4, 5, 8, 67, 101)
ORDER BY ProductID
-- here's another example that can lead to bugs:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
AND UnitPrice > 10
ORDER BY ProductID
-- note that the record with ProductID 13, 'Konbu' has a unitprice < 6
-- since the ORs and ANDs are evaluated left-to-right, the Unitprice > 10 has no effect
-- so parentheses are needed to control the order of operations, like this:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE (
CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
)
AND UnitPrice > 10
ORDER BY ProductID
-- this is MUCH clearer, and less conducive to bugs, phrased with IN
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID IN (2, 4, 5, 8, 67, 101)
AND UnitPrice > 10
ORDER BY ProductID
-- here's another way that IN is very convenient
-- back to the long example of ORs
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
ORDER BY ProductID
-- what if we want CategoryIDs NOT equal to 2, 4, 5, etc
-- this doesn't work:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE NOT
CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
ORDER BY ProductID
-- note the categoryID = 8 for productID=10
-- so we would need parentheses like this:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE NOT
(
CategoryID = 2
OR CategoryID = 4
OR CategoryID = 5
OR CategoryID = 8
OR CategoryID = 67
OR CategoryID = 101
)
ORDER BY ProductID
-- or more verbosely, but without parentheses
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE NOT CategoryID = 2
AND NOT CategoryID = 4
AND NOT CategoryID = 5
AND NOT CategoryID = 8
AND NOT CategoryID = 67
AND NOT CategoryID = 101
ORDER BY ProductID
-- so, again, this is much clearer and less prone to bugs phrase with IN:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE CategoryID NOT IN (2, 4, 5, 8, 67, 101)
ORDER BY ProductID
-- or equivalently
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE NOT CategoryID IN (2, 4, 5, 8, 67, 101)
ORDER BY ProductID
-- technically, 'IN' is comparison operator, like '=' or '<'
-- a phrase like '7 > 8' compares the value on the left with the value on the right
-- and returns a true or false
-- a phrase like '7 IN (4, 5, 8) compares the value on the left with
-- the list of values on the right, and returns a true or false
-- IN can be used with other data types:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE ProductName IN ('Chai', 'Tofu', 'Alice Mutton')
ORDER BY ProductID
-- for character based items, the above works,
-- but you can't combine LIKE and IN, by including LIKE wildcards:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE ProductName IN ('Chai%', 'Tofu', 'Alice Mutton')
ORDER BY ProductID
-- the above doesn't work - % is interpreted as a regular character, not a wildcard for matching
-- here's one using dates
SELECT *
FROM Employees
WHERE BirthDate IN ('19481208', '19520219')
-- another very cool feature of the IN operator,
-- is the ability to dynamically create the list of values
-- with a SELECT statement
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE ProductName IN ( SELECT ProductName
FROM Products
WHERE unitprice > 10)
ORDER BY ProductID
-- of course, this is a very simple example,
-- and it would be much more clearer to phrase it like this:
SELECT ProductID,
CategoryID,
SupplierID,
ProductName,
UnitPrice
FROM Products
WHERE unitprice > 10
ORDER BY ProductID
-- but the idea is very powerful
-- and allows for greater flexibility for phrasing your SQL
-- in some cases, a sub query with IN is the clearest way to state what you intend
-- in summary
-- IN is a logical operator - it returns True/False (1/0)
-- IN is sometimes used to replace a series of logical expressions connected with ORs
-- IN operates on a reference value and a list of values
-- IN returns True(1) if the reference value is *in* the list of values
-- IN - the list of values can be the result of a SELECT statement
-- Database by Doug
-- Douglas Kline
-- 1/20/2016
-- Using the IN operator
A video investigating the relative performance of network, disk IO, and CPU on SQL Server.
Here is the SQL that goes with the demo.
-- Database by Doug
-- 10/8/2015
-- What's Slow Demo?
-- for database performance
-- There are 3 basic items to worry about:
-- * IO
-- * Network
-- * CPU
-- I see many people focus on them in this order:
-- 1 - CPU
-- 2 - IO
-- 3 - network
-- this demo should show that you should focus on them in this order:
-- 1 - network
-- 2 - IO
-- 3 - CPU
-- part 1: network vs IO
use referencedb
SET STATISTICS IO ON
SELECT *
FROM Person
-- about 53 seconds
-- while this runs, here's some basics about the setup:
-- SQL Instance running as a virtual machine, no idea about the storage, general purpose
-- synthetic data with reasonable data distributions
-- 1.3M records
-- average record size is about 456 bytes
-- zero fragmentation
-- total table size about 587MB
-- some bogus fields for experimentation
-- no non-clustered indexes
--CREATE TABLE [dbo].[Person](
-- [ID] [int] IDENTITY(1,1) NOT NULL,
-- [salutory] [nvarchar](15) NULL,
-- [firstName] [nvarchar](25) NULL,
-- [middleInitial] [nchar](1) NULL,
-- [lastName] [nvarchar](25) NOT NULL,
-- [nameSuffix] [nvarchar](8) NULL,
-- [email] [nvarchar](255) NULL,
-- [entryDate] [datetime] NOT NULL CONSTRAINT [DF_Person_entryDate] DEFAULT (getdate()),
-- [lastUpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Person_lastUpdateDate] DEFAULT (getdate()),
-- [weight] [float] NULL,
-- [gender] [char](1) NULL,
-- [dateOfBirth] [date] NULL,
-- [bogusGUID] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_Person_bogusGUID] DEFAULT (newid()),
-- [bogusGUID2] [uniqueidentifier] NULL CONSTRAINT [DF_Person_bogusGUID2] DEFAULT (newid()),
-- [bogusChar] [char](100) NOT NULL CONSTRAINT [DF_Person_bogusChar] DEFAULT ('fred'),
-- [bogusNChar] [nchar](100) NOT NULL CONSTRAINT [DF_Person_bogusNChar] DEFAULT (N'george'),
-- CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [ID] ASC)
--) ON [PRIMARY]
-- ok, so what about this?
SELECT TOP 1 ID
FROM Person
-- one record, one value, 4 bytes
-- 0 seconds - the time for a round trip
-- so what is slow?
SELECT ID
FROM Person
-- 1.3M records, 4 bytes each
-- about 4 seconds
SELECT ID, ID
FROM Person
-- 1.3M records, 8 bytes each
-- about 5 seconds
SELECT bogusGuid
FROM Person
-- 1.3M records, 16 bytes each
-- about 5 seconds
SELECT bogusGUID,
bogusGUID2
FROM Person
-- 1.3M records, 32 bytes each
-- about 6 seconds
SELECT 75804 * 8/1024
SELECT bogusGUID,
bogusGUID2,
'1234567890'
FROM Person
-- 1.3M records, 42 bytes each
-- about seconds
SELECT bogusGUID,
bogusGUID2,
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
FROM Person
-- 1.3M records, 132 bytes each
-- about 16 seconds
SELECT bogusGUID,
bogusGUID2,
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890',
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'
FROM Person
-- 1.3M records, 232 bytes each
-- about 27 seconds
-- keep going, and you get a graph that looks like ...
-- but you are cheating - you have all your data in memory...
-- now starve the db server for memory
/**
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 256;
GO
RECONFIGURE;
GO
**/
SELECT ID
FROM Person
-- 1.3M records, 4 bytes each
-- about 4 seconds
-- network is slower than IO
-- even with physical IO
-- **************************************************
/**
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2147483647;
GO
RECONFIGURE;
GO
**/
-- part 2: IO vs CPU
SELECT AVG(weight)
FROM Person
-- 1.3M records, but only 8 bytes back
-- about 0 seconds
-- can we make it worse?
-- can we make the CPU worse?
SELECT weight
FROM Person
-- 1.3M records, 8 bytes back per record
-- 4 seconds
SELECT MAX(weight)
FROM Person
-- about 0 seconds
SELECT AVG(LOG(weight/1.7))
FROM Person
-- 1.3M records, 8 bytes back
-- about 0 seconds
SELECT AVG(SQRT(LOG(weight/1.7)))
FROM Person
-- about 0 seconds
SELECT AVG(SIN(SQRT(LOG(weight/1.7))))
FROM Person
-- about 0 seconds
SELECT AVG(SIN(SQRT(10+3.3*LOG(weight/1.7))))
FROM Person
-- about 1 seconds
SELECT AVG(SIN(SQRT(10+3.3*LOG(weight/1.7)))*PI())
FROM Person
-- about 1 seconds
-- taking just the average out
SELECT (SIN(SQRT(10+3.3*LOG(weight/1.7)))*PI())
FROM Person
-- 4 seconds
-- you can do tons of calculations, and not impact speed
-- summary: focus on items in this order
-- 1 - network
-- 2 - IO
-- 3 - CPU
-- caveats:
-- your mileage may vary
-- use your own judgement
-- this is the order in which I do investigations on a new database
-- end of what's slow
A short video covering the JOINs within a SQL SELECT statement.
For beginners. How to show columns from more than one table in a single SELECT statement. Covers joining with the WHERE clause and the JOIN statement, and some best practices.
Here is the SQL that goes with the demo.
-- Database by Doug
-- Douglas Kline
-- 10/1/2015
-- Introduction to JOINs
USE Northwind
-- suppose we want to look at our products
-- and what categories they are in
SELECT *
FROM Products
-- focus more on just a few columns
SELECT ProductID,
ProductName,
CategoryID
FROM Products
ORDER BY ProductID
-- so what category is CategoryID 1? 4?
SELECT *
FROM Categories
-- focus in on the categoryName
SELECT CategoryID,
CategoryName
FROM Categories
-- great, categoryID=1 is 'Beverages'
-- categoryID=4 is 'Dairy Products'
-- we used the CategoryID field in the Products table
-- to "look up" the name of the Category
-- but we don't want to do that for lots of records
SELECT ProductID,
ProductName,
CategoryID
FROM Products
ORDER BY ProductID
-- why not just add the CategoryName field to the SELECT clause?
SELECT ProductID,
ProductName,
CategoryID,
CategoryName
FROM Products
ORDER BY ProductID
-- CategoryName is not a field in the Products table
-- OK, let's add the Categories table to the FROM statement
SELECT ProductID,
ProductName,
CategoryID,
CategoryName
FROM Products,
Categories
ORDER BY ProductID
-- that got rid of the syntax error on CategoryName
-- but now CategoryID is ambiguous??
-- look at the columns list for each table
-- there's a CategoryID in both the Products table
-- and the Products table
-- so, we need to be more specific - which one do we mean
-- here's how
SELECT ProductID,
ProductName,
Products.CategoryID,
CategoryName
FROM Products,
Categories
ORDER BY ProductID
-- great, now we don't have any syntax errors
-- but we have 702 rows?
-- where is that coming from?
SELECT COUNT(CategoryID) AS [CategoryCount]
FROM Categories
-- 9 categories
SELECT COUNT(ProductID) AS [ProductCount]
FROM Products
-- 78 products
SELECT 9 * 78
-- 702 - just like the number of records
SELECT ProductID,
ProductName,
Products.CategoryID,
CategoryName
FROM Products,
Categories
ORDER BY ProductID
-- let's look closer
-- adding the categoryID from the Categories table
SELECT ProductID,
ProductName,
Products.CategoryID,
Categories.CategoryID,
CategoryName
FROM Products,
Categories
ORDER BY ProductID
-- that's odd, why is chai being shown next to the wrong category?
-- in fact, it's being shown next to *every* category
-- all products are shown next to every category
-- because we haven't stated how the products and categories should be related
-- the database engine returns all possible combinations of products and categories
-- this is called a CROSS JOIN
-- this is sometime what we want, but not usually
-- so , how do we limit the returned records below
-- so that only the ones with matching categoryIDs are kept?
-- using the WHERE clause
--
SELECT ProductID,
ProductName,
Products.CategoryID,
Categories.CategoryID,
CategoryName
FROM Products,
Categories
WHERE Products.CategoryID = Categories.CategoryID -- ********
ORDER BY ProductID
-- this looks a lot better
-- chai has a categoryID = 1, and it is shown next to the category with categoryID = 1
-- but the record count is 77?
-- aren't there 78 products?
-- looking at the products again...
SELECT ProductID,
ProductName,
CategoryID
FROM Products
ORDER BY ProductID
-- scrolling down, we can see that Dougs Mustard has a NULL categoryID
-- and looking back our combined query
SELECT ProductID,
ProductName,
Products.CategoryID,
Categories.CategoryID,
CategoryName
FROM Products,
Categories
WHERE Products.CategoryID = Categories.CategoryID -- ********
ORDER BY ProductID
-- scrolling down, note that Dougs Mustard doesn't appear
-- because this Products.CategoryID = Categories.CategoryID
-- is essentially this: NULL = Categories.CategoryID
-- which is false (NULLs in another video)
-- ok, looks like we have the logic right with this:
SELECT ProductID,
ProductName,
Products.CategoryID,
Categories.CategoryID,
CategoryName
FROM Products,
Categories
WHERE Products.CategoryID = Categories.CategoryID
ORDER BY ProductID
-- but humans don't really want to see the CAtegoryID
-- so we can remove them
SELECT ProductID,
ProductName,
CategoryName
FROM Products,
Categories
WHERE Products.CategoryID = Categories.CategoryID
ORDER BY ProductID
-- the above is called a JOIN of the Products and Categories table
-- the above is stated as a constraint in the WHERE clause
-- but it is a special kind of a constraint
-- between the Primary Key of one table, and the Foreign Key in another table
-- so one way to look at a JOIN is as a constraint
-- you make all possible combinations of the records from two tables
-- then remove the ones that don't have equal PK--FK combinations
-- another way to view a JOIN is as a specific combination of tables
-- combine the tables in a specific way to create a specific set of records
-- then show fields from the combined set of records
-- this is done in the FROM clause
SELECT ProductID,
ProductName,
CategoryName
FROM Products JOIN Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY ProductID
-- this is logically equivalent
-- it will always return the same records as the WHERE clause version
-- I like to format it like this:
SELECT ProductID,
ProductName,
CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY ProductID
-- I suggest you learn both ways
-- so you can read either way when you encounter it
-- also, sometimes one way is clearer than another in a certain context
-- the JOIN syntax signals your *intention* better than the WHERE clause
-- and generally organizes your statement better
-- conceptually, it is an binary operator between two tables,
-- with an ON clause to specify details that the operator will use
-- it is *not* a list of tables (separated by commas),
-- but an expression that states how to combine the tables
-- one last thing
-- safe SQL coding practice
-- right now the statement below works fine
-- it is syntactically correct
SELECT ProductID,
ProductName,
CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY ProductID
-- but what would happen if in the future
-- someone put a CategoryName field in the Products table?
-- then there would be a CategoryName field in both Products and Categories
-- they *should* be able to do that without breaking code
-- even if it doesn't make sense
-- then categoryName would become ambiguous and the code would not execute
-- so how do we prevent that possible future bug?
-- by *qualifying* every field like this
SELECT Products.ProductID,
Products.ProductName,
Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY Products.ProductID
-- safe coding rule:
-- whenever there is more than one table
-- fully qualify every field in every clause: SELECT, FROM, WHERE, ORDER BY, etc.
-- intro to JOIN...
