محمد رجبی محمد رجبی
مطالب
بررسی دو نکته (ترفند) کاربردی در SQL Server

1- اندازه گیری تعداد Transaction‌ها در واحد زمان روی یک Database خاص در SQL Server 

جهت بدست آوردن تعداد Transaction‌ها در واحد زمان( Transactions Per Second ) روی یک Database خاص در یک سیستم عملیاتی، جهت ارتقاء سخت افزاری ، تست فشار و ... می‌توانید از یک DMV با نام sys.dm_os_performance_counters به طریق زیر استفاده نمائید: 
declare @cntr_value bigint

Select @cntr_value=cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

/* ایجاد یک تاخیر مثلاً یک ثانیه */
waitfor delay '00:00:01'

Select cntr_value -@cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'
View معرفی شده تمامی شمارنده‌های عملکردی را برای یک Instance خاص شامل می‌شود، ستون instance_name  برابر نام بانک اطلاعاتی مورد نظر می‌باشد.

2- sys.sp_MSforeachtable 

از رویه‌های ذخیره شده UnDocumented در SQL Server می‌باشد و این قابلیت را دارا است که برای هر یک از جداول موجود در  یک بانک اطلاعاتی، یک رویه‌ای را اجرا کند. برای مثال با استفاده از دستور زیر، می‌توانید تعداد سطرها، اندازه‌ی داده‌ها و ایندکس‌های یک جدول را بدست آورید

EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';
به عنوان یک مثال کاربردی، با اجرای دستور زیر می‌توان جداول بانک اطلاعاتی مورد نظرتان را از لحاظ معیارهایی که پیشتر ذکر آن رفت، مورد بررسی قرار دهید.
 USE [AdventureWorksDW2008R2]
GO

CREATE TABLE #TableSpaceUsed(
[name] [nvarchar](120) NULL,
[rows] [nvarchar](120) NULL,
[reserved] [nvarchar](120) NULL,
[data] [nvarchar](120) NULL,
[index_size] [nvarchar](120) NULL,
[unused] [nvarchar](120) NULL
) ON [PRIMARY]

Insert Into #TableSpaceUsed
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

Select * from #TableSpaceUsed
Order by CAST([rows] as int) desc

Drop table #TableSpaceUsed
خروجی مثال فوق به شکل زیر است.


‫۱۰ سال و ۶ ماه قبل، چهارشنبه ۳ اردیبهشت ۱۳۹۳، ساعت ۲۰:۵۵
وحید نصیری وحید نصیری
نظرات مطالب
استفاده‌ی گسترده از DateTimeOffset در NET Core.
یک نکته‌ی تکمیلی: بانک اطلاعاتی SQLite از نوع داده‌ی DateTimeOffset پشتیبانی نمی‌کند

SQLite به صورت توکار از هیچ نوع داده‌ای خاصی برای کار با زمان یا تاریخ پشتیبانی نمی‌کند؛ اما متدهایی را برای کار با آن‌ها به همراه دارد و در این بین، EF Core فقط نوع داده‌ای DateTime را برای آن به خوبی پشتیبانی می‌کند. در سایر حالات استفاده‌ی از DateTimeOffset، پیام عدم امکان ترجمه‌ی این کوئری LINQ را به SQL، مشاهده خواهید کرد. به همین جهت برای کار بدون دردسر با زمان در SQLite و EF Core، بهتر است از همان DateTime استفاده کرد.
این روش‌ها را نیز مدنظر داشته باشید:
- در این بانک اطلاعاتی برای مثال می‌توان تاریخ را به صورت زیر ذخیره و بازیابی کرد:
((DateTimeOffset)value).Ticks.ToString()

- و یا می‌توان برای آن تبدیلگر نوشت:
namespace MySQLite
{
    public class SQLiteDbContext : DbContext
    {
        public SQLiteDbContext(DbContextOptions options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            addDateTimeOffsetConverter(builder);
        }

        private static void addDateTimeOffsetConverter(ModelBuilder builder)
        {
            // SQLite does not support DateTimeOffset
            foreach (var property in builder.Model.GetEntityTypes()
                                                  .SelectMany(t => t.GetProperties())
                                                  .Where(p => p.ClrType == typeof(DateTimeOffset)))
            {
                property.SetValueConverter(
                     new ValueConverter<DateTimeOffset, DateTime>(
                          convertToProviderExpression: dateTimeOffset => dateTimeOffset.UtcDateTime,
                          convertFromProviderExpression: dateTime => new DateTimeOffset(dateTime)
                    ));
            }

            foreach (var property in builder.Model.GetEntityTypes()
                                                  .SelectMany(t => t.GetProperties())
                                                  .Where(p => p.ClrType == typeof(DateTimeOffset?)))
            {
                property.SetValueConverter(
                     new ValueConverter<DateTimeOffset?, DateTime>(
                          convertToProviderExpression: dateTimeOffset => dateTimeOffset.Value.UtcDateTime,
                          convertFromProviderExpression: dateTime => new DateTimeOffset(dateTime)
                    ));
            }
        }
    }
}

// یک نمونه‌ی دیگر
private static readonly ValueConverter<object, string> DateTimeOffsetToStringConverter =
    new ValueConverter<object, string>(
              v => ((DateTimeOffset)v).ToString(@"yyyy\-MM\-dd HH\:mm\:ss.FFFFFFFzzz", CultureInfo.InvariantCulture),
              v => DateTimeOffset.Parse(v, CultureInfo.InvariantCulture));
‫۵ سال قبل، شنبه ۳۰ شهریور ۱۳۹۸، ساعت ۱۴:۱۰
وحید نصیری وحید نصیری
بازخوردهای دوره
تهیه کوئری بر روی ایندکس‌های Full Text Search
چنین قابلیتی به صورت توکار در SQL Server وجود ندارد. البته امکان «تعیین وزن و اهمیت کلمات در حال جستجو» با استفاده از واژه کلیدی ISABOUT وجود دارد و یا کوئری گرفتن بر روی rankها هم چنین قابلیتی را میسر می‌کند.
‫۷ سال و ۹ ماه قبل، دوشنبه ۲۷ دی ۱۳۹۵، ساعت ۲۱:۰۱
وحید نصیری وحید نصیری
نظرات مطالب
ساخت یک Form Generator ساده در MVC
امکان کوئری گرفتن پیچیده‌ی از آن مشکلی ندارد؛ ولی باید دوره‌ی XML را یکبار مرور کرده باشید. البته فیلدهای JSON هم به SQL Server 2016 اضافه شده‌اند که بحث دیگری است و می‌تواند مدنظر باشد.
‫۸ سال و ۴ ماه قبل، چهارشنبه ۹ تیر ۱۳۹۵، ساعت ۲۰:۲۴
علی یگانه مقدم علی یگانه مقدم
مطالب
MongoDb در سی شارپ (بخش نهم)
سال‌های مدیدی است که به طراحی پایگاه‌های sql پرداخته و تجاربی آموخته‌ایم. کتاب‌ها و مقالات زیادی در اینباره منتشر شده‌اند. از این‌رو در نحوه طراحی دیتابیس‌های رابطه‌ای اطلاعات زیادی کسب و مسائل زیادی را از این راه حل نموده‌ایم؛ ولی با ورود دیتابیس‌های NoSql و تنوع زیاد آن‌ها و روش‌های متنوعی که هر کدام از آن‌ها به طور جداگانه دارند باعث شد تجربه سال‌ها فعالیت و مدل ذهنی که داشتیم به یکباره تغییر کند و گاها بیشتر باعث گیج شدن می‌گردد. از این‌رو در این مقاله سعی داریم تکنیک‌ها مدل سازی اسناد را در دیتابیس مونگو، بررسی کنیم و مزایا و معایب هر یک را برشماریم.
در دیتابیس‌های قدیم، تمرکز بر روی نوشتن بود تا با کمترین افزونگی و تکرار و رعایت اصول ACID، اطلاعات را ذخیره نماییم. ولی در حال حاضر به دلیل دسترسی به فضاهای ذخیره سازی بزرگتر و همچنین افزایش ترافیک شبکه در واکشی دیتاها، قضیه عکس شده و تمرکز دیتابیس‌های NoSql بر روی خواندن میباشد. پس باید فاکتورهای مدل سازی طوری باشد تا خواندن در سریعترین حد امکان قرار بگیرد. البته مواردی چون حذف و به روزرسانی هم باید در این مورد بررسی شوند.
ارتباط اسناد با یکدیگر:
ارتباط اسناد از دو طریق امکان پذیر است:
  • حالت ارجاع  : شماره سند یا Object Id را شامل شده و در صورتیکه به اطلاعاتی نیاز داشتید، باید اطلاعات آن را در یک درخواست جداگانه واکشی نمایید. چون مونگو شامل جوین نبوده و جوین‌ها باید در سطح اپلیکیشن مدیریت شوند.
{
fname:'ali',
lname:'yeganeh',
accounts:[454354353,3455435]
}

  • حالت جاسازی سند (یا اسناد تو در تو) Embed :  در این حالت سند مورد نظر اطلاعات سند دیگری را در درون خود نگه میدارد. در این حالت به هیچ جوینی نیازی نیست و اطلاعات وابسته، به همراه خود سند اصلی واکشی می‌شوند. این نکته باید مورد توجه قرار بگیرد که مونگو یک دیتابیس غیر اتمیک هست و در صورتیکه اصل دیتا تغییر کند، تغییر یا به روزرسانی در سندهای Embed انجام نخواهد شد و در صورت نیاز باید خودتان به طور دستی آن را کنترل نمایید.
{
fname:'ali',
lname:'yeganeh',
accounts:[
{
  username:"ali",
  password:"123"
},
{
  username:"reza",
  password:"456"
}
]
}
مدل هایی با ارتباط یک به یک : 
در این نوع مدل سازی، دو سند داریم که یکی از آن‌ها Principle و دیگری Dependent محسوب می‌شود. برای ذخیره سازی آن‌ها عموما از حالت Embed استفاده میشود. در این حالت چون ارتباط بین دو سند به صورت یک به یک میباشد، در واقع این امکان وجود دارد تا سند مادری به طور جداگانه وجود نداشته باشد و همان سند به صورت Embed ذخیره میشود. در این حالت مشکلی از لحاظ اتمیک نبودن مونگو پیش نمیاید و  ویرایش راحت‌تری خواهد داشت.
مدل‌هایی با ارتباط یک به چند:
این اسناد را می‌توان به دو حالت بالا بر حسب نیازمندی سیستم ذخیره کرد. فرض کنید مثال زیر را که در سایت مونگو هم عنوان شده‌‌است، داریم:
book
{
     name:'Scarlet Letter",
     Language:"English",
     Pages:124,
...
}

