Archive

Posts Tagged ‘SGBDR’

SQL-Server – RANK et ROW_NUMBER

Récemment, j’ai du faire un petit exercice relativement simple : importer des données en base depuis un fichier CSV.
Avec le bulk insert, c’est assez simple :

BULK INSERT #Bulk
FROM 'C:\Temp\FichierAImporter.csv' 
WITH 
(
	FIELDTERMINATOR = ';',
	ROWTERMINATOR = '\n',
	CODEPAGE = 'ACP',
	ROWS_PER_BATCH = 35000
)

Là où ça s’est compliqué, c’est la génération des identifiants fonctionnels avant leur insertion dans les « vraies » tables.

Pour simplifier un peu, voici le cas.
Je dois importer les nouvelles adresses de clients.
J’ai deux champs : Client et Adresse qui sont chargées depuis le fichier CSV.

Mais surtout, j’ai deux identifiants fonctionnels à créer.
Le premier est un identifiant interne, de la forme « FR00000000 » dont la partie numérique est incrémentée pour chaque entrée. Le prochain identifiant est lisible depuis une table.
Le second identifiant est externe, c’est un numérique incrémenté par client.
C’est à dire que le client #1 possèdant déjà 50 adresses, la suivants sera la 51, la prochaine pour le client #2 est la 6ème.

La bonne vieille méthode, c’est de faire des boucles pour alimenter tout le bazar.
Mais comme on est dans une base de données, c’est quand même nettement mieux de faire de l’ensembliste.
C’est là que RANK et ROW_NUMBER viennent à la rescousse !

Lire la suite…

Catégories :Développement, SGBDR, Sql Server

SQL Server Profiler

Aujourd’hui, on va discuter d’un outil assez important que tous développeur qui peut être amené à faire du T-SQL (sous SQL Server) devrait connaître.
Bon, je dis ça, mais y a pas si longtemps, je ne le connaissais pas.
Mais c’est un outil ô combien pratique qui peut permettre de surveiller ce qui se passe sur une base de données et aider à résoudre des problèmes de performances. Et, accessoirement, il est plus « humainement » lisible qu’un plan d’exécution (enfin, ça, c’est mon avis…).

Bref, c’est outil, c’est SQL Server Profiler (oui, le titre spolait déjà le billet !).

Lire la suite…

Catégories :Développement, SGBDR, Sql Server

Traiter les données dans le code ou en base ?

Cela fait plusieurs fois que je vois de gros traitements de manipulation de données directement dans le code.

A chaque fois, il y a eu des variations, mais en règle générale, c’est un algorithme plus ou moins comme cela :

Récupération des données depuis la base de données.
Pour chaque ligne récupérée.
     Si le champX est égale à "variable1", alors on lance la procédure "procedure1" pour cette ligne.
     Si le champX est égale à "variable2", alors on lance la procédure "procedure2" pour cette ligne.
     Si le champX est égale à "variable3", alors on lance la procédure "procedure3" pour cette ligne.
     Sinon, on lance la procédure "procedure4" pour cette ligne.

Le tout avec du if, else if, esle (ou un switch, c’est selon), parfois d’autres boucles à l’intérieur de la première (forception, foreachception ou whileception, si je puis dire).

Comme je l’ai dis, il y a des variantes, mais cependant, si les causes sont différentes, l’effet reste le même : c’est pas performant.
Voir pire, ça occasionne des timeout vis-à-vis de la base de données.

Mais, alors, que faire ?
Le traitement ensembliste. En base de données.
Voilà, c’est tout.

Alors, je sais que parfois, la base ne doit être utilisée que pour stocker les données (sic…) et que le code métier ne doit se trouver qu’en un seul et unique endroit (sic bis…), que la base de données ne doit pas contenir d’intelligence (sic ter…).

Mais parfois (souvent ?), c’est juste très con de vouloir procéder comme cela.
Bon, l’avantage, c’est quand même que c’est tellement à la ramasse qu’optimiser n’est pas très compliqué… (en fait, l’étape « ohmondieucesthorrible » est plus longue à gérer).

Quand on a un SGBDR, il ne faut pas oublier que le G, c’est pour Gestion.
C’est autant de la gestion des base de données que de données elles-mêmes.
En somme, les SGBDR sont fait pour traiter de la données !!!

Comment on fait, du coup ?
A chaque fois que j’ai eu ce problème, il a été résolu avec des tables temporaires.
En somme, récupération des données vers une table temporaire, puis traitement des données depuis cette table en reprenant les conditions mentionnées plus haut.
Donc, j’ai du :

UPDATE #MaTable
SET ChampY = "Valeur1"
WHERE ChampX = "variable1"

Et là, miraculeusement (ou pas), au lieu de traiter les lignes une par une, le SGBDR va traiter toutes les lignes.

Après, il faut quand même réfléchir un peu à comment on peut procéder, mais il est tout à fait possible de gérer les données de cette manière.

Un exemple ?
Là, j’ai un traitement qui doit gérer 70.000 lignes.

C’est pas une volumétrie très importante, mais le traitement via code dure 20 minutes avant que la connexion à la base de données ne parte en timeout.
Avec les traitements ensemblistes, la même volume de données (c’est à dire le MÊME cas de test) dure…environ 15 secondes.

Oui, oui, 15 secondes.
Et même en purgeant le cache de SqlServer (pour info).
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Sur d’autres cas de tests, le gain de temps varie d’un facteur 1 (pour les très petits volumes) à 10 (pour les volumes plus importants et mixtes).
Le coup des 15 secondes est sur un scénario assez précis et j’avoue que…eh bien, je m’attendais pas à un tel gain ! ^^

J’ai pourtant 8 insertions de différents types (et différentes provenances) dans ma table temporaire, 18 updates différents puis insertion des données (conditionnées) dans une table physique et modification de deux autres tables (dont l’un compte plus de 67 millions d’entrée).

 
 
Alors OUI aux traitements de masse des données dans la base de données et NON aux mêmes traitements dans le code !!!

Catégories :Développement, Humeur, SGBDR

[C# SqlServer] Moteur de requêtage

Dans le cadre de mes tests unitaires, je dois exploiter une base de données.
Comprendre que testant des scénarios, je dois vérifier les données qui sont insérées en base et valider leur cohérence par rapport aux données fournies en entrée.
Accessoirement, je dois aussi exporter les données afin de pouvoir les retrouver facilement et ne pas me retrouver comme un couillon devant la base de développement nouvellement rechargée depuis la production et me dire que je voudrais bien comparer avant / après.

Dans ce billet, je vais donner le code d’un que j’ai utilisé pour exécuter mes requêtes, donc ça va être un peu brut.

Lire la suite…

Catégories :.Net, SGBDR, Sql Server

[SQLServer] Quelques tables systèmes

A l’instar du billet sur les tables systèmes de Sybase, voici un billet autour des tables systèmes de SQL Server.
Vu les éléments similaires, je vais passer assez vite, ceci dit (j’avoue, ce billet va me servir pour mémoire :)).

Pour obtenir la source d’un élément (procédure stockée, par exemple), c’est la même requête :

SELECT com.text AS TEXT 
FROM dbo.sysobjects obj 
INNER JOIN dbo.syscomments com 
    ON com.id = obj.id 
INNER JOIN dbo.sysusers users ON users.uid = users.uid
WHERE obj.name LIKE '<mon élément>' 
AND obj.type = 'P'  -- facultatif
AND users.name = 'dbo' 
ORDER BY obj.name, com.colid

Idem pour rechercher une chaîne, donc :

SELECT 
    obj.name AS 'PROC',
    com.text AS TEXT 
FROM dbo.sysobjects obj 
INNER JOIN dbo.syscomments com 
    ON com.id = obj.id 
INNER JOIN dbo.sysusers users ON users.uid = users.uid
WHERE obj.type = 'P' 
AND lower(com.text) LIKE lower('%TODO%')
AND users.name = 'dbo' 
ORDER BY obj.name, com.colid

Pour avoir des informations sur une table, la requête est un peu différente et remonte plus d’informations que celle de Sybase (comprendre que j’avais besoin de plus d’infos, donc j’ai poussé un peu le sujet !) :

SELECT cols.name AS Column_name, 
    types.name AS Type, 
    CASE WHEN cols.collation_name IS NOT NULL THEN cols.max_length ELSE cols.precision END AS Prec,
    cols.scale AS Scale,
    cols.is_identity AS Is_Identity,
    cols.is_computed AS Is_Computed,
    ISNULL(IsPrimaryKey, 0) AS Is_PrimaryKey,
    cols.is_nullable AS Is_Nullable
FROM sys.columns cols
INNER JOIN sys.types types
    ON types.system_type_id = cols.system_type_id
LEFT JOIN (
    SELECT  kcu.table_name as TABLE_NAME,
            kcu.column_name as COLUMN_NAME,
            1 AS IsPrimaryKey
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS  tc
    ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
) keys
    ON keys.COLUMN_NAME = cols.name
    AND keys.TABLE_NAME = Object_Name(cols.object_id)
WHERE Object_Name(cols.object_id) = '<TABLE>'
ORDER BY column_id

Ceci dit, le champ type de la table sysobjects comprends plus de valeurs possibles (24 en tout) que pour Sybase.
Là, je renvois directement vers la site de la MSDN pour plus d’informations : table sysobjects.

Comme on peut le voir, Sybase et SQL Server sont quand même assez proches l’un de l’autre.

Mais l’avantage de SQL Server, c’est quand même aussi SQL Server Management Studio.
Par exemple, il est possible de voir la liste des vues systèmes (Bases de données\Vues\Vues Systèmes), des fonctions prédéfinies (Bases de données\Programmabilité\Fonctions) ou des types ((Bases de données\Programmabilité\Types).
Et puis, la MSDN aussi, aide beaucoup, par exemple avec la liste des tables systèmes détaillées : Compatibility Views (Transact-SQL).

Donc, au final, ma préférence va à SQL Server. D’autant plus que son intégration est bien plus poussée dans l’écosystème Microsoft !

Catégories :SGBDR, Sql Server

[SQLServer] Tables temporaires et variables

SQL Server 2008 R2Parmi les petites choses bien pratiques que l’on retrouve dans SQLServer (bon, pas que, mais le billet ne traitera que de SQLServer), il y a les tables temporaires ainsi que les variables tables (une variable qui est de type table).

Il existe deux types de tables temporaires, pour des usages spécifiques.
Les tables variables sont destinées à un troisième usage.

Dans ce billet, nous allons voir comment les manipuler.

Note : la version utilisée pour ce billet est SQL Server 2008 R2, il est possible que tout ne fonctionne pas comme attendu sur les versions précédentes.

Lire la suite…

Catégories :SGBDR, Sql Server

[Sybase] Quelques tables systèmes

Dans Sybase, comme dans Oracle ou SQLServer, il y a des tables systèmes.
Autant ce n’est pas une bonne idée de les modifier, autant il peut être utile de les connaître et de les interroger.
Du coup, dans ce billet, on va voir quelques tables systèmes, comment les lier et les utiliser (toujours en lecture).

La table centrale, pour une utilisation autre que DBA, est la table sysobjects.
Elle peut être utile à connaître dans le cadre de développements et encore plus dans le cadre d’une maintenance (du genre quand on connait pas toute l’application…).

La colonne « type » est la plus importante, à mon sens, les valeurs sont :

  • D : Default
  • P : Procédures
  • RI : Contraintes de référentiel
  • S : Tables système
  • SF : Fonctions
  • TR : Triggers
  • U : Tables (créées par l’utilisateur)
  • V : Vues
  • Dans le tas, j’avoue que je ne vois pas trop l’intérêt des types D et RI…(actuellement, c’est les tables qui ont été créées à l’occasion d’une modification structurelle, avec un nom du style ).

    Quoiqu’il en soit, si on fait un bête et méchant :

    SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name
    

    On obtient toutes les tables utilisateur.

    Peut-on aller plus loin ? Bien sûr ! (et encore heureux !)

     
    Comment voir la source d’une procédure, par exemple ?

    SELECT com.text AS TEXT 
    FROM dbo.sysobjects obj 
    INNER JOIN dbo.syscomments com 
        ON com.id = obj.id 
    INNER JOIN dbo.sysusers users ON users.uid = users.uid
    WHERE obj.name LIKE '<mon élément>' 
    AND obj.type = 'P'  -- facultatif
    AND users.name = 'dbo' 
    ORDER BY obj.name, com.colid
    

    Mais cela fonctionne également avec les triggers, par exemple.
    Il suffit de changer type = ‘P’ en type = ‘TR’ (‘V’ pour les vues…) et indiquer le nom du trigger à la place de .

    Pourquoi filtrer sur l’utilisateur ‘dbo’ ?
    Parce que l’élément va potentiellement exister pour chacun des utilisateurs qui peuvent le voir/exécuter.
    Dans mon cas, il y a une trentaine utilisateurs…

    Et ‘colid’ ? C’est simplement l’index du bloc, avec pour base 1.
    Pour une procédure, par exemple, le premier bloc contiendra à minima ‘CREATE PROC […]’.

    Pas toujours utile.
    Je préfère la recherche d’un texte :

    SELECT 
        obj.name AS 'PROC',
        com.text AS TEXT 
    FROM dbo.sysobjects obj 
    INNER JOIN dbo.syscomments com 
        ON com.id = obj.id 
    INNER JOIN dbo.sysusers users ON users.uid = users.uid
    WHERE obj.type = 'P' 
    AND lower(com.text) LIKE lower('%TODO%')
    AND users.name = 'dbo' 
    ORDER BY obj.name, com.colid
    

    (bon, le pire, c’est que cette requête me retourne des résultats 😄 )
    Avec une requête de ce type, on va, par exemple, pouvoir chercher sur le nom d’une procédure, pour voir où elle est utilisée (certains logiciels le font, comme DBArtisan, mais…c’est pas toujours rapide).

    Allez, on attaque de suite une requête un peu plus complexe.

    SELECT 
        cols.name   as Column_name,
        types.name  as Type,
        cols.length as Length,
        cols.prec   as Prec,
        cols.scale  as Scale,
        CASE WHEN cols.status = 8 THEN 1
        ELSE 0 END  as Nulls,
        CASE WHEN cols.status = 128 THEN 1
        ELSE 0 END  as 'Identity'
    FROM sysobjects obj
    INNER JOIN syscolumns cols
        ON cols.id = obj.id
    INNER JOIN systypes types
        ON types.usertype = cols.usertype
    WHERE obj.type = 'S'
    AND obj.name = 'sysobjects'
    ORDER BY cols.colid
    

    Ça ressemble à un sp_help, non ? C’est le but.
    Le champ status est un peu particulier, il fait penser au enum flag

    • Bits 0–2 (valeur 1, 2, et 4) – indique si la colonne utilise un type binaire. Si la colonne utilise le type text/image, les bits 0 et 1 indique le statut de réplication :
      • 01 = toujours
      • 10 = si changement
      • 00 = jamais
    • Bit 3 ( valeur 8) – indique que la valeur peut être NULL.
    • Bit 4 ( valeur 16) – indique s’il existe plus de une contrainte pour cette colonne.
    • Bits 5 and 6 – utilisées en interne.
    • Bit 7 ( valeur 128) – indicates an identity column.

    D’autres petites choses utiles ?

    • sp_help : affiche un descriptif complet de l’élément passé en paramètre (table, procédure…).
    • sp_pkeys : affiche des informations sur les clefs d’une table.
    • sp_helpindex : affiche des informations sur les index d’une table.
    • sp_spaceused : affiche des informations concernant l’espace utilisé par une table.
    • sp_spaceused : utilisé sans paramètre, donne les informations concernant l’espace utilisé par la base de données courante (peut être un peu long).
    • sp_who [user] : permet d’avoir les utilisateurs connectés et ce qu’ils font, passer l’utilisateur en paramètre permet de filtrer dessus.
    • sp_displaylogin : permet d’obtenir des informations complètes sur l’utilisateur courante.
    • SELECT suser_name() : retourne l’utilisateur courant.
    • SELECT db_name() : retourne le nom de la base courante.

     
    Voilà, ce sont de petites choses, un début. Mais ça peut fournir des pistes utiles pour des requêtes plus complexes et plus utiles 🙂

Catégories :ASE, SGBDR