لیبرتاد لیبرتاد
نظرات مطالب
آموزش مفاهیم Data Warehouse
بسیار عالی 
آیا OLTP و DW می‌توانند بر روی یک سرور باشند مثلا" بر روی یک SQL Server باشند   
‫۱۰ سال و ۱۰ ماه قبل، چهارشنبه ۱۱ دی ۱۳۹۲، ساعت ۱۶:۳۳
وحید نصیری وحید نصیری
مطالب دوره‌ها
پشتیبانی از XML Schema در SQL Server
XML Schema چیست؟

XML Schema معرف ساختار، نوع داده‌ها و المان‌های یک سند XML است. البته باید درنظر داشت که تعریف XML Schema کاملا اختیاری است و اگر تعریف شود مزیت اعتبارسنجی داده‌های در حال ذخیره سازی در بانک اطلاعاتی را به صورت خودکار به همراه خواهد داشت. در این حالت به نوع داده‌ای XML دارای اسکیما، typed XML و به نوع بدون اسکیما، untyped XML گفته می‌شود.
به یک نوع XML، چندین اسکیمای مختلف را می‌توان نسبت داد و به آن XML schema collection نیز می‌گویند.



XML schema collections پیش فرض و سیستمی

تعدادی XML Schema پیش فرض در SQL Server تعریف شده‌اند که به آن‌ها sys schema collections گفته می‌شود.
 Prefix - Namespace
xml = http://www.w3.org/XML/1998/namespace
xs = http://www.w3.org/2001/XMLSchema
xsi = http://www.w3.org/2001/XMLSchema-instance
fn = http://www.w3.org/2004/07/xpath-functions
sqltypes = http://schemas.microsoft.com/sqlserver/2004/sqltypes
xdt = http://www.w3.org/2004/07/xpath-datatypes
(no prefix) = urn:schemas-microsoft-com:xml-sql
(no prefix) = http://schemas.microsoft.com/sqlserver/2004/SOAP
در اینجا پیشوندها و فضاهای نام sys schema collections را ملاحظه می‌کنید. از این اسکیماها برای تعاریف strongly typed امکانات موجود در SQL Server کمک گرفته شده‌است.
اگر علاقمند باشید تا این تعاریف را مشاهده کنید به مسیر Program Files\Microsoft SQL Server\version\Tools\Binn\schemas\sqlserver در جایی که SQL Server نصب شده‌است مراجعه نمائید. برای مثال در مسیر Tools\Binn\schemas\sqlserver\2006\11\events فایل events.xsd قابل مشاهده است و یا در مسیر Tools\Binn\schemas\sqlserver\2004\07 اسکیمای ابزارهای query processor و  show plan قابل بررسی می‌باشد.
مهم‌ترین آن‌ها را در پوشه Tools\Binn\schemas\sqlserver\2004\sqltypes در فایل sqltypes.xsd می‌توانید ملاحظه کنید. اگر به محتوای آن دقت کنید، قسمتی از آن به شرح ذیل است:
  <xsd:simpleType name="char">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="nchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="varchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="nvarchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="text">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="ntext">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
در اینجا نوع‌های توکار char تا ntext به xsd:string نگاشت شده‌اند و برای اعتبارسنجی datetime و نگاشت آن، از الگوی ذیل استفاده می‌شود؛ به همراه حداقل و حداکثر قابل تعریف:
  <xsd:simpleType name="datetime">
    <xsd:restriction base="xsd:dateTime">
      <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]{2}[037])?"/>
      <xsd:maxInclusive value="9999-12-31T23:59:59.997"/>
      <xsd:minInclusive value="1753-01-01T00:00:00.000"/>
    </xsd:restriction>
  </xsd:simpleType>
ادیتور SQL Server managment studio به خوبی، گشودن، ایجاد و یا ویرایش فایل‌هایی با پسوند xsd را پشتیبانی می‌کند.



تعریف XML Schema و استفاده از آن جهت تعریف یک strongly typed XML

XML Schema مورد استفاده در SQL Server حتما باید در بانک اطلاعاتی ذخیره شود و برای خواندن آن، برای مثال از فایل سیستم استفاده نخواهد شد.
CREATE XML SCHEMA COLLECTION invcol AS
'<xs:schema ... targetNamespace="urn:invoices">
...
</xs:schema>
'

CREATE TABLE Invoices(
id int IDENTITY PRIMARY KEY,
invoice XML(invcol)
)
در اینجا نحوه‌ی تعریف کلی یک XML Schema collection و سپس انتساب آن‌را به یک ستون XML ملاحظه می‌کنید. ستون invoice که از نوع XML تعریف شده، ارجاعی را به اسکیمای تعریف شده دارد.
در ادامه نحوه‌ی تعریف یک اسکیمای نمونه قابل مشاهده است:
CREATE XML SCHEMA COLLECTION geocol AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="urn:geo"
           elementFormDefault="qualified"
           xmlns:tns="urn:geo">
  <xs:simpleType name="dim">
    <xs:restriction base="xs:int" />
  </xs:simpleType>
  <xs:complexType name="Point">
    <xs:sequence>
      <xs:element name="X" type="tns:dim"  minOccurs="0" maxOccurs="unbounded" />
      <xs:element name="Y" type="tns:dim"  minOccurs="0" maxOccurs="unbounded" />
    </xs:sequence>
  </xs:complexType>
  <xs:element name="Point" type="tns:Point" />
</xs:schema>'
در این اسکیما، یک نوع ساده به نام dim تعریف شده‌است که محدودیت آن، ورود اعداد صحیح می‌باشد. همچنین امکان تعریف نوع‌های پیچیده نیز در اینجا وجود دارد. برای مثال نوع پچیده Point دارای دو المان X و Y از نوع dim در ادامه تعریف شده‌است. المانی که نهایتا بر این اساس در XML ظاهر خواهد شد توسط xs:element تعریف شده‌است.
اکنون برای آزمایش اسکیمای تعریف شده، جدول geo_tab را به نحو ذیل تعریف می‌کنیم و سپس سعی در insert دو رکورد در آن خواهیم کرد:
 declare @geo_tab table(
 id int identity primary key,
 point xml(content geocol)
)
 
insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>20</Y></Point>')
insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>test</Y></Point>')
در اینجا اگر دقت کنید، برای تعریف نام اسکیمای مورد استفاده، واژه content نیز ذکر شده‌است. Content مقدار پیش فرض است و در آن پذیرش XML Fragments یا محتوای XML ایی با بیش از یک Root element مجاز است. حالت دیگر آن document است که تنها یک Root element را می‌پذیرد.
در این مثال، insert اول با موفقیت انجام خواهد شد؛ اما insert دوم با خطای ذیل متوقف می‌شود:
 XML Validation: Invalid simple type value: 'test'. Location: /*:Point[1]/*:Y[1]
همانطور که ملاحظه می‌کنید، چون در insert دوم، در المان عددی Y، مقدار test وارد شده‌است و تطابقی با اسکیمای تعریف شده ندارد، insert آن مجاز نخواهد بود.



یافتن محل ذخیره سازی اطلاعات اسکیما در SQL Server

اگر علاقمند باشید تا با محل ذخیره سازی اطلاعات اسکیما، نوع‌های تعریف شده و حتی محل استفاده از آن‌ها در بانک‌های اطلاعاتی مختلف موجود آشنا شوید و گزارشی از آن‌ها تهیه کنید، می‌توانید از کوئری‌های ذیل استفاده نمائید:
 select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
select * from sys.xml_schema_elements
select * from sys.xml_schema_attributes
select * from sys.xml_schema_types
select * from sys.column_xml_schema_collection_usages
select * from sys.parameter_xml_schema_collection_usages
باید دقت داشت زمانیکه یک schema در حال استفاده است (یک رکورد ثبت شده مقید به آن تعریف شده باشد)، امکان drop آن نخواهد بود. حتما باید اطلاعات و ستون مرتبط، ارجاعی را به schema نداشته باشند تا بتوان آن schema را حذف کرد.
محتوای اسکیمای ذخیره شده به شکل xsd تعریف شده، ذخیره سازی نمی‌شود. بلکه اطلاعات آن تجزیه شده و سپس در جداول سیستمی SQL Server ذخیره می‌گردند. هدف از اینکار، بالا بردن سرعت اعتبارسنجی typed XMLها است.
بنابراین بدیهی است در این حالت اطلاعاتی مانند commnets موجود در xsd تهیه شده در بانک اطلاعاتی ذخیره نمی‌گردند.
برای بازیابی اطلاعات اسکیمای ذخیره شده می‌توان از متد xml_schema_namespace استفاده کرد:
 declare @x xml
select @x = xml_schema_namespace(N'dbo', N'geocol')
print convert(varchar(max), @x)
برای تعریف و یا تغییر یک XML Schema نیاز به دسترسی مدیریتی یا dbo است (به صورت پیش فرض). همچنین برای استفاده از Schema تعریف شده، کاربر متصل به SQL Server باید دسترسی Execute و References نیز داشته باشد.



نحوه‌ی ویرایش یک schema collection موجود

چند نکته:
- امکان alter یک schema collection وجود دارد.
- می‌توان یک schema جدید را به collection موجود افزود.
- امکان افزودن (و نه تغییر) نوع‌های یک schema موجود، میسر است.
- امکان drop یک اسکیما از collection موجودی وجود ندارد. باید کل collection را drop کرد و سپس آن‌را تعریف نمود.
- جداولی با فیلدهای nvarchar را می‌توان به فیلدهای XML تبدیل کرد و برعکس.
- امکان تغییر یک فیلد XML به حالت untyped و برعکس وجود دارد.

فرض کنید که می‌خواهیم اسکیمای متناظر با یک ستون XML را تغییر دهیم. ابتدا باید آن ستون XML ایی را Alter کرده و قید اسکیمای آن‌را برداریم. سپس باید اسکیمای موجود را drop و مجددا ایجاد کرد. همانطور که پیشتر ذکر شد، اگر اسکیمایی در حال استفاده باشد، قابل drop نیست. در ادامه مجددا باید ستون XML ایی را تغییر داده و اسکیمای آن‌را معرفی کرد.
روش دوم مدیریت این مساله، اجازه دادن به حضور بیش از یک اسکیما در مجموعه است. به عبارتی نگارش‌بندی اسکیما که به نحو ذیل قابل انجام است:
 alter XML SCHEMA COLLECTION geocol add @x
در اینجا به collection موجود، یک اسکیمای جدید (برای مثال نگارش دوم اسکیمای فعلی) اضافه می‌شود. در این حالت geocol، هر دو نوع اسکیمای موجود را پشتیبانی خواهد کرد.



نحوه‌ی import یک فایل xsd و ذخیره آن به صورت اسکیما

اگر بخواهیم یک فایل xsd موجود را به عنوان xsd معرفی کنیم می‌توان از دستورات ذیل کمک گرفت:
 declare @x xml
set @x = (select * from openrowset(bulk 'c:\path\file.xsd', single_blob) as x)
CREATE XML SCHEMA COLLECTION geocol2 AS @x
در اینجا به کمک openrowset فایل xsd موجود، در یک متغیر xml بارگذاری شده و سپس در دستور ایجاد یک اسکیما کالکشن جدید استفاده می‌شود.
از openrowset برای خواندن یک فایل xml موجود، جهت insert محتوای آن در بانک اطلاعاتی نیز می‌توان استفاده کرد.



محدودیت‌های XML Schema در SQL Server

تمام استاندارد XML Schema در SQL Server پشتیبانی نمی‌شود و همچنین این مورد از نگارشی به نگارشی دیگر نیز ممکن است تغییر یافته و بهبود یابد. برای مثال در SQL Server 2005 از xs:any پشتیبانی نمی‌شود اما در SQL Server 2008 این محدودیت برطرف شده‌است. همچنین مواردی مانند xs:include، xs:redefine، xs:notation، xs:key، xs:keyref و xs:unique در SQL Server پشتیبانی نمی‌شوند.



یک نکته‌ی تکمیلی

برنامه‌ای به نام xsd.exe به همراه Visual Studio ارائه می‌شود که قادر است به صورت خودکار از یک فایل XML موجود، XML Schema تولید کند. اطلاعات بیشتر 
‫۱۰ سال و ۹ ماه قبل، جمعه ۲۵ بهمن ۱۳۹۲، ساعت ۰۵:۴۱
وحید نصیری وحید نصیری
مطالب
استفاده از فیلدهای XML در NHibernate

در مورد طراحی یک برنامه "فرم ساز" در مطلب قبلی بحث شد ... حدودا سه سال قبل اینکار را برای شرکتی انجام دادم. یک برنامه درخواست خدمات نوشته شده با ASP.NET که مدیران برنامه می‌توانستند برای آن فرم طراحی کنند؛ فرم درخواست پرینت، درخواست نصب نرم افزار، درخواست وام، درخواست پیک، درخواست آژانس و ... فرم‌هایی که تمامی نداشتند! آن زمان برای حل این مساله از فیلدهای XML استفاده کردم.
فیلدهای XML قابلیت نه چندان جدیدی هستند که از SQL Server 2005 به بعد اضافه شده‌اند. مهم‌ترین مزیت آن‌ها‌ هم امکان ذخیره سازی اطلاعات هر نوع شیء‌ایی به عنوان یک فیلد XML است. یعنی همان زیرساختی که برای ایجاد یک برنامه فرم ساز نیاز است. ذخیره سازی آن هم آداب خاصی را طلب نمی‌کند. به ازای هر فیلد مورد نظر کاربر، یک نود جدید به صورت رشته معمولی باید اضافه شود و نهایتا رشته تولیدی باید ذخیره گردد. از دید ما یک رشته‌ است، از دید SQL Server یک نوع XML واقعی؛ به همراه این مزیت مهم که به سادگی می‌توان با T-SQL/XQuery/XPath از جزئیات اطلاعات این نوع فیلدها کوئری گرفت و سرعت کار هم واقعا بالا است؛ به علاوه بر خلاف مطلب قبلی در مورد dynamic components ، اینبار نیازی نیست تا به ازای هر یک فیلد درخواستی کاربر، واقعا یک فیلد جدید را به جدول خاصی اضافه کرد. داخل این فیلد XML هر نوع ساختار دلخواهی را می‌توان ذخیره کرد. به عبارتی به کمک فیلدهایی از نوع XML می‌توان داخل یک سیستم بانک اطلاعاتی رابطه‌ای، schema-less کار کرد (un-typed XML) و همچنین از این اطلاعات ویژه، کوئری‌های پیچیده هم گرفت.
تا جایی که اطلاع دارم، چند شرکت دیگر هم در ایران دقیقا از همین ایده فیلدهای XML برای ساخت برنامه فرم ساز استفاده کرده‌اند ...؛ البته مطلب جدیدی هم نیست؛ برنامه‌های فرم ساز اوراکل و IBM هم سال‌ها است که از XML برای همین منظور استفاده می‌کنند. مایکروسافت هم به همین دلیل (شاید بتوان گفت مهم‌ترین دلیل وجودی فیلدهای XML در SQL Server)، پشتیبانی توکاری از XML به عمل آورده‌ است.
یا روش دیگری را که برای طراحی سیستم‌های فرم ساز پیشنهاد می‌کنند استفاده از بانک‌های اطلاعاتی مبتنی بر key-value‌ مانند Redis یا RavenDb است؛ یا استفاده از بانک‌های اطلاعاتی schema-less واقعی مانند CouchDb.


خوب ... اکنون سؤال این است که NHibernate برای کار با فیلدهای XML چه تمهیداتی را درنظر گرفته است؟
برای این منظور خاصیتی را که قرار است به یک فیلد از نوع XML نگاشت شود، با نوع XDocument مشخص خواهیم ساخت:
using System.Xml.Linq;

namespace TestModel
{
   public class DynamicTable
   {
       public virtual int Id { get; set; }
       public virtual XDocument Document { get; set; }
   }
}

سپس باید جهت معرفی این نوع ویژه، به صورت صریح از XDocType استفاده کرد؛ یعنی نکته‌ی اصلی، استفاده از CustomType مرتبط است:
using FluentNHibernate.Automapping;
using FluentNHibernate.Automapping.Alterations;
using NHibernate.Type;

namespace TestModel
{
   public class DynamicTableMapping : IAutoMappingOverride<DynamicTable>
   {
       public void Override(AutoMapping<DynamicTable> mapping)
       {
           mapping.Id(x => x.Id);
           mapping.Map(x => x.Document).CustomType<XDocType>();
       }
   }
}

البته لازم به ذکر است که دو نوع NHibernate.Type.XDocType و NHibernate.Type.XmlDocType برای کار با فیلد‌های XML در NHibernate وجود دارند. XDocType برای کار با نوع System.Xml.Linq.XDocument طراحی شده است و XmlDocType مخصوص نگاشت نوع System.Xml.XmlDocument است.

اکنون اگر به کمک کلاس SchemaExport ، اسکریپت تولید جدول متناظر با اطلاعات فوق را ایجاد کنیم به حاصل زیر خواهیم رسید:
   if exists (select * from dbo.sysobjects
                where id = object_id(N'[DynamicTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
        drop table [DynamicTable]

   create table [DynamicTable] (
       Id INT IDENTITY NOT NULL,
      Document XML null,
      primary key (Id)
   )

یک سری اعمال متداول ذخیره سازی اطلاعات و تهیه کوئری نیز در ادامه ذکر شده‌اند:
//insert
object savedId = 0;
using (var session = sessionFactory.OpenSession())
{
   using (var tx = session.BeginTransaction())
   {
        var obj = new DynamicTable
        {
            Document = System.Xml.Linq.XDocument.Parse(
                    @"<Doc><Node1>Text1</Node1><Node2>Text2</Node2></Doc>"
                    )
        };
        savedId = session.Save(obj);
        tx.Commit();
    }
}

//simple query
using (var session = sessionFactory.OpenSession())
{
   using (var tx = session.BeginTransaction())
   {
        var entity = session.Get<DynamicTable>(savedId);
        if (entity != null)
        {
           Console.WriteLine(entity.Document.Root.ToString());
        }

        tx.Commit();
    }
}

//advanced query
using (var session = sessionFactory.OpenSession())
{
   using (var tx = session.BeginTransaction())
   {
       var list = session.CreateSQLQuery("select [Document].value('(//Doc/Node1)[1]','nvarchar(255)') from [DynamicTable] where id=:p0")
                               .SetParameter("p0", savedId)
                               .List();

        if (list != null)
        {
             Console.WriteLine(list[0]);
        }

        tx.Commit();
    }
}

و در پایان بدیهی است که جهت کار با امکانات پیشرفته‌تر موجود در SQL Server در مورد فیلد‌های XML ( برای نمونه: + و +) باید مثلا رویه ذخیره شده تهیه کرد (یا مستقیما از متد CreateSQLQuery همانند مثال فوق کمک گرفت) و آن‌را در NHibernate مورد استفاده قرار داد. البته به این صورت کار شما محدود به SQL Server خواهد شد و باید در نظر داشت که در کل تعداد کمی بانک اطلاعاتی وجود دارند که نوع‌های XML را به صورت توکار پشتیبانی می‌کنند.

‫۱۳ سال و ۵ ماه قبل، دوشنبه ۳۰ خرداد ۱۳۹۰، ساعت ۱۸:۰۳
وحید نصیری وحید نصیری
مطالب
بررسی کارآیی کوئری‌ها در SQL Server - قسمت ششم - بررسی عملگرهای دسترسی به داده‌ها در یک Query Plan
پس از آشنایی مقدماتی با نحوه‌ی خواندن یک Query Plan، اکنون نوبت به بررسی عملگرهایی است که در آن مشاهده می‌شوند و همچنین تغییرات در کوئری‌ها چگونه بر روی آن‌ها تاثیر گذاشته و آن‌ها را تغییر می‌دهند و این تغییرات چه تاثیری را بر روی کارآیی خواهند داشت.


عملگرهای Scans و Seeks

در حالت کلی می‌توان دو نوع جدول بدون و با ایندکس را درنظر گرفت. در حالت جداول بدون ایندکس، برای جستجوی اطلاعات نیاز به Table Scan وجود دارد و برعکس آن شامل یک Clustered index scan خواهد بود. گاهی از اوقات Clustered index scanها بهترین روش دریافت اطلاعات هستند و گاهی از اوقات خیر و نیاز به بررسی بیشتری دارند. بنابراین قانون کلی، حذف آن‌ها به محض مشاهده، نیست.
نوع دیگر عملگرهای دسترسی به داده‌ها، Seeks هستند که شامل Clustered index seeks و Non-clustered index seeks می‌شوند. در بسیاری از موارد عنوان می‌شود که Seeks کارآیی بهتری را به همراه دارند. هرچند این مورد نیاز به بررسی بیشتری دارد که در ادامه با مثال‌هایی آن‌ها را مرور خواهیم کرد.


بررسی عملگر Table scan در یک Query Plan

در ادامه تعدادی از عملگرهای مرتبط با data access را از لحاظ نحوه‌ی انتخاب و تغییر آن‌ها توسط بهینه ساز کوئری‌های SQL Server بررسی می‌کنیم. برای این منظور ابتدا در management studio از منوی Query، گزینه‌ی Include actual execution plan را انتخاب می‌کنیم. سپس کوئری‌های زیر را اجرا می‌کنیم:
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

SELECT *
INTO [Sales].[Copy_Orders]
FROM [Sales].[Orders];
GO

SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO
در اینجا در ابتدا، تمام رکوردهای جدول [Sales].[Orders]، به جدول [Sales].[Copy_Orders] کپی می‌شوند. سپس یک کوئری را بر روی این جدول کپی، اجرا کرده‌ایم.


همانطور که مشاهده می‌کنید، برای برآورده کردن قسمت where این کوئری، یک Table Scan صورت گرفته‌است؛ چون این جدول کپی، به همراه هیچ ایندکسی نیست. به همین جهت برای یافتن رکوردهای مدنظر، راه دیگری بجز اسکن کل جدول بانک اطلاعاتی وجود ندارد که بسیار ناکارآمد است.
همچنین اگر به برگه‌ی messages دقت کنیم، با توجه به روشن بودن STATISTICS IO، میزان logical reads نیز قابل مشاهده‌است:
(33035 rows affected)
Table 'Copy_Orders'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
به علاوه اجرای آن نیز کمی بیشتر از نیم ثانیه، طول کشیده‌است:
SQL Server Execution Times:
CPU time = 79 ms,  elapsed time = 762 ms.


بررسی عملگر Index Seek در یک Query Plan

اکنون سؤال اینجا است که آیا می‌توان این وضعیت را بهبود بخشید؟
بله. برای این منظور یک NONCLUSTERED INDEX را بر روی جدول کپی، ایجاد می‌کنیم؛ به نحوی که CustomerID لحاظ شده‌ی در قسمت where کوئری را پوشش دهد:
CREATE NONCLUSTERED INDEX [IX_Copy_Orders_CustomerID]
ON [Sales].[Copy_Orders] (
[CustomerID]
)
INCLUDE (
[OrderID], [OrderDate]
);
GO
چون مطابق کوئری، [OrderID] و [OrderDate] در قسمت where ذکر نشده‌اند، در اینجا INCLUDE شده‌اند.

در ادامه مجددا همان کوئری را اجرا می‌کنیم:
SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Copy_Orders]
WHERE [CustomerID] > 550;
GO
که سبب تولید کوئری پلن زیر می‌شود:


اینبار عملگر Table Scan قبلی به یک عملگر Index Seek بر روی NONCLUSTERED INDEX تعریف شده، تغییر کرده‌است و اگر به آمار I/O آن دقت کنیم، logical reads 106 قابل مشاهده‌است که بهبود قابل ملاحظه‌ای است نسبت به عدد 689 قبلی.


بررسی عملگر Clustered index scan در یک Query Plan

در ادامه همین کوئری را بر روی جدول [Sales].[Orders] اصلی اجرا می‌کنیم:
SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;
GO
که به صورت پیش‌فرض شامل این ایندکس‌ها است:


اجرای کوئری فوق، چنین کوئری پلنی را تولید می‌کند:


جدول [Sales].[Orders]، یک CLUSTERED INDEX را بر روی [OrderID] دارد و یک NONCLUSTERED INDEX را بر روی [CustomerID].
در کوئری پلن تولید شده، یک Clustered index scan مشاهده می‌شود. علت اینجا است که هرچند در جدول [Sales].[Orders] یک NONCLUSTERED INDEX بر روی  [CustomerID] تعریف شده‌است:
CREATE NONCLUSTERED INDEX [FK_Sales_Orders_CustomerID] ON [Sales].[Orders]
(
[CustomerID] ASC
)
اما قسمت INCLUDE ایندکس قبلی را که تعریف کردیم، ندارد و به همراه [CustomerID] و [OrderDate] نیست. به همین جهت اینبار logical reads 692 است.

بنابراین وجود عملگر Clustered index scan در یک کوئری پلن، یعنی نیاز به خواندن و اسکن کل جدول وجود دارد. برای اثبات آن، همین کوئری قبلی را که بر روی [Sales].[Orders] انجام دادیم، اینبار بدون قسمت where آن اجرا کنید. یعنی کوئری بر روی کل جدول انجام شود:
SELECT
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]
سپس به برگه‌ی messages مراجعه کرده و عدد logical reads آن‌را مشاهده کنید. این عدد دقیقا با عدد logical reads کوئری where دار، یکی است؛ که بیانگر اسکن کامل جدول در حالت Clustered index scan است.

سؤال: آیا Clustered index scan همواره کل یک جدول را اسکن می‌کند؟
پاسخ: خیر. اگر یک کوئری برای مثال دارای top/min/max باشد، کل جدول اسکن نخواهد شد:
SELECT TOP 10
    [CustomerID],
    [OrderID],
    [OrderDate]
FROM [Sales].[Orders]
WHERE [CustomerID] > 550;
تفاوت این کوئری با کوئری‌های قبلی، در داشتن یک top 10 است. اگر آن‌را اجرا کنیم، به کوئری پلن زیر خواهیم رسید:


هرچند در اینجا هم یک Clustered index scan صورت گرفته، اما اگر به برگه‌ی messages آن مراجعه کنیم، آمار I/O آن بیانگر تنها logical reads 5 است که معادل اسکن کل جدول نیست:
(10 rows affected)
Table 'Orders'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


مقایسه‌ی عملگرهای Index Scan و Index Seek

ابتدا کوئری زیر را اجرا می‌کنیم:
SELECT
    [CustomerID],
    [OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 30000;
این کوئری با کوئری قبلی از لحاظ قسمت select اندکی متفاوت بوده و در آن OrderDate حذف شده‌است. در قسمت where نیز کوئری بر روی OrderID صورت گرفته‌است.
در این جدول ایندکسی بر روی CustomerID وجود دارد و همچنین کلید اصلی جدول، OrderID است.

پس از اجرای این کوئری، به کوئری پلن زیر خواهیم رسید:


که بیانگر یک Index Scan است و نکته‌ی جالب آن، استفاده‌ی از ایندکس FK_Sales_Orders_CustomerID می‌باشد (نام این شیء، ذیل آیکن عملگر، مشخص است). یعنی SQL Server در اینجا از یک non-clustered index تعریف شده‌ی بر روی CustomerID استفاده کرده‌است.
اکنون اگر OrderID را تغییر دهیم چه اتفاقی رخ می‌دهد؟
SELECT
    [CustomerID],
    [OrderID]
FROM [Sales].[Orders]
WHERE [OrderID] > 60000;
اینبار به یک clustered index seek رسیدیم که بر روی کلید اصلی جدول یا همان PK_Sales_Orders که ذیل عملگر مشخص شده، رخ داده‌است:


در این مثال با دو ورودی مختلف، دو کوئری پلن مختلف تولید شده‌است؛ که مرتبط است با میزان اطلاعاتی که قرار است بازگشت داده شود.

اگر این دو کوئری را با هم اجرا کنیم (در طی یک batch)، به پلن مقایسه‌ای زیر خواهیم رسید که در آن هزینه‌ی Index Scan بیشتر است از clustered index seek:


به همراه آمار CPU و I/O ای به صورت زیر که اولی مرتبط است با index scan و دومی با clustered index seek:
(43595 rows affected)
Table 'Orders'. Scan count 1, logical reads 191, physical reads 1, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
CPU time = 31 ms,  elapsed time = 754 ms.


(13595 rows affected)
Table 'Orders'. Scan count 1, logical reads 131, physical reads 0, read-ahead reads 127, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
CPU time = 16 ms,  elapsed time = 276 ms.
به همین جهت است که عنوان می‌شود، scanها خوب نیستند و seekها بهترند.
‫۵ سال و ۳ ماه قبل، پنجشنبه ۱۳ تیر ۱۳۹۸، ساعت ۱۸:۱۰
امین پارسا امین پارسا
اشتراک‌ها
NewSQL چیست؟

Some NewSQL solutions

NewSQL-db-logosVoltDB  (in-memory database) : http://voltdb.com

ClustrixDB (distributed peer-to-peer SQL database) : http://www.clustrix.com

NuoDB (distributed database management) : http://www.nuodb.com

GenieDB (based on MySQL) : http://www.geniedb.com

ScaleArc (SQL traffic manager ) :  http://www.scalearc.com

ScaleDB (MySQL scalability) : http://scaledb.com

Tokutek/TokuDB (MySQL/MariaDB scallability): http://www.tokutek.com

JustoneDB (built upon PostgreSQL) : http://www.justonedb.com

MemSQL (in-memory database) : http://www.memsql.com   

NewSQL چیست؟
‫۶ سال و ۶ ماه قبل، دوشنبه ۳ اردیبهشت ۱۳۹۷، ساعت ۲۲:۵۶
فرهاد فرهمندخواه فرهاد فرهمندخواه
مطالب
انتخاب Sub Query درون پرانتزها به کمک [+Ctrl+Shift
بوسیله [+Ctrl+Shift شما می‌توانید از دورن Query‌های تودرتو، Sub Query مورد نظر را انتخاب نمایید، فقط کافیست، کرسر ماوس را ابتدا یا انتهای پرانتز قرار دهید.

 


امکان فوق از Sql Server 2005 به بعد گذاشته شده است.
منبع:
http://blog.sqlauthority.com
‫۱۲ سال و ۴ ماه قبل، دوشنبه ۲۶ تیر ۱۳۹۱، ساعت ۲۰:۱۸