کامپایل کوئری در EF Core قسمت دوم
SELECT * FROM tb1 WHERE x1 = '12';
SELECT * FROM tb1 WHERE x1 = '12';
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using Microsoft.SqlServer.TransactSql.ScriptDom; namespace SqlDomAnalyzer.Core { public static class PrettyPrintTSql { public static string FormatTSql(string tSql) { IList<ParseError> errors; TSqlScript sqlFragment; using (var reader = new StringReader(tSql)) { var parser = new TSql120Parser(initialQuotedIdentifiers: true); sqlFragment = (TSqlScript)parser.Parse(reader, out errors); } if (errors != null && errors.Any()) { var sb = new StringBuilder(); foreach (var error in errors) sb.AppendLine(error.Message); throw new InvalidOperationException(sb.ToString()); } var sql110ScriptGenerator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql120 }); string finalScript; sql110ScriptGenerator.GenerateScript(sqlFragment, out finalScript); return finalScript; } } }
نکتهی جالب دیگری که در اینجا وجود دارد، تهیهی یک خروجی همواره یک شکل است. برای نمونه سه عبارت SQL زیر را در نظر بگیرید:
SELECT * from tb1 WHERE x1 = '12'; SELECT * from tb1 where x1 = '12'; select * from tb1 WHERE x1 = '12';
در هر سه حالت یا هر حالت قابل تصور دیگری، خروجی SQL فرمت شدهی حاصل یک چنین شکلی را دارد:
SELECT * FROM tb1 WHERE x1 = '12';
موارد کاربرد آن؟
علاوه بر نمایش زیبای SQL فرمت نشده، احتمالا برنامههای Profiler ایی را دیدهاید که عنوان میکنند قادرند عبارات SQL همانند را تشخیص دهند (جهت یافتن Lazy loading اشتباه). یک چنین خروجی یکسانی، قابلیت تهیه Hash عبارات SQL دریافتی را میسر میکند؛ چون دیگر اینبار مهم نیست که اجزای تشکیل دهندهی یک عبارت SQL با حروف بزرگ هستند یا کوچک و فاصلهی بین آنها چقدر است و آیا در این بین خطوط جدیدی نیز وجود دارند و امثال آن. خروجی نهایی نرمال شدهی توسط Sql120ScriptGenerator همواره یک شکل است. از این دو قابلیت در برنامهی DNTProfiler استفاده شدهاست.
طراحی روابط و ارجاعات در RavenDB
در اینجا برای طراحی حالت بلاگهای مورد علاقه یک شخص در RavenDB فقط کافی است از مفهوم Includes آن استفاده کنید (نمونه آن «Includeهای یک به چند» در بحث). داخل کلاس User، یک آرایه شبیه به SupplierIds (مثال زده شده) به نام FavoriteBlogIds خواهید داشت. بارگذاری و گزارشگیری از آن برای نمایش لیست این بلاگها و سپس مطالب آنها، مانند مثالهای Include و Load ایی است که ارائه شد.
بنابراین در اینجا به چیزی مانند دو جدول مجزای کاربران و جدول ذخیره سازی لیست بلاگهای محبوب آنها نیازی نیست. لیست و آرایه Idهای بلاگهای مورد علاقهی یک کاربر، داخل سند JSON همان کاربر قرار میگیرد.
private static void seedDb(ApplicationDbContext context) { if (!context.Chapters.Any()) { var user1 = context.Users.Add(new User { Name = "Test User" }); context.Chapters.Add(new Chapter { Title = "Learn SQlite FTS5", Text = "This tutorial teaches you how to perform full-text search in SQLite using FTS5", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "Advanced SQlite Full-text Search", Text = "Show you some advanced techniques in SQLite full-text searching", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "SQLite Tutorial", Text = "Help you learn SQLite quickly and effectively", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "Handle markup in text", Text = "<p>Isn't this <font face=\"Comic Sans\">funny</font>?", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "آزمایش متن فارسی", Text = "برای نمونه تهیه شدهاست", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "Exclude test 1", Text = "in the years 2018-2019 something happened.", User = user1.Entity }); context.Chapters.Add(new Chapter { Title = "Exclude test 2", Text = "It was 2018 and then it was 2019", User = user1.Entity }); context.SaveChanges(); } }
ثبت اطلاعات فوق، چنین رکوردهایی را در جدول Chapters به وجود میآورد که شامل اطلاعات یونیکد، HTML ای و غیره است:
اجرای اولین کوئری بر روی جدول مجازی Chapters_FTS به صورت مستقیم
کوئریهای Full-text در SQLite، چنین شکل کلی را دارند و توسط تابع match انجام میشوند:
select * from Chapters_FTS where Chapters_FTS match "fts5"
همانطور که مشاهده میکنید در اینجا تنها دو ستونی که ایندکس شدهاند، در خروجی نهایی ظاهر میشوند؛ اما این جدول به همراه ستونهای مخفی توکار دیگری نیز هست:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5"
- Rowid با توجه به تعریفی که در قسمت قبل انجام دادیم:
CREATE VIRTUAL TABLE "Chapters_FTS" USING fts5("Text", "Title", content="Chapters", content_rowid="Id")
- تمام جداول مجازی FTS، به همراه ستون مخفی rank نیز هستند که میزان نزدیک بودن خروجی حاصل را به کوئری درخواستی مشخص میکنند. این عدد توسط تابعی به نام bm25 تهیه میشود. اگر کوئری FTS به همراه قسمت where نباشد، مقدار rank همواره نال خواهد بود. اما اگر قسمت where به همراه match قید شود، مقدار rank، مقدار از پیش محاسبه شدهی تابع توکار bm25 است. به همین جهت کار با این مقدار از پیش محاسبه شده، سریعتر از فراخوانی مستقیم متد bm25 است. برای مثال دو کوئری زیر اساسا یکی هستند؛ اما دومی سریعتر است:
select * from Chapters_FTS where Chapters_FTS match "fts5" ORDER BY bm25(fts); select * from Chapters_FTS where Chapters_FTS match "fts5" ORDER BY rank;
یک نکته: کوئری FTS فوق بر روی هر دو ستون title و text اجرا میشود (و یا هر ستون موجود دیگری که پیشتر ایندکس شده باشد).
اجرای اولین کوئری بر روی جدول مجازی Chapters_FTS توسط EF Core
پس از آشنایی مقدماتی با کوئری نویسی FTS در SQLite، بر انجام یک چنین کوئری در EF Core میتوان به صورت زیر عمل کرد:
- ابتدا باید یک موجودیت بدون کلید را مطابق ستونهای مخفی و ایندکس شدهی بازگشتی تهیه کنیم:
namespace EFCoreSQLiteFTS.Entities { public class ChapterFTS { public int RowId { get; set; } public decimal? Rank { get; set; } public string Title { get; set; } public string Text { get; set; } } }
- سپس نیاز است این موجودیت بدون کلید را به EF معرفی کنیم:
namespace EFCoreSQLiteFTS.DataLayer { public class ApplicationDbContext : DbContext { //... protected override void OnModelCreating(ModelBuilder builder) { base.OnModelCreating(builder); builder.Entity<ChapterFTS>().HasNoKey().ToView(null); } //... } }
- و در آخر روش کوئری گرفتن از جدول مجازی FTS در EF Core به صورت زیر میباشد که توسط متد FromSqlRaw به صورت پارامتری (مقاوم در برابر حملات تزریق اسکیوال)، قابل انجام است:
const string ftsSql = "SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH {0}"; foreach (var chapter in context.Set<ChapterFTS>().FromSqlRaw(ftsSql, "fts5")) { Console.WriteLine($"Title: {chapter.Title}"); Console.WriteLine($"Text: {chapter.Text}"); }
بررسی قابلیتهای ویژهی کوئریهای FTS در SQLite
اکنون که با روش کلی کوئری گرفتن از جدول مجازی FTS آشنا شدیم، نکات ویژهی آنرا بررسی میکنیم و در اینجا بیشتر پارامتر ذکر شدهی پس از عملگر match تغییر خواهد کرد و مابقی قسمتهای آن ثابت و مانند قبل هستند.
بجای عملگر match میتوان از = نیز استفاده کرد
دو کوئری زیر دقیقا به یک معنا هستند:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5"; SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS = "fts5";
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5" ORDER by rank;
جستجوهایی به همراه واژههایی در کنار هم
از دیدگاه FTS، دو کوئری زیر که در قسمت match آنها، واژهها با فاصله در کنار هم قرار گرفتهاند، یکی هستند:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn SQLite" ORDER by rank; SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn + SQLite" ORDER by rank;
علت اینجا است که یک full-text search بر اساس ایندکس شدن واژهها تولید میشود و هر کدام از این واژهها به یک توکن نگاشت خواهند شد. به همین جهت است که در اینجا تفاوتی بین + و فاصله در عبارت جستجو شده وجود ندارد. در این حالت اگر در یکی از ستونهای ایندکس شده، واژهی learn و یا واژهی SQLite بکار رفته باشد، در خروجی نهایی لیست خواهد شد.
امکان جستجو بر اساس پیشوندها
میتوان با استفاده از *، تمام توکنهای ایندکس شده و شروع شدهی با واژهی مشخصی را جستجو کرد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search*" ORDER by rank;
امکان استفاده از عملگرهای بولی NOT، AND و OR
اگر learn text را جستجو کنیم:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn text" ORDER by rank;
رکوردی با ID مساوی 1 بازگشت داده میشود. اما اگر نیاز باشد رکوردی بازگشت داده شود که حاوی learn باشد، اما text خیر، میتوان از عملگر NOT استفاده کرد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn NOT text" ORDER by rank;
که اینبار رکوردی با ID مساوی 3 را بازگشت دادهاست.
نکتهی مهم: عملگرهای بولی FTS مانند AND، OR، NOT و غیره باید با حروف بزرگ قید شوند.
در ادامه مثال دیگری از ترکیب عملگرهای بولی را مشاهده میکنید:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search AND sqlite OR help" ORDER by rank;
که تقدم و تاخر این عملگرها را میتوان توسط پرانتزها به صورت صریحی نیز مشخص کرد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search AND (sqlite OR help)" ORDER by rank;
امکان ذکر صریح ستونهای مدنظر در کوئری
همانطور که عنوان شد، حالت پیشفرض جستجوهای تمام متنی، جستجوی واژهی مدنظر در تمام ستونهای ایندکس شدهاست؛ اما شاید این مورد مدنظر شما نباشد. به همین منظور میتوان ابتدا نام ستون مدنظر را ذکر کرد و پس از آن یک : را قرار داد تا فقط جستجو بر روی آن ستون خاص صورت گیرد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "text:some AND title:sqlite" ORDER by rank;
امکان ترکیب نام ستونها به صورت {col2 col1 col3} نیز وجود دارد.
نکتهی مهم! در جستجوهای FTS در SQLite، ذکر - به معنای قید صریح نام یک ستون خاص است (و یا لیست ستونهایی به صورت {col2 col1 col3}-) که قرار نیست چیزی با آن(ها) انطباق داده شود (- شبیه به عملگر NOT عمل میکند؛ اینبار در مورد ستونها) و این مورد عموما تازهکاران را به اشتباه میاندازد. برای مثال در ابتدای بحث، دو رکورد را که دارای text ای مساوی عبارات زیر هستند، ثبت کردیم:
"in the years 2018-2019 something happened" "It was 2018 and then it was 2019"
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "2018-2019" ORDER by rank;
Execution finished with errors. Result: no such column: 2019
و یا میتوان عبارت جستجو شده را بین "" قرار داد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH '"2018-2019"' ORDER by rank;
و یا حتی میتوان '"2018 2019"' را نیز جستجو کرد که نتیجهی مشابهی را ارائه میدهد.
امکان جستجوی بر روی عبارات یونیکد
FTS5 و آخرین نگارش SQLite، به همراه tokenizer مخصوص یونیکد نیز هست و با اینگونه جستجوهای تمام متنی، مشکلی ندارد:
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "آزمایش" ORDER by rank;
توابع کمکی FTS در SQLite برای متمایز سازی عبارات یافت شدهی در متن
فرض کنید میخواهیم واژهی fts5 را جستجو کرده و همچنین در خروجی نهایی، هرجائیکه fts5 قرار دارد، آنرا به صورت bold نمایش دهیم. برای اینکار، تابع توکار highlight قابل استفادهاست. اما اگر در این بین خواستیم فقط قسمت کوتاهی از متن مورد نظر را که به جستجوی ما نزدیک است نمایش دهیم، میتوان از متد توکار snippet استفاده کرد:
SELECT rowid, highlight(Chapters_FTS, title, '<b>', '</b>') as title, snippet(Chapters_FTS, text, '<b>', '</b>', '...', 64) as text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5" ORDER BY rank
نکتهی مهم: چون بر اساس نکات قسمت قبل، متنی که به Chapters_FTS ارسال میشود، نرمال سازی شدهاست، متدهای فوق کارآیی خودشان را از دست میدهند. برای مثال اگر در کوئری فوق، واژهی funny را که به یک رکورد HTML ای اشاره میکند، جستجو کنیم، خروجی زیر را دریافت خواهیم کرد:
خروجی نهایی، چون به جدول اصلی chapters متصل است، اصل متن را بازگشت میدهد، اما چون اطلاعاتی را که به Chapters_FTS ارسال کردهایم، فاقد تگهای HTML هستند، تا خروجی دقیقی حاصل شود، متدهای highlight و snippet دیگر قادر به علامتگذاری خروجی نهایی نبوده و اینکار را باید خودمان به صورت دستی در سمت کلاینت انجام دهیم.
عموما اولین پاسخی که به این سؤال داده میشود این است که این نوع کوئریها اطلاعات زیادی را باز میگردانند و در نتیجه ترافیک شبکه بیجهت افزایش خواهد یافت. اما اگر طراحی دیتابیس صحیح بوده و اصول نرمال سازی در آن پیاده سازی شده باشد، این پاسخ آنچنان صادق نخواهد بود (زیرا جداول اینگونه دیتابیسها از تعداد فیلدهای بسیاری تشکیل نخواهند شد). برای مثال به نتیجه اجرای کوئریهای زیر بر روی دیتابیس AdventureWorks با 89 هزار رکورد، دقت بفرمائید:
SELECT * FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
مهمترین دلیلی که اینجا باید به آن دقت داشت، تفاوت چشمگیر execution plan این دو کوئری (Ctrl-L) و بحث index coverage است. اس کیوال سرور برای اجرای بهینه کوئریها از ایندکسهای موجود استفاده خواهد کرد. اگر ایندکس تعریف شده از تمامی فیلدهای درخواستی شما تشکیل شده باشد، دیگر حتی به سراغ جدول هم نخواهد رفت (به این مفهوم، پوشش ایندکسی گفته میشود).
برای تولید تصویر فوق، کلیدهای Ctrl+L را در management studio فشار دهید.
این دیتابیس را از آدرس زیر میتوانید دریافت کنید:
http://www.codeplex.com/MSFTDBProdSamples
کوئری اول از مزایای پوشش ایندکسی برخودار نخواهد بود (از روش جستجوی Clustered Index استفاده میکند) و در حالت دوم از Index Seek استفاده میگردد. حالت Index Seek یکصد بار بهینهتر از استفاده از Clustered Index عمل میکند زیرا در حالت کوئری اول باید تمامی رکوردهای جدول بررسی شوند (این عدد از مقایسه نتایج execution plan بدست آمده است).
تنها در صورتیکه بر روی تمامی فیلدهای جدول ایندکس تعریف کرده باشید (که اصلا توصیه نمیشود)، کوئری اول توسط ایندکسها پوشش داده شده و سریع اجرا خواهد شد.
بنابراین اگر از کندی اجرای کوئریها با تعداد رکورد بالا شکایت دارید بهتر است نگاهی به نحوه تعریف آنها داشته باشید و تنها فیلدهایی را در کوئری تعریف کنید که به آنها نیاز دارید. در این حالت از مزایای پوشش ایندکسی برخودار شده ، کوئریهای سریعتری را خواهید داشت و همچنین در این حالت میزان مصرف CPU و حافظه نیز بر روی سرور کمتر خواهد بود.
همچنین در حالت کوئریهایی از نوع دوم ذکر شده، موتور بهینه ساز اس کیوال سرور پیشنهادات بهتری را برای ایجاد ایندکسهای جدید و گوشزد نمودن کمبود آنها با ارائه included columns مناسب، ارائه میدهد.
بعلاوه مشخص ساختن تعداد دقیق فیلدهای مورد نیاز، نگهداری برنامه را سادهتر ساخته و فیلدهای اضافه شده آتی سبب تغییر رفتار کوئریها برنامه نخواهند شد و استفاده نکردن از آن نشانه این است که هیچ برآوردی از ابعاد واقعی کار در دست نیست.
مآخذ:
Speed Up Your Site! 8 ASP.NET Performance Tips
The real reason SELECT * queries are bad: index coverage
ساختار گزارش خاص
بررسی Semantic Search و FTS Table-valued functions
Select Name , c.[rank] From CONTAINSTABLE (Users , Name, '"Ali*" AND NOT "Ali Reza"', 10)
Employee ID: {Orders.EmployeeID} --- GLine: {GroupLine}
در فرم ظاهر شده مقادیر را به صورت زیر وارد نمایید:
Name: TotalPrice Alias: TotalPrice Type: decimal Value: Order_Details.UnitPrice * Order_Details.Quantity
ایجاد گزارش به صورت Master-Detail:
برای ایجاد چنین گزارشی نیاز به ارتباط بین جدولها میباشد. با توجه به نحوه ایجاد Connection برای این مثال، روابط بین جدولها انتقال داده نشده است ولی شما میتوانید رابطه بین جدولها را اضافه نمایید حتی اگر این رابطه در منبع اطلاعات وجود نداشته باشد. برای این مثال نیاز به دو رابطه بین جدول Orders Detail و جدولهای Orders, Products میباشد. برای انجام این کار کافیست در پنل Dictionary بر روی جدول Orders Detail رایت کلیک کنید و گزینه New Relation را انتخاب نماید. همانند تصاویر زیر مقادیر را ست نمایید.
حال بر روی صفحه طراحی بعد از بند DataBand1 به ترتیب بندهای Header و Data و Footer را اضافه نمایید. در بند HeaderBand2 چهار کامپوننت Text قرار دهید و به ترتیب از سمت چپ مقادیر زیر را در خصوصیت Text کامپوننتها قرار دهید.
ProductName UnitPrice Quantity TotalPrice
{Order_Details.Products.ProductName} {Order_Details.UnitPrice} {Order_Details.Quantity} {Order_Details.TotalPrice}
{Sum(DataBand2,Order_Details.TotalPrice)}
حال میتوانید خروجی گزارش Master-Detail را از سربرگ Preview مشاهده نمایید. در صورتی که همانند تصویر بالا گزارش را طراحی کرده باشید در خروجی گزارش فاصلهای بین سطرها ایجاد شده است که علت آن ارتفاع کمتر کامپوننتهای Text نسبت به بندهای خود میباشد. برای رفع این مشکل، شما سه راه حل دارید.
الف: یکسان سازی ارتفاع کاپوننتها با بند دربرگیرنده آنها
ب: ست کردن خصوصیت Can Shrink بند دربرگیرنده کامپوننتها به مقدار true
ج: ست کردن خصوصیت Grow to Height کامپوننتهای Text به مقدار true
در این مثال ما از روش دوم استفاده میکنیم و خصوصیت Can Shrink بندهای HeaderBand2 و DataBand2 را به مقدار true ست میکنیم.
کامپوننتهای Panel و Clone :
Panel: به شما امکان میدهد تا کامپوننتها و بندها را دربر بگیرد و در واقع گروهی از کامپوننتها را ایجاد خواهد کرد. پنل میتواند مستقل بر روی صفحه طراحی قرار گیرد و یا در یک بند.
Clone: به شما امکان کپی کردن یک بخش از گزارش را میدهد که البته آن بخش فقط میتواند از نوع Panel باشد.
این دو کامپوننت یکی از عوامل قدرت این گزارشساز میباشد. برای شروع یک Page (صفحه طراحی) دیگر به گزارش اضافه کنید. میتوانید با رایت کلیک بر روی نوار سربرگهای محیط طراحی گزینه New Page را انتخاب نمایید. بر روی Page2 یک کامپوننت Panel قرار دهید، سپس از پنل Dictionary جدول Countries را دراگ کرده و در Panel1 رها کنید. در فرم Data تمامی فیلدها و بند Header را انتخاب نمایید، سپس یک کامپوننت Clone به صفحه طراحی اضافه کنید بلافاصله فرم Select Container ظاهر میشود، Panel1 را انتخاب کنید. حال شما میتوانید خروجی گزارش را مشاهده کنید، خروجی Page2 بعد از خروجی Page1 ظاهر خواهد شد.