Utiliser SQL pour renvoyer une chaîne JSON

C'est une "meilleure pratique" en question. Nous avons des discussions à l'interne sur ce sujet et que vous souhaitez obtenir des commentaires d'un public plus large.

J'ai besoin de stocker mes données dans un traditionnel MS SQL Server table normale avec des colonnes et des lignes. J'ai parfois besoin de retourner un DataTable pour mon application web, et d'autres fois j'ai besoin de revenir un JSON chaîne.

Actuellement, je retourne à la table de la couche du milieu et de l'analyser dans un JSON chaîne. Cela semble bien fonctionner pour la plupart, mais parfois prendre du temps sur les grands ensembles de données (analyse des données, pas de retour de la table).

J'envisage de réviser les procédures stockées de manière sélective retour d'un DataTable ou un JSON chaîne. Je voudrais simplement ajouter un @isJson bit paramètre à la SP.

Si l'utilisateur voulait la chaîne au lieu de la table de la SP à l'exécution d'une requête comme ceci:

DECLARE @result varchar(MAX)
SELECT @result = COALESCE(@results ',', '') + '{id:"' + colId + '",name:"' + colName + '"}'
    FROM MyTable
SELECT @result

Ce produit quelque chose comme ce qui suit:

{id:"1342",name:"row1"},{id:"3424",name:"row2"}

Bien sûr, l'utilisateur peut également obtenir de la table en passant false à la @isJson paramètre.

Je veux être clair que le stockage des données n'est pas affectée, ni du point de vue existants et d'autres processus. C'est un changement pour que les résultats de certaines procédures stockées.

Mes questions sont:

  1. Quelqu'un a déjà essayé ce, dans une grande demande? Si oui, quel a été le résultat?
  2. Quelles questions avez-vous vu/peut-on s'attendre avec cette approche?
  3. Est-il un meilleur moyen plus rapide pour aller de table en JSON dans SQL Server autre que de modifier la procédure stockée de cette façon, l'analyse ou de la chaîne dans la couche du milieu?

