CODEWITHSUNDEEP
sqlsql-server
EBS_EDV
EBS_EDV

Reputation: 13

Optimizing MS-SQL Query

I'm struggling with the performance of one MS-sql query, which I have to run to create a report in our ERP system.

Hopefully you can help me with that query.

Here is the query:

"Original version": 

SELECT artikel.artikelnummer, artikel.bezeichnung, SUM(positionen.anzahl), artikel.Einheit
FROM artikel, auftrag, positionen 
INNER JOIN auftrag AS auftrag1 ON (auftrag1.auftrag = positionen.auftrag) 
INNER JOIN artikel AS artikel1 ON (positionen.artikel = artikel1.artikel)
WHERE 
artikel.warengruppe = 2
OR artikel.warengruppe = 1234
OR artikel.warengruppe = 1235
OR artikel.warengruppe = 1236
OR artikel.warengruppe = 1237
OR artikel.warengruppe = 1239
OR artikel.warengruppe = 1240
OR artikel.warengruppe = 2139
AND auftrag.lieferscheinnr IS NOT NULL
GROUP BY artikel.artikelnummer, artikel.bezeichnung,artikel.Einheit

"Translated version": 

SELECT article.articlenr, article.description, SUM(positions.amount), article.unit
FROM article, order, positions
INNER JOIN order AS order1 ON (order1.order = positions.order) 
INNER JOIN article AS article1 ON (positions.article = article1.article)
WHERE 
article.materialgroup = 2
OR article.materialgroup = 1234
OR article.materialgroup = 1235
OR article.materialgroup = 1236
OR article.materialgroup = 1237
OR article.materialgroup = 1239
OR article.materialgroup = 1240
OR article.materialgroup = 2139
AND order.dordernr IS NOT NULL
GROUP BY article.articlenr, article.description,article.unit

We want to count the amount of ink about all of our delivery orders (DO). In the table "auftrag" I have all of the DO numbers and the ordernumbers. In the table "positionen" I have all positions of the several orders including the right amount of inkbottles. In the table "artikel" I have all of the article-details like description, bottle size and so on. The column "artikel.warengruppe" contains the right material groups which contains the ink.

Now the problem is that the tables "auftrag" contains 160.000, "artikel" 155.000 and positionen 570.000 entries.

I abort the query after 1 hour runtime. So my question is how can I optimize the query?

My problem is that I cant change the ER-model.

Thank you very much in advance for your help. I hope you can understand my crappy english. ;)

Upvotes: 1

Views: 99

Answers (4)

Jermaine
Jermaine

Reputation: 103

Maybe something like this:

DECLARE @Articles TABLE (id INT);
 INSERT @Articles (id)
 VALUES (2), (1234), (1235), (1236), (1237), (1239), (1240), (2139);

 SELECT article.articlenr, article.description, SUM(positions.amount), article.unit
   FROM article, order, positions
  INNER JOIN [order] AS order1 ON (order1.order = positions.order) AND order.dordernr IS NOT NULL
  INNER JOIN article AS article1 ON (positions.article = article1.article)
  INNER JOIN @Articles AS Art ON Art.id = article.materialgroup
  GROUP BY article.articlenr, article.description, article.unit

Upvotes: 0

Dan
Dan

Reputation: 1959

You should check the execution plan. Here is a link on technet that can help you with that: http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx. It will tell you what indexes should be added or changed (I wonder column artikel.warengruppe is indexed at all). If you post the execution plan, people on SO will really be able to help you.

Upvotes: 0

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Apart from indexing, would something like this get you the results you need:

SELECT article.articlenr, article.description, SUM(positions.amount), article.unit
FROM positions
    INNER JOIN order AS order1 ON (order1.order = positions.order) 
    INNER JOIN article AS article1 ON (positions.article = article1.article)
WHERE 
    EXISTS (SELECT *
            FROM article a
            WHERE materialgroup IN (2,1234, 1235, 1236, 1237, 1239, 1240, 2139)
                AND a.article = article1.article)
AND order.dordernr IS NOT NULL
GROUP BY article.articlenr, article.description,article.unit

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If you phrase the where clause correctly, then performance will probably improve. I would suggest:

WHERE artikel.warengruppe in (2, 1234, 1235, 1236, 1237, 1239, 1240, 2139) and
      auftrag.lieferscheinnr IS NOT NULL;

Your logic is finding everything for the first 7 warengruppe values. Then it is also looking for 2139 where lieferscheinnr IS NOT NULL.

Second, you need to fix your from clause. It should be something like:

FROM artikel
INNER JOIN auftrag AS auftrag1 ON (auftrag1.auftrag = positionen.auftrag) 
INNER JOIN artikel AS artikel1 ON (positionen.artikel = artikel1.artikel)

Just avoid commas in the from clause. Your query was doing a massive cartesian product of all the tables and then joining the tables back in. An incredible amount of work.

Upvotes: 3

Related Questions