SelectEtoile : Base de connaissance SGBD

Posts Tagged 'requete'

Les Fonctions MySql

 

 

Fonctions sur les dates :

 

SELECT nom, naissance, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(naissance))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5))

-> AS age

-> FROM animal;

SELECT nom, naissance, MONTH(naissance) FROM animal;

 

 

Fonctions REGEXP :

 

SELECT * FROM animal WHERE nom REGEXP "fy$"; # colonne finissant par fy

SELECT * FROM animal WHERE nom REGEXP "^.....$";

LIKE : comme sybase

 

 

Utiliser AUTO_INCREMENT (equivalent identity)

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),

("lax"),("whale"),("ostrich");

SELECT * FROM animals;

Qui retourne :

+----+---------+

| id | name |

+----+---------+

| 1 | dog |

| 2 | cat |

| 3 | penguin |

| 4 | lax |

| 5 | whale |

| 6 | ostrich |

+----+---------+

Vous pouvez obtenir la valeur utilisée de la clef AUTO_INCREMENT avec la fonction SQL

LAST_INSERT_ID() ou la fonction d'API mysql_insert_id().

 

 

 

 

 

 

 

Trouver le compte de service d'une instance MSSQL

DECLARE @ServiceaccountName varchar(250)
DECLARE @CurrentSet varchar(250)
if @@SERVICENAME = 'MSSQLSERVER'
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\'+@@SERVICENAME
else
select @CurrentSet = 'SYSTEM\CurrentControlSet\Services\MSSQL$'+@@SERVICENAME
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@CurrentSet,
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'
 
SELECT @@servername + ' - ' + @ServiceaccountName

Volumétrie Tables / Indexes / Bases SYBASE ASE

 

Liste de la volumétrie des tables en version 12.5 :

 

Use mabase
Go
select     DateJour               = convert(datetime,convert(char(10),getdate(),103),103),    
Base = db_name(),  
Tables = Object_name(id),       
NombreLigne = max(rowcnt(doampg)) ,       
Reserved = (sum(reserved_pgs(id,doampg))+ sum(reserved_pgs(id, ioampg)))*2,        
Data = (sum(data_pgs(id, doampg)))*2,       
Indexes = (sum(data_pgs(id, ioampg)))*2  
from sysindexes        
where id > 100       
group by id        
order by object_name(id)

 

 

 

 

Liste de la volumétrie des tables en version 15 :

use mabase
go
select top 10 t.DateJour,t.Base,t.Tables,Nb_row=convert(varchar(10),sum(t.rwct)),Reserved=sum(t.reserved), Data=sum(t.data), Indexes=sum(t.indexes)
from (select  DateJour        = convert(datetime,convert(char(10),getdate(),103),103),
        Base            = convert(varchar(15),db_name()),
        Tables          = convert(varchar(20),object_name(id)),
        case when indid < 2 then (row_count(db_id(),id)) when indid > 1 then 0 end as rwct,
        reserved        = (reserved_pages(db_id(),id,indid))*(@@maxpagesize/1024),
        case when indid < 2 then (data_pages(db_id(),id,indid))*(@@maxpagesize/1024) when indid > 1 then 0 end as data,
        case when indid > 1 then (data_pages(db_id(),id, indid))*(@@maxpagesize/1024) when indid < 2 then 0 end as indexes
from sysindexes
where id > 100) t
group by t.DateJour,t.Base,t.Tables
order by Reserved desc

 

 

 

 

 

 

 

Analyser une requete sql SYBASE ASE

Les outils présentés ci-dessous permettent de tracer et d'analyser une requête sql sur un serveur SYBASE ASE.

 

  • set showplan on : affiche le plan d'exécution de la requête
  • set statistics io,time on : affiche le nombre d'io/ table/index et le temps des différentes étapes
  • set noexec on : combiner avec showplan, permet d'afficher le plan sans executer la requête
  • set tracefile dirname/filename [for spid] : redirige les résultats des ordres SQL d'un session vers le fichier spécifié (New ASE15)
