Ir para o conteúdo
  • Revista PROGRAMAR: Já está disponível a edição #60 da revista programar. Faz já o download aqui!

nunopicado

Converter query de MySQL para MSSQL - Dúvidas

Mensagens Recomendadas

nunopicado

Boas

Ando aqui a converter umas queries de MySQL para SQL Server.

Tenho já a base de dados toda convertida, mas há duvidas em algumas queries, especialmente porque não estou lá muito à vontade com SQL Server.

Em MySQL, tenho a seguinte query:

SELECT *, 
(SELECT PVP-(Desconto/100*PVP)) AS PVPcomDesconto, 
(SELECT PVPcomDesconto/(1+(Taxa/100))) AS PVPsemIVAcomDesconto, 
(SELECT PVPcomDesconto*Quant) AS TotalLinhaComIVA, 
(SELECT PVPsemIVAcomDesconto*Quant) AS TotalLinhaSemIVA, 
(SELECT SUM(TotalLinhaComIVA)) AS TotalDocumentoComIVA, 
(SELECT SUM(TotalLinhaSemIVA)) AS TotalDocumentoSemIVA, 
ConCat(FORMAT(PVP,2)," €") AS FPVP, 
ConCat(FORMAT(PCU,2)," €") AS FPCU, 
Format((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS TotalDocumento, 
ConCat(Format((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2)," €") AS FTotalDocumento, 
Format((SELECT SUM(((PVP-(Desconto/100*PVP))/(1+(Taxa/100)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS BaseDocumento, 
Format((SELECT SUM((PCU-(Desconto/100*PCU))*(1+(Taxa/100))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),'+query.Decimals('PriceDecimals')+') AS TotalDocumentoF, 
Format((SELECT SUM(((PCU-(Desconto/100*PCU)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),'+query.Decimals('PriceDecimals')+') AS BaseDocumentoF 
FROM LinhasDoc 
WHERE (DocID=0) AND (Tmp=01) 
AND (Valorizavel=0) 
ORDER BY ABS(LinhasDocID)

Comecei a converter, e neste momento, tenho isto:

SELECT *, 
(SELECT SUM(TotalLinhaComIVA)) AS TotalDocumentoComIVA, 
(SELECT SUM(TotalLinhaSemIVA)) AS TotalDocumentoSemIVA, 
ConCat(FORMAT(PVP,2),' €') AS FPVP, 
ConCat(FORMAT(PCU,2),' €') AS FPCU, 
Format((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS TotalDocumento, 
ConCat(Format((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2),' €') AS FTotalDocumento, 
Format((SELECT SUM(((PVP-(Desconto/100*PVP))/(1+(Taxa/100)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS BaseDocumento, 
Format((SELECT SUM((PCU-(Desconto/100*PCU))*(1+(Taxa/100))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS TotalDocumentoF, 
Format((SELECT SUM(((PCU-(Desconto/100*PCU)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS BaseDocumentoF 
FROM LinhasDoc 
WHERE (DocID=0) AND (Tmp=01) 
AND (Valorizavel=0) 
GROUP BY LinhasDocID, DocID, DocNumber, DocTextShort, DataDoc, DataSys, HoraDoc, DocRefID, LinhasDocRefID, ArtigoID, LoteID, Quant, QuantDev, QuantProc, Description, PCU, PVP, Taxa, Desconto, TmpDev, Valorizavel, Tmp, PVPcomDesconto, PVPsemIVAcomDesconto, TotalLinhaComIVA, TotalLinhaSemIVA
ORDER BY ABS(LinhasDocID)

Resumo:

Visto o uso de alias em subqueries não é permitido no MSSQL (ou é?), as linhas 2 a 5 da query original foram removidas. Substitui esses calculos por 4 Computed Columns com as respectivas formulas.

Depois, tive de acrescentar uma cláusula Group By descomunal, porque o raio do MSSQL não funciona sem ela quando há um SUM na query. É possível dar a volta a isto de outra maneira?

No momento, estou à procura de um substituto da função FORMAT do MySQL.

A ideia é converter um número FLOAT para STRING, sempre com duas casas decimais.

Ou seja:

1.5815215 converte-se em '1.58'

1.58 fica '1.58'

1.5 fica '1.50'

1 fica '1.00'

Ainda experimentei o ROUND, mas claro, é um valor numérico e 1 é 1, e não 1.00.

Também andei a ler sobre o CONVERT, mas aparentemente só dá para 2 ou 4 casas decimais, quando eu quero escolher quantas lhe ponho (se por sei lá que razão quiser trabalhar com 6 casas decimais, 1 tem de virar 1.000000).

ALguma ideia?

Obrigado

Editado por nunopicado

"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Bem, entretanto consegui converter, mas devo dizer que quanto mais vejo MSSQL, mais gosto do MySQL!

Então ficou assim:

Criei uma função para substituir do FORMAT do MySQL, assim:

CREATE FUNCTION [dbo].[MySQL_FORMAT] (@Valor FLOAT, @Decimals INT) 
RETURNS VARCHAR(20)
BEGIN
DECLARE @Input VarChar(20) = @Valor
if CHARINDEX('.',@Input) = 0
	begin
		SET @Decimals = @Decimals + 1
	end
RETURN RTRIM((LTRIM(STR(@Valor,LEN(@Valor)+@Decimals,@Decimals))))
END

E a query ficou assim:

SELECT *, 
(SELECT SUM(TotalLinhaComIVA)) AS TotalDocumentoComIVA, 
(SELECT SUM(TotalLinhaSemIVA)) AS TotalDocumentoSemIVA, 
([dbo].[MySQL_FORMAT](PVP,2) + ' €') AS FPVP, 
([dbo].[MySQL_FORMAT](PCU,2) + ' €') AS FPCU, 
[dbo].[MySQL_FORMAT]((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS TotalDocumento, 
([dbo].[MySQL_FORMAT]((SELECT SUM((PVP-(Desconto/100*PVP))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) + ' €') AS FTotalDocumento, 
[dbo].[MySQL_FORMAT]((SELECT SUM(((PVP-(Desconto/100*PVP))/(1+(Taxa/100)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS BaseDocumento, 
[dbo].[MySQL_FORMAT]((SELECT SUM((PCU-(Desconto/100*PCU))*(1+(Taxa/100))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS TotalDocumentoF, 
[dbo].[MySQL_FORMAT]((SELECT SUM(((PCU-(Desconto/100*PCU)))*Quant) FROM LinhasDOC WHERE (Valorizavel>0) AND (DocID=0) AND (Tmp=1)),2) AS BaseDocumentoF 
FROM LinhasDoc 
WHERE (DocID=0) AND (Tmp=01) 
AND (Valorizavel=0) 
GROUP BY LinhasDocID, DocID, DocNumber, DocTextShort, DataDoc, DataSys, HoraDoc, DocRefID, LinhasDocRefID, ArtigoID, LoteID, Quant, QuantDev, QuantProc, Description, PCU, PVP, Taxa, Desconto, TmpDev, Valorizavel, Tmp, PVPcomDesconto, PVPsemIVAcomDesconto, TotalLinhaComIVA, TotalLinhaSemIVA
ORDER BY ABS(LinhasDocID)

Não há forma de evitar tantas voltinhas por causa de uma coisa tão simples?

Ou se não, há alguma optimização a fazer à query para evitar isto?


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nelsonr

Boas,

podes usar o STR, assim:

SELECT STR(campofloat, 25, 2)

Se quiseres remover os espaços antes, podes usar o LTRIM

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Boas,

podes usar o STR, assim:

SELECT STR(campofloat, 25, 2)

Se quiseres remover os espaços antes, podes usar o LTRIM

:) Obrigado, mas já encontrei essa! :) Já a pus lá em cima.

Mas tive de acrescentar um IF antes, porque quando o numero não tem nenhuma casa decimal, o STR é parvo e só mete uma casa decimal, em vez de 2.

Estou a aumentar uma casa decimal quando não existe nenhum '.' no valor.

Mas isto é uma coisada muito estranha!

Para ajudar à festa, com apenas meia duzia de queries convertidas (apenas as essenciais para o programa arrancar e fazer a inserção de artigos na lista, isto está ultra-mega-lento.

Enquanto em MySQL seleccionar um artigo é imediato, no MSSQL, ao carregar nota-se um lag antes de ele aparecer na lista! :(


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nelsonr

:) Obrigado, mas já encontrei essa! :) Já a pus lá em cima.

Ah ok, nem tinha reparado :D

Enquanto em MySQL seleccionar um artigo é imediato, no MSSQL, ao carregar nota-se um lag antes de ele aparecer na lista! :(

Não sei se irás notar muita diferença, mas tens indexes nos campos que usas nos filtros?

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nelsonr

Mas tive de acrescentar um IF antes, porque quando o numero não tem nenhuma casa decimal, o STR é parvo e só mete uma casa decimal, em vez de 2.

Tive a testar isto e a mim não me acontece

SELECT campofloat  ,LTRIM(STR(campofloat, 25, 2))  FROM TABELA

campofloat	(No column name)
1,5815215	1.58
1,5 1.50
5	 5.00

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Não sei se irás notar muita diferença, mas tens indexes nos campos que usas nos filtros?

Não tinha (no MySQL também não).

Meti agora indexes nos campos DocID, Valorizavel e Tmp, os que uso no where, mas tá igual.

Entre clicar no botão referente a um artigo, e ele executar as duas querys que precisa para adicionar o artigo à lista*, demora-me entre meio segundo e um segundo.

No MySQL é imediato.

* No programa tenho uma lista que irá receber os artigos seleccionados.

Esta lista tem sempre uma linha aberta, mesmo sem artigo nenhum.

Ao adicionar um artigo, essa linha é modificada, e uma nova é inserida a seguir.

Outras alterações como alterar a quantidade e o desconto (que são aplicadas à linha seleccionada no momento) são imediatas, pelo que deduzo que a demora seja a inserir uma linha nova

Tendo em conta que esta é a tabela que tem 4 Computed Columns (qual é o termo para isto em português? :P ), será por aí a lentidão?

Se sim, que alternativas eu teria para simular o que tenho no MySQL (query em cima) sem as computed columns?

Tive a testar isto e a mim não me acontece

SELECT campofloat  ,LTRIM(STR(campofloat, 25, 2))  FROM TABELA

campofloat    (No column name)
1,5815215    1.58
1,5 1.50
5     5.00

A mim acontece, e é bem estranho. Sempre que o valor não tem nenhuma casa decimal, ele fica com uma a menos do que o especificado no parametro do STR.

Estou a usar o SQL Server Express 2012.


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Com o 2012 podes usar o format

SELECT FORMAT(campofloat, '#0.00')

Olha, bom saber... Infelizmente por hora não é opção, porque se é verdade que estou a usar o 2012 em casa, também é verdade que há locais onde no máximo vou poder meter o 2008 (WinXP).

Logo, se isto só funciona no 2012, não vou poder usar! :/

De qualquer maneira, não me parece que a questão da velocidade esteja no STR com o IF a acompanhar.

Meti só para testar o FORMAT como tu sugeriste, e o tempo continua a mesma coisa!

Estava aqui a medir, e o mesmo processo que no MySQL me varia entre 50 e 100 ms, no MSSQL varia-me entre 150 e 350 ms.

Alguma ideia de como apressar isto?

Editado por nunopicado

"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nelsonr

Já agora, tens de fazer SELECTs diferentes?

SELECT *,
(SELECT SUM(TotalLinhaComIVA)) AS TotalDocumentoComIVA,
(SELECT SUM(TotalLinhaSemIVA)) AS TotalDocumentoSemIVA
...

Não dava para por tudo num select

SELECT *,
SUM(TotalLinhaComIVA) AS TotalDocumentoComIVA,
SUM(TotalLinhaSemIVA) AS TotalDocumentoSemIVA
...

Podes também experimentar diminuir a quantidade de campos que tens ali, e vais adicionando um a um e vês em que parte está a degradar a execução

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Pois, neste caso até dá.

No MySql tinha isso em subqueries porque isso permitia-me usar os resultados de uma subquery na outra - as subqueries aceitam os alias usados noutras subqueries.

TotalLinhaComIVA e TotalLinhaSemIVA eram os resultados de duas subqueries diferentes.

No caso do MSSQL, como isso não é permitido, usei as Computed Columns, e removi as primeiras 4 subqueries. Como tal, estas duas deixaram de ser necessárias também, mas nem me lembrei disso antes.

Ainda assim, retirei esses SELECTS mas a nivel de velocidade tudo na mesma!

Editado por nunopicado

"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Podes também experimentar diminuir a quantidade de campos que tens ali, e vais adicionando um a um e vês em que parte está a degradar a execução

Medi o tempo que leva a fazer o refresh desta query, e são apenas 25ms (contra os menos de 10s no MySQL).

Ainda assim, 15ms não chega para justificar a demora!

Remover campos não me dá jeito, visto que todos eles podem ser usados em qualquer momento da execução do programa.

Os totais estão permanentemente a ser mostrados, e os demais são usados ou durante a selecção de artigos ou durante o fecho da conta, quando é criado o cabeçalho do documento e os campos são preenchidos.


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nelsonr

Remover campos não me dá jeito, visto que todos eles podem ser usados em qualquer momento da execução do programa.

Não estava a dizer para remover permanentemente.

Era apenas para tentar descobrir qual a parte que estava a causar atraso na execução.

Experimentaste com o FORMAT do sql2012, em vez da função que fizeste? Sei que não podes usar, mas era só para ver se o atraso vem de chamar a função Mysql_Format.

E também ver se dá para substituir os SELECT SUM apenas por SUM, como tinha dito no post #10

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Sim, fiz ambos, mas está sensivelmente na mesma.

Sim, fiz ambos, mas está sensivelmente na mesma.


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites
nunopicado

Está resolvido...

Parece que o problema era mesmo as Computed Columns.

Removi-as e meti os cálculos directamente na query e voltou tudo ao normal.

Ainda assim, obrigado pelas dicas. Vão dar jeito!


"A humanidade está a perder os seus génios... Aristóteles morreu, Newton já lá está, Einstein finou-se, e eu hoje não me estou a sentir bem!"

> Não esclareço dúvidas por PM: Indica a tua dúvida no quadro correcto do forum.

Partilhar esta mensagem


Ligação para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!

Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.

Entrar Agora

×

Aviso Sobre Cookies

Ao usar este site você aceita os nossos Termos de Uso e Política de Privacidade. Este site usa cookies para disponibilizar funcionalidades personalizadas. Para mais informações visite esta página.