speed difference dblinq results vs sql query in prompt

Question

I've setup a database to be used with dblinq.

CREATE TABLE 'quotes' (                                                                                                      
      'DBDate' int(8) unsigned NOT NULL,                                                                                         
      'TickerID' int(11) unsigned NOT NULL,                                                                                      
      'Open' double(12,4) NOT NULL,                                                                                              
      'High' double(12,4) DEFAULT NULL,                                                                                          
      'Low' double(12,4) DEFAULT NULL,                                                                                           
      'Close' double(12,4) DEFAULT NULL,                                                                                         
      'AdjClose' double(12,4) DEFAULT NULL,                                                                                      
      'Volume' int(11) unsigned NOT NULL,                                                                                        
      PRIMARY KEY ('TickerID','DBDate'),                                                                                         
      CONSTRAINT 'quotes_ibfk_1' FOREIGN KEY ('TickerID') REFERENCES 'tickers' ('TickerID') ON DELETE CASCADE ON UPDATE CASCADE  
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC             

the above is the mysql table schedule The MySQL server is on a different machine. When I run this mysql query on my test machine (so not the same machine as the server)

SELECT a.*, b.* FROM quotes a INNER JOIN quotes b ON a.DBDate = b.DBDate AND a.TickerID=956 and b.TickerID=957 order by a.dbdate asc;

I'll get an output as expected: 2934 rows in set (0.03 sec)

but when I want to get the same result in my C# envirement using DBLinq like this:

var tradeAbleA = (from a in _db.Quotes where a.TickerID == 956 select a);
var tradeAbleB = (from a in _db.Quotes where a.TickerID == 957 select a);
var myDataSet = (from a in tradeAbleA.AsEnumerable() join b in tradeAbleB.AsEnumerable() on a.DbdAte equals b.DbdAte orderby a.DbdAte ascending select new { a, b }).ToList();

it takes over a second to get the list filled. This is way too long. How can I speed this up? (I need it in a list) regards,

Matthijs

Answer 1

Shouldn't your Sql translate to the following linq?

var myDataSet = (from a in _db.Quotes 
    join b in db.Quotes on a.DbdAte equals a.DbdAte 
    where a.TickerID == 956 && b.TickerID == 957 
    orderby a.DbdAte ascending select new { a, b }).ToList();

In your current version, you create the query for a and b seperately, and by calling .AsEnumerable() on them in the 3rd of your linq expressions you force them to be evaluated. You move the results in memory, which then Linq to Objects joins for you (which can be expensive). You then order the remaining items in memory.

The above should allow you to pass all these steps through to the query provider which tend to be much faster.