Toujours faire un 'set tracefile off' en fin de session
Sinon, il est possible que toutes les autres sessions soient polluées par cette trace
  • set show_sqltext {on | off} : affiche le SQL text d'un requête, procédure stockée, curseur ...
  • dbcc traceon(3604) : affiche la sortie à l'écran. Il faut positionner cette option pour les commandes suivantes
  • dbcc traceon(302) : affiche le cout des jointures et des SARG sur chaque table
  • dbcc traceon(310) : affiche les plans de moins en moins couteux (NEW PLAN). Par contre, il n’affiche pas les plans de plus fort cout.
  • dbcc traceon(317) :  affiche les plans plus couteux que les NEW PLANS. Ce sont les WORK PLANS.
  • dbcc sqltext(spid) : affiche la requête lancée par le spid

 


Différents paramètres de 310 et 317:

  • total cost : estimation du cout total en millisecondes d’une jointure (lp*2ms+pp*18ms)

  • varno : position de la table dans la clause from (NB : max 16 tables / jointure, si varno > 16 worktable)

  • indexid : indid de l’index utilisé (0 si la table)

  • pathtype : type d’opération (sclause, join ou orstruct)

  • method : NESTED ITERATION (en majorité), REFORMATTING ou OR OPTIMIZATION

  • outerrows : nombre de lignes de la table externe pour chaque ligne de la table courante

  • joinsel : estimation de la selectivité de la table ou de l’indexe de la table courante

  • cpages : nombres de pages (donnees ou indexes) que l’optimiseur estime dans la table courante

  • prefetch : prefetch strategy (N : pas de prefetch, S : prefetch strategy). Important quand le server est configuré en Large IO.

  • Iosize : taille des blocks io choisi par l’optimiseur

  • Replace : strategie de replacement dans le cache (LRU ou MRU)

  • Lp : Nombre de logical reads = outerrows * cpages

  • Pp : nombre de physical reads

  • Corder : principale (premiere) colonne de l’indexe (colid de syscolumns)

  • Jnvar, Refcost, Refpages, reftotpages, ordercol[0] sont peu utilisables et peu importantes

 

 

Si vous avez un doute sur une requête, il est possible de forcer le plan d'exécution ou de forcer un index précis.

Cela est très utile si on pense qu'un index ou un plan est meilleur que ce que l'optimiseur SYBASE a choisi

 

Pour forcer le plan, il suffit de lancer la commande suivante :

set forceplan on -- avant de lancer la requête

Pour forcer l'index, il suffit de préciser entre parenthèses "index nom_index" comme indiqué dans l'exemple suivant :

select * from ma_table (index mon_index) where col1 ='00'

 

Le forceplan force l'optimiseur a choisir son plan dans l'ordre des tables de la clause FROM. L'optimiseur choisit toutefois les index qu'il peut prendre sauf si vous les avez forcer aussi.

 

Informations pratiques :

Si une valeur d’une colonne est plus importante que les autres (ex : 90 %), les jointures sur cette colonne (ex : valeur1 représentant 0.001 % de la table) ne prendront pas forcément le bon index.

En utilisant la commande sp_modifytats comme suit, cela permet de modifier l’indice de densité de la colonne et à l’optimiseur de prendre un meilleur indexe.

 

Sp_modifystats tablename,colonnename,’REMOVE_SKREW_FROM_DENSITY’

 

Lien très intéressant sur le sujet : http://www.sybase.com/detail?id=2602#539757

 

 

Visualisation du plan d'execution des requetes sous SQLServer

Visualisation du plan d'exécution des requêtes en cours

 

