miércoles, 7 de noviembre de 2007

Cálculo de letras del DNI mediante T-SQL

Me he visto en la necesidad de obtener la letra del DNI de un conjunto grandísimo de números de DNI.

Por optimización, el DNI se almacenó en un tipo de datos INT ( sin letra ) y se puso como clave primaria, pero por cosas de la vida, he tenido que lanzar un proceso que recalcule las letras de todos los DNI, para algo que no viene al caso

Aquí va la solución:



use GUSENET

go

create table dbo.Nifs(numnif int primary key);

go

insert into dbo.Nifs(numnif) values(12569875)

go

select * from nifs


-- CALCULO DE LA LETRA

DECLARE @letras_nif as CHAR(23)

set @letras_nif = 'TRWAGMYFPDXBNJZSQVHLCKE'

;

WITH SubSelect AS(
SELECT numnif,

CONVERT(INT,

  FLOOR(

     FLOOR(

           (

            (

             CONVERT(FLOAT,numnif)/23)-FLOOR(CONVERT(FLOAT,numnif)/23)

          )*100

    )*0.23+0.5

 )+1

)AS pos_letra

FROM dbo.Nifs

)

SELECT SUBSTRING(@letras_nif, pos_letra ,1) AS letra,

       str(numnif)+'-'+SUBSTRING(@letras_nif, pos_letra ,1)

FROM subselect


Salu2!

miércoles, 10 de octubre de 2007

Mover objetos de SQL Server a otro grupo de ficheros

Un problema típico de rendimiento de la BBDD a la que nos enfrentamos muchos es el cuello de botella que suponen los discos físicos. Imaginemos un entorno perfecto en el que todo son "index seeks", no tenemos "table scans" y encima la poca cantidad de datos y sobrada RAM, hacen que SQL Server tenga que ir pocas veces a disco para leer un dato. Pues bien, aun en esa situación que rara vez y entornos muy reducidos es posible , podemos mejorar el acceso a datos, paralelizando los accesos a disco.

Pongamonos en el caso mas simple: 2 tablas y un solo disco que además tiene un solo cabezal de lectura. Si hacemos una consulta que incluya las dos tablas (suponiendo tambien que es la primera y que no tiene datos en la caché ) , el cabezal va a tener que leer los datos de las dos tablas el solito; cuando si tuviéramos cada tabla en un disco físico distinto, cada lectura seria llevada a cabo por un cabezal diferente y evidentemente se dispondria del resultado antes.

No todos estamos en disposicion siempre de diseñar e implementar una BBDD desde 0 y distribuir las tablas en diferentes discos a sabiendas de la carga que van a tener cada una de ellas. Los cuellos de botella I/O se suele ver a posteriori cuando realizamos pruebas de carga de la aplicación y en la mayoria de casos por desgracia ya en producción.

No es el objeto de este post descubrir cuando tenemos problemas de disco, pero una vez ya las hemos dectectado, lo que hemos de analizar es como evitarlos o minimizarlos lo mejor que podamos.

Muchas veces, lo que vamos a hacer es comprar discos físicos en RAID y realizar la mejor configuración de tempdb, Grupos de ficheros y log de transacciones que podamos. Eso va a incluir en la mayoria de veces, que se creen grupos de ficheros en discos con la configuración mas óptima en RAID, para las tablas que mas sobrecarga conllevan.

Dichos grupos de ficheros van a albergar objetos, y claro el problema nos viene cuando queremos mover una tabla de un grupo de ficheros a otro. La solución es bien sencilla, pasa por utilizar la cláusula "MOVE TO" de DROP INDEX o ALTER TABLE ... DROP CONSTRAINT.

Dicha cláusula, nos va a permitir especificar donde mover una tabla en última instancia despues de borrar su índice clustered, puesto que lo que va a hacer es mover el contenido del "leaf level" del ya borrado índice al grupo de ficheros designado. Dicho "leaf level" no es ni mas ni menos que la tabla con sus datos en sí.

Tendremos que tener cuidado con el resto de índices "nonclustered" que pueda tener la tabla, puesto que estos se van a quedar en el grupo de ficheros antiguo, con lo que lo mejor si no queremos que esto sea así será borrarlos y volverlos a crear, pero esta vez bajo el grupo de ficheros que les pertenezca ( no tien porque ser el mismo donde se encuentra la tabla que acabamos de mover ).

Un ejemplo práctico de esto lo podeis ver aqui:

-- Creamos una tabla cualquiera
--

