File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes This started as a plea for help... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "This started as a plea for help..." Watch "This started as a plea for help..." New topic
Author

This started as a plea for help...

Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

But now it's just a simple little question.

My simple little question is: Is this SQL as 'efficient' as it could be?
heheheheheheh. Ok, kidding.

I'm not using high-powered Oracle or anything like that, so does anyone know of a tool that has a nice little "Query Optimizer". I'm fairly convinced there is a 'better' way to do my query

Is there such a thing? Take a SQL statement, plan it, and reverse engineer a much better plain-language SQL statement?
[ June 27, 2002: Message edited by: Mike Curwen ]
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Unless I'm completely missing the point of this query (and/or the question), the easiest way to do this is:

In general, when asking this sort of question, it's really helpful to post the table structures (which fields they contain, primary and foreign keys, etc.).
Also, if you're going to use table aliases, use an aliased version for every reference to the table. It makes life much easier.
Performance -- I'm very much the wrong person to address this issue. I know Oracle has commands for timing queries and "explain plan," but I don't really use those. I write (sometimes fairly complicated) ad hoc queries all the time but I'm almost never concerned with performance. (I work on a huge database project. There's a performance team that tunes poor-performing SQL that's embedded in code. They look at indexes, table sizes, hints, etc. There are guidelines about when it's quicker to use a join vs. exists. Don't use certain functions in the where clause. Etc.)
Simplifying queries -- Don't know about any tools. Why do you have 3 selects in a query that only needs 1?
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

Because it's not as simple as it looks, which is apparent if you see both the database schema and some test data.

within lookups, there can be (and for this query there is) two levels of lookup. given a 'HYG' type, there are 'HYG0', 'HYG1', 'HYG2' and 'HYG3' subtypes. And each of these is a product, but 3 different products for each, filtered by the range for which it is valid.

Starting from innermost:
Query1: find subtypes for 'HYG'
Query2: find products in the above list
Query3: find the name of the 'super type' for each product that falls within a certain date range.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Mike Curwen writes:
Because it's not as simple as it looks, which is apparent if you see both the database schema and some test data.
Well, don't keep us in suspense. Post the schema and some data. You asked "could my answer be better?" -- but you didn't say what the question was.
The query I proposed was based on assumptions I made regarding your original query. Show me why I'm wrong.
within lookups, there can be (and for this query there is) two levels of lookup.
What means "level"?
given a 'HYG' type, there are 'HYG0', 'HYG1', 'HYG2' and 'HYG3' subtypes.
What means "subtypes"? (In terms of relational databases. Was I wrong in assuming this is all relational?)
And each of these is a product, but 3 different products for each, filtered by the range for which it is valid.
Starting from innermost:
Query1: find subtypes for 'HYG'
Query2: find products in the above list
Query3: find the name of the 'super type' for each product that falls within a certain date range.

Honestly, I don't get all this levels, sub- and supertype business at all. Show me some tables and data. And a plain-language statement about what rows you want to pull.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: This started as a plea for help...