SELECT Db_name(runningqueries.database_id)                                AS dbname,
       runningqueries.start_time,
       runningqueries.status,
       Substring(runningtextqueries.TEXT,(runningqueries.statement_start_offset / 2) + 1,
                 ((CASE runningqueries.statement_end_offset
                     WHEN -1
                     THEN Datalength(runningtextqueries.TEXT)
                     ELSE runningqueries.statement_end_offset
                   END - runningqueries.statement_start_offset) / 2) + 1) AS currentstatement,
       runningtextqueries.TEXT                                            AS completequery,
       runningplanqueries.query_plan,
       runningqueries.cpu_time,
       runningqueries.total_elapsed_time,
       runningqueries.granted_query_memory                                AS numofpagesallocated,
       runningmemoryconsoqueries.requested_memory_kb                      AS requestedmemory_kb,
       runningmemoryconsoqueries.granted_memory_kb                        AS memoryallocated_kb,
       runningmemoryconsoqueries.required_memory_kb                       AS minimalmemoryneeded_kb,
       runningmemoryconsoqueries.used_memory_kb                           AS currentlymemoryused_kb,
       runningmemoryconsoqueries.max_used_memory_kb                       AS maxmemoryused_kb
FROM   sys.dm_exec_requests AS runningqueries
       CROSS APPLY sys.Dm_exec_sql_text(runningqueries.sql_handle) AS runningtextqueries
       CROSS APPLY sys.Dm_exec_query_plan(runningqueries.plan_handle) AS runningplanqueries
                   LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS runningmemoryconsoqueries
                     ON runningqueries.sql_handle = runningmemoryconsoqueries.sql_handle

GO

Requetes divers sur SYBASE ASE

Autojointure à regroupement :

SELECT col1, col2, count(*)

FROM tab

GROUP BY col1, col2

ORDER BY col1, col2

compute sum(count(*)) BY col1

compute sum(count(*))

 

 

Modification format de date :

 

02/02/02 en 02-02-02 :

select object_name(id), stuff(stuff(convert(varchar, moddate, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), moddate, 108),

 

 

Feb 7 2005 12:53PM en 20050207121240

convert(varchar, moddate, 112) + substring(convert(char(8), moddate, 108) , 1, 2) + substring(convert(char(8), moddate, 108) , 4, 2) + substring(convert(char(8), moddate, 108) , 7, 2)

 

 

 

Commandes sur la gestion des dates

2 format de data différents

declare @dat datetime, @dat2 smalldatetime
select @dat = getdate()
select @dat2 = getdate()
select @dat, @dat2


Difference entre 2 dates :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select datediff (hh,@dat1,@dat2)


Ajout 1 heure :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select dateadd (hh,+1,@dat2)


Affichage avec un format particulier

select convert (char(26),getdate(),108)

 

Affichage de la date du jour :

- 3 mois : select convert (smalldatetime, Dateadd (mm, -3, getdate()))

- 2 jours : select convert (smalldatetime, Dateadd (dd, - 2, getdate()))



Mise a jour des threshold

select "use "+db_name(dbid)+char(10)

+"go"+char(10)+

"sp_addthreshold @dbname='"+db_name(dbid)+"',@segname='logsegment',@free_space="+ convert( char(8), sum(size)*10/100)

+" , @proc_name='sp_thresholdaction' "+

char(10)+"go"+char(10) as "--seuil 30%"

from master..sysusages where segmap=4 group by db_name(dbid)



Utilisation de patindex et substring :

extraire dans S_AnoDescription, les 50 caractères après la chaine @ModuleName.

select distinct

G_PROCESSUS.G_PrsName,S_LOG.G_PrsID,

Substring(convert (varchar(1500),S_AnoDescription), patindex('%@ModuleName%',S_ANOMALY.S_AnoDescription) ,50 ),

S_ANOMALY.S_AnoID --,S_ANOMALY.S_LogID

from S_ANOMALY, S_LOG, G_PROCESSUS

where S_AnoCode in (506, 5003) AND

S_ANOMALY.S_LogID = S_LOG.S_LogID

AND G_PROCESSUS.G_PrsID= S_LOG.G_PrsID




Visualisation des Logins Externes :

SELECT  Server = s.srvname,

            Login = l.name,

            Externlogin = a.object_cinfo

        FROM    master.dbo.sysattributes a,

            master.dbo.sysservers s,

            master.dbo.syslogins l

        WHERE   a.class = 9 AND

            a.object_type = "EL" AND

            a.object_info1 = s.srvid AND

--          s.srvname LIKE @server AND

            a.object = l.suid