SQL performance

Database
Fan

SQL performance

SQL performance

Hi All
Teradata 12
DB is remote - there is NO possibility to admin
READ-ONLY access to views (made on tables 1 to 1)
but there is possibility create temp tasbles in TEMP space

SQL assistant
DB is big (telecom)

There is rather complex SQL - say SQL1

Optimizer estimate performance like 3.5 hours - really it works for 5-8 minutes - so OK.

SQL1 returns like 1.500 000 entries (not much really for this system)

Problem when new join added to SQL1
ON history table like:

JOIN SSHistotry SSH
ON SBS.Subscription_Id = SSH.Subscription_Id
AND CAST (CAST('2009-12-01' AS DATE) AS TIMESTAMP(0)) - INTERVAL '1' SECOND
BETWEEN SSH.Subs_Segment_Start_Dttm AND SSH.Subs_Segment_End_Dttm

Optimizer estimate 36 hours !!!!
when SQL works - (more than 1 hour - then aborted) skew about 99%
What could be problems with that ?
joins like this are standard in this system and works ok - many times without problems

I try change SSHistotry to other history table - result same
What could be ways of optimizing sql in this case ?
Creating temp table with index say on Subscription_Id
I ll try this way
- but may be there are some hints ?
1 REPLY
Fan

Re: SQL performance

ps
Add try create temp table
- all OK works fine
create takes about 5-10 minutes
and after that join with history table takes 5 second
but old sql - still works VERY slow