% Django y bases de datos - el lado B
% Javier Eduardo Rojas Romero

# El ORM de Django y la BD

## Temas

### N + 1 consultas

### Paginación

### El costo de un SELECT *

## N + 1 consultas

<table>
<tr>
<td>
```{.python}
class User(models.Model):
   name = models.CharField()
```
</td>
<td>
```{.python}
class Telephone(models.Model):
   number = models.CharField()
   user = models.ForeignKey('User')
```
</td>
</tr>
</table>

```{.python}
phones = Telephone.objects.filter(number__startswith="310")[:50]
for phone in phones:
    print(phone.user)
```

## N + 1 consultas

DEMO TIME

## Cómo evitar N + 1 consultas

### select\_related

```{.python}
Telephone.objects.filter(...).select_related('user')
```

```{.sql}
SELECT "main_telephone"."id",
       "main_telephone"."number",
       "main_telephone"."user_id",
       "main_user"."id",
       "main_user"."name"
FROM "main_telephone"
INNER JOIN "main_user" ON ("main_telephone"."user_id" = "main_user"."id")
WHERE "main_telephone"."number" LIKE '310%'
```

## Cómo evitar N + 1 consultas

### prefetch\_related

```{.python}
list(User.objects.all().prefetch_related('telephone_set')[:50])
```

```{.sql}
SELECT "main_user"."id",
       "main_user"."name"
FROM "main_user" LIMIT 50 
```

```{.sql}

SELECT "main_telephone"."id",
       "main_telephone"."number",
       "main_telephone"."user_id"
FROM "main_telephone"
WHERE "main_telephone"."user_id" IN (257402,
                                     257403,
                                     ...
                                     257450,
                                     257451)
```

## Paginación

```{.sql}
SELECT *
FROM product
ORDER BY name DESC
OFFSET 12500 LIMIT 100
```

> …the rows are first sorted according to the \<order by clause\> and then
> limited by dropping the number of rows specified in the \<result offset clause\>
> from the beginning…

