Optimizing MS-SQL Query

Question

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. ;)

Have you check the indexing? SQL Server Profiler

huMpty duMpty
– huMpty duMpty

2014-07-22 12:54:47 +00:00
Commented Jul 22, 2014 at 12:54 — huMpty duMpty
– huMpty duMpty, Commented Jul 22, 2014 at 12:54

Gordon Linoff · Accepted Answer · 2014-07-22 12:56:55Z

3

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.

answered Jul 22, 2014 at 12:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mordack550 Over a year ago

In MS SQL Server IN and OR are performance equivalent, the IN clause only improve readability. Also even if we were talking about MySQL, that actually has IN optimizations like binary searches, the performance improvement would not be so critical to reduce a 1 hour of query on a 570k rows table to something acceptable. (let's be honest, that's a small table). The FROM clause may be the problem.

Gordon Linoff Over a year ago

@mardack550 . . . In this case, the logic is not equivalent, because parentheses are missing from the where clause. I assume the intention is for these comparisons to be grouped together, and the in clause does this more cleanly than a bunch of ors.

How 'bout a Fresca · Accepted Answer · 2014-07-22 13:01:30Z

0

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

answered Jul 22, 2014 at 13:01

How 'bout a Fresca

2,3271 gold badge18 silver badges27 bronze badges

Comments

Dan · Accepted Answer · 2014-07-22 13:04:06Z

0

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.

answered Jul 22, 2014 at 13:04

Dan

1,9692 gold badges25 silver badges37 bronze badges

Comments

Jermaine · Accepted Answer · 2014-07-22 13:11:46Z

0

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

answered Jul 22, 2014 at 13:11

Jermaine

1031 silver badge8 bronze badges

Collectives™ on Stack Overflow

Optimizing MS-SQL Query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related