publisher
{
   name : "Orielly",
   ...

}
در این حالت هر کتاب باید ارتباطی با ناشر خود داشته باشد. در صورتیکه به صورت Embed داخل سند قرار بگیرد و هر کتابی شامل اطلاعات ناشر خود باشد، نکات زیر مورد بررسی قرار میگیرند:
book
{
     name:'Scarlet Letter",
     Language:"English",
     Pages:124,
...,
publisher:
{
   name : "Orielly",
   ...

}
}

نکات مثبت:
  1. در این حالت در صورتیکه واکشی هر کتاب به همراه اطلاعات ناشر را نیاز داشته باشیم و یا پرس وجوهای ترکیبی نیاز باشد، در سریعترین زمان ممکن واکشی انجام خواهد شد.
  2. درج و مدیریت آن راحت‌تر خواهد بود.
نکات منفی:
  1. در صورتیکه اطلاعات ناشر نیاز به تغییرات اساسی داشته باشد و باید در تمامی سندها اصلاح گردد، باید تمامی اسناد مربوط به اطلاعات کتاب به روزرسانی شوند که هزینه سنگین‌تری را خواهد داشت.
  2. دیتای تکراری زیادی ذخیره خواهد شد و در نتیجه حافظه بیشتری را میطلبد.
  3. در صورتیکه تنها به اطلاعات ناشر نیاز باشد و اطلاعات ناشر در سند دیگری وجود نداشته باشد و فقط در سند کتاب وجود داشته باشد، واکشی آن هزینه سنگین‌تری را خواهد طلبید. به همین جهت توصیه میشود در صورتیکه دیتای شما می‌تواند به صورت یک موجودیت مستقل هم عمل کند، اطلاعات آن در سند دیگری که من به آن سند اصلی میگویم ذخیره شوند تا نمونه‌ها از روی آخرین ویرایش آن ساخته شوند و موقعی‌که تنها به واکشی آن اطلاعات نیاز است، همان‌ها بیرون کشیده شوند.
در روشی دیگری میتوان ارجاعی از ناشر را به شکل زیر در کتاب نگهداری کرد:
book
{
     name:'Scarlet Letter",
     Language:"English",
     Pages:124,
...,
publisher:1212121
}
نکات مثبت:
  1. عدم وجود تکرار اطلاعات
  2. چون تنها یک سند برای ویرایش وجود دارد، نیازی به اصلاح اسناد توکار نیست و ویرایش، هزینه کمتری خواهد داشت.
نکات منفی:
  1. عدم وجود جوین: در صورتیکه نیاز به جوین بزرگی باشد، این نوع جوین باید در سطح برنامه شما انجام شود و هزینه بر خواهد بود.
