Whenever we are using multi-statement TVFs, we are essentially forcing nested loops logic on the database engine. Although multi-statement TVFs are smart enough and do not always execute once per row, when they do so, they may be much slower than nested loops.

As usual, inline UDFs shine as compared to multi-statement ones, at least in all the benchmarks in this post - let us run some tests and see for ourselves.

 Setting up data

First of all, here is a table, rather large, 512K rows, and rather wide, less than 20 rows per page, so that reading from it via nested loops is a natural choice in many cases:

CREATE TABLE dbo.Parent
    (
      ID INT NOT NULL
             PRIMARY KEY ,
      ParentNumber INT NOT NULL,
      SpaceFiller CHAR(500) NOT NULL
    ) ;
GO
DECLARE @adder INT ;
SET @adder = 1 ;
INSERT  INTO dbo.Parent
        ( ID, ParentNumber, SpaceFiller )
VALUES  ( 1, 1, '#' ) ;

WHILE @adder < 500000 
    BEGIN ;
        INSERT  INTO dbo.Parent
                ( ID ,
                  ParentNumber ,
                  SpaceFiller
                )
                SELECT  ID + @adder ,
                        ParentNumber ,
                        SpaceFiller
                FROM    dbo.Parent ;
        SET @adder = @adder * 2 ;
    END ;
GO   

The Child table is much smaller and narrow:

CREATE TABLE dbo.Child
    (
      ID INT NOT NULL
             PRIMARY KEY ,
      ParentID INT NOT NULL ,
      ChildNumber INT NOT NULL UNIQUE
    ) ;   
GO

INSERT INTO dbo.Child
        ( ID, ParentID, ChildNumber )
SELECT ID, -- ID - int
          ID, -- ParentID - int
          ID  -- ChildNumber - int
FROM dbo.Parent WHERE ID BETWEEN 1 AND 1000 ; 

 Comparing nested loops vs. multi-statement UDF calls

Here are the two UDFs which we are  going to benchmark:

 CREATE FUNCTION dbo.GetParentNumber_Multiline ( @ParentID INT )
RETURNS @parentInfo TABLE ( ParentNumber INT )
AS 
    BEGIN ;
        INSERT  @parentInfo
                ( ParentNumber
                )
                SELECT  ParentNumber
                FROM    dbo.Parent
                WHERE   ID = @ParentID ;
        RETURN ;                
    END ;
GO

CREATE FUNCTION GetParentNumber_Inline ( @ParentID INT )
RETURNS TABLE
AS
RETURN
    ( SELECT    ParentNumber
      FROM      dbo.Parent
      WHERE     ID = @ParentID
    ) ;  

Let us fire up the Profiler, make sure it records individual statements, and run the following script:

SELECT ID, ParentID, p.ParentNumber
INTO #t1
FROM dbo.Child
CROSS APPLY dbo.GetParentNumber_Multiline ( ParentID ) AS p ;

DROP TABLE #t1 ;

SELECT ID, ParentID, p.ParentNumber
INTO #t1
FROM dbo.Child
CROSS APPLY dbo.GetParentNumber_Inline ( ParentID ) AS p

DROP TABLE #t1 ; 

In the Profiler, we shall see that the multi-statement UDF has been invoked 1000 times, which is slow:

Multiline stats:

   CPU time = 312 ms,  elapsed time = 696 ms.

Inline stats:

   CPU time = 0 ms,  elapsed time = 7 ms.
 

Clearly the difference in performance is dramatic, while the plan is essentially the same - nested loops. We can highlight the query, compare the execution plans, and see for ourselves.

Multi-statement UDFs do not always run once per row

While we are at it, let us bust a myth: multi-statement UDFs do not have to run once per row. Let us have all child rows refer to only one parent:

UPDATE dbo.Child SET ParentID = 1 ; 

Let us rerun our benchmarks. In the profiler we shall see that the multi-statement UDF has been invoked only once. Execution costs are also quite comparable this time for both scripts:

   CPU time = 15 ms,  elapsed time = 21 ms.

As we have seen, the database engine is smart enough and may sometimes execute multi-statement UDFs once per distinct set of parameters, not once per row.

Let us set up test data differently, so that the second benchmark (the one using inline UDF) executes as a hash join:

UPDATE dbo.Child SET ParentID = ID ; 

TRUNCATE TABLE dbo.Parent ;
GO
INSERT INTO dbo.Parent
        ( ID, ParentNumber, SpaceFiller )
SELECT ID, ID, '*'        
FROM dbo.Child ;
  

The first benchmark still executes as a nested loop, and is much slower

Multi-statement UDF, nested loops:
   CPU time = 266 ms,  elapsed time = 674 ms.
   
Inline UDF, hash join:
   CPU time = 0 ms,  elapsed time = 5 ms.

Conclusion

As we have seen, if we want to force nested loops, we can use multi-statement UDFs.Also we have seen that those multi-statement UDFs can be very slow.

However, let us be cautious: when we want to benchmark real life solutions, we do not want to do it with the Profiler running, like I did in this post. Also, as Dave Ballantine has correctly pointed out, in some cases STATISTICS TIME may skew the results just as well.

Correlated subqueries do not have to execute once per row - on the contrary, they are equivalent to outer joins, and they may have the same execution plans and the same real execution costs (if we retrieve only one column via a correlated subquery, of course)

Let me provide a script which compares the performance of a query with one correlated subquery vs. an equivalent out join. First, we need two tables for our example:

CREATE TABLE dbo.Parent
    (
      ID INT NOT NULL
             PRIMARY KEY ,
      ParentNumber INT NOT NULL
    ) ;
GO
CREATE TABLE dbo.Child
    (
      ID INT NOT NULL
             PRIMARY KEY ,
      ParentID INT NOT NULL ,
      ChildNumber INT NOT NULL
    ) ;   

Next, let us populate them with 512K rows each:

DECLARE @adder INT ;
SET @adder = 1 ;
INSERT  INTO dbo.Parent
        ( ID, ParentNumber )
VALUES  ( 1, 1 ) ;

WHILE @adder < 500000 
    BEGIN ;
        INSERT  INTO dbo.Parent
                ( ID ,
                  ParentNumber
                )
                SELECT  ID + @adder ,
                        ParentNumber
                FROM    dbo.Parent ;
        SET @adder = @adder * 2 ;
    END ;
GO

INSERT INTO dbo.Child
        ( ID, ParentID, ChildNumber )
SELECT ID, -- ID - int
          ID, -- ParentID - int
          0  -- ChildNumber - int
FROM dbo.Parent ; 

We want to compare the performance of two queries without the overhead of retrieveing and or materializing result sets, so I will make sure the result sets are empty. The correlated subquery approach is as follows:

SET STATISTICS IO ON ;
SET STATISTICS TIME ON ;
GO
               
SELECT  ChildNumber ,
        ParentNumber
INTO    #t1
FROM    ( SELECT    ChildNumber ,
                    ( SELECT    ParentNumber
                      FROM      dbo.Parent AS p
                      WHERE     p.ID = c.ParentID
                    ) AS ParentNumber
          FROM      dbo.Child AS c
        ) AS t
-- no rows meet this criteria
WHERE   ChildNumber + ParentNumber = -123 ; 

The execution costs are:

 Table 'Child'. Scan count 9, logical reads 2502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parent'. Scan count 9, logical reads 2215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The equivalent outer join looks like this (run it in another tab):

SET STATISTICS IO ON ;
SET STATISTICS TIME ON ;
GO

SELECT  ChildNumber ,
        ParentNumber
INTO    #t2
FROM    ( SELECT    ChildNumber ,
                    ParentNumber
          FROM      dbo.Child AS c
                    LEFT OUTER JOIN dbo.Parent AS p ON p.ID = c.ParentID
        ) AS t
-- no rows meet this criteria
WHERE   ChildNumber + ParentNumber = -123 ;

Its real execution costs are the same.

More to the point, when I hit Ctrl+L in both tabs, I see the same execution plan.

Conclusion 

As we have seen, correlated subqueries do not have to execute once per each row. In fact, they are just macros - the optimizer can flatten them out and rewrite the queries involving them as outer joins.

Neither

UPDATE … IF (@@ROWCOUNT = 0) INSERT

nor

IF EXISTS(...) UPDATE ELSE INSERT

patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing and see for ourselves.

Here is the table we shall be using:

CREATE TABLE dbo.TwoINTs
    (
      ID INT NOT NULL PRIMARY KEY,
      i1 INT NOT NULL ,
      i2 INT NOT NULL ,
      version ROWVERSION
    ) ;
GO

INSERT  INTO dbo.TwoINTs
        ( ID, i1, i2 )
VALUES  ( 1, 0, 0 ) ;    

IF EXISTS(…) THEN pattern frequently fails under high concurrency.

Let us insert or update rows in a loop using the following simple logic: if a row with given ID exists, update it, and otherwise insert a new one. The following loop implements this logic. Cut and paste it into two tabs, switch into text mode in both tabs, and run them simultaneously.

-- hit Ctrl+T to execute in text mode

SET NOCOUNT ON ;

DECLARE @ID INT ;

SET @ID = 0 ;
WHILE @ID > -100000
    BEGIN ;
        SET @ID = ( SELECT  MIN(ID)
                    FROM    dbo.TwoINTs
                  ) - 1 ;
        BEGIN TRY ;

            BEGIN TRANSACTION ;
            IF EXISTS ( SELECT  *
                        FROM    dbo.TwoINTs
                        WHERE   ID = @ID )
                BEGIN ;
                    UPDATE  dbo.TwoINTs
                    SET     i1 = 1
                    WHERE   ID = @ID ;
                END ;
            ELSE
                BEGIN ;
                    INSERT  INTO dbo.TwoINTs
                            ( ID, i1, i2 )
                    VALUES  ( @ID, 0, 0 ) ;
                END ;
            COMMIT ; 
        END TRY
        BEGIN CATCH ;
            ROLLBACK ; 
            SELECT  error_message() ;
        END CATCH ;
    END ; 

When we run this script simultaneously in two tabs, we shall immediately get a huge amount of primary key violations in both tabs. This demonstrates how unreliable the IF EXISTS pattern is when it executes under high concurrency.

Note: this example also demonstrates that it is not safe to use SELECT MAX(ID)+1 or SELECT MIN(ID)-1  as the next available unique value if we do it under concurrency.

UPDATE … IF (@@ROWCOUNT = 0) BEGIN pattern is also unreliable.

Another common approach is to update a row first, and if no row was updated, insert it. It is also unreliable. Before demonstrating that, let us delete the rows inserted by the previous example:

DELETE  FROM dbo.TwoINTs WHERE   ID < 1 ;

Let us modify the loop which we ran in the previous example, as follows:

-- hit Ctrl+T to execute in text mode

SET NOCOUNT ON ;
DECLARE @ID INT ;
SET @ID = 0 ;
 
WHILE @ID > -100000
    BEGIN ;
        SET @ID = ( SELECT  MIN(ID)
                    FROM    dbo.TwoINTs
                  ) - 1 ;
        BEGIN TRY ;
            
            BEGIN TRANSACTION ;
      
            UPDATE  dbo.TwoINTs
            SET     i1 = 1
            WHERE   ID = @ID ;
            IF ( @@ROWCOUNT = 0 )
                BEGIN ;
                    INSERT  INTO dbo.TwoINTs
                            ( ID, i1, i2 )
                    VALUES  ( @ID, 0, 0 ) ;
                END ;
            COMMIT ; 
        END TRY
        BEGIN CATCH ;
            SELECT  error_message() ;  
            ROLLBACK ;     
        END CATCH ;

    END ;   

When we run this script simultaneously from two tabs, we are getting lots of primary key violations, just like when we ran our previous example.

As we have seen, the UPDATE … IF (@@ROWCOUNT = 0) pattern is unreliable under high concurrency too.

MERGE holds up perfectly well

 Again, let us delete the rows inserted by the previous example:

DELETE  FROM dbo.TwoINTs WHERE   ID < 1 ;

 Rerun the loop using MERGE:

-- hit Ctrl+T to execute in text mode
SET NOCOUNT ON ;
DECLARE @ID INT ;
SET @ID = 0 ;
 
WHILE @ID > -100000
    BEGIN ;
        SET @ID = ( SELECT  MIN(ID)
                    FROM    dbo.TwoINTs
                  ) - 1 ;
        BEGIN TRY ;
       
            MERGE dbo.TwoINTs AS target
                USING
                    ( SELECT    @ID ,
                                0 ,
                                0
                    ) AS source ( ID, i1, i2 )
                ON ( target.ID = source.ID )
                WHEN MATCHED
                    THEN
        UPDATE           SET
                i1 = 1
                WHEN NOT MATCHED
                    THEN 
          INSERT    ( ID, i1, i2 )
                         VALUES
                    ( @ID ,
                      0 ,
                      0
                    ) ;
 
        END TRY
        BEGIN CATCH ;
            SELECT  error_message() ;       
        END CATCH ;
    END ;   

In the context of our loop, MERGE always completes without a single error!

As we have seen, in this particular case MERGE holds up under high concurrency perfectly well. Of course, this does not mean that we can always use this new command without stress testing. Yet we should at least consider using it whenever we insert or update under high concurrency, with usual precautions and after thorough testing.

It is fun to be blogging again. I kind of neglected blogging for some time, because I am writing a book on defensive programming, and that requires a huge amount of time. Anyway, it feels good to be back. The book should be finished soon, and I'll start contributing here more.

 This post participates in the second SQL Tuesday

Clearly Linux is one of the most successful products ever. Let me quote a little bit from Linus Torvalds and other brilliant people involved in it:

"A strong vision and a sure hand sound like good things on paper. It's just
that I have never _ever_ met a technical person (including me) whom I
would trust to know what is really the right thing to do in the long run.

Too strong a strong vision can kill you - you'll walk right over the edge,
firm in the knowledge of the path in front of you."

here

"The most successful software have born from fixing/patching an
initial/simple implementation while the greatest software failures
have born from deep planning and design. "

here

More specifically, the following article is a great read

Of course, PIVOting uses up some CPU. However, if the data is dense (all the cells in the pivoted result set are not NULL), then the size of the pivoted result set may be significantly less. As a result,  the overall time to retrieve a pivoted result set and transmit it over the network may be less. Here are my benchmarks.

Let us create a helper table with 1M numbers:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO
            TRUNCATE TABLE dbo.Numbers;
INSERT INTO dbo.Numbers
    ( n )
        VALUES  ( 1
    );
GO
DECLARE @i INT;
    SET @i=0;
WHILE @i<21 
    BEGIN
    INSERT INTO dbo.Numbers
        ( n )
        SELECT n + POWER(2, @i)
        FROM dbo.Numbers;
    SET @i = @i + 1;
    END;    

Let us create and populate an typical EAV table:

CREATE TABLE dbo.ObjectAttributes(ObjectID INT NOT NULL, AttributeID INT NOT NULL, IntValue INT NOT NULL,
                CONSTRAINT PK_ObjectAttributes PRIMARY KEY(ObjectID, AttributeID));
GO
            TRUNCATE TABLE dbo.ObjectAttributes;
INSERT INTO dbo.ObjectAttributes(ObjectID, AttributeID, IntValue)
    SELECT n1.n, n2.n, n1.n
    FROM dbo.Numbers AS n1
            CROSS JOIN
            dbo.Numbers AS n2
    WHERE n1.n <100001 
        AND n2.n<11;


Let us select all the data from it as is a couple of times:

SET STATISTICS TIME ON;
SELECT * 
    FROM dbo.ObjectAttributes;

SELECT * 
    FROM dbo.ObjectAttributes;
 

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 8369 ms.
   
 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 5935 ms.

Let us select pivoted data:

SELECT ObjectID, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
    FROM dbo.ObjectAttributes
            PIVOT( MAX(IntValue) FOR AttributeID IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS pvt
    ORDER BY pvt.ObjectID;


Although the pivoted query used more CPU, the overall time was substantially less:

SQL Server Execution Times:
   CPU time = 1219 ms,  elapsed time = 1794 ms.
 

I was inspired by the following Aaron's post:

What is so bad about EAV, anyway?

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order - making such assumptions leads to unsafe queries. For example, the following query is not safe:

SELECT * FROM dbo.Messages
  WHERE ISDATE(VarcharColumn)=1 
    AND CAST(VarcharColumn) AS DATETIME)='20090707'
 

It can blow up at any time, and the reason is simple: the conditions in your WHERE clause can evaluate in any order, and the order can change the next time your query executes. If an invalid value casts before the check if it is valid, the query will blow up. Of course, I am not the first to point this out. Right from the top of my head I recall that you can find similar recommendations in Itzik Ben Gan's book on T-SQl Fundamentals, and in a recent Plamen Ratchev's post "Predicates in SQL". However, I would like to demonstrate how little it may take to break such code, how brittle it may be. I will provide a repro script in which a query originally succeeds, but fails after I have added an index.

Do not make assumptions which you cannot guarantee

Whenever you write such code as the previous sample, you clearly assume that the first condition, the call to ISDATE, will evaluate before the CAST. However, this assumption cannot be guaranteed, and when this assumption is not true, the query blows up. Even if this query always succeeds in your test runs, you cannot assume that it will always succeed in the future. The safe way to ensure that invalid values are never cast to datetime is this:

SELECT * FROM dbo.Messages
  WHERE CASE WHEN ISDATE(VarcharColumn)=1
    THEN CAST(VarcharColumn AS DATETIME) END ='20090707'


It is safe becasue it is documented that CASE expression evaluates the WHEN clause before evaluating the THEN clause.

When order in which conditions are evaluated changes - a repro script.

I am going to provide a repro script demostrating that provide that the order in which conditions are evaluated can change. However, I have to begin with a big disclaimer: there is no guarantee that this repro script will work as expected on your server. My repro script does work for me, and most likely will work for you too, but I cannot promise that.

Setting up test data

The following script adds 1M messages, all of them except one have invalid dates:

-- helper table
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;
GO
CREATE TABLE dbo.Messages(MessageID INT NOT NULL PRIMARY KEY,
  SenderID INT NOT NULL,
  ReceiverID INT NOT NULL,
  MessageDateAsVarcharColumn VARCHAR(30) NULL,
  SomeMoreData CHAR(200) NULL);
GO
INSERT INTO dbo.Messages(MessageID,
  SenderID,
  ReceiverID,
  MessageDateAsVarcharColumn,
  SomeMoreData)
SELECT n, n%1000, n/1000, 'Wrong Date', 'SomeMoreData'
  FROM dbo.Numbers;
GO
-- only one message has a valid date
UPDATE dbo.Messages SET MessageDateAsVarcharColumn='20090707' 
  WHERE SenderID = 123 AND ReceiverID = 456;  

Originally an unsafe query against test data succeeds

The following query succeeds on my server, selecting the only one row which has a valid date:

SELECT * FROM dbo.Messages
  WHERE SenderID = 123 
    AND ReceiverID = 456
    AND CAST(MessageDateAsVarcharColumn AS DATETIME)='20090707'
 

Because the query succeeds, clearly for all the rows the first two conditions are evaluated before MessageDate is cast to DATETIME, because only the row for which the first two conditions are turn has a valid datetime value. Let me repeat my disclaimer: there is no guarantee that this query will succeed on your server. Even if you reshuffle the conditions in your WHERE clause, the query still succeeds, so clearly the cast is attempted only after both other conditions are evaluated:

SELECT * FROM dbo.Messages
  WHERE CAST(MessageDateAsVarcharColumn AS DATETIME)='20090707'
    AND ReceiverID = 456
    AND SenderID = 123 
 

As you have seen, conditions in WHERE clause are not evaluated in left-to-right order.  You can also play with parenthesis trying to enforce one particular order, but that will not make any difference either.

After adding an index, the same query blows up

Here is the index:

CREATE INDEX Messages_SenderID_MessageDate
  ON dbo.Messages(SenderID, MessageDateAsVarcharColumn);


After it is created, the query blows up:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

My explanation is simple: the optimizer chooses to use the new non-clustered index, and it chooses to cast the MessageDate value and evaluate the third condition CAST(MessageDateAsVarcharColumn AS DATETIME)='20090707') before doing an expensive bookmark lookup. Let me repeat my disclaimer again: there is no guarantee that this query will blow upon your server.

Also I played a little bit with parenthesis, trying to use them to enforce a particular order of evaluation - of course that did not make any difference.

As you have seen, it is unsafe to make assumptions about the order in which conditions in your WHERE clause are evaluated. Whenever you see a query developed under such an assumption, rewrite it eliminating the unsafe assumption altogether.

If you are still reading this, can you do me a favor and post if this repro script worked on your server the way it did on mine. Please also post the output of SELECT @@VERSION.  I will be on vacation for a couple of weeks. This means that I will respond to questions, but not immediately.

This post continues my series on defensive database programming. Here are my previous posts  from the series: 

Defensive database programming: fun with triggers

Defensive database programming: fun with ROWCOUNT

Summarizing previous posts about defensive database programming

Defensive database programming: SET vs. SELECT.

Stress testing UPSERTs

Defensive database programming: fun with UPDATE.

Defensive database programming: eliminating IF statements.

Defensive database programming: fun with changing column widths.

Avoid mixing old and new styles of error handling.

Defensive database programming: adding ESCAPE clauses.

Defensive database programming: qualifying column names.

Defensive database programming: rewriting queries with NOT IN().

In some cases some of the columns involved in a query are highly correlated. If you manage to communicate to the optimizer that valuable information, it may come up with a more efficient plan. For example, consider the following table (the script that populates it is at the end of this post):

CREATE TABLE dbo.Events(EventID INT NOT NULL PRIMARY KEY,
  EventTime DATETIME NOT NULL,
  SomeMoreData CHAR(10)
); 

Suppose that your system inserts events one by one, that EventID keeps increasing as your system keeps inserting, and that EventTime also keeps increasing most of the time, with possible minor fluctuations caused by delays between the client and the server. Clearly EventID and EventDate are highly correlated in this scenario. Consider the following queries:

DECLARE @dfrom DATETIME, @dto DATETIME;
SELECT @dfrom = '20050102 12:34:56', @dto = '20050103 12:34:56';

SELECT COUNT(*) FROM(
SELECT EventID, EventTime, SomeMoreData FROM dbo.Events 
WHERE EventTime BETWEEN @dfrom AND @dto 
AND SomeMoreData > '1'
)AS t;


SELECT COUNT(*) FROM(
SELECT EventID, EventTime, SomeMoreData FROM dbo.Events 
WHERE EventTime BETWEEN '20050102 12:34:56' AND '20050103 12:34:56'
AND SomeMoreData > '1'
)AS t;  

Table 'Events'. Scan count 9, logical reads 4088, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 107 ms,  elapsed time = 21 ms.
 

 In this case more than 8% or rows meet the criteria (the script that populates test data is at the end of this post, you can run it and see for yourself), so both queries are executed as clustered index scans. However, because of the typical way the rows are inserted into the table, the rows that actually meet the criteria are very much clustered together - most pages do not contain any rows meeting the criteria, while on some pages most or all rows are selected. Communicating this information to the optimizer is easy and the query runs much faster, because it scans just a range of the clustered index:

DECLARE @dfrom DATETIME, @dto DATETIME;
SELECT @dfrom = '20050102 12:34:56', @dto = '20050103 12:34:56';

SELECT COUNT(*) FROM(
SELECT EventID, EventTime, SomeMoreData FROM dbo.Events 
WHERE EventTime BETWEEN @dfrom AND @dto
AND EventID BETWEEN (SELECT MIN(EventID) FROM dbo.Events AS e1 WHERE e1.EventTime = @dfrom)
  AND (SELECT MAX(EventID) FROM dbo.Events AS e1 WHERE e1.EventTime = @dto)
AND SomeMoreData > '1'
)AS t;   

 Table 'Events'. Scan count 3, logical reads 848, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 36 ms.

Of course, if the assumption that EventID and EventDate are highly correlated is no longer true, this query will no longer be a better choice.

 Setting up test data

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;
GO 

DROP TABLE dbo.Events
GO
CREATE TABLE dbo.Events(EventID INT NOT NULL PRIMARY KEY,
  EventTime DATETIME NOT NULL,
  SomeMoreData CHAR(10)
);
GO

INSERT INTO dbo.Events(EventID, EventTime, SomeMoreData)
SELECT n, DATEADD(second, n, '20050101'), 'some data'
FROM dbo.Numbers;
GO
-- reshuffle EventTime just a little bit
UPDATE dbo.Events SET EventTime = DATEADD(second, EventID%20, EventTime);
GO
CREATE INDEX Events_EventTime ON dbo.Events(EventTime);