Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ideas had some disadvantages, so we could't figure out what's the best way to go.

We have a main entity "Transaction" which should be processed by "ProcessingRules". The processing rules can be configured by the users in the Web application (each rule has a different execution scheduling. One might be running every hour, whereas the others might run nightly).

Lets say Transaction gets 10'000 new records a day.

This would lead to a DB design where I need to keep the State "Processed YES/NO" for each "ProcessingRule" and "Transaction".

I thought the proper way is to have a relation table between the "ProcessingRoles" and the "Transaction". If no record present, the record has not been processed by this role yet.

Transaction [0..1] ------ [*] TransactionRuleProcessing [*] ------- [0..1] ProcessingRule

But when I think of the Query, this would lead into a WHERE NOT EXISTS (SELECT 1 FROM TransactionRuleProcessing...) query for the rule to identify new or unprocessed records.

If we have a large amount of rows in Transaction, I think this will affect performance because the NOT EXISTS will have to join the whole table to the state table. If I'm not mistaken, this might cause a performance issue.

On the other side, if we had only one state directly on the Transaction table, we could add an index and there would be no join between the large Transaction table and the state table.

Question:

Is it true that such a NOT EXISTS query would have to join the whole Transaction table with the TransactionRuleProcessing table to identify non existing (processed) rows? How could this affect performance of the database with a large Transaction table? What would be other recommendations to flag a record by a various amount of states?

Any ideas very much appreciated

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, StanleyJohns, dezso Sep 2 '13 at 5:25

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

2 Answers 2

Using the cross reference table between Transaction and ProcessingRule is going to be a very efficient way of returning Transaction records that need to be processed.

You can get a list of Transactions that need processing by:

USE Test;

CREATE TABLE Transactions
(
    TransactionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTity(1,1)
    --extra Transaction fields here as necessary
);

CREATE TABLE Rules
(
    RuleID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    --extra Rules fields here as necessary
);

CREATE TABLE TransactionRuleProcessing
(
    TransactionID INT NOT NULL
    , RuleID INT NOT NULL
);



DECLARE @RuleID INT = 123; --123 is the rule being processed

SELECT T.*
FROM Transactions AS T
    LEFT JOIN (
        SELECT * 
        FROM TransactionRuleProcessing AS TRP1 
        WHERE TRP1.RuleID = @RuleID
    ) AS TRP ON T.TransactionID = TRP.TransactionID
WHERE TRP.TransactionID IS NULL;
share|improve this answer
    
Thanks. I still don't get how joins work: If I had only one state, I would add it to the Transactions table and add an index for it. I assume this would be very fast, because there would be only a few hundred with state Unprocessed at the time. I assume the performance would be constant even if the table grows continuous. But when the state is kept in another table, the Database Engine would have to join the whole Transaction table to identify its state. This would slow down performance if the table contains more records. Is my assumption wrong? Can non-existing rows be indexed? –  Thomas Zweifel Jun 4 '13 at 6:54
add comment

You don't necessarily need to use a NOT EXISTS statement, you could always outer join the TransactionRuleProcessing table to the Transaction table and the ProcessingRule tables, and then simply check for empty columns in the result set that originate from the TransactionRuleProcessing table.

So basically, you could do something like this :

SELECT  A.id as TransactionId, 
    B.id as RuleId, 
    CASE ISNULL(C.TransactionId,0) WHEN 0 THEN 'No' ELSE 'Yes' END as DoesExist
FROM 
    Transactions A
    LEFT OUTER JOIN ProcessingRules B ON 1 = 1
    LEFT OUTER JOIN TransactionProcessRules C ON C.Transactionid = A.id AND C.RuleId = B.id

If your Transaction.id column and TransactionRuleProcessing.TransactionId columns are properly indexed, you shouldn't run into any significant problems, performance wise

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.