3 Incorporated my answer
source | link

For the first two queries all it has to do is seekscan in the clustered index to the first entry for that value of IDUkazatelIDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatelIDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where aswhereas the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleanerThat said, addingit seems you have a group by may givetable with IDUkazatel values rather than an explicit OR clause. The code below will work with that arrangement, simply replace the query plannertable name @T with the clue that it only needs to care about one row per valuename of the table containing IDUkazatel values:

SELECT 
    MinCas = MIN(casCA.PartialMinimum)
FROM @T AS T
CROSS APPLY  
(
    SELECT  
   SELECT IDUkazatel, cas=MIN   PartialMinimum = MIN(casPD.Cas)
    FROM dbo.PenData pAS PD
    WHERE p.IDUkazatel 
 IN (24, 25) GROUP BY IDUkazatel
  PD.IDUkazatel = T.IDUkazatel
) AS minimumsCA;

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is In an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25ideal world, though depending on the spread of dataSQL Server query optimizer would perform this may be worse (many values for IDUkazatel then it may have to scan quiterewrite for to find the first values it cares aboutyou, if there are few thenbut it will probably find the relevant one relatively quicklydoes not always consider this option today.

For the first two queries all it has to do is seek in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where as the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleaner, adding a group by may give the query planner the clue that it only needs to care about one row per value:

SELECT MIN(cas)
FROM   (
        SELECT IDUkazatel, cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel IN (24, 25) GROUP BY IDUkazatel
    ) AS minimums

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25, though depending on the spread of data this may be worse (many values for IDUkazatel then it may have to scan quite for to find the first values it cares about, if there are few then it will probably find the relevant one relatively quickly.

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), whereas the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

That said, it seems you have a table with IDUkazatel values rather than an explicit OR clause. The code below will work with that arrangement, simply replace the table name @T with the name of the table containing IDUkazatel values:

SELECT 
    MinCas = MIN(CA.PartialMinimum)
FROM @T AS T
CROSS APPLY  
(
    SELECT  
        PartialMinimum = MIN(PD.Cas)
    FROM dbo.PenData AS PD
    WHERE  
        PD.IDUkazatel = T.IDUkazatel
) AS CA;

In an ideal world, the SQL Server query optimizer would perform this rewrite for you, but it does not always consider this option today.

2 deleted 2 characters in body
source | link

For the first two queries all it has to do is scan downseek in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where as the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleaner, adding a group by may give the query planner the clue that it only needs to care about one row per value:

SELECT MIN(cas)
FROM   (
        SELECT IDUkazatel, cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel IN (24, 25) GROUP BY IDUkazatel
    ) AS minimums

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25, though depending on the spread of data this may be worse (many values for IDUkazatel then it may have to scan quite for to find the first values it cares about, if there are few then it will probably find the relevant one relatively quickly.

For the first two queries all it has to do is scan down the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where as the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleaner, adding a group by may give the query planner the clue that it only needs to care about one row per value:

SELECT MIN(cas)
FROM   (
        SELECT IDUkazatel, cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel IN (24, 25) GROUP BY IDUkazatel
    ) AS minimums

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25, though depending on the spread of data this may be worse (many values for IDUkazatel then it may have to scan quite for to find the first values it cares about, if there are few then it will probably find the relevant one relatively quickly.

For the first two queries all it has to do is seek in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where as the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleaner, adding a group by may give the query planner the clue that it only needs to care about one row per value:

SELECT MIN(cas)
FROM   (
        SELECT IDUkazatel, cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel IN (24, 25) GROUP BY IDUkazatel
    ) AS minimums

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25, though depending on the spread of data this may be worse (many values for IDUkazatel then it may have to scan quite for to find the first values it cares about, if there are few then it will probably find the relevant one relatively quickly.

1
source | link

For the first two queries all it has to do is scan down the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), where as the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

Or perhaps cleaner, adding a group by may give the query planner the clue that it only needs to care about one row per value:

SELECT MIN(cas)
FROM   (
        SELECT IDUkazatel, cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel IN (24, 25) GROUP BY IDUkazatel
    ) AS minimums

(I've not generated data and tried these queries - run them on your data to see if they result in a better query plan)

Another option that might help is an index over cas (or maybe cas, IDUkazatel). That way with your original query it can scan down this index until it finds the first row where IDUkazatel is 24 or 25, though depending on the spread of data this may be worse (many values for IDUkazatel then it may have to scan quite for to find the first values it cares about, if there are few then it will probably find the relevant one relatively quickly.