dcsimg
January 20, 2017
Hot Topics:

Writing Database Objects in CLR: Advanced Scenarios

  • September 21, 2005
  • By Sahil Malik
  • Send Email »
  • More Articles »

Sending Results Back in One Shot

An alternative to the Sieve of Eratosthenes for calculating prime numbers is to simply find them the brute force way: by finding divisors. You can do this easily using the divisors TVF written in my previous article. That means if the following query gives the output "1", the number is prime:

Select count(*) from divisor(@Input)

So, as you find prime numbers, you can keep inserting them into a table and return them in one shot at the end. But this means you need a table in which to store the prime numbers. So, set that up first by using the following script:

Create Table PrimeNumber
(
   PrimeNumber Int
)

With that set up, you can write the stored procedure that will populate the above table as it finds primary numbers and reads from the above table to return the results. The following is the code for this stored procedure:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void FindPrimes2(int input)
{
   using (SqlConnection contextConnection = 
      new SqlConnection("context connection=true"))
   {
      try
      {
         contextConnection.Open();
         // Setup commands
         SqlCommand divisorCountCmd = 
            contextConnection.CreateCommand();
         divisorCountCmd.CommandText = 
            "Select count(*) from divisor(@Input)";
         divisorCountCmd.Parameters.AddWithValue(
            "@Input", 2);// set dummy value
         SqlCommand insertPrimeCommand = 
            contextConnection.CreateCommand();
         insertPrimeCommand.CommandText = 
            "Insert into PrimeNumber Values (@Input)";
         insertPrimeCommand.Parameters.AddWithValue(
            "@Input", 2); // dummy value
         for (int i = 2; i < input; i++)
         {
            divisorCountCmd.Parameters[0].Value = i;
            int numDivisors = (int) divisorCountCmd.ExecuteScalar();
            if (numDivisors == 1) // Its a prime number
            {
               insertPrimeCommand.Parameters[0].Value = i;
               if (insertPrimeCommand.ExecuteNonQuery() != 1)
                  throw new Exception(
                     "Cannot insert into PrimeNumber");
            }
         }
         // By now we should have filtered all the numbers out.
         SqlCommand fetchPrimes = contextConnection.CreateCommand();
         fetchPrimes.CommandText = 
            "Select PrimeNumber from PrimeNumber";
         SqlDataReader primeRdr = 
            fetchPrimes.ExecuteReader(CommandBehavior.CloseConnection);
         SqlContext.Pipe.Send(primeRdr);
         primeRdr.Close();
      }
      catch (Exception)
      {
         throw; // no exception logging infrastructure for demo :-/
      }
      finally
      {
         contextConnection.Close();
      }
   }
}

Notice something strange? Observe the connection string for the connection being used:

using (SqlConnection contextConnection =
   new SqlConnection("context connection=true"))

Well, you need a connection because you have a few commands (divisorCountCmd, insertPrimeCmd, and fetchPrimes) that actually work with underlying database objects. Also, you need to specify a connection string to get a valid and usable connection object. So, you should specify a connection string that looks like this:

"Server=Win2k3-smalik; Database=Test; Integrated Security=SSPI"

Although that would certainly work, it sounds incredibly wasteful to go over the entire networking stack, re-authenticate, and then create a brand new connection—when you are already on a connection. So, wouldn't it be nice if you could simply get a hold of the existing connection? That is exactly what the connection string "context connection = true" would do. It hands you back the connection you are already on.

Once you do have a hold of the context connection, the rest is plain ADO.NET code. Put simply, if the number of divisors of a number (other than the number itself) is 1, the number indeed is a prime number. When a prime number is found, the code simply inserts it into the PrimeNumber table using the insertPrimeCmd SqlCommand.

Finally, it uses a simple SqlDataReader to stream the results back in one shot using the following line of code:

SqlContext.Pipe.Send(primeRdr);

Also, because you are trying to ensure that the number of divisors other than the number itself is only 1, don't try to divide by every single number out there. Instead, try to focus on only the prime numbers less than the input number. This can easily be done by modifying the TVF code to the following:

[Microsoft.SqlServer.Server.SqlFunction(
   FillRowMethodName = "RowFiller", DataAccess = DataAccessKind.Read,
   TableDefinition = "Divisor int")]
public static IEnumerable Divisor(Int32 input)
{
   ArrayList divisors = new ArrayList();
   using (SqlConnection contextConnection = 
      new SqlConnection("context connection = true"))
   {
      try
      {
         contextConnection.Open();
         SqlCommand fetchPrimes = contextConnection.CreateCommand();
         fetchPrimes.CommandText = 
            "Select PrimeNumber from PrimeNumber";
         SqlDataReader primeRdr = 
            fetchPrimes.ExecuteReader(CommandBehavior.CloseConnection);
         if (primeRdr.HasRows)
         {
            while (primeRdr.Read())
            {
               int i = primeRdr.GetInt32(0);
               if (input % i == 0)
               {
                  divisors.Add(i);
               }
            }
         }
         primeRdr.Close();
      }
      finally
      {
         contextConnection.Close();
      }
      return divisors;
   }
}
public static void RowFiller(object row, out int Divisor)
{
   Divisor = (int)row ;
}

Now, if you rerun the stored procedure and check its performance degradation over an increasing set of numbers, you'll see the results in Figure 2.



Click here for a larger image.

Figure 2. Divisors TVF Stored Procedure Performance Results

As you can see, the downgrade in performance is far more linear this time. In fact, because the bottleneck is the PrimeNumber table, you can use all sorts of conventional database tricks to speed up inserts into that table.

Note: Writing to a table can never be as fast as working completely in memory.

Another interesting point is that the context connection was opened in two places: one in the stored procedure and second in the TVF. This doesn't mean you can have as many instances of context connections open at any given time. In fact, you can have only one context connection concurrently open inside one stack frame that was opened within the same stack frame. Thus, because the stored procedure calling the T-SQL, which in turn calls the TVF, nests such stack frames or data activation records, you can concurrently maintain two open context connections—even though they are really the same connection.

Your other option, of course, is to simply use a non-context connection. It will be more expensive than context connections, but you have the full power of connection pooling right inside SQL Server 2005. After all, it is the same CLR inside or outside SQL Server 2005.

Some Lingering Issues

Of course, the remaining issue with this code is a PrimeNumber table that needs to be cleaned between runs of the stored procedure. Wouldn't it be nice if somehow you could just roll back the entire transaction? But, what if your operation required some work being done in T-SQL, some in SQLCLR (or multiple SQLCLR objects), and some in your business layer or data layer using SqlCommand running under a SqlTransaction? What if you were not using a context connection but a connection to a separate SQL Server 2005 database? What if it was a SQL Server 2000 database, or even an Oracle database being used through SQLCLR? Or what if it wasn't a database at all, what if it was some non-database operation that needed to be wrapped up inside a transaction?

I will cover these possibilities and more in my next article, which will discuss the integration of System.Transactions and SQLCLR.

Download the Code

To download the accompanying source code for the demonstration, click here.

About the Author

Sahil Malik has worked for a number of top-notch clients in Microsoft technologies ranging from DOS to .NET. He is the author of Pro ADO.NET 2.0 and co-author of Pro ADO.NET with VB.NET 1.1. Sahil is currently also working on a multimedia series on ADO.NET 2.0 for Keystone Learning. For his community involvement, contributions, and speaking, he has also been awarded the Microsoft MVP award.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel