Search
Twitter

Entries in SOQL (9)

Wednesday
Jul082015

When to use SOSL vs SOQL in Salesforce

Both SOQL and SOSL are extremely powerful query tools in Salesforce but they have different uses. In this article we'll explore what these two query languages are best suited for and how to take advantage of them. First let's start out with some simple definitions.

SOQL (Salesforce Object Query Language)

If you are at all familar with SQL (Structured Query Language), SOQL is very similar to SQL in that you use to a SELECT commond to speficy a source object (example Lead), a list of fields to grab, and the conditions for selecting the rows.

Select Id, LastName, FirstName, Company, Email from Lead where email = 'salesforcegeneral@gmail.com'

Just like any database, Salesforce uses indexes to make queries return results faster. SOQL and SOSL use different indexes.

What are some common indexes that can be used to make a SOQL query more efficient? Answer:

  • Primary Keys (Id, Name, Owner Fields)
  • Foreign Keys (lookup and master-detail relationship fields)
  • Custom fields marked as External ID or Unique - often times these are used to tie Salesforce records to external systems.
  • Audit Dates (i.e. LastModifiedDate)

Fields that CANNOT be indexed (and part of the reason SOSL is important) include:

  • Long text fields
  • Multi-select picklists
  • Binary fields (i.e. file, blob, encrypted text)
  • Currency fields (when multicurrency is enabled)

SOSL (Salesforce Object Search Language)

SOSL is used to construct text searches. SOSL's true power is that it enables you to search text accross multiple objects whereas SOQL is just a single object. Example: 

FIND {Salesforce General} IN Name Fields RETURNING lead(name, phone)

When to use SOQL
When to use SOSL
You know which object, fields you want to retrieve
You don't specifically know which object or field the data resides in but you need an efficient way to find the records
You want to get a count of the number of records that meet a specific criteria. i.e. where OwnerId = X
You want to get data from multiple objects and field in the most efficient way - the objects may or may not be related to one another.
You want to sort the results as part of the query
As described above when you are trying to query data in fields that cannot be indexed yet you have a large data
set
You want to get data from number, checkbox or date
fields
 
You want to get data from a single object or multiple objects that are related to each other (i.e. Accounts and Contacts)
 
Thursday
Jan082015

SOQL Using Scope (new in Winter 15')

SOQL query filtering with the new USING SCOPE clause

One really useful thing added in SOQL with the Winter 15' release is the ability to a Scope clause to a SOQL query. A common task we all find ourselves with at various times is the need to grab a group of leads or accounts for example and perform an action on them. More specifically if you use territories, you may need to get a list of all the accounts in Territory X and do something with them.

Prior to Scope, you would attack the problem backward. i.e. what makes up Territory X (who is in it, what is the territory region etc etc.) You would then build a query around those parameters. With scope there is now an easier option. Scope takes one enumeration values { Everything, Mine, My_Territory, My_Team_Territory or Team}

Example (grab all the accounts that belong to you)

SELECT Id FROM Account USING SCOPE Mine

Example (grab all the accounts that are in your territory) 

SELECT Id FROM Account USING SCOPE My_Territory 

 

Note: This also works on Customer Objects

Wednesday
Aug142013

Writing More Efficient SOQL Queries

One of the things that I think can never be covered in enough detail is how/why your SOQL queries should be as tight as possible. Any of us who have worked on Force.com long enough know that when you don't right good queries you're going to meet the dreaded Governor. It's difficult to keep all the limits straight and often times your code/queries work great in your testing and initial deployment but as records build up in the system or your code is deployed elsewhere you get a taste of how inefficient things are - generally in the form of a governor exception. I thought what we could do is first give a primer and then do a second or third part of the article over the coming weeks/months to add more examples and greater detail. In my experience, the place you need to be most careful is any type of query that runs inside a trigger (in the context of the trigger). A very common issue that most of us have hit or will hit is that everything works like a champ as long as you've got less than 100k records in a given table. Example - less than 100k leads. Once you cross that threshold you may start to see some "non-selective query" type errors like this one

Click to read more ...

Tuesday
May282013

Including Archived and Deleted Records in your SOQL Query

In some instances when performing a SOQL query, some records are omitted from appearing in the results. This includes deleted and archived records. For example, Tasks more than a year old (365 days) are automatically omitted unless the user specifies otherwise and that's where the  "ALL ROWS fuction" comes in handy.

If you add ALL ROWS at the end of a SOQL query, archived and deleted records will be included in the results. Here's an example below of query returning ALL tasks:

SELECT Id FROM Task ALL ROWS

As you can probably imagine, pulling all deleted and archived records into the results could a be hard to manage...so using the "IsDeleted" and "IsArchived" after the "WHERE" clause can help filter the results:

SELECT Id FROM Task ALL ROWS WHERE IsDeleted = false AND IsArchived = false
Wednesday
Apr172013

Use SOQL's OFFSET Clause to Paginate your Visualforce Pages

Before Salesforce Summer '12 was released sporting the OFFSET function pagination feature, developers relied on less efficient methods of paginating. In today's post, I want to explain how the OFFSET technology works.

Click to read more ...

Monday
Apr012013

SOQL: Retrieve Opportunities Between Two Dates

The query below will help you retrieve opportunities created between two dates. Please note that in the below query, the field type is “DateTime”; if you want to use the code for the “Date” field, just remove the time value.

 

Select createdDate, Id, lastModifiedDate

from Opportunity

where createdDate > 2013-03-26T24:00:00Z and createdDate < 2013-04-01T23:00:00Z

 

Click to read more ...

Wednesday
Mar202013

SOQL Date Formats

Coming from an SQL background, I’ve always had issues with SOQL’s date format. So, today, I’m going to share with you some tips on how to utilize SOQL Date queries, specifically using Built in Date Functions:

Before we dig in too deep though, we must know the syntax for Date in Force.com:

YYYY-MM-DD

 

In Force.com, there are two ways of querying Date:

#1 Specify the date in the query

The following query will return all Id’s for all the leads that were created on 2012-12-12 : Select ID, Date from Lead where Date= dateValue (2012-12-12)


#2 Query with a Built in Function

For example, the following query will return all Id’s for the Lead that were created yesterday: Select Id, Date from Lead where Date= YESTERDAY

I hope this helps!

Thursday
May102012

Offset Pagination Coming to SOQL

I can't begin to tell you how excited I am about this addition! This is a major performance improvement for those of us trying to go through large result sets returned with a SOQL statement. By giving us the new OFFSET, we can now specify the starting row offset as part of the query results. 

Basically, this is a nice and painless way for us Force.com developers for paging through large result sets and quickly jumping to a particular subset row. As an example, let's say you want to return a list of products to a user in a table and show them 50 at a time with a back/next option to page through the results. When the user skips to page Two in the result set, we want to start with the 51st record in the query result set. Previously we had to jump through some hoops to accomplish that in Apex/SOQL. Now, with OFFSET it's pretty darn easy.

SELECT Name

   FROM VendorProducts__c

WHERE ModelYear__c > '2001'

ORDER BY Name

   LIMIT 50

OFFSET 50

This accomplishes what we were trying to do by showing us a resulting set of 50 records that begins with the 51st record in that query result set. LOVE IT!

Friday
Jun102011

Standard Indexed Fields

I was recently doing some consulting for a company that works with Salesforce managed packages.  They’d come across a problem that had turned into one heckuva knot they were having trouble unraveling.  Turns out the issue had to do with some obscure details about Salesforce indexing of fields on its standard objects.  These details were only discovered via many emails and conversations with Salesforce support.

When Salesforce developers and admins write queries that are so broad that they bring back “too much” data, they often get an error message like this:

The following exception was encountered:  System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

The workaround is to try and create tighter queries by using a standard Salesforce field that has been indexed.  But even if a field is indexed, a filter might still not be selective when the filter value includes null (for instance binding with a list that contains null), or data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times).

If at all possible you want to construct your queries using the standard Salesforce indexed fields or work with Salesforce to index a column that you are going to use frequently in your lookups.

The standard Salesforce indexed fields are:

  •  Lead: Company, Email, Lead Owner, Name.
  • Contact: Account Name, Contact Owner, Email, Name, Reports To.
  •  Account: Account Name, Account Owner, Account Record Type, Parent Account,
  •  On top of these, system time fields (last modified, created date etc) and any lookup fields are also indexed.

 

What happens if I need more indexed fields in Salesforce?

By default your primary keys, forgeign keys, custom fields marked as External Ids/Unique along with the system audit dates (like last modified) are indexed. If you have a specific field that you need indexed, you can reach out to Salesforce support. Keep in mind though that there is a limit to how many you can add and they are going to weigh the pros and cons with you to see if this is going to help your situation.

Certain fields cannot be used as indexes. Examples include: multi-option picklists, currency fields if the org has multi-currency enabled, certain formula fields, binary fields (like a blob or file). In general any complex field type is not a candidate.