Home T-SQL too expensive query with select in where/having condition and composite primary key
Reply: 0

T-SQL too expensive query with select in where/having condition and composite primary key

user4689
1#
user4689 Published in June 19, 2018, 6:15 pm

I've created an http://www.sqlfiddle.com/#!18/f8137/2 to show you the schema

I have three entities "Invoice"->1:n->"Payment"->1:n->"Taking"

Each entity has it's own Total (amount) and a sign that shows if i have to add or subtract a value.

The problem is that i can't find an efficient way to "SELECT" my invoices that have an open bill (the sum of takings differs from the amount of the invoice). I have thousands of records and these two selects take a lot of time to be executed (from 25 to 30 seconds).

Here the creation of the schema

CREATE TABLE Sign (
  sign_code INT NOT NULL IDENTITY(1,1),
  sign_value INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  PRIMARY KEY (sign_code)
);

CREATE TABLE Invoice (
    invoice_year int NOT NULL,
    invoice_number int NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    sign INT NOT NULL,
    PRIMARY KEY (invoice_year, invoice_number) ,
    FOREIGN KEY (sign) REFERENCES Sign(sign_code)
);

CREATE TABLE Payment (
    invoice_year int NOT NULL,
    invoice_number int NOT NULL,
    payment_row int NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    sign INT NOT NULL,
    PRIMARY KEY (invoice_year, invoice_number, payment_row), 
    FOREIGN KEY (invoice_year, invoice_number) REFERENCES Invoice(invoice_year, invoice_number),
    FOREIGN KEY (sign) REFERENCES Sign(sign_code)
);

CREATE TABLE Taking (
    taking_year int NOT NULL,
    taking_row INT NOT NULL,
    invoice_year int NOT NULL,
    invoice_number int NOT NULL,
    payment_row int NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    sign INT NOT NULL,
    PRIMARY KEY (taking_year, taking_row), 
    FOREIGN KEY (invoice_year, invoice_number, payment_row) REFERENCES Payment(invoice_year, invoice_number, payment_row),
    FOREIGN KEY (invoice_year, invoice_number) REFERENCES Invoice(invoice_year, invoice_number),
    FOREIGN KEY (sign) REFERENCES Sign(sign_code)
);

Invoice

invoice_year    invoice_number  amount  sign
2018            1               100.2   1
2018            2               98.4    1

Payment

invoice_year    invoice_number  payment_row amount  sign
2018            1               1           50      1
2018            1               2           50.2    1
2018            2               1           90.4    1
2018            2               2           8       1

Taking

taking_year taking_row  invoice_year    invoice_number  payment_row amount  sign
2018        1           2018            1               1           80      1
2018        2           2018            1               1           80      2
2018        3           2018            1               1           25      1
2018        4           2018            1               1           25      1
2018        5           2018            1               2           25.1    1
2018        6           2018            1               2           24.1    1
2018        7           2018            2               1           90.4    1
2018        8           2018            2               2           8       1

Sign

sign_code   sign_value  description
1           1           CREDIT
2           -1          DEBT

These are the queries i've wrote

SELECT COUNT(*) 
FROM Invoice AS I
INNER JOIN Sign S1 ON I.sign = S1.sign_code
WHERE I.amount*S1.sign_value - (SELECT SUM(T.amount*S2.sign_value)
                                FROM Taking T
                                INNER JOIN Sign S2 ON T.sign = S2.sign_code
                                WHERE T.invoice_year = I.invoice_year AND T.invoice_number = I.invoice_number
                               ) <> 0;

SELECT I.*
FROM Invoice AS I
INNER JOIN Sign S1 ON I.sign = S1.sign_code
WHERE I.amount*S1.sign_value - (SELECT SUM(T.amount*S2.sign_value)
                                FROM Taking T
                                INNER JOIN Sign S2 ON T.sign = S2.sign_code
                                WHERE T.invoice_year = I.invoice_year AND T.invoice_number = I.invoice_number
                               ) <> 0

Also, these entities have composite primary keys and i have to use with doctrine and knp-paginator-bundle so i have to "COUNT" the number of rows

  • https://github.com/doctrine/doctrine2/issues/2910

  • Single id is not allowed on composite primary key in entity using knp paginator

Any idea on how to improve at least the execution time?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.346383 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO