Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 05 Dec 2005 @ 14:59:59 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: EXPLAIN rights
 
From:   Anomy Anom

<-- Anonymously Posted: Monday, December 05, 2005 09:56 -->

  I agree that an explain should be run in the production box. But not with the developers user-id!  


  Every SQL-statement in the script is EXPLAINed on the production box, using the logon-user that will be used when the script eventually executes in production (a project-user we call this).  


  This way we can check for syntax errors, AND also that the project-user has the necessary access rights.<SNIP>  


Ole,

Thank you for the feedback.

We have a very dynamic environment and we move very fast. We have gone from Zero (as in the Teradata's switch is ON) to 4 dozen ad-hoc, fully promptable reports, and 3.5 terabytes of DW data in 10 months. We recently started the process of tuning the production scripts to shave run times and we keep finding ways to improve queries; hence my request made to developers to start doing EXPLAINS and have them be aware that there's a lot they can do to make things work better the first time around and eliminate rework.

My developer's need the power to quickly check if their scripts will perform way before it even gets to the walkthrough stage and without compromising the security of the DW by having an EXPLAIN id floating around. It is already painful enough to move data from Prod to Dev in Teradata, the last thing they need to hear is that Teradata does not let you do EXPLAINS against the Prod database because of rights. I will reiterate the choices I have:

1. I get to do the EXPLAINS. That means the developer needs to send me the script and I need issue the statement to analyze it, save it and send it back to the developer as feedback. I now waste my time doing explains because Teradata is unable to do an EXPLAIN w/o rights defined and the developer has to wait for feedback on their request.

2. I issue an ID for EXPLAINS and open the door to unnecessary risk. Now anyone can UPDATE, DELETE, or INSERT data into the Production database.


This is a serious deficiency. The DBS software needs to be upgraded to distinguish between an EXPLAIN and the real SQL statement. Someone said why waste the CPU bandwidth doing EXPLAINS if the statement won't be executed. What does it matter? CPU is CPU and doing the EXPLAIN is a good use of CPU because it is done as a quality step very early in the development process.

I would rather "waste" CPU time doing EXPLAINS that will never execute than waste developer's time waiting for me to do an EXPLAIN. Worst yet is to wait until the script is ready to go into production just to discover the solution will not pass the EXPLAIN test. By the time the walkthrough happens there should not be any surprises.

Teradata needs to empower developers by being able to distinguish between an EXPLAIN request and the real thing.

If someone has a suggestion about how to empower developers to get the EXPLAIN information on the fly I would like to hear it.


Anonymous



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023