نگهداری نام کتاب‌ها در ناشر
انعطاف مونگو برای ایجاد مدل، گزینه‌های زیادی را پیش رو میگذارد و واقعا مدلسازی را بیشتر از قبل، چالش برانگیز میکند. در حالت دیگر میتوان اطلاعات کتاب را به صورت ارجاع، در سند ناشر نگهداری کرد. به عنوان مثال زمانیکه نیاز داریم کتب منتشرشده یک ناشر را ببینیم، شاید این گزینه بهتر باشد. البته در این حالت باید بتوان ارجاعات به کتاب را در تعداد محدودی نگهداری کرد؛ در غیر این صورت با تعداد زیادی ارجاع که شاید هیچگاه نیازی هم به آن‌ها نیست، خواهیم رسید و در این حالت شاید ارجاع به ناشر در سند کتاب بسیار بهتر به نظر برسد. البته میتوان در این حالت ناشر تنها به تعداد معدودی از آخرین کتابهایش دسترسی داشته باشد تا کاربر بتواند آخرین کتاب‌های منتشر شده‌ی ناشر را ببیند. 
حال با اطلاعات بالا چگونه مدلسازی کنیم؟
همانطور که گفتیم ابتدا تمرکز شما باید برای خواندن اطلاعات باشد و سپس معیارهایی چون به روزرسانی نیز بررسی گردند. به عنوان نمونه اطلاعات یک پست در وبلاگ را در نظر بگیرید. این سند شامل سندهای توکاری چون دسته بندی، اطلاعات نویسنده، معیارهایی چون امتیازدهی و بخش نظرات میباشد. در این حالت چون همه عناصر قرار است با یکدیگر بیرون کشیده شوند و در واقع تنها با یک سند سروکار داریم، کار بسیار سریعتر و راحت‌تر است. پس این ساختار گزینه مناسبی برای نمایش است:
Post
{
title:"C#",
body:"About C#",
tags:['C#','.Net','microsoft'],
Categories:[{name:'Programming'}],
votes:[{rate:3,user:42342},{rate:5,user:423445},...],
comments:[
{
text:"my comment1",
time:"10/2/1396",...},
...

]
}

حال این تصور را داشته باشید که ما تنها یک پست را نشان نمیدهیم و بلکه پست‌ها به صورت یک لیست قرار است نمایش داده شوند و با گزینه‌ی مشاهده‌ی مطلب می‌توانیم یک پست را به صورت کامل ببینیم. در این صورت همه اطلاعات همانند قبل هستند، بجز بخش نظرات که دیگر در این حالت کاربردی ندارد و دیتای اضافی است که به ناچار باید خوانده شود. پس در این حالت میگوییم این مدل برای خواندن مناسب نیست، چون باید تمام نظرات اسنادی که در لیست قرار دارند هم خوانده شوند. پس باید بخش نظرات را از سند پست وبلاگ جدا کنیم.
{
POST:45453,
count:35,
comments:[...]
}
سپس میگوییم هر سند نهایتا 16 مگابایت اطلاعات را نگهداری میکند و هم اینکه تعداد نظرات ممکن است بسیار زیاد باشند. پس هر سند را به تعدادی نظر محدود میکنیم به این حالت میگویند داریم یک Bucket میسازیم و مثلا هر باکت را به 100 کامنت محدود میکنیم. تا به الان وضعیت طراحی بهتری نسبت به قبل پیدا کردیم:
{
post:345345,
capacity:100,
count:35,
bucket:2,
comments:[...]
}
در این حالت حتی میتوانیم کامنتها را صفحه بندی کرده و در هر صفحه یک باکت را بخوانیم. برای نمایش این دو مورد آخری برای جداسازی دیتا بسیار خوب است. حتی میتوان یک کامنت را به همراه پاسخ‌های آن که به صورت درخت واره قرار گرفته اند نیز در یک سند جداگانه ذخیره کرد.
نکاتی که باید در حین طراحی در نظر بگیرید:
  1. همیشه به این نکته توجه داشته باشید که نباید بگذارید تعداد آرایه‌های یک سند خیلی بزرگ شوند. در غیر اینصورت کارآیی مونگو به خصوص در حین ویرایش سند پایین خواهد آمد. در حین ویرایش، اگر سندی از اندازه‌ی خود بزرگتر نشود، مشکلی پیش نمیاید ولی اگر فضایی بیش از آنچه که  قبلا داشته به آن اضافه شود، سند نیاز به جابجایی و گسترش فضا خواهد داشت. در این حالت باید مونگو سند را به جای دیگری که فضای کافی برای آن وجود دارد، انتقال بدهد و میزان Disk Fragment به طبع بالا خواهد رفت. همچنین اندیس‌های آرایه‌ای هم با جابجا شدن دیتا نیاز به، به روزرسانی خواهند داشت و زمانی هم صرف به روزرسانی اندیس‌ها خواهد شد.
  2. مدیر محصول مونگو اظهار نظر صریحی در این مورد نکرد‌ه‌است، ولی به نظر می‌رسد نوع فرمت BSON از یک اسکن خطی در حافظه استفاده میکند و زمان بیشتری صرف پیدا کردن المان‌های انتهایی در آرایه خواهد شد؛ پس بیشتر عملیات در این نوع سند، با کندی مواجه خواهند شد. با توجه به کامنت‌هایی که در سایت‌ها و شبکه‌های اجتماعی یافت شده‌است، آرایه ای با بیش از صدهزار آیتم ساده میتواند آسیب زا باشد؛ به همین دلیل توصیه میشود که اگر بیش از صدهزار آیتم نیاز است، از همان حالت Bucket استفاده شود.
  3. استفاده از اندیس‌ها هم سابقه‌ی دیرینه‌ای داشته و سعی کنید کوئری هایی بزنید که بر اساس اندیس‌های تعریف شده باشند تا واکشی دیتا سریعتر شود. پس نحوه کوئری نویسی و انتخاب فیلدی که اندیس میشود بسیار مهم است.
  4. استفاده از Projection تاثیری بر خواندن اسناد ندارد و هر سند به طور کامل واکشی می‌شود. projection تنها در بار‌ه‌ی ترافیک یا انتقال حجم کمتری از اطلاعات به سمت کلاینت تاثیرگذار میباشد. پس استفاده از projection بجای جدا سازی اسناد را دنبال نکنید.
‫۶ سال و ۵ ماه قبل، یکشنبه ۳۰ اردیبهشت ۱۳۹۷، ساعت ۲۰:۵۵
وحید نصیری وحید نصیری
نظرات مطالب
استفاده از چندین بانک اطلاعاتی به صورت همزمان در EF Code First
امکان ساخت رشته‌ی اتصالی، به همراه ذکر صریح Provider مورد استفاده هم وجود دارد. چند مثال:
public static string CreateConnectionStringForSQLCe(string dbPath = @"|DataDirectory|\NerdDinners.sdf")
{
   SqlCeConnectionStringBuilder sqlConnection = new SqlCeConnectionStringBuilder();
   sqlConnection.Password = "9023fase93";
   sqlConnection.DataSource = dbPath;

   EntityConnectionStringBuilder connection = new EntityConnectionStringBuilder();
   connection.Metadata = @"res://*/NerdDinnersModel.csdl|res://*/NerdDinnersModel.ssdl|res://*/NerdDinnersModel.msl";
   connection.Provider = "System.Data.SqlServerCe.3.5";
   connection.ProviderConnectionString = sqlConnection.ToString();

   return connection.ToString();
}


public static string CreateConnectionStringForSQLServer()
{
   //Build an SQL connection string
   SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
   {
      DataSource = "MyPC", // Server name
      InitialCatalog = "db1",  //Database
      UserID = "user1",         //Username
      Password = "mypassword",  //Password
   };
 
   //Build an Entity Framework connection string
   EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder
   {
      Provider = "System.Data.SqlClient",
      Metadata =   "res://*/testModel.csdl|res://*/testModel.ssdl|res://*/testModel.msl",
      ProviderConnectionString = sqlString.ToString()
   };
   return entityString.ConnectionString;
}
این روش با EF code first هم کار می‌کند و در سازنده‌ی دوم کلاس Context که connectionString را می‌پذیرد، قابل استفاده‌است.
‫۹ سال و ۸ ماه قبل، دوشنبه ۱۱ اسفند ۱۳۹۳، ساعت ۱۶:۱۸
وحید نصیری وحید نصیری
مطالب
لینک‌های هفته‌ی آخر دی

وبلاگ‌ها ، سایت‌ها و مقالات ایرانی (داخل و خارج از ایران)


Visual Studio


ASP. Net



طراحی و توسعه وب



PHP


اس‌کیوال سرور


سی شارپ


عمومی دات نت


ویندوز


مسایل اجتماعی و انسانی برنامه نویسی


متفرقه


‫۱۵ سال و ۱۰ ماه قبل، پنجشنبه ۲۶ دی ۱۳۸۷، ساعت ۰۴:۰۱