Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

For MySQL:
I could retrieve record 20 to record 40 by using the command limit:
For example: SELECT * from presidents WHERE terms > 2 LIMIT 20, 40;
So my question is in order to achieve the same result using MS SQL2000, what will the syntax be by using DBI??

I have read some FAQ and it provide me with the following method:
# prepare and execute statement
my $array_ref = $sth->fetchall_arrayref();

# we want 0 through 20
foreach my $row (@$array_ref[0 .. 20]) {
    # do something
}

[download]
So my second question is this a good method in term of processing speed.

Edit kudra, 2002-05-10 Added code tags

Replies are listed 'Best First'.
Re: How can I retrieve records X to Y using DBI?
by gmax (Abbot) on May 10, 2002 at 09:53 UTC
    In this thread there are a few nodes discussing LIMIT and its implication. This node gives a MS SQL variation.

    As I have already stressed before, the LIMIT instruction does not mean that you are fetching records from number X to Y. Relational databases don't have record numbers, unless some specific brand implementation supports such concept.
    What you get with a LIMIT, is a subset of your query result, which can change depending on many factors (see an example in the above mentioned node.)

    As for the example you are presenting, it does not do what you want. Your example will fetch all the records resulting from your query, and do something for the first 20 records.
    If you want only the first N records, a standard DBI method is: 
    my $count =0;
my $maximum = 20;
while (my $array_ref = $sth->fetchrow_arrayref()) {
    last if $count++ >= $maximum;
    # do something with your records
}
$sth->finish(); # will clean the memory in the database

    [download]
    However, a much better and strongly recommended method is using a WHERE clause in your SQL. Using WHERE you are reducing the records to select, thus speeding up the query (less records to select, less to fetch)
    If you can't select what you want using this clause, you might have some database design problems.

    HTH
     _  _ _  _  
(_|| | |(_|><
 _|
[reply]
[d/l]

Back to Seekers of Perl Wisdom