Kamil Kliczbor @ asptip.net

26Jun/090

Cache na przykładzie .NET Pet Shop 4

Ciekawe jest, że jest to przykład dobrego stylu programowania. Między innymi zastosowano tam użycia cache w oparciu o Hashtable. Przeprowadzone testy wykazują, że użycie cache nie do końca przynosi spodziewane rezultaty:

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace ConsoleApplication1

{

   class Program

   {

      static void Main(string[] args) {

 

         int[] lengths = PrepareParamsArray();

 

         DateTime start = DateTime.Now;

         for (var t1 = 0; t1 < lengths.Length; t1++) {

            GetWithCache(lengths[t1]);

         }

         DateTime stop = DateTime.Now;

         TimeSpan test1 = stop - start;

         Console.WriteLine("Test with cache time: {0} ms", test1.TotalMilliseconds);

 

         start = DateTime.Now;

         for (var t2 = 0; t2 < lengths.Length; t2++) {

            GetWithoutCache(lengths[t2]);

         }

         stop = DateTime.Now;

         TimeSpan test2 = stop - start;

         Console.WriteLine("Test without cache time: {0} ms", test2.TotalMilliseconds);

 

         start = DateTime.Now;

         for (var t3 = 0; t3 < lengths.Length; t3++) {

            GetWithCacheLonger(lengths[t3]);

         }

         stop = DateTime.Now;

         TimeSpan test3 = stop - start;

         Console.WriteLine("Test with cache long time: {0} ms", test3.TotalMilliseconds);

 

         start = DateTime.Now;

         for (var t2 = 0; t2 < lengths.Length; t2++) {

            GetWithoutCacheLonger(lengths[t2]);

         }

         stop = DateTime.Now;

         TimeSpan test4 = stop - start;

         Console.WriteLine("Test without cache long time: {0} ms", test4.TotalMilliseconds);

 

         Console.ReadKey();

      }

 

      private static SqlParameter[] GetWithCache(int i) {

         SqlParameter[] parms = TestSqlParams.GetParameters("TEST2" + i);

 

         if (parms == null) {

            parms = Get2Params(i);

            TestSqlParams.StoreParameters("TEST2" + i, parms);

         }

 

         return parms;

      }

 

      private static SqlParameter[] GetWithoutCache(int i) {

         return Get2Params(i);

      }

      

      private static SqlParameter[] GetWithCacheLonger(int i) {

         SqlParameter[] parms = TestSqlParams.GetParameters("TEST10" + i);

 

         if (parms == null) {

            parms = Get10Params(i);

            TestSqlParams.StoreParameters("TEST10" + i, parms);

         }

 

         return parms;

      }

 

      private static SqlParameter[] GetWithoutCacheLonger(int i) {

         return Get10Params(i);

      }

 

      private static SqlParameter[] Get2Params(int i) {

         var parms = new[] {

                    new SqlParameter("@Quantity" + i, SqlDbType.Int),

                    new SqlParameter("@ItemId" + i, SqlDbType.VarChar, 10)};

 

         return parms;

      }

 

      private static SqlParameter[] Get10Params(int i) {

         var parms = new[] {

                    new SqlParameter("@Quantity" + i, SqlDbType.Int),

                    new SqlParameter("@ItemId" + i, SqlDbType.VarChar, 10),

               new SqlParameter("@Desc" + i, SqlDbType.Char, 120),

               new SqlParameter("@Amount" + i, SqlDbType.Int),

               new SqlParameter("@Price" + i, SqlDbType.Money),

               new SqlParameter("@ShipNo" + i, SqlDbType.VarChar, 40),

               new SqlParameter("@ShipPlace" + i, SqlDbType.NChar, 30),

               new SqlParameter("@Available" + i, SqlDbType.Bit),

               new SqlParameter("@CanSellPlace" + i, SqlDbType.NText, 1024),

               new SqlParameter("@Guid" + i, SqlDbType.UniqueIdentifier)

         };

 

         return parms;

      }

 

      public static int[] PrepareParamsArray() {

         const int arrLength = 1000000;

         var parmsLen = new int[arrLength];

 

         for (var i = 0; i < arrLength; i++) {

            parmsLen[i] = i % 45;

         }

         return parmsLen;

      }

 

   }

}

Oraz:

using System;

using System.Collections;

using System.Data.SqlClient;

 

namespace ConsoleApplication1

{

   class TestSqlParams

   {

      private static Hashtable cache = Hashtable.Synchronized(new Hashtable());

 

      public static void StoreParameters(string key, params SqlParameter[] parameters)

      {

         cache[key] = parameters;

      }

      

      public static SqlParameter[] GetParameters(string key)

      {

         var cached = (SqlParameter[]) cache[key];

         if (cached == null) return null;

 

         var cloned = new SqlParameter[cached.Length];

 

         for(int index = 0, cacheLen = cached.Length; index < cacheLen; index++)

         {

            cloned[index] = ((SqlParameter) ((ICloneable) cached[index]).Clone());

         }

 

         return cloned;

      }

   }

}

Wyniki są zadziwniające:
Test with cache time: 3432 ms
Test without cache time: 1070 ms
Test with cache long time: 16982 ms
Test without cache long time: 6349 ms

Filed under: Cache, Wydajność No Comments
24Jun/090

Restore bazy "z palca"

Jeżeli masz problem z restorem bazy, to mozna spróbować zrobić to ręcznie (bez kreatora). Opis procedury znajduje się tutaj.

W moim przypadku wygląda to tak:

RESTORE FILELISTONLY
FROM DISK = 'D:\Projects\myDB_20090624\myDB_20090624.bak'
GO

ALTER DATABASE myDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE myDB
FROM DISK = 'D:\Projects\myDB_20090624\myDB_20090624.bak'
WITH MOVE 'myDB_Log' TO 'D:\Projects\DatabaseFiles\myDB.LDF',
MOVE 'myDB_Primary' TO 'D:\Projects\DatabaseFiles\myDB.MDF',
MOVE 'myDB_System' TO 'D:\Projects\DatabaseFiles\myDB_system.NDF',
MOVE 'myDB_Index' TO 'D:\Projects\DatabaseFiles\myDB_index.NDF'

ALTER DATABASE myDB SET MULTI_USER
GO
Filed under: MS SQL Server, SQL No Comments