IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New How would you do this?
We have a table that tracks the status of our orders. Each transaction gets an entry in the orderstatus table, keyed to the ordernum, and with a status that defines what the transaction was.

I'm trying to create a query to show me all orders that have ever had status = '$status' but have never had status = '$no_status'.

What I've tried is:
SELECT DISTINCT\n    orders.ordernum ,\n    no_orderstatus.status AS nostatus\nFROM\n    orders ,\n    orderstatus\n    LEFT JOIN orderstatus no_orderstatus ON (\n        orders.ordernum = no_orderstatus.ordernum\n    )\nWHERE\n    orderstatus.status = '$status'\n    AND no_orderstatus.status = '$no_status'\n    AND orders.ordernum = orderstatus.ordernum\nHAVING\n    nostatus IS NULL

Tried the query and I killed it at about 3 minutes execution time. The orders table has ~1.5 million rows, orderstatus has ~12.5 million, so it may just take that long.


PS: This is in MySQL 3.something, so no subselects.

[Edit to remove unwanted filter in WHERE clause]
===

Implicitly condoning stupidity since 2001.`
Expand Edited by drewk May 13, 2003, 08:31:03 PM EDT
Expand Edited by drewk May 13, 2003, 09:54:30 PM EDT
New Kludgy, but without subselects I end up using TRANSFORMs
TRANSFORM status
SELECT orderstatus.ordernum
FROM orderstatus
WHERE orderstatus.ordernum = '$ordernum'
GROUP BY orderstatus.ordernum
PIVOT orderstatus.status


Then pass through the result set and test for (($no_status Is Null) AND (Not $status Is Null)). Since you are filtering by ordernum in WHERE, your result set should always be one entry, I think. Did you intend that?

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New Oops, didn't mean to filter on ordernum
But in any case, it turns out my query was correct, and it was just taking far too long. I worked around the issue by requiring that you select a start date for an on-screen report, and warning the user that doing a printable report without a start date could take an extremely long time.

Are TRANSFORM and PIVOT available in MySQL? I'm not familiar with them.
===

Implicitly condoning stupidity since 2001.
New Oops, no they're not.
Teach me to flail out the shortcuts. Wow, no transform, no subselects...tell me again why you're using MySQL? >:)

Many fears are born of stupidity and ignorance -
Which you should be feeding with rumour and generalisation.
BOfH, 2002 "Episode" 10
New It was there when I was hired
===

Implicitly condoning stupidity since 2001.
New counts?
Off the top of my head, I am envising using counts of some kind with a group-by. It would roughly have something like:

having count($status) > 0 and count($no_status) = 0

Maybe use a temp or virtual table to sum the status first per order. I wish more RDBMS allow temp tables. It allows one to mentally break the problem into smaller steps.
________________
oop.ismad.com
New Sybase, Oracle, Postgres, MSSQL all allow temp tables.
Dunno about DB/2.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New I hear they are slower than equiv run-on SQL statements
________________
oop.ismad.com
New Depends on the complexity of the operation...
...as well as which specific server you are talking about. I know SQLServer 6.5 had real problems with Joins on more than 4 tables (up to about 6 or 7 on the later ones). So if you have joins involving more than this threshold, temp tables outperform the more complex (but singular) SQL constructs.\r\n\r\nGenerally, though, a single SQL statement will perform better, but it's usually because the programmers use temp tables not as an optimization trick - rather it's a way to break the problem up into smaller and more easily to manipulate chunks. So, yes, if you can state the problem in a more concise single statement, you'll usually have much better throughput.
New Not the only reason...
temp tables can stick around for a while (for multiple operations). A single SQL statement (if there were multiple operations) would have have to be reprocessed each time.

I won't mention the read locks that single SQL statement would put on the system. (The corellary danger, is that there aren't read-locks on the system for a temp table. Design your application accordingly. :-)
New Other considerations...
I haven't found a way to tune the indexing on temp tables, which can present some difficulties if the temp tables get large and the lookups are based on multiple keys. In addition, because the data changes each and every time you load the temp tables, the queries against the temp table will usually be automatically re-planned - whereas permanent tables tend to be more stable and the query plans don't have to be compiled with each invocation.

Not sure of the other servers, but SQLServer does have read/write locks on the temp tables. Of course, the tables from which you got the data are not locked - which is what you were probably trying to indicate.

SQLServer has a new concept for procedures called the Table Variables, which oddly enuf are not locked - getting you some performance boost. I like the idea, but it would be nice if their version of T-SQL was consistent and let you pass table variables around like any other variable. As it is, you can't seem to pass the table vars from one stored procedure to another, seriously crimping how you can modularize an application.
New Not much larger
Generally, though, a single SQL statement will perform better, but it's usually because the programmers use temp tables not as an optimization trick - rather it's a way to break the problem up into smaller and more easily to manipulate chunks. So, yes, if you can state the problem in a more concise single statement, you'll usually have much better throughput.

I don't think the one-big-statement approach is necessarily more "concise". It is generally just naming a chunk(s) and referencing the name instead of embedding that chunk within a statement. It might take a few more characters, but not much. In some cases it may shrink the total size because there may be less need for table qualifiers on columns because the name pool for tables is smaller if you divide.
________________
oop.ismad.com
     How would you do this? - (drewk) - (11)
         Kludgy, but without subselects I end up using TRANSFORMs - (tseliot) - (3)
             Oops, didn't mean to filter on ordernum - (drewk) - (2)
                 Oops, no they're not. - (tseliot) - (1)
                     It was there when I was hired -NT - (drewk)
         counts? - (tablizer) - (6)
             Sybase, Oracle, Postgres, MSSQL all allow temp tables. - (admin) - (5)
                 I hear they are slower than equiv run-on SQL statements -NT - (tablizer) - (4)
                     Depends on the complexity of the operation... - (ChrisR) - (3)
                         Not the only reason... - (Simon_Jester) - (1)
                             Other considerations... - (ChrisR)
                         Not much larger - (tablizer)

Powered by thermodynamics!
65 ms