Eu tenho 2 tabelas no meu banco de dados.

Tabela 1

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------

Mesa 2

-----------------------------------------
| gender | address | phone | birthdate |
-----------------------------------------

na tabela #1 as colunas address e phone2 estão vazias e os valores das colunas gender e birthdate são os mesmos da tabela #2.

Como posso ler dados da tabela nº 2 e atualizar endereço e telefone2 na tabela nº 1 com valores da tabela nº 2 endereço e colunas de telefone quando sexo e data de nascimento são os mesmos em cada linha?

por exemplo: estes são alguns dados na Tabela #1

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------
| john | doe    | 12345| [email protected]| Male  |         |         | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| [email protected]| Male  |         |         | 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| [email protected]|Female|         |         | 1970-01-01|
-------------------------------------------------------------------------

e aqui estão alguns dados na tabela #2

-----------------------------------------
| gender | address | phone | birthdate  |
-----------------------------------------
| Male   | 1704test|0457852|1980-01-01  |
-----------------------------------------
| Female | 1705abcs|0986532|1970-01-01  |
-----------------------------------------
| Male   | 1602cyzd|0326589|1990-01-01  |
-----------------------------------------

Eu quero atualizar a tabela #1 com dados da tabela #2 e verificar sexo e data de nascimento e fazer a tabela #1 como

-------------------------------------------------------------------------
| name | family | phone | email | gender | phone2 | address | birthdate |
-------------------------------------------------------------------------
| john | doe    | 12345| [email protected]| Male   |0457852 |1704test | 1980-01-01|
-------------------------------------------------------------------------
| mike | clark  | 65432| [email protected]| Male   |0326589  |1602cyzd| 1990-01-01|
-------------------------------------------------------------------------
| Sara | King   | 875465| [email protected]|Female |0986532  |1705abcs| 1970-01-01|
-------------------------------------------------------------------------

Como posso fazer isso?

A tabela tpossui dois índices:

create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);

insert into t (a,b)
select i, i
from generate_series(1, 100000) g(i)
;

Nenhum índice é usado com o ANYoperador:

explain analyze
select *
from t
where (a,b) = any(array[(1,1),(1,2)])
;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1000 width=8) (actual time=0.042..126.789 rows=1 loops=1)
   Filter: (ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   Rows Removed by Filter: 99999
 Planning time: 0.122 ms
 Execution time: 126.836 ms

Mas um deles é usado com o INoperador:

explain analyze
select *
from t
where (a,b) in ((1,1),(1,2))
;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_a_b_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
   Index Cond: (a = 1)
   Filter: ((b = 1) OR (b = 2))
   Heap Fetches: 1
 Planning time: 0.161 ms
 Execution time: 0.066 ms

Ele usa o índice de registro se o registro for convertido para o tipo correto:

explain analyze
select *
from t
where (a,b)::int_pair = any(array[row(1,1),row(1,2)])
;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Index Scan using t_row_idx on t  (cost=0.42..12.87 rows=2 width=8) (actual time=0.106..0.126 rows=1 loops=1)
   Index Cond: (ROW(a, b)::int_pair = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 0.208 ms
 Execution time: 0.203 ms

Por que o planejador não usa o índice de não registro para o ANYoperador como o usa para o INoperador?

Vendo esta nota no mysqld.log:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)

Parece haver menção a algo assim aqui: instância do MySQL parando "fazendo o índice SYNC"

Minha pergunta é: qual ação deve ser tomada, se houver, quando esta nota for vista nos logs?

Versões do MySQL e do SO:
mysql-community-server- 5.7.9 -1.el7.x86_64
centos-release-7-1.1503.el7.centos.2.8.x86_64

Executando SHOW VARIABLES LIKE 'innodb%'; como sugerido mostra:

innodb_page_cleaners | 1

De acordo com este blog , os parâmetros para uma função ou um procedimento armazenado são essencialmente passados ​​por valor se não forem OUTPUTparâmetros e tratados essencialmente como uma versão mais segura de passagem por referência se forem OUTPUTparâmetros.

A princípio, pensei que o objetivo de forçar a declaração do TVP READONLYera sinalizar claramente aos desenvolvedores que o TVP não pode ser usado como OUTPUTparâmetro, mas deve haver mais coisas acontecendo porque não podemos declarar não-TVP como READONLY. Por exemplo, o seguinte falha:

create procedure [dbo].[test]
@a int readonly
as
    select @a

Msg 346, Nível 15, Estado 1, Teste de procedimento
O parâmetro "@a" não pode ser declarado READONLY, pois não é um parâmetro com valor de tabela.

1. Como as estatísticas não são armazenadas no TVP, qual é a lógica por trás da prevenção de operações DML?
2. Está relacionado a não querer que o TVP seja OUTPUTparâmetro por algum motivo?

Estou tentando executar um UNPIVOTem várias colunas contidas em sys.databasesvárias versões do SQL Server, variando de 2005 a 2012.

O UNPIVOTestá falhando com a seguinte mensagem de erro:

Msg 8167, Nível 16, Estado 1, Linha 48

O tipo de coluna "CompatibilityLevel" está em conflito com o tipo de outras colunas especificadas na lista UNPIVOT.

O T-SQL:

DECLARE @dbname SYSNAME;
SET @dbname = DB_NAME();

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc)
        , CompatibilityLevel            = CONVERT(VARCHAR(50), CASE d.[compatibility_level] WHEN 70 THEN 'SQL Server 7' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' ELSE 'UNKNOWN' END)
        , AutoClose                     = CONVERT(VARCHAR(50), CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(VARCHAR(50), CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(VARCHAR(50), CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(VARCHAR(50), CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(VARCHAR(50), CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  )
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc)
        , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

Isso é projetado para fornecer uma lista bem formatada de opções de banco de dados para o banco de dados fornecido, semelhante a:

+----------+----------------------------+----------------------------+
| Database | Configuration Item         | Value in Use               |
+----------+----------------------------+----------------------------+
| master   | RecoveryModel              | SIMPLE                     |
| master   | CompatibilityLevel         | SQL Server 2008            |
| master   | AutoClose                  | FALSE                      |
| master   | AutoCreateStatistics       | TRUE                       |
| master   | AutoShrink                 | FALSE                      |
| master   | AutoUpdateStatistics       | TRUE                       |
| master   | AutoUpdateStatisticsAsynch | FALSE                      |
| master   | CloseCursorOnCommit        | FALSE                      |
| master   | DefaultCursor              | GLOBAL                     |
| master   | ANSINULL_Default           | FALSE                      |
| master   | ANSINULLS_Enabled          | FALSE                      |
| master   | ANSIPadding_Enabled        | FALSE                      |
| master   | ANSIWarnings_Enabled       | FALSE                      |
| master   | ArithmeticAbort_Enabled    | FALSE                      |
| master   | ConcatNullYieldsNull       | FALSE                      |
| master   | CrossDBOwnerChain          | TRUE                       |
| master   | DateCorrelationOptimized   | FALSE                      |
| master   | NumericRoundAbort          | FALSE                      |
| master   | Parameterization           | SIMPLE                     |
| master   | QuotedIdentifiers_Enabled  | FALSE                      |
| master   | RecursiveTriggers_Enabled  | FALSE                      |
| master   | TrustWorthy                | TRUE                       |
| master   | VARDECIMAL_Storage         | TRUE                       |
| master   | PageVerify                 | CHECKSUM                   |
| master   | BrokerEnabled              | FALSE                      |
| master   | DatabaseReadOnly           | FALSE                      |
| master   | EncryptionEnabled          | FALSE                      |
| master   | RestrictedAccess           | MULTI_USER                 |
| master   | Collation                  | Latin1_General_CI_AS_KS_WS |
+----------+----------------------------+----------------------------+

Quando executo isso em um servidor com Latin1_General_CI_AS_KS_WSagrupamento, a instrução é bem-sucedida. Se eu modificar o T-SQL para que determinados campos tenham uma COLLATEcláusula, ele será executado em servidores que tenham outros agrupamentos.

O código que funciona em servidores com agrupamentos diferentes Latin1_General_CI_AS_KS_WSé:

DECLARE @dbname SYSNAME;
SET @dbname = DB_NAME();

SELECT [Database]            = unpvt.DatabaseName
    , [Configuration Item]   = unpvt.OptionName
    , [Configuration Value]  = unpvt.OptionValue
FROM (
    SELECT 
        DatabaseName = name 
        , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc) COLLATE SQL_Latin1_General_CP1_CI_AS
        , CompatibilityLevel            = CONVERT(VARCHAR(50), CASE d.[compatibility_level] WHEN 70 THEN 'SQL Server 7' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' ELSE 'UNKNOWN' END) 
        , AutoClose                     = CONVERT(VARCHAR(50), CASE d.is_auto_close_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoCreateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_create_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoShrink                    = CONVERT(VARCHAR(50), CASE d.is_auto_shrink_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatistics          = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , AutoUpdateStatisticsAsynch    = CONVERT(VARCHAR(50), CASE d.is_auto_update_stats_async_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CloseCursorOnCommit           = CONVERT(VARCHAR(50), CASE d.is_cursor_close_on_commit_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DefaultCursor                 = CONVERT(VARCHAR(50), CASE d.is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END)
        , ANSINULL_Default              = CONVERT(VARCHAR(50), CASE d.is_ansi_null_default_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSINULLS_Enabled             = CONVERT(VARCHAR(50), CASE d.is_ansi_nulls_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIPadding_Enabled           = CONVERT(VARCHAR(50), CASE d.is_ansi_padding_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ANSIWarnings_Enabled          = CONVERT(VARCHAR(50), CASE d.is_ansi_warnings_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ArithmeticAbort_Enabled       = CONVERT(VARCHAR(50), CASE d.is_arithabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , ConcatNullYieldsNull          = CONVERT(VARCHAR(50), CASE d.is_concat_null_yields_null_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , CrossDBOwnerChain             = CONVERT(VARCHAR(50), CASE d.is_db_chaining_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DateCorrelationOptimized      = CONVERT(VARCHAR(50), CASE d.is_date_correlation_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , NumericRoundAbort             = CONVERT(VARCHAR(50), CASE d.is_numeric_roundabort_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [Parameterization]            = CONVERT(VARCHAR(50), CASE d.is_parameterization_forced WHEN 0 THEN 'SIMPLE' ELSE 'FORCED' END)
        , QuotedIdentifiers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_quoted_identifier_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RecursiveTriggers_Enabled     = CONVERT(VARCHAR(50), CASE d.is_recursive_triggers_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , [TrustWorthy]                 = CONVERT(VARCHAR(50), CASE d.is_trustworthy_on WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , VARDECIMAL_Storage            = CONVERT(VARCHAR(50), 'TRUE')
        , PageVerify                    = CONVERT(VARCHAR(50), page_verify_option_desc  ) COLLATE SQL_Latin1_General_CP1_CI_AS
        , BrokerEnabled                 = CONVERT(VARCHAR(50), CASE d.is_broker_enabled WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , DatabaseReadOnly              = CONVERT(VARCHAR(50), CASE d.is_read_only WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , EncryptionEnabled             = CONVERT(VARCHAR(50), CASE d.is_encrypted WHEN 0 THEN 'FALSE' ELSE 'TRUE' END)
        , RestrictedAccess              = CONVERT(VARCHAR(50), user_access_desc) COLLATE SQL_Latin1_General_CP1_CI_AS
        , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
    FROM sys.databases d
    WHERE name = @dbname
        OR @dbname IS NULL
    ) src
UNPIVOT
(
    OptionValue FOR OptionName IN
    (
        RecoveryModel
        , CompatibilityLevel
        , AutoClose
        , AutoCreateStatistics 
        , AutoShrink 
        , AutoUpdateStatistics 
        , AutoUpdateStatisticsAsynch 
        , CloseCursorOnCommit 
        , DefaultCursor 
        , ANSINULL_Default 
        , ANSINULLS_Enabled 
        , ANSIPadding_Enabled 
        , ANSIWarnings_Enabled 
        , ArithmeticAbort_Enabled 
        , ConcatNullYieldsNull 
        , CrossDBOwnerChain 
        , DateCorrelationOptimized 
        , NumericRoundAbort 
        , [Parameterization] 
        , QuotedIdentifiers_Enabled 
        , RecursiveTriggers_Enabled 
        , [TrustWorthy] 
        , VARDECIMAL_Storage 
        , PageVerify 
        , BrokerEnabled 
        , DatabaseReadOnly 
        , EncryptionEnabled 
        , RestrictedAccess 
        , Collation
    )
) AS unpvt;

O comportamento observado é que os campos a seguir não observam a ordenação do servidor ou a ordenação do banco de dados; eles são sempre apresentados em Latin1_General_CI_AS_KS_WSagrupamento.

No SQL Server 2012, podemos sys.sp_describe_first_result_setfacilmente obter metadados sobre as colunas retornadas de uma determinada consulta. Eu usei o seguinte para determinar a incompatibilidade de agrupamento:

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
SELECT 
    DatabaseName                    = CONVERT(VARCHAR(50), d.name)
    , RecoveryModel                 = CONVERT(VARCHAR(50), d.recovery_model_desc) 
    , Collation                     = CONVERT(VARCHAR(50), d.collation_name)
FROM sys.databases d
WHERE name = DB_NAME();
';

EXEC sp_describe_first_result_set @command = @cmd;

Os resultados:

Por que o agrupamento dessas colunas é definido estaticamente?

Eu li estes artigos no PCMag por Itzik Ben-Gan :

Procure e você fará a varredura Parte I: Quando o otimizador não otimiza
Procure e você fará a varredura Parte II: Chaves Ascendentes

No momento, estou tendo um problema de "Grouped Max" com todas as nossas tabelas particionadas. Usamos o truque fornecido por Itzik Ben-Gan para obter um max(ID), mas às vezes ele simplesmente não funciona:

DECLARE @MaxIDPartitionTable BIGINT
SELECT  @MaxIDPartitionTable = ISNULL(MAX(IDPartitionedTable), 0)
FROM    ( SELECT    *
          FROM      ( SELECT    partition_number PartitionNumber
                      FROM      sys.partitions
                      WHERE     object_id = OBJECT_ID('fct.MyTable')
                                AND index_id = 1
                    ) T1
                    CROSS APPLY ( SELECT    ISNULL(MAX(UpdatedID), 0) AS IDPartitionedTable
                                  FROM      fct.MyTable s
                                  WHERE     $PARTITION.PF_MyTable(s.PCTimeStamp) = PartitionNumber
                                            AND UpdatedID <= @IDColumnThresholdValue
                                ) AS o
        ) AS T2;
SELECT @MaxIDPartitionTable 

eu pego esse plano

Mas depois de 45 minutos, olhe para as leituras

reads          writes   physical_reads
12,949,127        2       12,992,610

de onde eu saio sp_whoisactive.

Normalmente é executado muito rapidamente, mas não hoje.

Editar: estrutura da tabela com partições:

CREATE PARTITION FUNCTION [MonthlySmallDateTime](SmallDateTime) AS RANGE RIGHT FOR VALUES (N'2000-01-01T00:00:00.000', N'2000-02-01T00:00:00.000' /* and many more */)
go
CREATE PARTITION SCHEME PS_FctContractualAvailability AS PARTITION [MonthlySmallDateTime] TO ([Standard], [Standard])
GO
CREATE TABLE fct.MyTable(
    MyTableID BIGINT IDENTITY(1,1),
    [DT1TurbineID] INT NOT NULL,
    [PCTimeStamp] SMALLDATETIME NOT NULL,
    Filler CHAR(100) NOT NULL DEFAULT 'N/A',
    UpdatedID BIGINT NULL,
    UpdatedDate DATETIME NULL
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [DT1TurbineID] ASC,
    [PCTimeStamp] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
) ON [PS_FctContractualAvailability]([PCTimeStamp])

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_UpdatedID_PCTimeStamp] ON [fct].MyTable
(
    [UpdatedID] ASC,
    [PCTimeStamp] ASC
)
INCLUDE (   [UpdatedDate]) 
WHERE ([UpdatedID] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
GO

Primeiras palavras

Você pode ignorar com segurança as seções abaixo (e incluindo) JOINs: Starting Off se você quiser apenas decifrar o código. O pano de fundo e os resultados servem apenas como contexto. Por favor, olhe o histórico de edições antes de 2015-10-06 se você quiser ver como era o código inicialmente.

Objetivo

Por fim, quero calcular as coordenadas GPS interpoladas para o transmissor ( Xou Xmit) com base nos carimbos DateTime dos dados GPS disponíveis na tabela SecondTableque flanqueiam diretamente a observação na tabela FirstTable.

Meu objetivo imediato para atingir o objetivo final é descobrir a melhor forma de se juntar FirstTablepara SecondTableobter esses pontos de tempo de flanco. Mais tarde, posso usar essas informações e calcular coordenadas GPS intermediárias assumindo um ajuste linear ao longo de um sistema de coordenadas equiretangulares (palavras elegantes para dizer que não me importo que a Terra seja uma esfera nessa escala).

Perguntas

1. Existe uma maneira mais eficiente de gerar os carimbos de data/hora antes e depois mais próximos?
   • Corrigido por mim mesmo, apenas pegando o "depois" e, em seguida, obtendo o "antes" apenas conforme relacionado ao "depois".
2. Existe uma maneira mais intuitiva que não envolva a (A<>B OR A=B)estrutura.
   • Byrdzeye forneceu as alternativas básicas, no entanto, minha experiência no "mundo real" não se alinhou com todas as 4 de suas estratégias de junção com o mesmo desempenho. Mas todo o crédito a ele por abordar os estilos alternativos de junção.
3. Quaisquer outros pensamentos, truques e conselhos que você possa ter.
   • Até agora, tanto byrdzeye quanto Phrancis foram bastante úteis a esse respeito. Achei que o conselho de Phrancis foi excelentemente apresentado e forneceu ajuda em um estágio crítico, então vou dar a ele uma vantagem aqui.

Eu ainda gostaria de qualquer ajuda adicional que eu possa receber em relação à pergunta 3. Os marcadores refletem quem eu acredito que mais me ajudou na pergunta individual.

Definições de tabela

Representação semivisual

PrimeiraTabela

Fields
  RecTStamp | DateTime  --can contain milliseconds via VBA code (see Ref 1) 
  ReceivID  | LONG
  XmitID    | TEXT(25)
Keys and Indices
  PK_DT     | Primary, Unique, No Null, Compound
    XmitID    | ASC
    RecTStamp | ASC
    ReceivID  | ASC
  UK_DRX    | Unique, No Null, Compound
    RecTStamp | ASC
    ReceivID  | ASC
    XmitID    | ASC

SegundaTabela

Fields
  X_ID      | LONG AUTONUMBER -- seeded after main table has been created and already sorted on the primary key
  XTStamp   | DateTime --will not contain partial seconds
  Latitude  | Double   --these are in decimal degrees, not degrees/minutes/seconds
  Longitude | Double   --this way straight decimal math can be performed
Keys and Indices
  PK_D      | Primary, Unique, No Null, Simple
    XTStamp   | ASC
  UIDX_ID   | Unique, No Null, Simple
    X_ID      | ASC

Tabela de detalhes do receptor

Fields
  ReceivID                      | LONG
  Receiver_Location_Description | TEXT -- NULL OK
  Beginning                     | DateTime --no partial seconds
  Ending                        | DateTime --no partial seconds
  Lat                           | DOUBLE
  Lon                           | DOUBLE
Keys and Indicies
  PK_RID  | Primary, Unique, No Null, Simple
    ReceivID | ASC

tabela ValidXmitters

Field (and primary key)
  XmitID    | TEXT(25) -- primary, unique, no null, simple

violino SQL...

...para que você possa brincar com as definições e o código da tabela Esta pergunta é para o MSAccess, mas, como Phrancis apontou, não há um estilo SQL fiddle para o Access. Portanto, você deve poder acessar aqui para ver minhas definições de tabela e código com base na resposta de Phrancis :
http://sqlfiddle.com/#!6/e9942/4 (link externo)

JOINs: Começando

Minha estratégia JOIN atual de "entranhas internas"

Primeiro, crie um FirstTable_rekeyed com a ordem das colunas e a chave primária composta, (RecTStamp, ReceivID, XmitID)todas indexadas/classificadas ASC. Também criei índices em cada coluna individualmente. Em seguida, preencha-o assim.

INSERT INTO FirstTable_rekeyed (RecTStamp, ReceivID, XmitID)
  SELECT DISTINCT ROW RecTStamp, ReceivID, XmitID
  FROM FirstTable
  WHERE XmitID IN (SELECT XmitID from ValidXmitters)
  ORDER BY RecTStamp, ReceivID, XmitID;

A consulta acima preenche a nova tabela com 153.006 registros e retorna em cerca de 10 segundos.

O seguinte é concluído em um ou dois segundos quando todo esse método é agrupado em um "SELECT Count(*) FROM ( ... )" quando o método de subconsulta TOP 1 é usado

SELECT 
    ReceiverRecord.RecTStamp, 
    ReceiverRecord.ReceivID, 
    ReceiverRecord.XmitID,
    (SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
    FROM FirstTable_rekeyed AS ReceiverRecord
    -- INNER JOIN SecondTable AS XmitGPS ON (ReceiverRecord.RecTStamp < XmitGPS.XTStamp)
         GROUP BY RecTStamp, ReceivID, XmitID;
-- No separate join needed for the Top 1 method, but it would be required for the other methods. 
-- Additionally no restriction of the returned set is needed if I create the _rekeyed table.
-- May not need GROUP BY either. Could try ORDER BY.
-- The three AfterXmit_ID alternatives below take longer than 3 minutes to complete (or do not ever complete).
  -- FIRST(XmitGPS.X_ID)
  -- MIN(XmitGPS.X_ID)
  -- MIN(SWITCH(XmitGPS.XTStamp > ReceiverRecord.RecTStamp, XmitGPS.X_ID, Null))

Consulta JOIN anterior "inner guts"

Primeiro (rápido... mas não bom o suficiente)

SELECT 
  A.RecTStamp,
  A.ReceivID,
  A.XmitID,
  MAX(IIF(B.XTStamp<= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,
  MIN(IIF(B.XTStamp > A.RecTStamp,B.XTStamp,Null)) as AfterXTStamp
FROM FirstTable as A
INNER JOIN SecondTable as B ON 
  (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)
GROUP BY A.RecTStamp, A.ReceivID, A.XmitID
  -- alternative for BeforeXTStamp MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
  -- alternatives for AfterXTStamp (see "Aside" note below)
  -- 1.0/(MAX(1.0/(-(B.XTStamp>A.RecTStamp)*B.XTStamp)))
  -- -1.0/(MIN(1.0/((B.XTStamp>A.RecTStamp)*B.XTStamp)))

Segundo (mais lento)

SELECT
  A.RecTStamp, AbyB1.XTStamp AS BeforeXTStamp, AbyB2.XTStamp AS AfterXTStamp
FROM (FirstTable AS A INNER JOIN 
  (select top 1 B1.XTStamp, A1.RecTStamp 
   from SecondTable as B1, FirstTable as A1
   where B1.XTStamp<=A1.RecTStamp
   order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)
ON A.RecTStamp = AbyB1.RecTStamp) INNER JOIN 
  (select top 1 B2.XTStamp, A2.RecTStamp 
   from SecondTable as B2, FirstTable as A2
   where B2.XTStamp>A2.RecTStamp
   order by B2.XTStamp ASC) AS AbyB2 --MIN (time points after)
ON A.RecTStamp = AbyB2.RecTStamp; 

Fundo

Eu tenho uma tabela de telemetria (com o apelido de A) de pouco menos de 1 milhão de entradas com uma chave primária composta baseada em um DateTimecarimbo, uma ID de transmissor e uma ID de dispositivo de gravação. Devido a circunstâncias fora do meu controle, minha linguagem SQL é o Jet DB padrão no Microsoft Access (os usuários usarão 2007 e versões posteriores). Apenas cerca de 200.000 dessas entradas são relevantes para a consulta devido ao ID do transmissor.

Existe uma segunda tabela de telemetria (alias B) que envolve aproximadamente 50.000 entradas com uma única DateTimechave primária

Na primeira etapa, concentrei-me em encontrar os carimbos de data/hora mais próximos dos carimbos da primeira tabela a partir da segunda tabela.

Resultados do JOIN

Curiosidades que descobri...

...ao longo do caminho durante a depuração

É realmente estranho escrever a JOINlógica como FROM FirstTable as A INNER JOIN SecondTable as B ON (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)@byrdzeye apontou em um comentário (que desde então desapareceu) é uma forma de junção cruzada. Observe que substituir LEFT OUTER JOINno INNER JOINcódigo acima parece não causar impacto na quantidade ou identidade das linhas retornadas. Também não consigo deixar de fora a cláusula ON ou dizer ON (1=1). Apenas usar uma vírgula para unir (em vez de INNERou LEFT OUTER JOIN) resulta em Count(select * from A) * Count(select * from B)linhas retornadas nesta consulta, em vez de apenas uma linha por tabela A, como o JOINretorno explícito (A<>B OR A=B). Isso claramente não é adequado. FIRSTnão parece estar disponível para uso dado um tipo de chave primária composta.

O segundo JOINestilo, embora indiscutivelmente mais legível, sofre por ser mais lento. Isso pode ocorrer porque dois JOINs internos adicionais são necessários contra a tabela maior, bem como os dois CROSS JOINs encontrados em ambas as opções.

Aparte: Substituir a IIFcláusula por MIN/ MAXparece retornar o mesmo número de entradas.
MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
funciona para o timestamp "Antes" ( MAX), mas não funciona diretamente para o "Depois" ( MIN) da seguinte forma:
MIN(-(B.XTStamp>A.RecTStamp)*B.XTStamp)
porque o mínimo é sempre 0 para a FALSEcondição. Esse 0 é menor do que qualquer pós-época DOUBLE(do qual um DateTimecampo é um subconjunto no Access e no qual esse cálculo transforma o campo). Os métodos IIFe MIN/ MAXAs alternativas propostas para o valor AfterXTStamp funcionam porque a divisão por zero ( FALSE) gera valores nulos, que as funções de agregação MIN e MAX ignoram.

Próximos passos

Levando isso adiante, desejo encontrar os timestamps na segunda tabela que flanqueiam diretamente os timestamps na primeira tabela e realizar uma interpolação linear dos valores de dados da segunda tabela com base na distância de tempo para esses pontos (ou seja, se o timestamp de a primeira tabela está a 25% do caminho entre o "antes" e o "depois", gostaria que 25% do valor calculado viesse dos dados do valor da 2ª tabela associados ao ponto "depois" e 75% do "antes" ). Usando o tipo de junção revisado como parte das entranhas internas, e após as respostas sugeridas abaixo, eu produzo ...

    SELECT
        AvgGPS.XmitID,
        StrDateIso8601Msec(AvgGPS.RecTStamp) AS RecTStamp_ms,
        -- StrDateIso8601MSec is a VBA function returning a TEXT string in yyyy-mm-dd hh:nn:ss.lll format
        AvgGPS.ReceivID,
        RD.Receiver_Location_Description,
        RD.Lat AS Receiver_Lat,
        RD.Lon AS Receiver_Lon,
        AvgGPS.Before_Lat * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lat * AvgGPS.AfterWeight AS Xmit_Lat,
        AvgGPS.Before_Lon * (1 - AvgGPS.AfterWeight) + AvgGPS.After_Lon * AvgGPS.AfterWeight AS Xmit_Lon,
        AvgGPS.RecTStamp AS RecTStamp_basic
    FROM ( SELECT 
        AfterTimestampID.RecTStamp,
        AfterTimestampID.XmitID,
        AfterTimestampID.ReceivID,
        GPSBefore.BeforeXTStamp, 
        GPSBefore.Latitude AS Before_Lat, 
        GPSBefore.Longitude AS Before_Lon,
        GPSAfter.AfterXTStamp, 
        GPSAfter.Latitude AS After_Lat, 
        GPSAfter.Longitude AS After_Lon,
        ( (AfterTimestampID.RecTStamp - GPSBefore.XTStamp) / (GPSAfter.XTStamp - GPSBefore.XTStamp) ) AS AfterWeight
        FROM (
            (SELECT 
                ReceiverRecord.RecTStamp, 
                ReceiverRecord.ReceivID, 
                ReceiverRecord.XmitID,
               (SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
             FROM FirstTable AS ReceiverRecord 
             -- WHERE ReceiverRecord.XmitID IN (select XmitID from ValidXmitters)
             GROUP BY RecTStamp, ReceivID, XmitID
            ) AS AfterTimestampID INNER JOIN SecondTable AS GPSAfter ON AfterTimestampID.AfterXmit_ID = GPSAfter.X_ID
        ) INNER JOIN SecondTable AS GPSBefore ON AfterTimestampID.AfterXmit_ID = GPSBefore.X_ID + 1
    ) AS AvgGPS INNER JOIN ReceiverDetails AS RD ON (AvgGPS.ReceivID = RD.ReceivID) AND (AvgGPS.RecTStamp BETWEEN RD.Beginning AND RD.Ending)
    ORDER BY AvgGPS.RecTStamp, AvgGPS.ReceivID;

...que retorna 152928 registros, correspondendo (pelo menos aproximadamente) ao número final de registros esperados. O tempo de execução é provavelmente de 5 a 10 minutos no meu sistema i7-4790, 16 GB de RAM, sem SSD, Win 8.1 Pro.

Referência 1: o MS Access pode lidar com valores de tempo de milissegundos - realmente e arquivo de origem [08080011.txt]

Estou definindo um cron job para coletar resultados do criador de perfil de banco de dados MongoDB. Eu gostaria de coletar resultados dentro de um período de 24 horas. Eu pretendo executar o comando mongo com javascript .

A pergunta é, no shell do Mongo , como escrevo uma consulta para encontrar um intervalo de datas de 24 horas atrás? Tal como:

db.system.profile.find({
    "timestamp" : {
        $lte : <current date & time>,
        $gt : <date & time 24 hrs ago>
    }
})

Temos um grande banco de dados SQL Server particionado utilizando estatísticas incrementais. Todos os índices são particionados alinhados. Quando tentamos reconstruir uma partição online por partição, todas as estatísticas desaparecem depois que o índice é reconstruído.

Abaixo está um script para replicar o problema no SQL Server 2014 com o banco de dados AdventureWorks2014.

--Example against AdventureWorks2014 Database

CREATE PARTITION FUNCTION TransactionRangePF1 (DATETIME)
AS RANGE RIGHT FOR VALUES 
(
   '20130501', '20130601', '20130701', '20130801', 
   '20130901', '20131001', '20131101', '20131201', 
   '20140101', '20140201', '20140301'
);
GO

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO 
(
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], 
  [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], 
  [PRIMARY], [PRIMARY], [PRIMARY]
);
GO

CREATE TABLE dbo.TransactionHistory 
(
  TransactionID        INT      NOT NULL, -- not bothering with IDENTITY here
  ProductID            INT      NOT NULL,
  ReferenceOrderID     INT      NOT NULL,
  ReferenceOrderLineID INT      NOT NULL DEFAULT (0),
  TransactionDate      DATETIME NOT NULL DEFAULT (GETDATE()),
  TransactionType      NCHAR(1) NOT NULL,
  Quantity             INT      NOT NULL,
  ActualCost           MONEY    NOT NULL,
  ModifiedDate         DATETIME NOT NULL DEFAULT (GETDATE()),
  CONSTRAINT CK_TransactionType 
    CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))
) 
ON TransactionsPS1 (TransactionDate);


INSERT INTO dbo.TransactionHistory
SELECT * FROM Production.TransactionHistory
--  SELECT * FROM sys.partitions
--  WHERE object_id = OBJECT_ID('dbo.TransactionHistory');

CREATE NONCLUSTERED INDEX IDX_ProductId ON dbo.TransactionHistory (ProductId) 
  WITH (DATA_COMPRESSION = ROW, STATISTICS_INCREMENTAL=ON)  
  ON TransactionsPS1 (TransactionDate)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT 'Stats are avialable'  

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

PRINT 'After online index rebuild by partition stats are now gone'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Rebuild the stats with a rebuild for all paritions (this works)' 
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
  PARTITION = ALL WITH (ONLINE = ON , DATA_COMPRESSION = ROW, 
  STATISTICS_INCREMENTAL = ON)

PRINT 'Stats are back'
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

PRINT 'Works correctly for an offline rebuild by partition'
ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
  PARTITION = 9 WITH (ONLINE = OFF , DATA_COMPRESSION = ROW)

    --stats still there  
DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);

ALTER INDEX [IDX_ProductId] ON [dbo].[TransactionHistory] REBUILD 
  PARTITION = 9 WITH (ONLINE = ON , DATA_COMPRESSION = ROW)

DBCC SHOW_STATISTICS('dbo.TransactionHistory', IDX_ProductId);
PRINT' stats are gone!!!!!!'

Conforme mostrado, não podemos reconstruir índices por partição online sem perder todas as estatísticas para o índice. Este é um grande problema de manutenção para nós. Quase parece que a opção incremental de estatísticas precisa fazer parte da sintaxe de reconstrução de índice único ou a opção on-line precisa tratá-la adequadamente, como faz a opção off-line.

Por favor, deixe-me saber se eu estou perdendo alguma coisa?

Atualizações:

No que diz respeito à nossa necessidade de estatísticas incrementais: estamos particionando em um ID de cliente interno e não em uma data. Portanto, quando um novo cliente é trazido (grande back-load de dados), podemos simplesmente atualizar as estatísticas da partição e evitar rapidamente a criação de planos ruins para esse novo cliente. Acho que vou arquivá-lo com a Microsoft como um bug e ver o que eles têm a dizer e seguir com a solução de apenas reamostrar as estatísticas para essa partição.

Conectar relatório de bug:

As estatísticas desaparecem após a reconstrução do índice online com estatísticas incrementais

Atualização: a Microsoft confirmou que é um bug.

Tenho uma tabela com 2 colunas. O tipo de ambas as colunas é definido como varchar(38). Se eu criar uma linha com um valor vazio para uma das colunas, ela ocupará o mesmo espaço de armazenamento como se o valor não estivesse vazio?

Em outras palavras, o MySQL reservará espaço de armazenamento para a coluna (dependendo de seu tipo) quando uma linha for criada?