All perguntas(dba)

Tenho a tarefa de inserir dados em algumas tabelas e retornar dados de sua interseção em uma consulta. As tabelas são vinculadas através de campos específicos ( solditemsrefere-se a productse invoices, invoicesrefere-se a customersetc). O truque aqui é que idin invoicesé definido automaticamente na inserção de linha. A consulta a seguir retorna um resultado vazio, a menos que eu remova JOINon invoices. Se isso SELECTfor feito em uma consulta separada, tudo estará OK. O que está errado?

WITH newid AS (
INSERT INTO invoices (customer,idate)
    VALUES (777,(SELECT now()::date))
    RETURNING id
),
sold AS (
INSERT INTO solditems (invoiceid,prod,qty)
    VALUES ((SELECT id FROM newid), 888, 1),
           ((SELECT id FROM newid), 999, 2)
    RETURNING *
)
SELECT * FROM sold AS s
   JOIN products AS p ON p.id=s.prod
   JOIN invoices AS i ON i.id=s.invoiceid;

Achei que a maneira de fazer isso era acessar meu banco de dados existente (Sql Server 16.0.1105.1 Developer Edition) no SSMS e executar Tasks | Gerar scripts...

Isso inclui uma FullTextTable. Então obtive o seguinte script (apenas mostrando o início):

USE [master]
GO
/****** Object:  Database [LouisHowe]    Script Date: 11/15/2023 5:56:31 PM ******/
CREATE DATABASE [LouisHowe]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LouisHowe', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'LouisHowe_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\LouisHowe_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [LouisHowe] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [LouisHowe].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [LouisHowe] SET ANSI_NULL_DEFAULT OFF 

E parece que o banco de dados foi criado ok. Mas recebi perto de 200 mensagens de erro (mostrando apenas as primeiras):

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'ON'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 5011, Level 14, State 5, Line 12
User does not have permission to alter database 'LouisHowe', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 12
ALTER DATABASE statement failed.

O que preciso fazer para corrigir o script?

Estou no SSMS conectado ao meu banco de dados SQL no Azure

Cliquei com o botão direito no login - nenhuma opção de propriedades no menu

Como posso alterar a senha de um login?

Criei um banco de dados para um app, onde possuo diversas redes, que possuem vários nós e elementos e também vários cenários, todos relacionados às redes. [veja a imagem abaixo]

Minha dúvida é: como o sce_resultse sce_paramatersterá informações de resultados e parâmetros do nodese elementsde uma rede, devo também construir esse relacionamento entre as tabelas nodes& elementse as tabelas sce_results& sce_paramatersvia chaves estrangeiras?

ou como fica complicado, a forma como criei as tabelas está boa? Talvez uma questão mais geral, quando é bom construir esses relacionamentos por meio de chaves estrangeiras?

Até agora, desde que nodes& elementstenho seus nomes, posso combiná-los com as namecolunas em sce_results& sce_paramaterstabelas, depois de baixar todos os dados necessários para fazer cálculos adicionais ou criar gráficos de resultados, etc... Portanto, eu realmente não preciso desse relacionamento de meu ponto de vista. O que você acha?

Exemplo:

Network1: Nodes: [Node1, Node2, Node3]
          Elements: [Element1, Element2]
          Scenarios: [ScenarioX]
ScenarioX: Paramater: [Pressure_Node1 = 5, Pressure_Node2 = 10, ...]
           Result: [Flow_Node1 = 100, ...]

# In this case nodes table looks like:
id | net_id | name | ... | x | y
1    1        Node1
2    1        Node2
...      

# In this case sce_parameter table looks like:
id | sce_id | name | extension | value | unit
1    1        Node1  Pressure    5
2    1        Node2  Pressure    10
...

Tenho duas consultas SQL relativamente complexas às quais associo usando um arquivo UNION ALL. Cada consulta individual é rápida e retorna instantaneamente. O problema é que, uma vez unidos, eles têm um desempenho terrivelmente ruim e muitas vezes expiram.

Esta é a consulta completa:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND (type = 1 OR type = 3)
    AND user_id = 'USER_ID'
    ORDER BY "counter" ASC
    LIMIT 100
) as sub1
UNION ALL
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND type = 2
    AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
    ORDER BY "counter" ASC
    LIMIT 100
) as sub2
ORDER BY counter ASC -- SLOW!!
LIMIT 100;

Depois de algumas pesquisas, descobri que o motivo da lentidão está no ORDER BY counter ASCfinal da declaração (não nas declarações internas - tudo bem). Se estiver lá, leva mais de um minuto e expira. Sem ele, ele retorna em 5ms.

Isso não faz sentido para mim, já que a essa altura temos um total de 200 linhas, então a ordenação deve ser muito rápida (especialmente porque as linhas já estão ordenadas!).

Tentei analisar as consultas, mas nada se destaca para mim:

• Consulta completa (leva mais de 60 segundos)

 Limit  (cost=1.70..325537.14 rows=100 width=101)
   ->  Merge Append  (cost=1.70..651072.57 rows=200 width=101)
         Sort Key: changes.counter
         ->  Limit  (cost=0.56..106310.10 rows=100 width=101)
               ->  Index Scan using changes_pkey on changes  (cost=0.56..4162018.71 rows=3915 width=101)
                     Index Cond: (counter > '-1'::integer)
                     Filter: (((user_id)::text = 'USER_ID'::text) AND ((type = 1) OR (type = 3)))
         ->  Limit  (cost=1.12..544758.47 rows=100 width=101)
               ->  Nested Loop  (cost=1.12..11516171.34 rows=2114 width=101)
                     ->  Index Scan using changes_pkey on changes changes_1  (cost=0.56..3986383.73 rows=10843703 width=101)
                           Index Cond: (counter > '-1'::integer)
                           Filter: (type = 2)
                     ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..0.69 rows=1 width=24)
                           Index Cond: ((user_id = 'USER_ID'::text) AND (item_id = (changes_1.item_id)::text))

• Consulta completa sem último ORDER BY(leva 5 ms) :

 Limit  (cost=23934.97..180049.76 rows=100 width=101)
   ->  Append  (cost=23934.97..336164.56 rows=200 width=101)
         ->  Limit  (cost=23934.97..23935.22 rows=100 width=101)
               ->  Sort  (cost=23934.97..23944.76 rows=3915 width=101)
                     Sort Key: changes.counter
                     ->  Bitmap Heap Scan on changes  (cost=284.11..23785.34 rows=3915 width=101)
                           Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                           Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
                           ->  Bitmap Index Scan on changes_user_id_index  (cost=0.00..283.13 rows=6209 width=0)
                                 Index Cond: ((user_id)::text = 'USER_ID'::text)
         ->  Limit  (cost=312214.83..312226.33 rows=100 width=101)
               ->  Gather Merge  (cost=312214.83..312357.89 rows=1244 width=101)
                     Workers Planned: 1
                     ->  Sort  (cost=311214.82..311217.93 rows=1244 width=101)
                           Sort Key: changes_1.counter
                           ->  Nested Loop  (cost=148.64..311167.28 rows=1244 width=101)
                                 ->  Parallel Bitmap Heap Scan on user_items  (cost=148.07..11209.94 rows=1785 width=24)
                                       Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                                       ->  Bitmap Index Scan on user_items_user_id_index  (cost=0.00..147.31 rows=3034 width=0)
                                             Index Cond: ((user_id)::text = 'USER_ID'::text)
                                 ->  Index Scan using changes_item_id_index on changes changes_1  (cost=0.56..167.91 rows=13 width=101)
                                       Index Cond: ((item_id)::text = (user_items.item_id)::text)
                                       Filter: ((counter > '-1'::integer) AND (type = 2))

Estou pensando que poderia simplesmente remover a instrução "ordenar por" e ordená-las eu mesmo no código, mas isso não parece muito claro.

Alguma ideia do que pode ser feito para melhorar isso?

Também fiz esta pergunta no Stack Overflow (desculpas se isso for uma violação de protocolo).

Estou migrando um banco de dados MS Access para MS SQL Server, onde irei mantê-lo usando SQL Server Management Studio (SSMS).

O banco de dados Access possui um relacionamento que conecta duas instâncias da mesma tabela por meio de uma tabela intermediária, conforme indicado pelo diagrama de relacionamento de acesso a seguir.

Este relacionamento de entidade permite a representação da estrutura da organização.

O Access permite criar o relacionamento acima usando arrastar e soltar e adicionar automaticamente o sufixo _1 para distinguir a instância adicionada.

O SQL é:

SELECT [T-Organisation].[Organisation Unit Id], 
[T-Organisation].[Organisation UnitName], 
[T-Organisation_1].[Organisation Unit Id], 
[T-Organisation_1].[Organisation UnitName] 
FROM ([T-Organisation] 
INNER JOIN [T-OrganisationSubsidiary] ON 
[T-Organisation].[Organisation Unit Id] = [T-OrganisationSubsidiary].[Parent Organisation Unit]) 
INNER JOIN [T-Organisation] AS [T-Organisation_1] ON 
[T-OrganisationSubsidiary].[Subsidiary Organisation Unit] = [T-Organisation_1].[Organisation Unit Id];

A menos que esteja faltando alguma coisa, o SSMS não me permite adicionar (arrastar e soltar) cópias de tabelas ao Diagrama de Relacionamento como o Access faz. Isso significa que preciso criar uma cópia física da tabela T-Organisation e nomeá-la manualmente, por exemplo, T-Organisation_1 ou existe uma solução melhor?

Sou um engenheiro de software com cerca de 10 anos de experiência na construção de aplicações web Laravel para pequenas empresas. Estou bastante confortável com o design de banco de dados, mas esse não é meu maior ponto forte.

Um dos aplicativos que desenvolvi é uma ferramenta de terceiros para monitorar aberturas e cliques em boletins informativos por e-mail. Este aplicativo armazena eventos de abertura e clique, e os usuários da ferramenta consultam esses eventos para ver o desempenho de seus boletins informativos por e-mail. A contagem atual de linhas da tabela de eventos analíticos é de cerca de 1,5 bilhão, o que representa cerca de um ano e meio de dados.

Pretendo migrar para um novo design de banco de dados e gostaria de ver se meu entendimento do design de banco de dados está no caminho certo.

Estas são as tabelas do novo banco de dados:

Table contacts {
  id integer
  email_address string
  created_at datetime
  updated_at datetime
  indexes {
    id [pk]
    email_address
    created_at
  }
}

Table contact_opens {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  opened_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    opened_at
  }
}

Table contact_clicks {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  clicked_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    clicked_at
  }
}

Pretendo usar o MariaDB em um servidor linode dedicado com 64 GB de RAM, particionar o banco de dados por mês e ajustar o InnoDB da seguinte maneira:

innodb_buffer_pool_size = 48GB
InnoDB File-Per-Table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 0_DIRECT
innodb_log_file_size = 8GB
table_open_cache = 20

As consultas neste banco de dados segmentarão os Contatos com base em Aberturas e Cliques. Por exemplo:

• Contatos com 5 ou mais aberturas nos últimos 30 dias
• Contatos com pelo menos 1 clique nos últimos 5 dias
• Contatos com 1 clique em cada um dos últimos 30 dias
• etc., consultas padrão de segmentação de boletins informativos por e-mail (como visto no Mailchimp)

As consultas retornarão de 10 mil a 300 mil contatos por vez.

Fiz muitas pesquisas e parece que indexação, particionamento e/ou fragmentação parecem ser a melhor opção para ajudar a acelerar as consultas. Parece que uma combinação de indexação e particionamento seria mais adequada para este caso de uso específico.

Esse uso parece um design de banco de dados sólido, onde a tabela de aberturas cresce cerca de um bilhão de linhas por ano e a tabela de cliques cresce cerca de 300 milhões de linhas por ano?

Atualização 1: o esquema não será alterado. Ele está mudando um pouco em relação à forma como foi projetado anos atrás, mas apenas para acomodar as alterações descritas nesta questão para tornar as consultas mais rápidas.

Atualização 2: As 300 mil consultas de contato são para correspondências em massa (boletins informativos por e-mail). Atualizados os tipos de dados nas tabelas MySQL acima.

Sou estudante de banco de dados e executei a seguinte consulta para aprender algumas coisas ao mesmo tempo (LEFT/RIGHT JOIN, UNION, WHERE + RegEx). O que me preocupa é a ordem de execução. Eu tenho duas tabelas, como tal:

create table practicaleft(
    id smallint primary key,
    nombre varchar,
    cumple date
);

create table practicaright(
    id smallint primary key,
    apellido varchar,
    cumpleanios date
);

