Difference in Execution time of a SQL

Database
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.
Enthusiast

Difference in Execution time of a SQL

Hi Members,

 

I am finding huge difference in execution time of a query when i am trying to do a SELECT COUNT(*) versus SELECT with Column names. Query with SELECT COUNT(*) runs within 2 mins.. while SELECT Column data gives back the result set at 6 mins.

 

Please let me if there is a reason for this huge difference in the execution time ?

 

Thanks,

Sundar

7 REPLIES
Enthusiast

Re: Difference in Execution time of a SQL

I am sure you must have heard about COUNT(*) and GROUP BY optimization.

I think that will be a good starting point.

Senior Apprentice

Re: Difference in Execution time of a SQL

Hi,

 

As @vkbagare said there is a Teradata optimisation for the COUNT(*) processing, but two such queries are asking different questions - so there is often likely to be a run time difference.

 

The optimisation that Vinay refers to is that COUNT(*) doesn't read the actual data rows, it only reads the internal cylinder indexes. These occupy much less disk space, therefore fewer IO's, therefore faster processing.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference in Execution time of a SQL

Thanks to both of you for your explanation. I certainly agree that COUNT(*) optimization leads to a faster result... is this also true if the Query is constructed by joining multiple tables ? I thought this is applicable only if we did COUNT(*) from a Single table.

 

In my case, I am trying to construct a SELECT query by joining on multiple tables. Please help me understand this.

 

Thanks,

Sundar

Senior Apprentice

Re: Difference in Execution time of a SQL

Hi Sundar,

 

No, that will not apply if multiple tables in a query. For this optimisation:

- single table (see below)

- no where clause

- no aggregation (apart from "COUNT(*)")

- no sub-queries

Basically your query has to be finding 'the number of rows in the table' - or the only processing against a particular table.

In the following query, the optimisation will apply to the derived table processing:

SEL databasename
  ,rowcount
FROM dbc.databasesv
CROSS JOIN (SELECT COUNT(*) AS rowcount FROM dbc.accessrights) AS dt1

HTH
Dave

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference in Execution time of a SQL

Thanks for the detail explanation, Dave. But, I am unsure on why my query (which is sourcing data by joining on Multiple tables) is executing within 2 mins when I do a COUNT(*) as opposed to 6 mins when Column names are mentioned in the upper most SELECT clause. My query looks something like the below:

SELECT

COUNT(*) FROM

driver inner join table 1 on <<cond >>

inner join table 2 <<cond>>

etc; -- 1mins & 30 sec

 

SELECT * FROM

FROM

driver inner join table 1 on <<cond >>

inner join table 2 <<cond>>

etc; -- 6mins

 

Thanks,

Sundar

 

Senior Apprentice

Re: Difference in Execution time of a SQL

This might simply be down to volume of data in the spool file(s) - which will be written to/from disk if big enough.

 

It is not the optimisation that @vkbagare and I talked about.  That is unlikely to apply in this case.

 

In your "count(*)" query the final spool file is a single row with a single column - very small. Building up to that I would expect the spool files to be small as well.

 

In you "select *" query the spool files will hold every column from all derived tables, typically much larger - both in terms of row length and number of rows.

 

It might be worth explaining both queries and looking at the estimated spool file sizes.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Difference in Execution time of a SQL

Thank you Dave.. Make sense. I will work on this and will let you know the outcome.

 

Again thanks to all !!