CODEWITHSUNDEEP
sqlquery-optimizationteradata
Deepu298
Deepu298

Reputation: 75

SQL optimization improve efficiency | filter data on non primary key column

I have a SQL Teradata table with 4 columns. Cust_id & Act_num are indexed columns, and Date is not indexed.

I need to filter the data on the DATE column.

Select 
    Cust_id,
    Act_num,
    Date,
    Max(score) Max_Score
from
    table_name
where 
    date >= 1240601
group by 
    1, 2, 3

The query takes a long time to run, as it is filtering on the non-indexed column, and when I run that query to retrieve data for more than 6 months, it shows error insufficient spool space.

I have read only access and limited spool space which cannot be changed.

Please suggest ways to optimize the query. I already tried self join and exists

Upvotes: 1

Views: 56

Answers (2)

Nikhil
Nikhil

Reputation: 3950

Create a a Btree index on the date columnn :

create index idx_date_column_name on tablename(date_columnname);

Btree indexes are optimised for range values and ordering and range value filter where date >= 1240601 is present in your query

Upvotes: 0

Pratyush Goutam
Pratyush Goutam

Reputation: 153

You can split the query into smaller timeframes if your spool space is insufficient. Try using this query:

-- Query for first 3 months
INSERT INTO temp_table
SELECT 
    Cust_id, Act_num, Date, MAX(score) AS Max_Score
FROM 
    table_name
WHERE 
    date BETWEEN 1240601 AND 1240901
GROUP BY 
    Cust_id, Act_num, Date;

-- Query for next 3 months
INSERT INTO temp_table
SELECT 
    Cust_id, Act_num, Date, MAX(score) AS Max_Score
FROM 
    table_name
WHERE 
    date BETWEEN 1240902 AND 1241201
GROUP BY 
    Cust_id, Act_num, Date;

-- Combine results from temp_table
SELECT * FROM temp_table;

Let me know if this helps.

Upvotes: 0

Related Questions