select command - what's the best performance?

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

Hi there,

I would like execute a select that will generate a cursor with 1000 rows.

I want to skip the first 600 rows and process only the last 400.

I have 2 ways to implement it:

1) I can skip the first 600 rows through my SELECT command:

In this case, my cursor would have only the 400 rows I actually want and my COBOL program would fetch only 400 rows.

My SELECT command is the one that will have the duty to discard the rows I don’t want.

There will be some SCAN in order to accomplish the task. The access plan IS NOT only by an index.

2) My COBOL program can also simply fetch all 1000 rows and bypass the first 600 I don’t want to process:

There will be NO scan.

The access plan IS ONLY by an index.

Which solution provides the best performance?

Thanks,
JC

stodolas · Posted: Sun Dec 16, 2007 10:41 pm

Try it both ways and see. The scan may not be that bad if the table(s) is/are small. If they are large though the select of 1000 and skip to 601 would probably be faster.