Select with inner join same table, frequent execution, multiple plans

Hi,

this is a query run 2000 X /min. SELECT VR1.OID FROM VREGEL VR1 WHERE VR1.AID = @P1 AND {fn UCASE(VR1.NAME)} = @P2 AND VR1.VERSION = (SELECT MAX(VR2.VERSION) FROM VREGEL VR2 WHERE VR2.AID = VR1.AID AND VR2.OID = VR1.OID AND VR2.VON <= @P3 AND VR2.BIS >= @P4)

The content of the tbale looks like:

 AID    OID    VERSION    VON    BIS    ERFASSUNG    ERFASSUNGSUSER    NAME    K_KONTIERUNGSART    K_BERECHART_MA    BEZEICHNUNG
 7    1    1    1900-01-01 00:00:00.000    9999-01-01 00:00:00.000    1900-01-01 00:00:01.000    Migration    Verteilung_NurGF    7    0    100% für die GF
 7    2    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_40_0    7    0    33,00/mit OM/3,0er (Version 1)
 7    2    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_40_0    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    3    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_42_87    7    0    33,00/mit OM/3,0er (Version 1)
 7    3    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_42_87    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    4    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_42_88    7    0    33,00/mit OM/3,0er (Version 1)
 7    4    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_42_88    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    5    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_43_0    7    0    33,00/mit OM/3,0er (Version 1)
 7    5    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_43_0    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    6    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_43_75    7    0    33,00/mit OM/3,0er (Version 1)
 7    6    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_43_75    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    7    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_45_0    7    0    33,00/mit OM/3,0er (Version 1)
 7    7    2    2010-04-01 00:00:00.000    9999-01-01 00:00:00.000    2010-04-09 00:00:02.000    Migration    Verteilung_AP_001_GF_45_0    7    0    33,00/mit OM/3,0er (Version 1) (abgelaufen)
 7    8    1    1980-01-01 00:00:00.000    9999-01-01 00:00:00.000    1994-07-08 00:00:01.000    Migration    Verteilung_AP_001_GF_45_5    7    0    33,00/mit OM/3,0er (Version 1)

Execution plan:

alt text

How, besides caching, to tune this statement? Thank you so much BR Frank

execplan.jpg (45.1 kB)
avatar image By F. 1 asked Jul 25, 2016 at 01:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x7
x2
x1

asked: Jul 25, 2016 at 01:51 PM

Seen: 319 times

Last Updated: Jul 25, 2016 at 01:51 PM