Improve select SQL Server performance

Asked

Viewed 44 times

-1

I have a table with 2 million records and I need to take 1000 by 1000, I know I can use TOP, but use the form below pq need to control the quantity, example: I get 1000 after the next 1000, then the next 1000 and so on, ie in place of this thousand, is a variable, anyway this is my select:

select * from (select *, ROW_NUMBER() OVER(ORDER BY cd_cpfcgc ASC)as Row from cad_cliente) as teste where teste.Row between 1 and 1000

But when performing is taking about 20 seconds, is there any other way to improve performance? because when running on the server is giving timeout by the delay.

Ever since I thank.

sql sql-server

  • if you want a better analysis you need to put in your question the structure of the tables and if they have index. Only seeing the Execution Plain is that you may have a more real idea of the problem, if you have table scan, if index is missing, loop interactions.. I put this in the question to help in the analysis

2 answers

0

You can create an index in the table by sorting column:

CREATE INDEX idx_cliente_cpfcgc ON cad_cliente(cd_cpfcgc);

-1

You could try a simpler strategy, using the order by together with limitand offset to paginate items from 1000 to 1000, in this case its variable would be the value offset. To the primeira página the value would be 0, na segunda página its offset value would be 1000, in the terceira página would be 2000 and so on.

SELECT * FROM cad_cliente ORDER BY cd_cpfcgc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;

This code follows the idiomatic pattern of T-SQL. Which is a Microsoft sql language.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.