April 28, 2015 at 5:44 am
Hi,
when retrieving data from our DMS, the users often get a sql-timeout.
It seems, that the SQL-Statement is not really optimized due to it's complexity.
In a first step we noticed that the selection with "where value IN" is not optimized:
the plan does not use a index for that.
After splitting into an "or" statement the plan does still not use an index -
not explicable to me (should join two index searches).
I went better when splitting the "or" into a UNION, but sometimes still timeouts -
(maybe depending on the searched data).
Now playing within the mstudio, I could solve the problem by first selecting the most limiting tables and then joining the others - as I would do when programming it ... but why does the Server not know how to optimize ?
SQL1: old statement (executing time 180 sec.) (using "IN")
SQL2: better statement (executing time 94 sec.) (USING UNION)
SQL3: new statement (executing time 1 sec.) (reordered JOINS) ?!
Any hints, why I have to manually have to reorder the joins ?
April 28, 2015 at 6:34 am
is fk columns indexed properly?
looks like objkeys table has two columns parented, okeyname(guess if not correct me) if it is true why you need to use char columns in where clause.
if you can provide execution plan and table definitions that would be helpful to figure out the cause for slowness.
April 28, 2015 at 7:02 am
April 28, 2015 at 7:16 am
looks like his table is an Entity-Attribute-Value model, so he uses the same table multiple times for various lookups.
Lowell
April 28, 2015 at 7:29 am
objkeys has several columns ... we need parentid (key to the object-table), okeyname and okeydata
(okeyname is the Name of the value and okeydata is the value itself,
e.g. okeyname "VENDOR" & okeydata "AMAZON" and okeyname "ORDERNO" & okeydata "4711" for the same parentid).
So after getting the objectid I have to join the objkeys several times to get the okeydata for every okeyname.
okeydata has an index : "okeyname + okeydata, including parentid" - so this should be fine
What I don't understand: when moving the selection from "parentid in (" to the JOIN it runs much slower ...
FAST:
select top 1 objid, ok12.okeydata
FROM
(
SELECTparentid from objkeys where okeyname = 'BELNR' and okeydata = 'DRA10171462'
andparentid in (select parentid from objkeys where okeyname = 'LIBELART' and (okeydata = 'Eingangsrechnung' or okeydata = 'Eingangsgutschrift'))
) Daten
INNER JOIN objekte AS obj ON daten.parentid = obj.objid
INNER JOIN objkeys AS ok12 ON daten.parentid = ok12.parentid AND ok12.okeyname = 'LIBELART'
SLOW:
select top 1 objid, ok12.okeydata
FROM
(
SELECTparentid from objkeys where okeyname = 'BELNR' and okeydata = 'DRA10171462'
) Daten
INNER JOIN objekte AS obj ON daten.parentid = obj.objid
INNER JOIN objkeys AS ok12 ON daten.parentid = ok12.parentid AND ok12.okeyname = 'LIBELART'
and okeyname = 'LIBELART' and (okeydata = 'Eingangsrechnung' or okeydata = 'Eingangsgutschrift')
ALSO FAST (???): ("and parentid in (select parentid from objkeys)" is always true !)
select top 1 objid, ok12.okeydata
FROM
(
SELECTparentid from objkeys where okeyname = 'BELNR' and okeydata = 'DRA10171462'
and parentid in (select parentid from objkeys)
) Daten
INNER JOIN objekte AS obj ON daten.parentid = obj.objid
INNER JOIN objkeys AS ok12 ON daten.parentid = ok12.parentid AND ok12.okeyname = 'LIBELART'
and okeyname = 'LIBELART' and (okeydata = 'Eingangsrechnung' or okeydata = 'Eingangsgutschrift')
April 28, 2015 at 7:36 am
Table definitions and indexes would go a long way to helping you here. Can you post them?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2015 at 7:45 am
script attached ...
some indexes on objkeys are stupid for searching (e.g. okeydata without okeyname / okeyno) but were released by the software vendor;
we added the "KH_..." Indexes for better Performance when selecting okeyname and/or okeydata.
April 28, 2015 at 8:24 am
I took a closer look to the execution plans: can't see big differences ... the main Job is the "RID Lookup" with about 70% but very small costs in both cases.
So why does it differ from 1 sec to 99 sec ?
April 28, 2015 at 8:56 am
April 28, 2015 at 8:58 am
The big difference between the plans is the order in which SQL Server is applying predicates, and it shows most drastically in the first nested loop join.
In the fast plan, the seek that is the outer input to the loop is estimating about 6 rows to be returned, but it actually returns a single row, so the seek that is the inner input gets executed once and returns 4140 rows.
In the slow plan, the seek that is the outer input to the loop is using a different set of predicates, and incorrectly estimating 1 row to be returned. Instead, it returns 13,267 rows, and the seek that is the inner input is executed over 8,000 times.
Not only is there a big performance hit just from having to seek that many times, but the number of rows processed is much higher (about 150,000 instead of 4,000).
It seems the poor estimate for the different set of predicates used is the primary difference between the two.
April 28, 2015 at 10:28 am
hm .. but why does the optimizer not solve this ?
Is it to me, to control and change the select-Statements ?
April 28, 2015 at 11:41 am
Well, there are a couple points there.
1) The optimizer is not perfect, and does make mistakes. Especially with many joins and complex predicates, the number of ways to access the data can be so large that the optimizer does not have time to evaluate enough plans to pick a good plan, much less the best one.
2) If the statistics used by the optimizer are incorrect, then no amount of intelligence in the optimizer will guarantee a good plan, because it's starting with incorrect assumptions about the data.
In this case, for example, the estimate for the rows returned by the following WHERE clause is wrong in both the fast and the slow plan:
okeyname = 'LIBELART' and (okeydata = 'Eingangsrechnung' or okeydata = 'Eingangsgutschrift')
By luck it happens that in the plan that is returning more quickly for you, the incorrect estimate is for that seek on the inside of a nested loop join, and it doesn't impact the query as badly as when that seek is the outer input.
As far as SQL Server is concerned, there is very little difference between the plans, but that's based on incorrect estimates. If the data were distributed the way SQL Server thinks it is distributed, then both plans would work about as well.
I'd check statistics on the index that's being used (KH_ix_keydata_keyname), and see if they need to be updated.
This can also happen if the values in question are not values used as a RANGE_HI_KEY for a histogram step, as then SQL Server has to guess at the number of rows based on the average number of rows for each distinct value in that histogram step. Sometimes that average and the actual number of rows for a value can be quite different.
In that case, updating statistics won't really help, although there are some things that can help (filtered statistics for values often used in queries, for example).
At any rate, there's no getting around the fact that sometimes the optimizer needs a little help from us carbon-based life forms. 'Tis the nature of the beast 🙂
Cheers!
April 28, 2015 at 2:02 pm
Regarding how to get the optimizer to do what you want when it seems like the engine has got the row counts wrong,
http://sqlbits.com/Sessions/Event14/Query_Tuning_Mastery_Clash_of_the_Row_Goals
There are more convensional methods but the tips in the above are when the conventional methods dont work and you have to nudge the optimizer gently.
April 29, 2015 at 12:11 am
Thanks a lot for your help and quick response !
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply