How do I write efficient SQL queries in Pipeline Cloud?

Overview 

Following best practices for query writing help ensure your Pipeline Cloud queries run efficiently and return results in a timely manner. 

Step 1: Limit result sets 

1. Use the TOP query hint to restrict the number of rows returned: 

SELECT TOP 100 FirstName, LastName FROM Contacts WHERE State = 'CA' 

2. Start with smaller limits (like 100) and increase as needed: 

SELECT TOP 1000 FirstName, LastName FROM Contacts WHERE State = 'CA' 

3. For pagination or large data sets, consider using larger limits carefully: 

SELECT TOP 10000 FirstName, LastName FROM Contacts WHERE State = 'CA' 

Step 2: Specify exact columns 

1. Avoid using SELECT * which returns all columns: 

-- Inefficient query 

SELECT * FROM Contacts WHERE LastName = 'Smith' 

 

-- Efficient query 

SELECT VanID, FirstName, LastName, Email FROM Contacts WHERE LastName = 'Smith' 

2. Only select the columns you actually need for your analysis 

Step 3: Avoid problematic join types 

1. Do not use CROSS JOIN unless absolutely necessary: 

-- Avoid this type of join 

SELECT c.FirstName, e.EventName 

FROM Contacts c CROSS JOIN Events e 

2. Always use proper join conditions with ON clauses: 

-- Better approach 

SELECT c.FirstName, e.EventName 

FROM Contacts c  

INNER JOIN EventSignups es ON c.VanID = es.VanID 

INNER JOIN Events e ON es.EventID = e.EventID 

Step 4: Check for soft-deleted records 

1. When querying certain tables, check the DateSuppressed column to exclude soft-deleted records: 

SELECT FirstName, LastName  

FROM Contacts  

WHERE DateSuppressed IS NULL 

2. Tables with soft delete functionality include: 

   1. Contacts 

   2. Events 

   3. EventSignups 

   4. Address 

What else do you need help with? 

• Understanding Pipeline Cloud 
• How do I adapt MySQL queries to Microsoft SQL? 
• How do I verify the database schema?