Gestion des performances

Ce guide explique certaines des fonctionnalités que vous pouvez utiliser pour optimiser les performances des requêtes N1QL.

Utilisation des variable dans les requêtes

cbq> \SET -args ["Île-de-France", "Paris"];
cbq> SELECT COUNT(*) FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE region = $1 AND ville = $2;
{
    "requestID": "99a87115-e9b6-48f8-952e-4f2d56070fc9",
    "signature": {
        "$1": "number"
    },
    "results": [
    {
        "$1": 7
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "73.326024ms",
        "executionTime": "73.247239ms",
        "resultCount": 1,
        "resultSize": 23,
        "serviceLoad": 12
    }
}

cbq> \SET -$ville "Paris";
cbq> SELECT COUNT(*) FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE ville = $ville;
{
    "requestID": "1945c751-1c81-44ee-b2e3-4d262e96373c",
    "signature": {
        "$1": "number"
    },
    "results": [
    {
        "$1": 7
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "22.810682ms",
        "executionTime": "22.698645ms",
        "resultCount": 1,
        "resultSize": 23,
        "serviceLoad": 12
    }
}

Utilisation des Prepared Statement

cbq> PREPARE FORCE queryName AS SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE prenom = $1;

cbq> EXECUTE queryName USING ["Rachid"];
{
    "requestID": "48ae0adf-2850-4a94-9ff5-7a6a72c654de",
    "signature": "{\"*\":\"*\"}",
    "results": [
    {
        "etudiant": {
            "classe": [
                "Informatique",
                "Math"
            ],
            "email": "rachid.benhocine@example.com",
            "nom": "Benhocine",
            "prenom": "Rachid",
            "region": "Île-de-France",
            "ville": "Paris"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "14.406081ms",
        "executionTime": "14.338026ms",
        "resultCount": 1,
        "resultSize": 319,
        "serviceLoad": 12
    }
}

Par défaut, couchbase ne crée pas de Prepared Statement pour toutes les requêtes exécutées sauf si on modifie le paramètre Auto-Prepare à TRUE. Cela peut être intéressant de le positionner si on lance plusieurs fois la même requêtes mais avec des valeurs différentes pour la clause WHERE.

Index Advisor

Pour obtenir des recommandations d'index pour une seule requête, vous pouvez utiliser Index Advisor.

cbq> ADVISE SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE email = "rachid.benhocine@example.com";
{
    "requestID": "825edae5-c45b-44f2-a77d-63bf03e8b5de",
    "signature": "json",
    "results": [
    {
        "#operator": "Advise",
        "advice": {
            "#operator": "IndexAdvice",
            "adviseinfo": {
                "current_indexes": [
                    {
                        "index_statement": "CREATE PRIMARY INDEX #primary ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`",
                        "keyspace_alias": "etudiant"
                    }
                ],
                "recommended_indexes": {
                    "indexes": [
                        {
                            "index_statement": "CREATE INDEX adv_email ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`(`email`)",
                            "keyspace_alias": "etudiant",
                            "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."
                        }
                    ]
                }
            }
        },
        "query": "SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE email = \"rachid.benhocine@example.com\";"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "26.144736ms",
        "executionTime": "26.052925ms",
        "resultCount": 1,
        "resultSize": 1032,
        "serviceLoad": 12
    }
}

La fonction ADVISOR() vous permet également d'obtenir des recommandations d'index pour plusieurs requêtes.

cbq> SELECT ADVISOR((SELECT RAW statement FROM system:completed_requests)) AS Recommandations;

On peut utiliser aussi la fonction ADVISOR() pour avoir des recommandation sur toutes les nouvelles requête dans la même session.

cbq> SELECT ADVISOR({"action": "start", "response": "0s", "duration": "10m"});
{
    "requestID": "2c7d4f51-87fd-430a-8131-1a1581440cf7",
    "signature": {
        "$1": "object"
    },
    "results": [
    {
        "$1": {
            "session": "78f36d2b-d154-4c06-9064-1c0d080271e2"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.339037ms",
        "executionTime": "2.280346ms",
        "resultCount": 1,
        "resultSize": 95,
        "serviceLoad": 12
    }
}

cbq> SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE email = "rachid.benhocine@example.com";
{
    "requestID": "116c9790-def2-4b30-a011-36ff6c5b21d1",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "etudiant": {
            "classe": [
                "Informatique",
                "Math"
            ],
            "email": "rachid.benhocine@example.com",
            "nom": "Benhocine",
            "prenom": "Rachid",
            "region": "Île-de-France",
            "ville": "Paris"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.983365ms",
        "executionTime": "4.897802ms",
        "resultCount": 1,
        "resultSize": 319,
        "serviceLoad": 12
    }
}
cbq> SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE LOWER(nom) = "benhocine";
{
    "requestID": "53060e53-ca93-4b07-90be-c27cd7aea77b",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "etudiant": {
            "classe": [
                "Informatique",
                "Math"
            ],
            "email": "rachid.benhocine@example.com",
            "nom": "Benhocine",
            "prenom": "Rachid",
            "region": "Île-de-France",
            "ville": "Paris"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.778809ms",
        "executionTime": "9.641502ms",
        "resultCount": 1,
        "resultSize": 319,
        "serviceLoad": 12
    }

cbq> SELECT ADVISOR({"action": "stop", "session": "78f36d2b-d154-4c06-9064-1c0d080271e2"});
{
    "requestID": "a1c8caee-364f-41bc-b258-91e53af378b1",
    "signature": {
        "$1": "object"
    },
    "results": [
    {
        "$1": []
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "51.760736ms",
        "executionTime": "51.693071ms",
        "resultCount": 1,
        "resultSize": 24,
        "serviceLoad": 12
    }
}

cbq> SELECT ADVISOR({"action": "get", "session": "78f36d2b-d154-4c06-9064-1c0d080271e2"});
{
    "requestID": "95f201cf-3778-4fbd-9943-6f94568d0554",
    "signature": {
        "$1": "object"
    },
    "results": [
    {
        "$1": [
            [
                {
                    "current_used_indexes": [
                        {
                            "index": "CREATE PRIMARY INDEX #primary ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`",
                            "statements": [
                                {
                                    "run_count": 1,
                                    "statement": "SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE email = \"rachid.benhocine@example.com\";"
                                }
                            ]
                        },
                        {
                            "index": "CREATE INDEX etudiant_idx1 ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`(`nom`)",
                            "statements": [
                                {
                                    "run_count": 1,
                                    "statement": "SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE LOWER(nom) = \"benhocine\";"
                                }
                            ]
                        }
                    ],
                    "recommended_indexes": [
                        {
                            "index": "CREATE INDEX adv_email ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`(`email`)",
                            "statements": [
                                {
                                    "run_count": 1,
                                    "statement": "SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE email = \"rachid.benhocine@example.com\";"
                                }
                            ]
                        },
                        {
                            "index": "CREATE INDEX adv_lower_nom ON `default`:`ecole-bucket`.`ecole-scope`.`etudiant`(lower(`nom`))",
                            "statements": [
                                {
                                    "run_count": 1,
                                    "statement": "SELECT * FROM `ecole-bucket`.`ecole-scope`.etudiant WHERE LOWER(nom) = \"benhocine\";"
                                }
                            ]
                        }
                    ]
                }
            ]
        ]
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.169798ms",
        "executionTime": "3.107561ms",
        "resultCount": 1,
        "resultSize": 2304,
        "serviceLoad": 12
    }
}