• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

This started as a plea for help...

 
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic