Best way:

Try to divide the queries and run separately to check which part is taking how much time. Catch the performance of the six cursors one at a time anc...

A quick look and I can see big usage of dbo.Calendario table. Can you check if statistics are updated and all the indexes are defragmented on this table ?

You should check any blocking issues. Check the fragmentation of tables involved as well.

Can you please send the sproc and tables definition ? Also the number of records per table.

I remember facing same sort of issues. However updating the statistics of tables involved solved the issue. Can you send the function definition ?

I'm not using any UDFs or table variable It's just simple insert as below:

Insert into dbo.TableA

PageId, col1, col2, col3, col4, col5, col6, col7, col8)

select

(row_number()OVER (ORDER BY col1, col2, col3)...

Definitely not and i'm having problems. Scratched my head a lotz. There I can see index seek in the execution plan. Still its slow. What can be the possibilities to...

Is around 50 seconds good time to insert around 2500000 records to a physical table A from another table B with 8000000 records ? There are just 8 columns in...

With reference to same topic, I created one physical table and trying to inser all the records with page information in it at once. My base table consists of around...

There is definitely not the proper informaiton. Can you share one example of any query, the time taken, tables involved, data contained within them and result number of records etc...

Sorry Guys. One of the table had old statistics and updating the same resolved the issue. All the results are coming as expected.

Can there be downside of this methodology ?

Jeff, thanks for that idea. However I just gave you one example. Most of our issues involve more than one joins and few left outer joins as well.

These days...

Jeff, I am awaiting suggestions from you on my thought and your code as well.

I was thinking of one mechanism. Say we can have one index on the columns participating in pagination [this way these columns will be in sorted manner]. We can set...