Então, insiro alguns dados aleatórios:

INSERT INTO practicaleft VALUES
(1, 'John', CURRENT_DATE - 1),
(5, 'Alice', CURRENT_DATE - 5),
(3, 'Bob', CURRENT_DATE - 3),
(7, 'Eva', CURRENT_DATE - 7);

INSERT INTO practicaright VALUES
(5, 'Doe', CURRENT_DATE - 5),
(6, 'Smith', CURRENT_DATE - 6),
(3, 'Johnson', CURRENT_DATE - 3),
(4, 'Brown', CURRENT_DATE - 4);

Depois, executo esta consulta:

select id, nombre
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright 
where cumpleanios > current_date - 5;

Os resultados? Aqui você vai:

4   "Brown"
5   "Alice"
5   "Doe"
3   "Johnson"
3   "Johnson"
4   "Brown"

TL;DR: esta consulta é dividida em três partes e os resultados são mesclados com o operador UNION ALL.

Agora vem a questão. Pode-se acreditar que isso é executado instrução por instrução e, portanto, a ordem deveria ser:

5   "Alice"
5   "Doe"
3   "Johnson"
4   "Brown"
3   "Johnson"
4   "Brown"

Mas isso não está acontecendo. A única maneira de corrigir isso é adicionar alguma string aleatória como um campo, assim:

select id, nombre, 'part1' as query_part
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido, 'part2' as query_part
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido, 'part3' as query_part
from practicaright 
where cumpleanios > current_date - 25;

O que está acontecendo? Eu pulei alguma mecânica SQL realmente importante?

Eu tenho a seguinte tabela.

  Column  |            Type             | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 code     | text                        |           | not null | 
 price_at | timestamp without time zone |           | not null | 
 price    | double precision            |           | not null | 

Preciso usar os dados desta tabela para criar um gráfico de velas que terá velas de 6 horas. Cada vela é representada pelos campos { x, open, close, low, high }onde x é o timestamp UNIX do início do período.

A consulta a seguir funciona, mas usar distinctfaz com que a consulta demore mais. Normalmente, quando as pessoas não querem usar distinto, elas usam group by, mas não posso usar isso com as funções da janela e não tenho certeza se isso ajudaria de qualquer maneira. Existe uma maneira de eliminar o uso de distinto nesta consulta para torná-la mais rápida e ainda retornar os mesmos resultados?

with price_quotes as (
        select
            extract (epoch from price_at) - (extract (epoch from price_at) % extract (epoch from '6 hours'::interval)) as period_begin,
            extract (epoch from price_at) as quote_time,
            price
        from quote)
select distinct
    period_begin as x,
    first_value (price) over (partition by period_begin order by quote_time asc) as open,
    last_value (price) over (partition by period_begin order by quote_time asc rows between current row and unbounded following) as close,
    min (price) over (partition by period_begin) as low,
    max (price) over (partition by period_begin) as high
from price_quotes
order by x asc

Eu sinto que este é um problema comum e já vi isso em alguns desafios de SQL, mas pela minha vida, não consigo pensar claramente em uma solução.

Digamos que você tenha uma hierarquia desigual. Elementos que pertencem a outros elementos mas você não conhece o topo.

Digamos que seja um organograma da empresa para mantê-lo simples (na verdade, são dependências de tarefas, mas eh).

Então há uma mesa. Nome do funcionário e nome do chefe.

Employee name: Bob .... Boss Name: Dora
Employee name Dora .... Boss Name: Kim

E assim por diante. No meu caso, há uma informação adicional. Uma pessoa só se reporta a uma pessoa. Relacionamento um para um.

Existem N elementos no topo da cadeia que possuem Nome: Quem quer que seja Chefe: Nulo.

Então eu estava fazendo algo da seguinte forma:

select employee_name, boss_name
from boss_table b1
left join boss_table b2 on b1.boss_name = b2.employee_name
left join boss_table b3 on b2.boss_name = b3.employee_name

E assim por diante para tentar encontrar o 'Chefe Raiz' ou 'Chefe Superior' de cada funcionário. No entanto, alguns desses elementos aninhados são MUITO profundos - não quero fazer 20 junções ... ou pelo menos digitá-los - sinto que uma função recursiva é a resposta óbvia, mas não consigo descobrir - pensamentos?