All perguntas(dba)

Estou tentando comparar duas consultas:

Pergunta 1:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  AND tableA.e=tableB.e 

Pergunta 2:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a AND tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  
WHERE tableA.e=tableB.e 

Estou certo em dizer que essas duas consultas dão os mesmos resultados?

Além disso, é correto dizer que a primeira consulta cria uma tabela maior para a qual fazer uma WHEREcondição maior; enquanto no segundo caso temos uma tabela construída menor à qual o simples WHEREé então aplicado.

Supondo que os resultados sejam os mesmos, qual consulta deve ser preferida? Existe um problema óbvio de desempenho?

Eu tenho um procedimento armazenado (SS2k8) com alguns parâmetros com valor de tabela que às vezes serão nulos ou vazios. Eu vi este post do StackOverflow que diz que TVPs nulos/vazios devem simplesmente ser omitidos da lista de parâmetros de chamada. Meu problema é que não consigo descobrir como verificar vazio ou nulo dentro do procedimento armazenado, pois "IF (@tvp IS NULL)" falha na criação do procedimento com a mensagem 'Deve declarar a variável escalar "@tvp"'. Eu tenho que fazer um SELECT COUNT(*) no TVP e checar por zero?

Trecho do código:

CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS

IF (@tvp IS NOT NULL) -- doesn't work
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

Sei que é algo que deve ser evitado por questões de desempenho, mas estou tentando mostrar uma condição em que apareça como uma demonstração de como garantir que não apareça.

No entanto, acabo com um aviso de índice ausente, mas o otimizador opta por não criar um índice temporário.

A consulta que estou usando é

SELECT 
    z.a
FROM dbo.t5 AS z WITH(INDEX(0))
WHERE 
    EXISTS 
    (
        SELECT y.a 
        FROM dbo.t4 AS y
        WHERE y.a = z.a
    )
OPTION (MAXDOP 1);

Os esquemas de tabela são:

CREATE TABLE dbo.t4
(
    a   integer NULL,
    b   varchar(1000) NULL,
    p   varchar(100) NULL
);

CREATE TABLE dbo.t5
(
    a   integer NULL,
    b   varchar(1000) NULL
);

CREATE UNIQUE CLUSTERED INDEX c1 
ON dbo.t5 (a);

Ambas as tabelas têm 10.000 linhas, que você pode simular com:

UPDATE STATISTICS dbo.t4 
WITH 
    ROWCOUNT = 10000, 
    PAGECOUNT = 1000;

UPDATE STATISTICS dbo.t5 
WITH 
    ROWCOUNT = 10000,
    PAGECOUNT = 1000;

O plano de consulta é:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="5532.16" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.407384" StatementText="select a from t5  z WITH(INDEX(0))  where exists (select a from t4 where a=z.a )" StatementType="SELECT" QueryHash="0x1B882FCEA34AEAF4" QueryPlanHash="0x1B276DC04B718F7C" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="2912" CachedPlanSize="32" CompileTime="10" CompileCPU="10" CompileMemory="296">
            <MissingIndexes>
              <MissingIndexGroup Impact="82.4536">
                <MissingIndex Database="[planoper]" Schema="[dbo]" Table="[t4]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[a]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="2912" RequiredMemory="1024" DesiredMemory="2912" RequestedMemory="2912" GrantWaitTime="0" GrantedMemory="2912" MaxUsedMemory="896" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104846" EstimatedPagesCached="11834" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="11" EstimateCPU="0.228447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5532.16" LogicalOp="Left Semi Join" NodeId="0" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.407384">
              <OutputList>
                <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                </HashKeysProbe>
                <ProbeResidual>
                  <ScalarOperator ScalarString="[planoper].[dbo].[t4].[a]=[planoper].[dbo].[t5].[a] as [z].[a]">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </ProbeResidual>
                <RelOp AvgRowSize="11" EstimateCPU="0.0110785" EstimateIO="0.0565368" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0676153" TableCardinality="10000">
                  <OutputList>
                    <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="false" ForcedIndex="true" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t5]" Alias="[z]" Column="a" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[planoper]" Schema="[dbo]" Table="[t5]" Index="[c1]" Alias="[z]" IndexKind="Clustered" />
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="11" EstimateCPU="0.011157" EstimateIO="0.100162" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.111319" TableCardinality="10000">
                  <OutputList>
                    <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="10000" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[planoper]" Schema="[dbo]" Table="[t4]" Column="a" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[planoper]" Schema="[dbo]" Table="[t4]" IndexKind="Heap" />
                  </TableScan>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Ele até me diz para criar este índice:

USE [planoper];
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[t4] ([a]);

É possível consultar um arquivo delimitado por tabulações do Sql Server Management Studio para visualizar seus dados sem salvá-los em nenhum lugar?

Eu sei que você pode BULK INSERTa partir de um arquivo delimitado por tabulações usando algo como:

BULK INSERT SomeTable
FROM 'MyFile.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

no entanto, isso requer que você conheça as colunas com antecedência e crie uma tabela para armazenar os dados.

Também sei que você pode consultar alguns outros tipos de arquivo, como CSV ou Excel, sem definir as colunas com antecedência usando OPENROWSETe os drivers do Excel, como:

-- Query CSV
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=\\Server\Folder\;HDR=Yes;', 
    'SELECT * FROM MyFile.csv')

-- Query Excel
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 8.0;Database=MyFile.xls', 
    'SELECT * FROM [Sheet1$]')

FormatAlém disso, se eu alterar a chave de registro HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Textde CSVDelimitedpara TabDelimitedno SQL Server, a consulta CSV acima lerá corretamente um arquivo de texto delimitado por tabulações; deixar assim.

Tentar usar Format=TabDelimitedno OPENROWSETtambém não funciona

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=\\Server\Folder\;HDR=Yes;Format=TabDelimited', 
    'SELECT * FROM MyFile.txt')

Eu fiz algumas tentativas de copiar as Textchaves de registro das chaves Enginese ISAM Formatspara algo personalizado cujo padrão é TabDelimited, no entanto, ele ainda está lendo arquivos em CSVFormatvez de TabDelimitedformatar, então devo estar perdendo alguma coisa aqui.

Existe uma maneira de consultar um arquivo delimitado por tabulações para visualizar seu conteúdo sem precisar criar uma tabela e BULK INSERTfazer isso?

Estou usando o SQL Server 2005

Há um debate no trabalho de minha esposa sobre apenas usar varchar(255)para todos os varcharcampos em tabelas temporárias em procedimentos armazenados. Basicamente, um campo deseja usar 255 porque sempre funcionará mesmo se a definição mudar, e o outro campo deseja manter o tamanho nas tabelas de origem para possíveis melhorias de desempenho.

O acampamento de desempenho está certo? Existem outras implicações? Eles estão usando o SQL Server.

No SO, alguém perguntou recentemente Por que o ORDER BY não está usando o índice?

A situação envolvia uma tabela InnoDB simples no MySQL com três colunas e 10 mil linhas. Uma das colunas, um número inteiro, foi indexada - e o OP procurou recuperar toda a tabela classificada nessa coluna:

SELECT * FROM person ORDER BY age

Ele anexou EXPLAINa saída mostrando que essa consulta foi resolvida com um filesort(em vez do índice) e perguntou por que isso aconteceria.

Apesar da dica que faz FORCE INDEX FOR ORDER BY (age) com que o índice seja usado , alguém respondeu (com comentários de apoio/votos positivos de outros) que um índice só é usado para classificação quando todas as colunas selecionadas são lidas do índice (ou seja, como normalmente seria indicado por Using indexna Extracoluna de EXPLAINsaída). Posteriormente, foi dada uma explicação de que percorrer o índice e, em seguida, buscar colunas da tabela resulta em E/S aleatória, que o MySQL considera mais caro do que um arquivo filesort.

Isso parece ir contra o capítulo do manual sobre ORDER BYOtimização , que não apenas transmite a forte impressão de que a satisfação ORDER BYde um índice é preferível a realizar uma classificação adicional (na verdade, filesorté uma combinação de quicksort e mergesort e , portanto, deve ter um limite inferior de ; enquanto percorrer o índice em ordem e procurar na tabela deveria ser - então isso faz todo o sentido), mas também deixa de mencionar essa suposta "otimização" ao mesmo tempo em que afirma:Ω(nlog n)O(n)

As consultas a seguir usam o índice para resolver a ORDER BYpeça:

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

Na minha leitura, esse é precisamente o caso nesta situação (ainda assim o índice não estava sendo usado sem uma dica explícita).

Minhas perguntas são:

• É realmente necessário que todas as colunas selecionadas sejam indexadas para que o MySQL opte por usar o índice?

  • Em caso afirmativo, onde isso está documentado (se houver)?

  • Se não, o que estava acontecendo aqui?

Estou escrevendo um aplicativo que precisa liberar um grande número de atualizações no banco de dados por um longo período de tempo e fiquei preso em como otimizar a consulta. Atualmente estou usando INSERT INTO ... VALUES (..), (..) ON DUPLICATE KEY UPDATE, que funciona para agrupar todos os valores em uma consulta, mas é executado de forma extremamente lenta em tabelas grandes. Na verdade, eu nunca preciso inserir linhas.

Outras abordagens que vi são atualizar usando SET value = CASE WHEN...(o que seria difícil de gerar devido à maneira como estou construindo as consultas e não tenho certeza sobre o desempenho de CASEcentenas/milhares de chaves) e simplesmente várias concatenadas atualizações. Algum deles seria mais rápido que o meu método atual?

Fico perplexo que, até onde eu saiba, não exista uma maneira idiomática e eficiente de fazer isso no MySQL. Se realmente não houver uma maneira mais rápida que ON DUPLICATE KEY, valeria a pena mudar para o PostgreSQL e usar sua UPDATE FROMsintaxe?

Quaisquer outras sugestões também são muito apreciadas!

Edit: aqui está uma das tabelas que são atualizadas com frequência. Eu removi os nomes das colunas por serem irrelevantes.

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` bigint(20) unsigned NOT NULL DEFAULT '0',
  `b` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c` enum('0','1','2') NOT NULL DEFAULT '0',
  `d` char(32) NOT NULL,
  -- trimmed --
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Estamos tendo problemas para lidar com o tráfego durante os horários de pico para nosso servidor de banco de dados. Estamos procurando melhorar o hardware (veja esta pergunta sobre esse lado das coisas ), mas também queremos trabalhar na configuração do pooling e no ajuste do servidor.

O aplicativo em que estamos trabalhando é um jogo multijogador baseado em turnos para smartphones, onde o back-end consiste em Rails com unicórnio e PostgreSQL 9.1 como banco de dados. Atualmente, temos 600.000 usuários registrados e, como o estado do jogo é armazenado no banco de dados, vários milhares de gravações são feitas a cada dois segundos. Analisamos os arquivos de log do PostgreSQL usando o PgBadger e durante as horas críticas recebemos muitos

FATAL: remaining connection slots are reserved for non-replication superuser connections

A solução ingênua para combater esse problema seria aumentar max_connections (que atualmente é 100) em postgresql.conf . Eu li http://wiki.postgresql.org/wiki/Number_Of_Database_Connections que indica que isso pode não ser a coisa certa a fazer. No artigo mencionado, é referido encontrar o ponto ideal entre max_connections e pool size .

O que pode ser feito para encontrar esse ponto ideal? Existem boas ferramentas para medir o desempenho de E/S para diferentes valores de max_connections e tamanho do pool ?

Nossa configuração atual é de 4 servidores de jogo, cada um com 16 trabalhadores unicórnios e um tamanho de pool de 5.

Aqui estão as configurações postgres não padrão que estamos usando:

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB

Digamos que eu tenha a Tabela A: BookingsPerPerson

Person_Id    ArrivalDate    DepartureDate
123456       2012-01-01     2012-01-04
213415       2012-01-02     2012-01-07

O que eu preciso alcançar com uma visão é o seguinte:

Person_Id    ArrivalDate    DepartureDate    Jan-01    Jan-02    Jan-03    Jan-04    Jan-05    Jan-06    Jan-07
123456       2012-01-01     2012-01-04       1         1         1         1
213415       2012-01-02     2012-01-07                 1         1         1         1         1         1

O sistema é para eventos, então cada reserva de hotel pode levar de 1 a 15 dias, mas não mais que isso. Todas as idéias seriam muito apreciadas.

Eu tenho uma tabela de log que captura o carimbo de data e hora de quando determinados arquivos foram exportados para outro sistema.

A tabela exportedLog atualmente possui três campos:

id                (primary key)
messageId         (int)
exportedDateTime  (datetime)

Revendo isso, descobri que o idcampo não serve para nada, pois não há junções para esta tabela. A única coisa que funciona nesta tabela é a inserção do trabalho em lote que processa as mensagens e insere nesta tabela de log.

Devo remover o idcampo?

Devo ter uma chave primária em um messageIdou em exportedDateTimeambos?