Database Query Locator vs Simple Query

Question

I want to know what is the difference between Database.QueryLocator() and simple SOQL Query i.e.

List<Inventory__c> invent = [Select Id,Quantity__c,AccountId__c,Name,Expiry_Date__c From Inventory__c];

And

Database.getQueryLocator([Select Id,Quantity__c,AccountId__c,Name,Expiry_Date__c From Inventory__c]);

I want to know what is QueryLocator class in Apex.. I went through this link but cannot understand its structure..

Answer 1

Database.QueryLocator : when used the governor limit for the total number of records retrieved by SOQL queries is bypassed. For example, a batch Apex job for the Account object can return a QueryLocator for all account records (up to 50 million records) in an org. Another example is a sharing recalculation for the Contact object that returns a QueryLocator for all account records in an org.

For the regular query you do have a limit of 50,000 rows per transaction

Answer 2

Before we proceed about QueryLocator class and let's see the limits of it.

I wrote a sample Debug statement to see the limits of the number of rows returned by query locator and Normal Square bracket [] query

System.debug(Limits.getLimitQueryLocatorRows()); // 10000
System.debug(Limits.getLimitQueryRows()); //50000

So in a Synchronous transaction when you use Database.getQueryLocator(10,000) you get less number of rows than normal [] Square bracket query(50,000).

Now, let's see how to use QueryLocator. QuerryLocator is nothing but a query cursor. Which is pretty useless on its own, but it can help us get an Iterator. And that iterator can help us iterating through that set of records.

Database.QueryLocator ql = Database.getQueryLocator([SELECT id from Account order by CreatedDate desc Limit 10000 ]);

Database.QueryLocatorIterator iterator = ql.iterator();

while(iterator .hasNext()){
    Account acc =(Account) iterator.next();
    //Work on your list

}

Bit tedious aint? Such a pain....

Why would one want to use QueryLocatior instead of Normal Square bracket querry?

The answer lies in HEAP. As you can see from the code above, the Iterator can get only 1 record at a time, thus taking less memory, where as the SOQL [] gets the big List which can sometimes cause HeapLimitExceeded exception. Thus if you are querying large number of fields and processing them and have concern that you can reach heap limitations, it's better to use Querrylocator instead of normal SOQL.