source d'informationauteur davids | 2012-07-16

  1. 8

    Personnellement, je pense que le meilleur endroit pour ce genre de manipulation de chaîne est dans le code du programme dans un langage expressif, qui a des fonctions et peut être compilé. Faire en T-SQL n'est pas bon. Code de programme peut avoir rapidement des fonctions qui ne évitement correcte.

    Réfléchissons un peu les choses:

    • Lorsque vous déployez de nouvelles versions de la pièces et de morceaux de votre demande, où est le meilleur endroit pour que cette fonctionnalité puisse être?

    • Si vous devez restaurer votre base de données (et de tous ses procédures stockées) qui affectent négativement quoi que ce soit? Si vous êtes le déploiement d'une nouvelle version de votre site web avant la fin, sera le JSON de conversion d'être lié dans la base de données causer des problèmes?

    • Comment allez-vous échapper les caractères correctement? Envoyez-vous des dates? Le format de la date de chaînes et comment seront-ils convertis à la Date réelle des objets à l'autre bout (si nécessaire)?

    • Comment allez-vous l'unité de test (et avec les tests automatisés!) pour prouver qu'il fonctionne correctement? Comment allez-vous le test de régression?

    • SQL Server fonctions définies par l'utilisateur peuvent être très lente. Êtes-vous content d'utiliser un lent fonction, ou pour la vitesse de pirater votre code SQL des choses comme Replace(Replace(Replace(Replace(Value, '\', '\\'), '"', '\"'), '''', '\'''), Char(13), '\n')? Qu'en Unicode, \u et \x échapper? Comment au sujet de fractionnement '</script>' en '<' + '/script>'? (Peut-être que ça ne s'applique pas, mais peut-être qu'il n'a, selon la façon dont vous utilisez votre JSON.) Votre T-SQL procédure va faire tout cela, et être réutilisable pour les différents jeux d'enregistrements, ou allez-vous le réécrire à chaque fois dans chaque SP que vous avez besoin de retour JSON?

    • Vous ne pouvez avoir qu'une SP qui doit renvoyer du JSON. Pour l'instant. Certains jours, vous pouvez avoir plus. Ensuite, si vous trouvez un bug, vous devez les fixer à deux endroits différents. Ou cinq. Ou plus.

    Il peut sembler comme vous êtes rendu les choses encore plus compliquées par le fait d'avoir la couche du milieu, faire la traduction, mais je vous promets que ça va être mieux dans le long terme. Que faire si votre produit échelles et commence à aller massivement parallèle, vous pouvez toujours jeter plus de serveurs web à moindre coût, mais vous ne pouvez pas si facilement corrigé de la base de données de ressources du serveur de saturation! Afin de ne pas rendre la base de données en faire plus qu'il ne le devrait. C'est une couche d'accès aux données, pas une couche de présentation. En faire le minimum d'effort possible. Écrire du code pour tout le reste. Vous serez heureux vous avez fait.

    Vitesse de Conseils pour la Manipulation des chaînes dans une Application Web

    1. Assurez-vous que votre site web de concaténation de chaîne de code ne souffre pas de Schlemiel l'Algorithme du Peintre. Soit écrire directement à la mémoire tampon de sortie JSON est généré (la Réponse.Écrire), ou utiliser un objet StringBuilder, ou d'écrire les parties du JSON à un tableau et Join() plus tard. Ne faites pas de la plaine de vanille concaténation pour une plus longue et la plus longue chaîne.
    2. Déréférencement des objets aussi peu que possible. Je ne sais pas votre langage côté serveur, mais si elle arrive à être en ASP Classique, ne pas utiliser de noms de champ--soit obtenir une référence à chaque champ dans une variable, ou à tout le moins utiliser integer champ d'index. Référence à un champ basé sur son nom à l'intérieur d'une boucle est (beaucoup) moins bon rendement.
    3. De l'utilisation de pré-construit des bibliothèques. Ne pas rouler votre propre lorsque vous pouvez utiliser une essayé et vrai de la bibliothèque. La Performance doit être égal ou supérieur à votre propre et (le plus important), il sera testé et corriger.
    4. Si vous allez passer du temps à faire cela, de le rendre suffisamment abstrait pour gérer la conversion d'un jeu d'enregistrements, pas seulement celui que vous avez maintenant.
    5. Utiliser le code compilé. Vous pouvez toujours obtenir le meilleur code lors de la compilation, et non interprété. Si vous identifiez que le JSON-routines de conversion sont vraiment le goulot d'étranglement (et vous DEVEZ prouver ce pour de vrai, ne faites pas de supposition) puis obtenir le code dans quelque chose qui est compilé.
    6. De réduire les longueurs de chaîne. Ce n'est pas un gros, mais si possible l'utilisation d'une lettre json noms au lieu de plusieurs lettres. Pour un géant du jeu d'enregistrements cette sera ajouter jusqu'à des économies sur les deux extrémités.
    7. De s'assurer qu'il est au Format gzip. Ce n'est pas tant du côté serveur de l'amélioration, mais je ne pouvais pas parler de JSON performance sans être complète.

    Les Dates de passage en JSON

    Ce que je recommande est d'utiliser une JSON schema (lui-même en JSON, de définir la structure du virtuel jeu d'enregistrements à suivre). Ce schéma peut être envoyé comme un en-tête pour le "jeu d'enregistrements" à suivre, ou il peut être déjà chargé dans la page (inclus dans la base des fichiers javascript) afin de ne pas avoir à être envoyé à chaque fois. Puis, dans votre JSON analyser de rappel (ou post-rappel sur la résultante finale de l'objet) regardez dans le schéma de la colonne en cours et de faire les conversions nécessaires. Vous pourriez envisager d'utiliser le format ISO depuis dans ECMAScript 5 mode strict il est censé être meilleur support de date et de votre code peut être simplifié sans avoir à changer le format des données (et un simple objet à détecter peut vous permettre d'utiliser ce code pour n'importe quel navigateur qui prend en charge):

    Date

    Les Dates sont désormais capables à la fois de l'analyse et de la sortie de l'ISO format des dates.

    Le constructeur Date maintenant essaie d'analyser la date comme si c'était la norme ISO-formaté, d'abord, se déplace ensuite sur les autres intrants qu'il accepte.

    En outre, la date d'objets ont maintenant une nouvelle .toISOString() méthode qui affiche la date dans un format ISO.
    var date = new date("2009-05-21T16:06:05.000 Z");

    d'impression( date.toISOString() );
    //2009-05-21T16:06:05.000 Z

  2. 3

    Je ne voudrais pas faire de cette façon, vous êtes en train de faire (contatenating)

    Vous pouvez essayer de créer un CLR de la fonction SQL qui utilise JSON.net et renvoie un varchar.

    Voir ici comment créer SQL CLR Fonctions:
    http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs. 80).aspx

    Quelque chose comme ceci (code non testé)

    [Microsoft.SqlServer.Server.SqlFunction]
public static SqlString MyFunctionName(int id) {
    //Put your code here (maybe find the object you want to serialize using the id passed?)
    using (var cn = new SqlConnection("context connection=true") ) {
        //get your data into an object
        var myObject = new {Name = "My Name"};
        return new SqlString(Newtonsoft.Json.JsonConvert.SerializeObject(myObject));
    }
}