Could you point a way to split a very long MSSQLServer query to make it retrieves just parts of the lines for each time?
Could you point a way to split a very long MSSQLServer query to make it retrieves just parts of the lines for each time?
I'm running a query that must retrieve 3.200.0000 lines, resulted from some group operations:
SELECT nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep,
max(Cem) AS cem , max(Infosaude) AS infosaude, max(Procare) as procare
FROM v_UnificacaoPacientes_v5
GROUP BY nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep
ORDER BY nome;
Is that a way to partitionate the results in subsets of lines and then assemble it ?
Thanks in advance.
Developer and Analyst
We need to migrate all the medical patients from legate system to the new CRM system.
Developer and Analyst
SELECT nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep,
max(Cem) AS cem , max(Infosaude) AS infosaude, max(Procare) as procare
FROM v_UnificacaoPacientes_v5
GROUP BY nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep
ORDER BY nome
OFFSET 10000000
FETCH NEXT 1000000 ROWS ONLY;
SELECT nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep,
max(Cem) AS cem , max(Infosaude) AS infosaude, max(Procare) as procare
FROM v_UnificacaoPacientes_v5
GROUP BY nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep
ORDER BY nome
OFFSET 10000001
FETCH NEXT 1000000 ROWS ONLY;
SELECT nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep,
max(Cem) AS cem , max(Infosaude) AS infosaude, max(Procare) as procare
FROM v_UnificacaoPacientes_v5
GROUP BY nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep
ORDER BY nome
OFFSET 20000001
FETCH NEXT 1000000 ROWS ONLY;
SELECT nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep,
max(Cem) AS cem , max(Infosaude) AS infosaude, max(Procare) as procare
FROM v_UnificacaoPacientes_v5
GROUP BY nome, Nome_Valido, cpf, CPF_Valido, data_nasc, telefone, endereco, cidade, uf, cep
ORDER BY nome
OFFSET 30000001
FETCH NEXT 1000000 ROWS ONLY;
Am I in the right way?
If you are planning just 4 parts then the concept is OK. The OFFSET in the first query should be 0 and all other offsets should be minus one - 1000000, 2000000, ...
Developer and Analyst
Unfortunatelly the performance is very slow in my case, so I have to adopt another strategy.
Just remember the data porting to a new system is one time task and if it takes hours then it is still OK.
Developer and Analyst
I agree with your considerations about data migration is an unique event that where time to be consumed on strategies must to be only the necessary.
What I did is to split the data using the name initials and save the results on .CSV(s) - and then maybe join all the CSV(s).
Another company of the group I found have at about 21 million register. By going this way is slow but possible and I'm getting it.
If you are porting data between two different servers then you could have two more options:
1) Create a backup, restore it on the second server and then all queries should work relatively quickly. You may even prepare "export database" on the original server and then port the backup of this db.
2) Create a Linked server on the new machine and execute necessary queries on the linked server. Of course, millions of data rows will still consume time.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.