create table tablaprueba(a int not null, b varchar(2))
go
-- Le damos un nombre a la clave primaria
-- ( que va a hacer ademas por definición,
-- de indice clústered de nuestra tabla )

alter table tablaprueba
add constraint pk_tablaprueba primary key(a)
go
-- Ahora, le metemos ademas un índice no clustered,
-- para que veamos que es lo que pasa

create nonclustered index idx_tablaprueba_b on tablaprueba(b)
go
-- Le metemos unas filitas de datos...
insert into tablaprueba (a,b) values (1,'a')
go
insert into tablaprueba (a,b) values (2,'b')
go
insert into tablaprueba (a,b) values (3,'c')
go


-- Hacemos una consulta para ver que tenemos datos, obviamente
select * from tablaprueba with(nolock)
go


Ahora, podemos comprobar como tenemos la tabla creada bajo el grupo de ficheros por defecto de la BBDD en cuestión, ya que no le hemos indicado al crear la tabla bajo que grupo de ficheros la queríamos.


Lo que nos interesa ahora es mover la tabla al grupo de ficheros que previamente he creado y llamado GUSENET_1, valiéndome de la sentencia antes mencionada.


-- Y ahora lo movemos al grupo de ficheros que queramos. En este caso lo he llamado GUSENET_1, que previamente he creado ya.
alter table tablaprueba drop constraint pk_tablaprueba with(move to GUSENET_1)
/*
-- Notese que si el índice clústered no hubiera sido una clave primaria, en vez de usar
-- la sentencia alter table... tendriamos que haber utilizado DROP INDEX, con idénticos resultados, la tabla se hubiera movido al mismo sitio
DROP INDEX pk_tablaprueba
ON tablaprueba WITH ( MOVE TO GUSENET_1);
GO
*/



Una vez ejecutado esto, podemos ver que realmente nos la ha movido:


-- Una vez ya hemos movido la tabla, si volvemos a realizar la consulta, vemos que seguimos teniendo datos, pero la planificación de la consulta, al no tener índice nos hace un Table Scan
select * from tablaprueba with(nolock) where a = 2
go


-- Puesto que queremos que todo siga como estaba, le haremos el índice de nuevo y lo pondremos en el grupo de ficheros que queramos, en este ejemplo nos lo llevamos al mismo grupo de ficheros donde hemos movido la tabla
alter table tablaprueba
add constraint pk_tablaprueba primary key(a) on GUSENET_1
go

--Ahora ya podemos ver , como realiza la consulta utilizando el índice clústered.
select * from tablaprueba with(nolock) where a = 2
go





jueves, 27 de septiembre de 2007

Encender y apagar SQL Server mediante código

Hoy me he visto en la necesidad de controlar por código el servicio de SQL Server , para hacer una pequeña libreria que compruebe el estado del mismo. Como siempre, las librerias de .NET me han ayudado mucho ;)

Aqui tenemos el código necesario para arrancar el servicio del agente de SQL Server , en el caso de que detectemos que se encuentra caido.


using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo.Wmi;
namespace PruebaWMI
{
class Program
{
static void Main(string[] args)
{
ManagedComputer mc = new ManagedComputer();
// Aqui es donde le decimos el nombre del servicio, en mi caso tengo nombre de instancia ECB_SQL
Service svc = mc.Services["SQLAgent$ECB_SQL"];
// Si el servicio no está activo, lo inicio ;)
if (svc.State.Equals(ServiceState.Running))
Console.WriteLine("run");
else
{
Console.WriteLine("no run");
svc.Start();
}
}
}
}


Para que esto funcione, has de añadir las librerias Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, Microsoft.SqlServer.WmiEnum.

lunes, 24 de septiembre de 2007

TransactionScope, TableAdapters y el modo desconectado

Si alguna vez has utilizado los TableAdapters para trabajar y te has enfrentado al dilema de qué hacer cuando vas a guardar de forma transaccional, quizas te interese conocer un enfoque no muy conocido por la gente.

Imaginemos el caso de disponer de dos DataTables, cada uno de los cuales dispone de un DataAdapter que le indica qué comandos utilizará ante una inserción, borrado o actualización. Imaginemos ademas, que queremos que las operaciones realizadas de forma desconectada con los DataTables han de ser actualizadas si o si, es decir que o se actualizan los dos DataTables en la BBDD o ninguno.

Vamos a utilizar un par de cositas nuevas que vienen en .NET 2.0, las clases parciales y la clase TransactionScope

