Archives of the TeradataForum

Message Posted: Mon, 05 Dec 2005 @ 22:07:26 GMT


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

Subj:		Re: EXPLAIN rights

From:		McCall, Glenn David

Anomy.Anom wrote:

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.

Hi Anonymous,

This is obviously a major concern for you - and clearly the way Teradata is currently working doesn't meet your needs.

Even if NCR/Teradata agreed to change the way the parsing engine works, it isn't going to be available tomorrow. Such a change would be complicated by the body of people that think it is valuable that the Parsing Engine check the access rights.

I can certainly understand your need. I am not sure I agree that the current mode of operation is a serious deficiency, but I do agree that developer productivity is more important than machine time. So instead of working against the issue, work around it - it will take less effort than you might be thinking.

If I were in your position what I would do is get one of the developers (or do it myself more likely) to build a simple service that they could use to verify the explain. The production system logon credentials are built into the service itself and the service will ensure that the query submitted is explained - not run.

If it were me, I would build a web service in Java running on tomcat or something like that - everything is free. To build a basic query explain service would require approximately 20 lines of Java code in the form of a servlet.

There are two things the servlet must do to guarantee an explain:

1) pre-pend the word "explain " to the front of the supplied query, and

2) strip off any characters following a ";"

You would also need a jsp to receive the query from the developer and display the results. This would take another 20-30 lines of code.

All up for a moderately experienced Java programmer half a days work - you could have had it up and running by now. For an experienced Java programmer an hour or so - fully debugged and operational. In fact I'm wishing now instead of writing this diatribe I just did it and sent it to you, it would have taken me about the same amount of time (too late - I chose poorly). But you would have needed to be able to set up an environment to host it.

If you were so inclined, you could also get the servlet to record the explain request, who submitted it, when they submitted it and the output.

To use such a service, the developers start their browser, paste there query into a textbox click submit and voila query explained.

I hope this helps

Glenn Mc


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

Attachments

Library

Quick Reference