Query execution time prediction

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Query execution time prediction

Suppose I have a query which I am running on dev and test environment which is handling some 1 million records now when I am migrating the query in prod environment it will be dealing with more than 10 billion records. Now while running the query in dev or test environment its taking just 10 seconds.

Now my questions are.

1) as I dont have that much storage to migrate the 10 billion record in dev or test how can I just calculate how much time that query can take in prod environment provided I cant migrate more than a billion record? Is there any process?

Please help with suggestion.

Re: Query execution time prediction

I really need this help please help.

Teradata Employee

Re: Query execution time prediction

Hi deborshi_c,


This is a tuff question because what I'll say here may be totally wrong.

Timing a query is already hard because of the total workload of the platform varies, this is why we look at IO / CPU / Spool because they aren't dependant of the workload. Let suppose you have a constant workload.


First, try to find the intrinseque difference between your two environements.

Running a non trivial query on both on them on similar volume, similar general workload, if one answers in 10 seconds and the other on in 20 seconds you can suppose the first one is twice faster.


Then about your 1 million rows vs 10 billions rows.

The number of rows in the table doesn't really matter.

What matters is the number of rows spooled and processed.

If you table is, for example, daily partitionned and you work on one day, it's ok - the performance of the query should remain the same over time (assuming volume on a day by day basis is almost constant).


If you have to process all those 10 billions rows, well, it's probably not a good scaling query.




Re: Query execution time prediction

Thank you Walder for your suggestion but I have different question after reading your post. You said about spooling. Please tell me how to assume that the query I am going to run will not get spool space issue.

Say for an example we can get how much spool is given to us and we we have a query to run which will process huge records. So in this scenario can we assume that this query will get any spool issue or not. Please suggest.