La clase TransactionScope nos facilita la labor creando un ámbito transaccional, donde cada operación sobre cualquier origen de datos, va a realizarse de forma transaccional. Esto hemos de tenerlo muy claro cuando trabajemos con esta clase porque hemos de ser conscientes del peligro que podemos sufrir si trabajamos mal con ella.

Si quisiéramos una actualización transaccional de los dos DataTables:

...
using(TransactionScope transaccion = new TransactionScope())
{
//aqui va todo lo que quiero que sea transaccional
TableAdapter1.Update(dataTable1);
TableAdapter2.Update(dataTable2);
...
}
...


Si intentamos hacer esto, lo que vamos a obtener va a ser una pérdida de rendimiento brutal de la aplicación ( o una excepción si no tenemos habilitado el coordinador de transacciones de microsoft en el servidor ). Esto es porque si recordamos, los tableadapters trabajan de forma desconectada con lo que cada llamada al método Update del TableAdapter va a abrir una conexion , utilizarla y cerrarla. TransactionScope como he dicho se va a encargar de que todo sea realizado bajo una única transacción, pero como resulta que tendremos dos conexiones diferentes contra un origen de datos, él lo escala al coordinador de transacciones de microsoft , lo cual no es bueno ( en este escenario donde realmente no nos hace falta ).

Lo que queremos entonces es que todo se realice bajo la misma conexión y ahi es donde nos podemos encontrar el problema.

Afortunadamente, en ADO.NET 2.0, disponemos del modificador de clase "partial", que nos permite programar una clase en varios archivos. En principio, para los tipos duros que programen los TableAdapters "a pelo" o no tipados, esta parte no les interesa ya que como lo hacen a pelo, ellos mismos tienen acceso a la parte "private" de la clase, pero para los que queremos ir a lo práctico y utilizamos el diseñador del Visual Studio 2005, este truco no está nada mal.

Podemos añadir un método que nos asigne al TableAdapter una conexión que le pasemos por parámetro, con lo que el TableAdapter no necesitará ya crear una nueva.

using System;
using System.Data.SqlClient;
namespace Proyecto.DataLayer.dsProyectoTableAdapters
{
public partial class TableAdapter1
{
public void AssignConnection ( SqlConnection con )
{
this._connection = con;
}
}
}


Con estas simples líneas de código, le acabamos de añadir la funcionalidad de especificarle una conexión a un TableAdapter1 ( cuyos InsertCommand, UpdateCommand,... ya tendremos definidos por ejemplo mediante el editor de Visual Studio 2005 ).

Ahora solo falta utilizarlo y para ello , solamente hay que tener presente que la conexión la tendremos que asignar a los TableAdapters que queramos tener involucrados en la transacción ( insisto, esto es para que no nos escale TransactionScope al coordinador de transacciones de Microsoft en los casos en los que no sea necesario ).

La modificacion anterior quedaria de la siguiente manera:

...
// Creamos el objeto de conexión a la BBDD
SqlConnection conexion = new SqlConnection(cadenaconexioon);
// Asignamos ese objeto de conexión a todos los TableAdapters que queramos que funcionen
// en la misma transacción
TableAdapter1.AssignConnection(conexion);
TableAdapter2.AssignConnection(conexion);
// Abrimos un ámbito transaccional y dentro metemos las llamadas a los métodos Update de los TableAdapters
using(TransactionScope transaccion = new TransactionScope())
{
conexion.Open();
//aqui va todo lo que quiero que sea transaccional
TableAdapter1.Update(dataTable1);
TableAdapter2.Update(dataTable2);
...
conexion.Close();
}
...


Existen tambien métodos para trabajar de forma transaccional mediante Enterprise Library 3.0 y el espacio de nombres TransactionScope que pueden resultarte mas fáciles, asique , esto depende del gusto del consumidor. Solo puedo decirte que este tipo de configuración de la capa de negocio funciona a las mil maravillas en entornos de producción.

Como evitar los temidos deadlocks

Lamentablemente no hay una respuesta única a este temido problema. Todo depende de muchos factores, especialmente de arquitectura y diseño del acceso a datos , pero puedo dar unos consejos que aunque pueden resultar obvios, estan muy relacionados con este tipo de problemas.

Transacciones, cuanto menos duren mejor
Quizas de lo mas importante a tener en cuenta es que las transacciones han de ser cuanto mas rápidas mejor. Si tienes que modificar un DataTable, hazlo en modo desconectado y fuera de la transacción, de forma que la transacción sea única y exclusivamente de modificacion de datos. Es muy comun ver en algunas empresas aperturas de transacciónes que duran minutos, pero que se pueden dejar en segundos si se deja el preprocesado de forma desconectada y luego se lanza un update completo de todo a la vez, sin calculos ni nada.

