Best practices of Data Query

Knowledge Center > API > Data Query > Best practices of Data Query

Best practices of Data Query

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

Keep the smaller table on the RIGHT when using INNER JOIN

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, rateplan is the larger table and rateplancharge is the smaller table. We will list rateplan to the LEFT of the INNER JOIN and rateplancharge to the RIGHT. 

Recommended Not recommended
SELECT c.rateplanid
FROM Rateplan rp
INNER JOIN Rateplancharge c on c.rateplanid = rp.id
WHERE rp.subscriptionid = 'xxxxx000001'
SELECT c.*
FROM Rateplancharge c
INNER JOIN Rateplan rp on c.rateplanid = rp.id
WHERE rp.subscriptionid = 'xxxxx000001'

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.

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
    
Last modified

Tags

This page has no custom tags.

Classifications

(not set)