Optimization of SQL Select for enumerators

Question

How can this query be optimized for enumerators:

SELECT * FROM Customers

Table Customers
customerId int - has index on it
customerName, etc

SqlReader that returns a set customers will be read on-demand in an enumerator fashion. While it can return huge datasets that can be read/consumed slowly in a foreach loop, every other query on the same table will encounter a lot of contentions. How can this be optimized/avoided? Cursors or selecting into temp tables?

Here is a code example that will cause a lot of contentions (I profiled it and the numbers look bad indeed):

public void DumpCustomers()
{
    Thread thread = new Thread(AccessCustomers);
    thread.Start();

    // GetCustomers returns enumerator with yield return; big # of customers 
    foreach (Customer customer in GetCustomers())  
    {
       Console.WriteLine(customer.CustomerName);
       System.Threading.Thread.Sleep(200);
    }
    thread.Abort();
}

public void AccessCustomers()
{
   while (true)
   {
      Console.WriteLine(GetCustomer("Zoidberg").CustomerName);
      Thread.Sleep(100);
   }
}

P.S. I will also need to optimize this in MySQL.

Answer 1

1) Do you need the '*' cant you specify the columns.

2) Use multi-part names dbo.tablename.fieldname - this speeds it up

3) try a locking hint with (nolock) or (readpast)

4) Whats the IO profile? Does SQL have to pull the data from disk every time it runs?

5) Do you find one of the cores on your server max out while the other one is idle?

6) Cache it! Until you know there has been a change- then reload it.

I've run out of ideas..