terça-feira, 24 de abril de 2012

The Connection String Reference

Decorar pra que? Se dá pra consultar e liberar a memória para coisas mais interessantes.

Site de referência de Strings de Conexão para maioria dos Bancos de Dados (SQL Server, Oracle, DB2, etc) e Arquivos (Excel, Access, DBF).

http://www.connectionstrings.com/

terça-feira, 17 de abril de 2012

Instant SQL Formatter - Formatar SQL

Cansado de pegar aquelas queries todas zoneadas no banco de dados, e ter que arrumar a query manualmente antes de conseguir começar alterar alguma coisa?

O Instant SQL Formatter pode ajudar, você "cola" sua query no site, clica em "Format SQL", e o site formata sua query do jeito que você quiser, suporta vários bancos de dados e permite você selecionar suas preferências pessoais utilizando as opções do lado direito do site para formatar palavras chave, nomes das funções e quebra de linhas.

http://www.dpriver.com/pp/sqlformat.htm



terça-feira, 3 de abril de 2012

Query Dinâmica + Paginação - SQL Server

Agora, nenhuma novidade, só juntei os posts anteriores para criar uma Query Dinâmica com Paginação, aplicando os conceitos de melhoria de performance.

As dicas de performance já foram explicadas nos 2 posts anteriores:
http://solucaocompilada.blogspot.com.br/2012/04/query-dinamica-sqlserver.html
http://solucaocompilada.blogspot.com.br/2012/04/paginacao-sql-server-rownumber-e.html

Segue exemplo juntando tudo:
create procedure sp_modelo_pesquisa_paginacao (
@cpf varchar(14) = null,
@codigo int = null,
@nome varchar(55) = null,
@email varchar(45) = null,
@pagina int = 0,
@pagina_tam int = 0)
as
begin

declare @sql nvarchar(4000)
declare @count nvarchar(4000)
declare @campos nvarchar(4000)
declare @tabelas nvarchar(4000)
declare @filtros nvarchar(4000)
declare @error int
declare @rowcount int

/* Seleciona os campos
O primeiro campo é utilizado na paginação.
O segundo campo mostra a quantidade de páginas caso seja usada a paginação. */
select @campos = N'select ROW_NUMBER() OVER (ORDER BY codigo ASC) AS Row, '
+ N'case when @ppagina_tam = 0 then 0 else (@ptotalregistros) / @ppagina_tam + case when (@ptotalregistros) % @ppagina_tam > 0 then 1 else 0 end end as total_pagina , '
+ N'codigo, nome, cpf '

/* Seleciona as tabelas. */
select @tabelas = N'from cliente '

/* Monta os critérios para a seleção. */
select @filtros = N'where 1=1 '
+ case when @cpf is not null then N'and cpf = @pcpf ' else N'' end
+ case when @codigo is not null then N'and codigo = @pcodigo ' else N'' end
+ case when @nome is not null then N'and nome like ''%'' + @pnome + ''%''' else N'' end
+ case when @email is not null then N'and email = @pemail ' else N'' end

/* Calcula a quantidade de registros */
select @count = N'declare @ptotalregistros int '
+ N'select @ptotalregistros = count(*) '
+ @tabelas
+ @filtros

/* Monta Query */
select @sql = @campos + @tabelas + @filtros

/* Paginação. */
select @sql = @count
+ N'select ' + case when @pagina_tam = 0 then N'' else N'top (@ppagina_tam)' end
+ N'* from (' + @sql + N') as lista where Row > @ppagina_tam * ( @ppagina - 1 )'

/* Faz a chamada da query utilizando a sp_executesql. */
exec sp_executesql @sql,
N'@ppagina as int,
@ppagina_tam as int,
@pcpf varchar(14),
@pcodigo int,
@pnome varchar(55),
@pemail varchar(45)'
,
@ppagina = @pagina,
@ppagina_tam = @pagina_tam,
@pcpf = @cpf,
@pcodigo = @codigo,
@pnome = @nome,
@pemail = @email

/* Armazena as variáveis globais de erro e linhas afetadas. */
select @error = @@ERROR, @rowcount = @@ROWCOUNT

/* Verifica se houve erro na execução da query dinâmica. */
if @error <> 0
begin
    select coderro = 1, msgerro = 'Erro ao executar procedure'
    return ( 1 )
end

/* Verifica se a consulta retornou algum registro. */
if @rowcount = 0
begin
    select coderro = 2, msgerro = 'Consulta no retornou nenhum registro com os critérios informados'
    return 2
end

end

segunda-feira, 2 de abril de 2012

Paginação - SQL Server (row_number e rowcount)

Pesquisando formas de fazer paginação no SQL Server achei algumas coisas interessantes, mas o que mais me agradou foi a paginação com a utilização do comando row_number, por não utilizar o "not in" e nem tabela temporária.

O comando row_number foi criado no SQL2005, se a versão em que você está desenvolvendo for inferior à esta, existem soluções que utilizam o rowcount, que também parece bastante eficiente, porém requer uma única coluna de ordenação (o que geralmente mata qualquer implementação), mas segue um link que pode servir para alguns casos:

Exemplo utilizando o rowcount: http://www.4guysfromrolla.com/webtech/042606-1.shtml
Obs: - RowCount requer uma única coluna de ordenação.

Utilizando o row_number para paginação:

create procedure listar(
    @cidade varchar(50),
    @pagina int,
    @tamanhopagina int)
as
begin

declare @TotalRows int
-- Fazer o count(*) é mais rápido do que usar o Count(*) OVER() dentro da próxima
-- query.
select @TotalRows = COUNT(*) from cliente where cidade = @cidade

-- Seleciona a quantidade mxima de registros por pgina (@tamanhopagina).
Select Top(@tamanhopagina) * from
(
    Select
    -- campo utilizado na paginação (informar a ordenação dos campos aqui).
    RowID=ROW_NUMBER() OVER (ORDER BY codigoempresa, codigocliente),
    -- retorna a quantidade de registros total da query.
    @TotalRows as TotalRows, -- Count(*) OVER() é mais lento e utiliza mais páginas de
                                                  -- dados do que usar o count(*) em outra query.
    -- Calcula a quantidade de páginas de acordo com o número de
    -- registros e a quantidade de registros por página.
    @TotalRows / @tamanhopagina + case
                                                             when @TotalRows % @tamanhopagina > 0
                                                             then 1
                                                             else 0
                                                             end QtdePaginas,
    -- evitar usar o * para melhoria na performance, retorne somente os campos que for utilizar.
    *
    from cliente
    where cidade=@cidade
) lista
-- Faz a conta básica ((@pagina-1)*@tamanhopagina) para retornar somente os
-- registros corretos de acordo com número da página e o tamanho da página,
-- utilizando o campo gerado pelo row_number() over (), nomeado aqui de RowId.
Where lista.RowId > ((@pagina-1)*@tamanhopagina)

end

Query Dinâmica - SQLServer

Uma boa prática na criação de consultas dinâmicas quando criamos telas de pesquisa onde o usuário cria a combinação de critérios que serão aplicadas no banco de dados é a query dinâmica, geralmente mal vista pelos desenvolvedores, mas uma análise breve nos resultados de performance comparado à outros métodos mostra que essa é uma boa solução para pesquisas que geram critérios também dinâmicos.

Essas pesquisas geralmente dificultam o banco de dados na criação de bons planos de execução dependendo de como a solução é desenvolvida, utilizando-se queries dinâmicas como no exemplo abaixo os planos de execução são criados corretamente graças à chamada com a sp_executesql.

Para funcionar melhor ainda é bastante recomendável a criação dos parâmetros que serão passados para a sp_executesql.

1 - Exemplo:
create procedure sp_modelo_pesquisa_paginacao (
@cpf varchar(14) = null,
@codigo int = null,
@nome varchar(55) = null,
@email varchar(45) = null)
as
begin

declare @sql nvarchar(4000)
declare @error int
declare @rowcount int

/* Seleciona os campos. */
select @sql = N'select codigo, nome, cpf '

/* Seleciona as tabelas. */
select @sql = @sql + N'from cliente '

/* Monta os critrios para a seleo. */
select @sql = @sql + N'where 1=1 '
+ case when @cpf is not null then N'and cpf = @pcpf ' else N'' end
+ case when @codigo is not null then N'and codigo = @pcodigo ' else N'' end
+ case when @nome is not null then N'and nome = @pnome' else N'' end
+ case when @email is not null then N'and email = @pemail ' else N'' end

/* Faz a chamada da query utilizando a sp_executesql. */
exec sp_executesql @sql,
N'@pcpf varchar(14),
@pcodigo int,
@pnome varchar(55),
@pemail varchar(45)'
,
@pcpf = @cpf,
@pcodigo = @codigo,
@pnome = @nome,
@pemail = @email

/* Armazena as variveis globais de erro e linhas afetadas. */
select @error = @@ERROR, @rowcount = @@ROWCOUNT

/* Verifica se houve erro na execução da query dinâmica. */
if @error <> 0
begin
select coderro = -1, msgerro = 'Erro ao executar procedure'
return -1
end

/* Verifica se a consulta retornou algum registro. */
if @rowcount = 0
begin
select coderro = -2, msgerro = 'Consulta não retornou nenhum registro com os critérios informados'
return -2
end

end

Muitas soluções econtradas na web sugerem a utilização desses métodos abaixo, mas evite-os, são péssimos no quesito performance.

  - Não utilize funções ISNULL contra colunas em uma cláusula WHERE.
  - Não utilize comparações do tipo (Coluna = @Variavel OR @Variavel = ‘’).

Se quiser entender melhor, recomendo uma visita à matéria do Gustavo Maia Aguiar, que explica muito bem, com análises de performance, sobre esse assunto:
- http://gustavomaiaaguiar.wordpress.com/2011/05/19/consultas-parametrizadas-isnull-e-sql-dinmica/

Abraços!
Diego Feliciano