Best practices when writing SQL
This article describes the best practices for using Data Query and provides best-practice samples for common query use cases.
General best practices
To query data using Data Query, Zuora recommends the following best practices.
Familiarize yourself with the business objects
Zuora recommends that you use the following statement to get familiar with Zuora objects and object schemas before you start using Data Query to retrieve data.
SHOW TABLES SHOW COLUMNS FROM|IN {table} DESCRIBE {table} DESC {table}
Reference all new variables with a table and variable declaration
Zuora recommends that you add a table and variable declaration when you reference new variables in the SELECT statement. For example:
Recommended | Not recommended |
---|---|
SELECT sub.id, a.accountnumber FROM Subscription sub, Account a WHERE sub.initialterm > 12 AND a.balance > 0 |
SELECT sub.id, a.accountnumber FROM sub, a WHERE sub.initialterm > 12 AND a.balance > 0 |
Use JOINs effectively
-
INNER JOIN - Keep the smaller table on the RIGHT when using INNER JOIN
INNER JOIN is an alternative to LEFT, RIGHT, and OUTER JOINs. For INNER JOINs, Zuora recommends that you keep the smaller table on the RIGHT and the bigger one on the LEFT when using INNER JOIN. See the syntax below.
SELECT * FROM bigger_left_table INNER JOIN smaller_right_table ON bigger_left_table.smaller_right_id = smaller_right_table.id
In the following example, rateplancharge is the larger table and rateplan is the smaller table. We will list rateplancharge to the LEFT of the INNER JOIN and rateplan to the RIGHT.
Recommended Not recommended SELECT c.* FROM Rateplancharge c INNER JOIN Rateplan rp on c.rateplanid = rp.id WHERE rp.subscriptionid = 'xxxxx000001'
SELECT c.rateplanid FROM Rateplan rp INNER JOIN Rateplancharge c on c.rateplanid = rp.id WHERE rp.subscriptionid = 'xxxxx000001'
- Index Join - Enable Index Join for data sets that you can narrow down with unique identifiers
Index Join is an addition to LEFT, RIGHT, OUTER, and/or INNER JOINs and can be enabled whenever a JOIN is used. Index join is a useful addition to other joins when you have a specific reference value in your WHERE clause to index another large table by.
For example, you want to get Account and Subscription information tied to a specific ProductRatePlanCharge. The ProductRatePlanCharge table is one of the largest tables in your query. However, you only need the ProductRatePlanCharge that matches the specific ID in your WHERE clause. You can use the Index Join in your query as follows:
SELECT Account.AccountNumber, Subscription.Name FROM ProductRatePlanCharge PRPC JOIN RatePlanCharge RPC ON PRPC.id = RPC.productrateplanchargeid JOIN RatePlan RP ON RP.id = RPC.rateplanid JOIN Subscription ON Sub.id = RP.subscriptionid JOIN Account ON Account.id = Subscription.accountid WHERE PRPC.id = '1375'
Note that when using Index Join, the field used in your WHERE clause must be found in the table that you are indexing (the table on the LEFT side of the JOIN). This field should narrow down the set of records returned from the indexed-table to be less than the query processing limit. In this example, ProductRatePlanCharge table will be indexed by the
id
, which is1375
, to narrow down the ProductRatePlanCharges that the query JOINs with RatePlanCharges.You can use Index Join through the following ways:
- Make a Submit data query API call with
useIndexJoin
set totrue
in the request body. - Tick the Use index join checkbox on Retrieve: Data Query Workflow task UI.
- Make a Submit data query API call with
Use LIMIT to sample your query results
Before your first time running a full query, Zuora recommends that you add LIMIT statement to get a quick snapshot of the first few entries. See the syntax below.
SELECT balance, accountId, invoiceDate FROM Invoice WHERE accountId = '123-456-7890' AND balance > 0 ORDER BY invoiceDate DESC LIMIT 1
Once you ensure the result is desirable and meaningful, you can run the full query.
Note that the LIMIT function only affects the output records limitation.
Filter efficiently
A SQL filter allows you to narrow down the query result set to only the rows that you are interested in. Zuora recommends the following rules to make your filter efficient.
- Define clear date ranges with updateDate, createDate, effectiveStartDate, and so on.
Recommended Not recommended SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate >= DATE('2019-01-01') AND rpc.effectivestartdate <= DATE('2019-08-01')
SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate <= CURRENT_DATE
- Separate a large query into smaller ones. See the following example that separates a big query into smaller ones by date ranges. Data Query can simultaneously process a maximum of 5 queries per tenant.
Date range Separated query January - March
SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate >= DATE('2019-01-01') AND rpc.effectivestartdate <= DATE('2019-03-31')
April - June
SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate >= DATE('2019-04-01') AND rpc.effectivestartdate <= DATE('2019-06-30')
July - August
SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate >= DATE('2019-07-01') AND rpc.effectivestartdate <= DATE('2019-08-01')
- Use exact strings instead of strings containing a wildcard.
Recommended Not recommended SELECT a.name FROM Account a WHERE a.accountnumber = 'A00000977'
SELECT a.name FROM Account a WHERE a.accountnumber LIKE 'A%'
- Avoid using NULL in your filter.
The following filter is NOT recommended.
SELECT rpct.includedunits FROM Rateplanchargetier rpct WHERE rpct.discountamount IS NULL AND rpct.discountpercentage IS NULL
- Avoid using functions on the left-hand side of the comparison operator.
Recommended Not recommended SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE rpc.effectivestartdate > DATE('2019-01-01')
SELECT a.accountnumber FROM Rateplancharge rpc ... WHERE DATEDIFF(MONTH, rpc.effectivestartdate, '2019-01-01') < 0
- Filter on fields with subsets of unique values.
The following table lists the commonly used large tables and the fields with subsets of unique values. Zuora recommends that you use these fields to optimize your filter.
Zuora business object Fields with subsets of unique values RatePlanCharge
-
createdDate, updatedDate, effectiveStartDate: used to filter by date range
-
billingDay: used for unique days (for example, not the 1st, 15th, or 30/31st)
RatePlanChargeTier
-
createdDate, updatedDate: used to filter by date range
-
startingUnit, endingUnit: used for batch jobs
InvoiceItem
-
chargeDate, createdDate, revRecStartDate, serviceStartDate, serviceEndDate, updatedDate: used to filter by date range
TaxationItem
-
createdDate, taxDate, updatedDate: used to filter by date range
-
locationCode, jurisdiction: used to filter location-specific data for objects like Tax
JournalEntry
-
accountPeriodId: used to group sets
-
createdDate, journalEntryDate, transferDate, updatedDate: used to filter by date range
-
notes: used as the unique information to filter by
-
status: used to filter journal entries
Usage
-
createdDate, startDateTime, endDateTime, submissionDateTime, updatedDate: used to filter by date range
-
Best-practice samples for common queries
See the following samples of best practices for some common use cases.
- Get a list of Subscriptions and the associated Accounts whose custom field geographic_region_c is "West"
SELECT subscription.id, account.id FROM Subscription INNER JOIN account ON subscription.accountid = account.id WHERE account.geographicregion__c = ‘West’
- GET the Products updated since January 1, 2019
SELECT * FROM Product WHERE updateddate >= DATE('2019-01-01')
- GET the Accounts with Usage for the Products with specified SKUs
SELECT a.accountnumber FROM Usage u, Account a, Product p WHERE a.accountnumber = u.accountnumber AND p.sku IN ( 'SKU-200001', 'SKU-200002', 'SKU-200003', 'SKU-200004' ) GROUP BY a.accountnumber