En definitiva, todo proceso que puedas sacar fuera de una transacción, sácalo.

Lecturas Sucias
Si puedes utilizar el modo de aislamiento no bloqueante ( WITH (NOLOCK) ) al realizar consultas, utilizalo. Muchas veces es la propia aplicación la que de forma lógica impide que alguien modifique un dato de forma que no pasa nada si hacemos las consultas con NOLOCK. Esto esta muy relacionado con la arquitectura.

Un ejemplo de esto es por ejemplo la tipica aplicación de facturación que impide que dos usuarios modifiquen la misma factura evitando que la abra mas de uno a la vez. En este caso, todas las consultas las podremos hacer con lectura sucia porque sabemos que todo lo que obtendremos va a ser válido y no va a producirle error al cliente.

Optimiza tus accesos a Base de Datos
Usa índices allí donde los necesitas. Un indice puede acelerar la operación notoriamente. Un update puede durar por ejemplo 1 minuto o puede durar 0.0000000001 segundos dependiendo del índice que utilicemos.
Al igual que un índice puede mejorar, el pasarse tambien puede empeorar ya que los índices no se mantienen solos y las inserciones se pueden ver afectadas.
El terreno de la optimización de las Bases de Datos es un terreno bastante peliagudo donde se combina el conocimiento de la aplicación ( qué datos se van a recuperar y a partir de qué otros ) con el de la BBDD a optimizar. Pese a esto, no es raro que este proceso sea infinito de forma que siempre estemos mejorándolo, ya que la BBDD suele ser para la mayoria un ente dinámico que avanza con el tiempo y la aplicación que soporta.


Vigila tus herramientas
Si utilizas herramientas de terceros que accedan a datos ( herramientas de reporting , por poner un ejemplo ) , comprueba el tipo de aislamiento que esta utilizando dicha herramienta para realizar sus operaciones. En una ocasión me encontré que la mayoria de interbloqueos se debian a que una herramienta de reportes muy conocida estaba realizando las consultas con aislamiento SERIALIZABLE, y cuando un usuario intentaba imprimir un reporte bloqueaba a prácticamente todo el mundo. Esto la mayoria de ocasiones se soluciona en el RegEdit de Windows ;)

Optimiza tu servidor de Base de Datos
Puede parecer algo secundario, pero la buena salud de un servidor afecta, y mucho, a la salud de la aplicación.
Todo lo anterior no nos sirve de mucho si nuestro servicio de SQL Server no dispone de memoria suficiente para realizar las operaciones y está continuamente paginando a disco. Encontrar una buena salud de nuestro servidor de BBDD tambien ha de estar en nuestra cabeza cuando queramos minimizar el impacto de una consulta contra la BBDD.

Como vemos, todo es muy genérico pero resulta importante tenerlo en mente siempre que diseñemos un acceso a datos , sea al motor de Base de Datos que sea.

Optimizando consultas ADO.NET a tablas dbf de VFP

Para mi primer post , que mejor manera de postear algo, que hacerlo sobre un problema para el que no he encontrado respuesta en ningun lado y que me ha llevado de cabeza durante mucho tiempo.


Resulta que si tenemos una tabla dbf de Visual Foxpro ( con muchos registros, pongamos 2 millones ) en la que tenemos definidos índices compuestos , y queremos hacer consultas desde .net , nos encontraremos con el problema del rendimiento.




Despues de darle muchas vueltas, descubrí que el problema principal eran los índices, pero no que no tuviera , sino que la forma en la que estaban definidos no se la tragaba el driver de Foxpro para .NET.


El problema radica en que para que el driver OLEDB para Visual Foxpro 9.0, utilice los índices de nuestra tabla, estos han de estar definidos como union de cadenas mediante el operador "+", si utilizamos el operador "-" , no utilizará el índice.


Es decir, que en la siguiente imagen, si realizaramos una consulta sobre la tabla , utilizaria el índice idx2 en vez del idx1 ( de hecho si no existiera idx2, la consulta no utilizaria el índice ).






El resultado es evidentemente espectacular porque pasabamos de consultas de 5 minutos ( inviables en producción ) a consultas de 2 segundos.

Espero que te sirva esta información si algun dia has de conectar con tablas dbf desde .net utilizando ADO.NET.