Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Limit Returned Data

Keeping It Small

This part is easy to understand, but you don't necessarily think of it right away. Limit the data your queries are returning. There are 2 concepts that I want to impart to you.



Limit the Data in Join Statements

Let's review the below query.

SELECT *
FROM InventoryItems i
JOIN Warehouses w
  ON w.PK_Warehouses = i.FK_Warehouses
JOIN Cities c
  ON c.PK_Cities = w.FK_Cities
JOIN WarehouseSizes whs
  ON whs.PK_WarehouseSizes = w.FK_WarehouseSizes
JOIN WarehouseEmployees whe
  ON whe.PK_WarehouseEmployees = w.FK_WarehouseEmployees
WHERE i.Cost >= $1000

Now we should pretend that all of these tables have 500,000 rows. One thing we could do to prevent joining on all the data is to limit the amount being returned further up in the query. As seen below:

SELECT *
FROM InventoryItems i
JOIN Warehouses w
  ON w.PK_Warehouses = i.FK_Warehouses
  AND i.Cost >= $1000
JOIN Cities c
  ON c.PK_Cities = w.FK_Cities
JOIN WarehouseSizes whs
  ON whs.PK_WarehouseSizes = w.FK_WarehouseSizes
JOIN WarehouseEmployees whe
  ON whe.PK_WarehouseEmployees = w.FK_WarehouseEmployees

Now this is not always applicable, but it is a concept that you should be thinking about on those queries that have 10-20 joins against large tables (we will get to breaking down large queries in another section).



Don't SELECT * Unless You Need Too

There are several instances when it can just be easier to select all of the columns from a table, rather than just the ones you need. Here is an example using the above query:

SELECT *
INTO #Temp
FROM InventoryItems i
JOIN Warehouses w
  ON w.PK_Warehouses = i.FK_Warehouses
JOIN Cities c
  ON c.PK_Cities = w.FK_Cities
JOIN WarehouseSizes whs
  ON whs.PK_WarehouseSizes = w.FK_WarehouseSizes
JOIN WarehouseEmployees whe
  ON whe.PK_WarehouseEmployees = w.FK_WarehouseEmployees

Does the temp table really need to contain all of the columns? If there are 50,000 rows being returned, it can help speed things up if you only select out the columns needed. Especially if it is only 5 of 100 columns!

Think about it in volumes (I just made up an arbitrary number for the size of a field):
50,000 rows x 100 columns x 1Byte field size = 5,000KB
OR
50,000 rows x 5 columns x 1Byte field size = 250KB





Comments

Name:
Title:   
Commenting Currently Disabled


Starleigh
8/3/2011 6:11:52 PM



In awe of that asnwer! Really cool!