Re: Possible Performance Improvement to Select Statement

  Thanks everyone for your input. Ask Tom is a great resource of knowledge. Sometimes getting a question into his team is another feat on it's own.

  Just to reply to some questions about my query. It looks at first glance rather redundant and pointless to have the subquery or to use a temporary table, but I assure you it's completely needed.

  Perhaps a better example of what it is that I'm trying to accomplish.  I have Customer Orders that are composed of Work Orders. An example would be a Customer Order for a car. A single Order would contain 4 Work Orders for the wheels. As a wheel is installed it's Work Order status is changed to complete (9). When the 4 wheels are installed and completed the Customer Order should also in turn be changed to completed.

   Customer Order = "completed"

       Wheel Work Order = "completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "completed"

  However I have in my database Customer Orders that are "not complete" but have all of their Work Orders "completed". If I run simply the query looking for Customer Order = "not completed" and Work Order "completed" I get too many results that are not entirely what I'm looking for. Example:

    Customer Order = "not completed"

       Wheel Work Order = "not completed"
       Wheel Work Order = "not completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "not completed"

   Because one of the Work orders are "completed" I get the entire Work Order even though nothing is wrong with it. This is where creating the temp tablespace and the second query come into play.

   I want to use "distinct" in my query because I only want to see 1 instance of the Customer Order. The query uses the 2 tables that contain about 40,000 entries each. Of these tables it turns out that there were only 12 Customer Orders that were not correct. When I run my query I should get Customer Orders that look like this:

     Customer Order = "not completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "completed"
       Wheel Work Order = "completed"

  As for checking performance I was not looking to benchmark this query because I use it only for housekeeping. My original script took about 2 hours to complete now I get the same job done in about 3 minutes. I was just looking for a way to run this script quicker and with lesser hit on my server processes.

   This query works! If you have a similiar rype of query feel free to use it as an example. Even though the table feilds might be in German you can substitute it with different names for easier understanding.

   Thanks again everyone for your input ;) Received on Mon Feb 07 2005 - 03:14:38 CST