[SQL:2011, Part 2, §4.15.3 Derived tables](http://www.wiscorp.com/sql20nn.zip)

----------------------

```{.sql}
SELECT *
FROM product
ORDER BY name DESC
LIMIT 100
```

![](offset-1.png)

----------------------

```{.sql}
SELECT *
FROM product
ORDER BY name DESC
OFFSET 100 LIMIT 100
```

![](offset-2.png)

----------------------

```{.sql}
SELECT *
FROM product
ORDER BY name DESC
OFFSET 200 LIMIT 100
```

![](offset-3.png)

----------------------

```{.sql}
SELECT *
FROM product
ORDER BY name DESC
OFFSET 300 LIMIT 100
```

![](offset-4.png)

----------------------

![](offset-1.png)
![](offset-2.png)

![](offset-3.png)
![](offset-4.png)

[Fuente imágenes](http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way)


## Paginación basada en llaves/índices

-------------------------------


```{.sql}
SELECT *
FROM product
ORDER BY name DESC
LIMIT 100
```

![](pagkeys-1.png)

----------------------

```{.sql}
SELECT *
FROM product
WHERE name > ${last_name_previous_page}
ORDER BY name DESC
LIMIT 100
```

![](pagkeys-2.png)

----------------------

```{.sql}
SELECT *
FROM product
WHERE name > ${last_name_previous_page}
ORDER BY name DESC
LIMIT 100
```

![](pagkeys-3.png)

----------------------

```{.sql}
SELECT *
FROM product
WHERE name > ${last_name_previous_page}
ORDER BY name DESC
LIMIT 100
```

![](pagkeys-4.png)

----------------------

![](pagkeys-1.png)
![](pagkeys-2.png)

![](pagkeys-3.png)
![](pagkeys-4.png)

En Django: [infinite scroll pagination](https://github.com/nitely/django-infinite-scroll-pagination)

[Fuente imágenes](http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way)

## El costo de un SELECT *

![](innodb_block.png)

[Fuente imagen](http://yoshinorimatsunobu.blogspot.com.co/2010/11/handling-long-textsblobs-in-innodb-1-to.html)

## Evitando costos, desde Django

Escoger cuáles columnas traer

---------------------

### Instancias del ORM

`QuerySet.defer('html_content')`

`QuerySet.only('id', 'name')`

### Listas

`QuerySet.values('id', 'name')`

`QuerySet.values_list('id', 'name')`

# Consultas e Índices

## Temas

### Índices
### Índices más interesantes
### Costos


## Índices

Estructura de datos auxiliar que permite ubicar/seleccionar registros de una
tabla, sin tener que examinar **toda** la tabla.

  * Árboles B
  * Hashes
  * Datos multidimensionales
  * *Full text search*

## Árboles B

[DEMO TIME](https://www.cs.usfca.edu/~galles/visualization/BTree.html)

## Árboles B en bases de datos

  * O(n) -> O(log~250~(n))
  * == , \< , \>

## Índices en la práctica

DEMO TIME

Tomado de [engineering-blog.alphasights.com](http://engineering-blog.alphasights.com/how-to-stop-worrying-and-love-the-index/)

## Múltiples columnas

  * Los índices son útiles si la columna indexada tiene pocos duplicados.
  * El orden en las columnas del índice importa.
  * Dos índices por el precio de uno.

## LIKE / ILIKE

DEMO TIME


## LIKE / ILIKE

  * Los índices B permiten búsqueda con prefijos (`LIKE 'Texto%'`)
  * ¿Necesidades más avanzadas? GIN/GiST - *Full text search*

## Costos - Espacio

Un índice contiene **una copia de los datos indexados**, y apuntadores a los
registros correspondientes en la tabla.

## Costos - Mantenimiento

**Todos** los índices de una tabla deben ser actualizados al crear, actualizar^\*^ ,
o borrar registros.


# Mecanismos de control de concurrencia

## Temas

### Actualizaciones concurrentes
### Control usando bloqueo
### Control usando bloqueo optimista
### Delegar actualizaciones a la base de datos

## Actualizaciones concurrentes

<table>
<tr>
<td>
```{.python}
class Account(models.Model):
   amount = models.IntegerField()
   user = models.ForeignKey('User')
```
</td>
<td>
```{.python}
orig_value = Account.objects.get(id=1)
print(orig_value.amount)  # 200

first_request = Account.objects.get(id=1)   # 200
second_request = Account.objects.get(id=1)  # 200

first_request.amount += 300
second_request.amount += 100

first_request.save()   # in DB, amount = 500
second_request.save()  # in DB, amount = 300
```
</td>
</tr>
</table>

## SELECT FOR UPDATE

Impide modificaciones a los registros seleccionados, hasta que termine la
transacción actual.

```{.python}
from django.db import transaction
```
<table>
<tr>
<td>
```{.python}
with transaction.atomic():
    account = Account.objects\
      .select_for_update().get(id=1)
    account.amount += 300
    account.save()
```
</td>
<td>
```{.python}
with transaction.atomic():
    account = Account.objects\
      .select_for_update().get(id=1)
    # esperando ...
    # esperando ...
    account.amount += 100
    account.save()
```
</td>
</tr>
</table>

## Control usando bloqueo optimista

Estrategia: actualizar la columna del registro sólo si ésta no ha cambiado

```{.sql}
SELECT amount
FROM main_account
WHERE id = 1

-- (id: 1, amount: 200)
```

```{.sql}
UPDATE main_account
SET amount = 500
WHERE id = 1
  AND amount = 200
```

## Columna de versión de registro

Estrategia general: actualizar el registro sólo si su versión no ha cambiado:

```{.sql}
SELECT id
     , version
     , amount
FROM main_account
WHERE id = 1

-- (id: 1, amount: 200, version: 3)
```

```{.sql}
UPDATE main_account
SET amount = 500, version = version + 1
WHERE id = 1
  AND version = 3  -- versión anterior
```

## django-concurrency

https://github.com/saxix/django-concurrency

<table>
<tr>
<td>
```{.python}
from django.db import models
from concurrency.fields import IntegerVersionField

class ConcurrentModel( models.Model ):
    version = IntegerVersionField( )
    name = models.CharField(max_length=100)
```
</td>
<td>
```{.python}
a = ConcurrentModel.objects.get(pk=1)
a.name = '1'

b = ConcurrentModel.objects.get(pk=1)
b.name = '2'

a.save()
b.save()  # lanza RecordModifiedError
```
</td>
</tr>
</table>


## Delegar actualizaciones a la base de datos

```{.sql}
UPDATE main_account
SET amount = amount + 300
WHERE id = 1
```

## Desde Django

https://docs.djangoproject.com/en/1.9/ref/models/expressions/#f-expressions

```{.python}
from django.db.models import F 

Account.objects.filter(id=1).update(amount=F('amount')+300)
```

```{.sql}
UPDATE "main_account"
SET "amount" = ("main_account"."amount" + 300)
WHERE "main_account"."id" = 1
```

# Para terminar

-------------

### Para terminar

  * ¿Cuántas consultas ejecuta tu vista?
  * ¿Usan índices? ¿*sequential scans*?
  * ¿Es realmente importante comunicar cuántos registros tienes?
  * ¿Necesitas traer todas las columnas de tus registros?
  * ¿Es crítico evitar que tus registros sean actualizados **simultáneamente**?

## Gracias por su atención

![](fairy.jpg) ![](pony.png)