Existe uma maneira de executar várias operações usando a WITHinstrução?
Algo como
WITH T AS
(
SELECT * FROM Tbl
)
BEGIN
OPEN P_OUTCURSOR FOR
SELECT * FROM T;
SELECT COUNT(*) INTO P_OUTCOUNT FROM T;
END;
Eu quero selecionar alguns dados e a contagem deles...
É possível fazer o InnoDB usar índices iguais aos do MyISAM em vez do índice clusterizado devido à limitação da RAM enquanto obtém o benefício de seu desempenho de simultaneidade?
Gostaria de saber se eu poderia solicitar algum feedback sobre um procedimento armazenado que estou executando e se há uma maneira mais eficiente de lidar com o cenário (tenho certeza de que haverá!).
Basicamente tenho um único SP que chamo para retornar uma lista de registros (Jobs) que podem ter um ou mais status e uma ordem de classificação (estou usando RowNum para paginação). No momento estou usando WITH RECOMPILE porque as variações nos status podem mudar o tempo todo (dependendo do usuário etc). Há também alguma filtragem acontecendo.
Estou usando uma instrução IF para executar essencialmente o mesmo pedaço de código com a única alteração sendo a ordem de classificação.
Acho que minhas perguntas são: Existe uma maneira melhor de fazer isso (talvez SP's diferentes para status diferentes)? Estou complicando demais as coisas devido à falta de conhecimento (bastante provável) O SP está realmente ok, mas requer pequenos ajustes para reduzir o número de linhas?
Eu colei uma parte do SP abaixo - a única diferença para o código completo são as instruções IF adicionais para as diferentes ordens de classificação ...
Eu apreciaria qualquer feedback.
Desde já, obrigado!
PROCEDURE [dbo].[sp_Jobs]
@PageNumber int,
@PageSize int,
@FilterExpression varchar(500),
@OrderBy varchar(50),
@CustomerID int,
@ShowNotSet bit,
@ShowPlaced bit,
@ShowProofed bit,
@ShowReProofed bit,
@ShowApproved bit,
@ShowOnTime bit,
@ShowLate bit,
@ShowProblem bit,
@ShowCompleted bit,
@ShowDispatched bit,
@ShowUnapproved bit,
@ShowClosed bit,
@ShowReturned bit,
@UserID int
WITH RECOMPILE
AS
--JobNumber DESC
if @OrderBy='JobNumberDESC'
BEGIN
WITH Keys AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (ORDER BY JobNumber DESC) as rn,P1.jobNumber,P1.CustID,P1.DateIn,P1.DateDue,P1.DateOut,p1.client,p1.MasterJobStatusID,p1.MasterJobStatusTimestamp,p1.OwnerID
FROM
vw_Jobs_List P1 WITH (NOLOCK)
WHERE
(@CustomerID = 0 OR CustID = @CustomerID)
AND (@UserID = 0 OR OwnerID = @UserID)
AND ((@ShowNotSet = 1 AND MasterJobStatusID=1) OR (@ShowPlaced = 1 AND MasterJobStatusID=2) OR (@ShowProofed = 1 AND MasterJobStatusID=3) OR (@ShowReProofed = 1 AND MasterJobStatusID=4) OR (@ShowApproved = 1 AND MasterJobStatusID=5) OR (@ShowOnTime = 1 AND MasterJobStatusID=6) OR (@ShowLate = 1 AND MasterJobStatusID=7) OR (@ShowProblem = 1 AND MasterJobStatusID=8) OR (@ShowCompleted = 1 AND MasterJobStatusID=9) OR (@ShowDispatched = 1 AND MasterJobStatusID=10) OR (@ShowUnapproved = 1 AND MasterJobStatusID=11) OR (@ShowClosed = 1 AND MasterJobStatusID=12) OR (@ShowReturned = 1 AND MasterJobStatusID=13)) AND (Search LIKE '%'+@FilterExpression+'%')
ORDER BY
P1.JobNumber DESC ),SelectedKeys AS (
SELECT TOP (@PageSize)SK.rn,SK.JobNumber,SK.CustID,SK.DateIn,SK.DateDue,SK.DateOut
FROM
Keys SK
WHERE
SK.rn > ((@PageNumber-1) * @PageSize)
ORDER BY
SK.JobNumber DESC)
SELECT SK.rn,J.JobNumber,J.OwnerID,J.Description,J.Client,SK.CustID,OrderNumber, CAST(DateAdd(d, -2, CAST(isnull(SK.DateIn,0) AS DateTime)) AS nvarchar) AS DateIn, CAST(DateAdd(d, -2, CAST(isnull(SK.DateDue,0) AS DateTime)) AS nvarchar) AS DateDue,CAST(DateAdd(d, -2, CAST(isnull(SK.DateOut,0) AS DateTime)) AS nvarchar) AS DateOut, Del_Method,Ticket#, InvoiceEmailed, InvoicePrinted, InvoiceExported, InvoiceComplete, JobStatus,j.MasterJobStatusID,j.MasterJobStatusTimestamp,js.MasterJobStatus
FROM SelectedKeys SK JOIN vw_Jobs_List J WITH (NOLOCK) ON j.JobNumber=SK.JobNumber JOIN tbl_SYSTEM_MasterJobStatus js WITH (NOLOCK) ON j.MasterJobStatusID=js.MasterJobStatusID
ORDER BY
SK.JobNumber DESC
END
--ELSE IF para outra classificação de colunas
Usando a ferramenta de linha de comando psql, como listar todas as tabelas postgres em um esquema específico
Ao executar o PostgreSQL com um único comando SQL, os códigos de erro são retornados conforme o esperado:
% psql -c "SELECT * FROM AWDASDASDASDAS" my_db
ERROR: relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
1
Mas ao executar um arquivo, o erro é suprimido:
% psql -f test.sql my_db
psql:test.sql:1: ERROR: relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
0
Alguma ideia de como recuperar esses erros?
Eu tenho TRUNCATEuma tabela enorme (~ 120Gb) chamada files:
TRUNCATE files;
VACUUM FULL files;
O tamanho da tabela é 0, mas nenhum espaço em disco foi liberado. Alguma idéia de como recuperar meu espaço em disco perdido?
ATUALIZAÇÃO: O espaço em disco foi liberado após ~ 12 horas, sem nenhuma ação da minha parte. Eu uso o servidor Ubuntu 8.04.
Estamos acessando o MySQL a partir do conector Microsoft ADO.NET.
Ocasionalmente, vemos o seguinte impasse em nosso status innodb e não conseguimos identificar a causa do problema. Parece que a transação (2) está esperando e segurando o mesmo bloqueio?
------------------------
LATEST DETECTED DEADLOCK
------------------------
110606 5:35:09
*** (1) TRANSACTION:
TRANSACTION 0 45321452, ACTIVE 0 sec, OS thread id 3804 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
MySQL thread id 84, query id 3265713 localhost 127.0.0.1 famdev Updating
UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com', phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>', iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42', location_lat = <lat>, location_long = -<lng>, gps_strength = 3296, picture_blob_id = 1190, authority = 1, active = 1, date_created = '2011-04-13 20:21:20', last_login = '2011-06-06 05:35:09', panic_mode = 0, battery_level = NULL, battery_state = NULL WHERE people_id = 3125
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321452 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
0: len 8; hex 8000000000000c35; asc 5;; 1: len 6; hex 000002b38ce6; asc ;; 2: len 7; hex 00000002801f89; asc ;; 3: len 8; hex 800000000000064a; asc J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc J] Z;; 13: len 8; hex <data>; asc J] Z;; 14: len 8; hex a39410acaa9b4340; asc C@;; 15: len 8; hex <data>; asc m S ;; 16: len 2; hex 8ce0; asc ;; 17: len 8; hex 80000000000004a6; asc ;; 18: len 4; hex 80000001; asc ;; 19: len 1; hex 81; asc ;; 20: len 8; hex <data>; asc JR ;; 21: len 8; hex <data>; asc J] ;; 22: len 1; hex 80; asc ;; 23: SQL NULL; 24: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 0 45321448, ACTIVE 0 sec, OS thread id 3176 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 85, query id 3265714 localhost 127.0.0.1 famdev Updating
UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com', phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>', iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07', location_lat = <lat>, location_long = -<lng>, gps_strength = 3296, picture_blob_id = 1190, authority = 1, active = 1, date_created = '2011-04-13 20:21:20', last_login = '2011-06-06 05:35:09', panic_mode = 0, battery_level = NULL, battery_state = NULL WHERE people_id = 3125
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321448 lock mode S locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
0: len 8; hex 8000000000000c35; asc 5;; 1: len 6; hex 000002b38ce6; asc ;; 2: len 7; hex 00000002801f89; asc ;; 3: len 8; hex 800000000000064a; asc J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc J] Z;; 13: len 8; hex <data>; asc J] Z;; 14: len 8; hex a39410acaa9b4340; asc C@;; 15: len 8; hex <data>; asc m S ;; 16: len 2; hex 8ce0; asc ;; 17: len 8; hex 80000000000004a6; asc ;; 18: len 4; hex 80000001; asc ;; 19: len 1; hex 81; asc ;; 20: len 8; hex <data>; asc JR ;; 21: len 8; hex <data>; asc J] ;; 22: len 1; hex 80; asc ;; 23: SQL NULL; 24: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 11144 n bits 152 index `PRIMARY` of table `family`.`people` trx id 0 45321448 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
0: len 8; hex 8000000000000c35; asc 5;; 1: len 6; hex 000002b38ce6; asc ;; 2: len 7; hex 00000002801f89; asc ;; 3: len 8; hex 800000000000064a; asc J;; 4: len 4; hex <data>; asc <name>;; 5: len 30; hex <data>; asc <data>;...(truncated); 6: SQL NULL; 7: SQL NULL; 8: len 16; hex <data>; asc <redacted>@yahoo.com;; 9: SQL NULL; 10: len 10; hex <data>; asc <phone>;; 11: len 30; hex <data>; asc android:<id>;...(truncated); 12: len 8; hex <data>; asc J] Z;; 13: len 8; hex <data>; asc J] Z;; 14: len 8; hex a39410acaa9b4340; asc C@;; 15: len 8; hex <data>; asc m S ;; 16: len 2; hex 8ce0; asc ;; 17: len 8; hex 80000000000004a6; asc ;; 18: len 4; hex 80000001; asc ;; 19: len 1; hex 81; asc ;; 20: len 8; hex <data>; asc JR ;; 21: len 8; hex <data>; asc J] ;; 22: len 1; hex 80; asc ;; 23: SQL NULL; 24: SQL NULL;
*** WE ROLL BACK TRANSACTION (1)
Lemos este artigo sobre o próximo bloqueio de chave, mas não parece se aplicar a nós porque não estamos fazendo seleções para atualização.
Atualizar
Esta manhã, descobri uma assinatura de impasse ligeiramente diferente, que pode ser a causa raiz desse impasse. Publiquei esse impasse como uma pergunta separada para manter as coisas simples. Atualizarei aqui se puder confirmar que a outra pergunta é a causa.
Ouvi dizer que armazenar índices em um grupo de arquivos e unidade diferentes aumenta o desempenho em um banco de dados porque a unidade não precisa ir e voltar entre o índice e os dados aos quais o índice se refere. Também ouvi dizer que isso é um mito.
Quando é aconselhável armazenar índices não clusterizados em um grupo de arquivos e uma unidade separados? Que evidência de perfmon/profiler me levaria a chegar a essa conclusão? O hardware desempenha um papel na decisão (se um RAID/SAN é usado em uma única unidade)?
Eu sei que a pergunta pode parecer muito estúpida, mas nunca entendi essa parte.
O SQL*Plus funciona com SQL e PL/SQL. Como sei se algum código é SQL ou PL/SQL? Se meu código tiver um loop for, não é mais SQL?
PL/SQL é uma extensão para SQL ter loops, condicionais etc. Então qualquer código SQL é por padrão código PL/SQL? Não é assim?
Existe uma demarcação entre SQL e PL/SQL?
Dois exemplos de diferenciação b/w SQL e PL/SQL que desencadearam esta questão:
Qual é a diferença entre essas duas instruções create table?
https://stackoverflow.com/questions/2267386/oracle-11g-varray-of-objects/2267813#2267813
Eu preciso mover um monte (mais de 100) de tabelas grandes (milhões de linhas) de um banco de dados SQL2008 para outro.
Originalmente, usei apenas o Assistente de Importação/Exportação, mas todas as tabelas de destino estavam sem chaves primárias e estrangeiras, índices, restrições, gatilhos etc. (As colunas de identidade também foram convertidas em INTs simples, mas acho que perdi uma caixa de seleção no bruxo.)
Qual é a maneira certa de fazer isso?
Se fossem apenas algumas tabelas, eu voltaria para a fonte, executaria o script da definição da tabela (com todos os índices, etc.) e executaria as partes de criação de índice do script no destino. Mas com tantas tabelas, isso parece impraticável.
Se não houvesse tantos dados, eu poderia usar o assistente "Criar scripts..." para fazer o script da fonte, incluindo dados, mas um script de 72m de linha simplesmente não parece uma boa ideia!