عثمان رحیمی عثمان رحیمی
مطالب
آشنایی با SplitQuery در EF Core 5x
در دیتابیس‌های رابطه‌ای، داده‌ها(رکوردها)ی مرتبط، با استفاده از Join بدست آورده می‌شوند و بعضا نیاز هست برای رسیدن به یک داده‌ی مورد نیاز، باید چندین Join بین جداول مختلف به کار برده شود. در Entity Framework ، زمانیکه قصد بدست آوردن داده‌های مرتبط را داریم، از Include  استفاده می‌کنیم که در نهایت منجر به همان left Join می‌شود.
برای درک بهتر و توضیح راحت‌تر، فرض کنید بر روی دیتابیس سایت جاری، قصد داریم لیست هر کاربر را به همراه مقالاتی که در سایت منتشر کرده‌است، بدست بیاوریم. برای اینکار قطعه کد زیر را خواهیم داشت :
  var users = context.Users.Include(x => x.Articles).ToList();
دستور فوق، منجر به تولید T-SQL زیر خواهد شد:
SELECT [u].[Id], [u].[FirstName], [u].[LastName], [a].[Id], [a].[Approved], [a].[AuthorId], [a].[Body], [a].[PubDate], [a].[Subject]
FROM [Users] AS [u]
LEFT JOIN [Articles] AS [a] ON [u].[Id] = [a].[AuthorId]
ORDER BY [u].[Id], [a].[Id]
اجرای این دستور، خروجی زیر را به همراه دارد:

شکل یک

همانطور که در عکس فوق مشاهده میکنید، کاربر با شناسه‌ی 1، ده مقاله را منتشر کرده‌است که به ازای تعداد مقالات، سه فیلد شناسه کاربر، نام و نام خانوادگی، تکرار شده‌است و همین اتفاق برای کاربر با شناسه‌ی 2 هم تکرار شده‌است. قطعا در اکثر نرم افزارها، نیاز به چنین کوئری‌ها و داده‌هایی زیاد است و جلوگیری از این تکرار داده‌ها، می‌تواند بر روی کارایی نرم افزار تاثیر گذار باشد.


Cartesian explosion

اجرای یک Join بین جداول با رابطه‌ی one to many، منجر به تکرار ستون‌های جدول طرف one، به تعداد رکورد‌های مرتبط می‌شود. این اتفاق باعث هدر رفت منابع و همچنین کند شدن اجرای کوئری خواهد شد که این مشکل تحت عنوان Cartesian explosion problem شناخته می‌شود.


از نسخه EF Core5.0، امکانی اضافه شده‌است که کمک می‌کند این مشکل را برطرف کنیم و سرعت اجرای کوئری‌ها سریع‌تر شود. Entity Framework به صورت پیش فرض، کوئری‌ها را در قالب یک دستور (یک رفت و برگشت) انجام میدهد، اما میتوان این رفتار را با استفاده از قابلیت SplitQuery تغییر داد.


متد ()SplitQuery

با استفاده از این متد، به Entity Framework الزام میکنیم که بجای استفاده از Join در یک کوئری، کوئری‌های جداگانه‌ای را بر روی دیتابیس اجرا کند. برای کوئری اول که در بالا نوشتیم، به صورت زیر می‌توانیم SplitQuery را اعمال کنیم:

 var users = context.Users.AsSplitQuery().Include(x => x.Articles).ToList();

کوئری حاصل از کد فوق به صورت زیر می‌باشد:

-- First Part  
 SELECT [u].[Id], [u].[FirstName], [u].[LastName]
      FROM [Users] AS [u]
      ORDER BY [u].[Id]
-- Second Part
   SELECT [a].[Id], [a].[Approved], [a].[AuthorId], [a].[Body], [a].[PubDate], [a].[Subject], [u].[Id]
      FROM [Users] AS [u]
      INNER JOIN [Articles] AS [a] ON [u].[Id] = [a].[AuthorId]
      ORDER BY [u].[Id]

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

شکل 2- خروجی حاصل بعد از اجرا به صورت SplitQuery


فعال سازی به صورت سراسری

همانطور که بیان شد، EF به صورت پیش فرض  کوئری‌ها را در قالب یک درخواست اجرا می‌کند. اگر تمایل دارید خاصیت SplitQuery بر روی تمامی کوئری‌ها اعمال شود، می‌توانید به صورت زیر این امکان را به صورت سراسری اعمال نمایید.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFQuerying;",
            o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

اگر SplitQuery را به صورت سراسری فعال کردید و نیاز داشتید جایی یک کوئری را به همان روش SignleQuery اجرا کنید، میتوانید از متد SingleQuery به صورت زیر استفاده نمایید.

var users = context.Users.AsSingleQuery().Include(x => x.Articles).ToList();


عکس زیر مقایسه ای بین اجرای کوئری‌ها به صورت Single و Split می‌باشد:

مبنع:  thinktecture  



در رابطه با SplitQuery موارد زیر مطرح می‌باشد :

  • زمانیکه کوئری تبدیل به دو یا چند کوئری می‌شود، ممکن است بعد از اجرا کوئری اول و قبل از اجرای کوئری دوم، یک به روزرسانی انجام شود که ممکن است consistency نقض شود.
  • در این حالت، چندین درخواست و رفت و برگشت اجرا می‌شود که همین می‌تواند باعث تاخیر و افزایش زمان گردد.
‫۳ سال و ۷ ماه قبل، چهارشنبه ۱۳ اسفند ۱۳۹۹، ساعت ۱۸:۴۰
وحید نصیری وحید نصیری
نظرات مطالب
اتصال SQL Server به MySQL
دو نکته تکمیلی:
- برای انتقال کل اطلاعات یک جدول از SQL Server به MySQL لینک شده با اجرای فقط یک کوئری:

insert into openquery(mysql, 'select f1,f2 from testdb.testtable') select f1,f2 from testdb.dbo.myTable

در اینجا testdb.testtable مربوط به طرف MySQL است و testdb.dbo.myTable مربوط به طرف SQL Server .

- کوئری گرفتن از Linked server به صورت زیر هم می‌تواند باشد (بر اساس دیتابیس پیش فرض ذکر شده در پروایدر استرینگ):
SELECT * FROM mysql...testtable
‫۱۳ سال و ۳ ماه قبل، سه‌شنبه ۱۱ مرداد ۱۳۹۰، ساعت ۱۸:۰۳
وحید نصیری وحید نصیری
نظرات مطالب
بررسی خطاهای ممکن در حین راه اندازی اولیه برنامه‌های ASP.NET Core در IIS
حداقل دو دلیل را می‌تواند داشته باشد:
- کاربر application pool برنامه (و یا کاربری که توسط آن به بانک اطلاعاتی متصل می‌شوید)، دسترسی مناسبی را به SQL Server ندارد. در قسمت مدیریت کاربران SQL Server، این کاربر باید حداقل دسترسی‌های db_datareader و db_dataweriter را بر روی دیتابیس شما داشته باشد.
- کوئری‌های شما بر اساس اسکیمای dbo صادر می‌شوند، اما اسکیمای واقعی بانک اطلاعاتی موجود در هاست، برای کاربر شما مثلا user1 است و نه dbo مدیریتی. در این حالت نمی‌توانید کوئری‌هایی مانند select * from dbo.table1 را صادر کنید. باید کوئری‌های شما با اسکیمای جدید select * from user1.table1 اجرا شوند.
این موارد در نظرات سری EF Code First بررسی شده‌اند و در اینجا هم تفاوتی نمی‌کنند؛ چون موارد پایه‌ای مدیریت دسترسی‌های SQL Server هستند.
‫۵ سال و ۱۰ ماه قبل، چهارشنبه ۲۱ آذر ۱۳۹۷، ساعت ۱۶:۳۸
مهدی ملائیان مهدی ملائیان
نظرات مطالب
مقایسه رکوردهای دو جدول
در ادامه مطلب جناب نصیری بازبینی پلن اجرائی دو دستور هم جالب توجه است.هر دو دستور یک پلان یکسان دارند ولی دستور Except فشرده‌تر و زیباتر است.
----SQL SERVER 2005 Method
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder;
GO

----SQL SERVER 2000 Method which works&nbspIN SQL SERVER 2005
USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO
تصویر پلان اجرائی دو کوئری :

‫۸ سال و ۸ ماه قبل، دوشنبه ۱۰ اسفند ۱۳۹۴، ساعت ۱۹:۲۶
محسن خان محسن خان
نظرات مطالب
متد LastOrDefault در EF
ممنون. روش دوم به select top 1 در حین استفاده از SQL Server ترجمه میشه.
‫۱۱ سال و ۳ ماه قبل، سه‌شنبه ۱ مرداد ۱۳۹۲، ساعت ۱۳:۰۸
وحید نصیری وحید نصیری
مطالب
افزودن اکانت مدیریتی فراموش شده به SQL Server
فرض کنید
- تمام اکانت‌های مدیریتی توکار SQL Server را حذف کرده‌اید (یا برایتان حذف کرده‌اند).
- بجز کاربر SA، تمام کاربران را از نقش SYSADMIN حذف کرده‌اید؛ شامل تمام اکانت‌های ویندوزی و همچنین خود SQL Server.
- پسورد SA را هم فراموش کرده‌اید یا ندارید.

خوب، الان چکار می‌خواهید بکنید؟!
احتمالا نصب مجدد سرور را پیشنهاد دهید یا بانک اطلاعاتی Master را بازسازی کنید که در هر دو حالت تمام تنظیمات سرور را از دست خواهید داد. روش دیگری هم بدون از دست دادن تنظیمات سرور وجود دارد که در ادامه آن‌را بررسی خواهیم کرد.


افزودن یک اکانت مدیریتی جدید به SQL Server

اگر دسترسی کامل مدیریتی خود را به SQL Server از دست داده‌اید باید ابتدا به آن سرور لاگین کنید؛ با این فرض که کاربر وارد شده به سیستم، جزو local administrators group است.
 C:\>sqlcmd -S .
1> create login [MachineName\TestUser] from windows;
2> go
1> exec sp_addsrvrolemember 'MachineName\TestUser','sysadmin'
2> go
1> select is_srvrolemember('sysadmin', 'MachineName\TestUser')
2> go
-----------
1
(1 rows affected)
1>
سپس خلاصه مواردی را که ملاحظه می‌کنید، اجرای سه دستور است:
الف) اجرای sqlcmd با پارامتر S و مشخص سازی وهله‌ی مورد نظر
البته حالت کامل آن در صورتیکه از وهله‌ی پیش فرض استفاده نمی‌کنید SQLCMD –S Server_Name\Instance_Name است. S نیز در اینجا باید با حروف بزرگ نوشته شود.
ب) create login را بر روی یکی از اکانت‌های محلی سیستم اجرا کنید. مثلا MachineName\administrator یا هر اکانت موجود دیگری که لازم است. نام آن نیز باید حتما به شکل server_name\user_name باشد.
در حین استفاده از sqlcmd، هر فرمان زمانی اجرا می‌شود که در سطر پس از آن، یک go نوشته شده و enter شود.
ج) سپس توسط sp_addsrvrolemember به این اکانت اضافه شده، دسترسی sysadmin بدهید.

برای آزمایش آن فقط کافی است از متد is_srvrolemember برای کوئری گرفتن استفاده کنید و یا سعی کنید توسط اکانت اضافه شده، به SQL Server لاگین کنید. این اکانت اکنون در قسمت Security/logins قابل مشاهده است.

اگر نمی‌خواهید از اکانت‌های ویندوزی استفاده کنید، create login را به نحو ذیل مقدار دهی کنید:
 C:\>sqlcmd -S .
1> use master
2> go
Changed database context to 'master'.
1> create login test_user with password='123#123'
2> go
1> exec sp_addsrvrolemember 'test_user','sysadmin'
2> go
1>
سپس به این کاربر اضافه شده با کلمه‌ی عبور مشخص، توسط sp_addsrvrolemember دسترسی sysadmin بدهید.
‫۱۰ سال و ۸ ماه قبل، جمعه ۹ اسفند ۱۳۹۲، ساعت ۰۴:۳۰
وحید نصیری وحید نصیری
نظرات مطالب
PersianDatePicker یک DatePicker شمسی به زبان JavaScript که از تاریخ سرور استفاده می‌کند
برای جدا کردن قسمت‌های مختلف تاریخ رشته‌ای از متد split استفاده کنید. نمونه‌ی این مورد در سورس همین کتابخانه بکار رفته‌است (split را در آن جستجو کنید). در سمت سرور هم متد Split برای کار با رشته‌ها وجود دارد.
‫۹ سال و ۷ ماه قبل، پنجشنبه ۲۸ اسفند ۱۳۹۳، ساعت ۰۳:۵۸
وحید نصیری وحید نصیری
مطالب
بررسی کارآیی کوئری‌ها در SQL Server - قسمت دوم - جمع آوری اطلاعات آماری کوئری‌ها توسط Extended Events
همانطور که در قسمت قبل نیز بررسی کردیم، Management Studio برای جمع آوری اطلاعات آماری کوئری‌های زنده بسیار مفید است؛ اما تهیه‌ی آن دستی است. باید کوئری را اجرا کرد و سپس مراحلی را طی نمود تا به نتایج آماری حاصل از کوئری‌ها رسید و همچنین دست آخر باید از نتایج آن نیز یک خروجی دستی را تهیه کرد. روش دیگری نیز برای جمع آوری اطلاعات آماری کوئری‌ها در SQL Server توسط Extended Events/Trace وجود دارد که به ازای هر کوئری، قابل استخراج است. علاوه بر آن می‌توان از Dynamic management objects و یا Query store نیز استفاده کرد. این دو برخلاف Extended Events/Trace، اطلاعات تجمعی گروهی از کوئری‌ها را بازگشت می‌دهند. همچنین در اینجا performance monitor نیز می‌تواند مورد استفاده قرار گیرد؛ اما محدوده‌ی دید آن کل بانک اطلاعاتی است.


Extended Events/Trace

Extended Events، زیر ساخت مدیریت رخ‌دادها در SQL Server است. برای مثال در نگارش 2016 آن بیش‌از 300 رخ‌داد در SQL Server تعریف شده‌اند و زمانیکه در مورد اجرای کوئری‌ها بحث می‌کنیم، این رخ‌دادها بیشتر مدنظر ما هستند:
sql_statement_completed
sp_statement_completed
rpc_completed
sql_batch_completed
کار آن‌ها دریافت اطلاعاتی در مورد logical reads، میزان مصرف CPU، مدت زمان اجرای کوئری‌ها و امثال آن‌ها است. در این بین، دو مورد اول بیش از همه مورد استفاده قرار می‌گیرند.
علاوه بر این‌ها، رخ‌دادهای بسط یافته‌ی زیر را نیز می‌توان مورد استفاده قرار داد:
query_post_compilation_showplan
query_post_execution_showplan
query_pre_execution_showplan
اما به علت هزینه‌بر بودن تولید execution plan به ازای هر کوئری، آنچنان مورد استفاده قرار نمی‌گیرند.


استفاده از Extended Events برای جمع آوری اطلاعات آماری کوئری‌ها

برای آزمایش نحوه‌ی کار با Extended Events، ابتدا رویه‌ی ذخیره شده‌ی زیر را ایجاد می‌کنیم:
USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Application].[usp_GetCountryInfo];
GO

CREATE PROCEDURE [Application].[usp_GetCountryInfo]
    @Country_Name NVARCHAR(60)
AS

SELECT *
FROM [Application].[Countries] [c]
    JOIN [Application].[StateProvinces] [s]
    ON [s].[CountryID] = [c].[CountryID]
WHERE [c].[CountryName] = @Country_Name;
GO
این کوئری شبیه به کوئری‌است که در قسمت قبل مورد استفاده قرار گرفت؛ با این تفاوت که به همراه یک * SELECT است که استفاده‌ی از آن توصیه نمی‌شود و در اینجا بیشتر جهت بررسی کارآیی این کوئری، تعریف شده‌است.
سپس یک سشن Extended Events سفارشی را به صورت زیر ایجاد می‌کنیم:
/*
Create XE session to capture sql_statement_completed
and sp_statement_completed
*/
IF EXISTS (
SELECT *
FROM sys.server_event_sessions
WHERE [name] = 'QueryPerf')
BEGIN
    DROP EVENT SESSION [QueryPerf] ON SERVER;
END
GO

CREATE EVENT SESSION [QueryPerf] 
ON SERVER 
ADD EVENT sqlserver.sp_statement_completed(WHERE ([duration]>(1000))),
ADD EVENT sqlserver.sql_statement_completed(WHERE ([duration]>(1000)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPerf\test.xel',max_file_size=(256))
WITH (
  MAX_MEMORY=16384 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO
در این سشن، رخ‌دادهای sp_statement_completed و sql_statement_completed مورد استفاده قرار گرفته‌اند. هر کدام نیز بر اساس مدت زمان اجرای کوئری، فیلتر شده‌اند. در اینجا عدد 1000، یعنی یک میلی ثانیه که عدد بسیار کوچکی است؛ اما برای دمو، مفید است. نتیجه‌ی عملیات نیز در مسیر C:\Temp\QueryPerf ذخیره خواهد شد.

سپس نیاز است تا این سشن را که QueryPerf نام دارد، در قسمت management->extended events، اجرا و آغاز کرد:


در ادامه ابتدا بر روی بانک اطلاعاتی WideWorldImporters، کلیک راست کرده و یک پنجره‌ی new query جدید را ایجاد می‌کنیم:
WHILE 1 = 1
BEGIN
   EXECUTE [Application].[usp_GetCountryInfo] N'United States';
END
در این پنجره با یک حلقه‌ی بی‌پایان، رویه‌ی ذخیره شده‌ای را که ایجاد کردیم، بارها و بارها اجرا خواهیم کرد (نکته‌ی «عدم نمایش ردیف‌های بازگشت داده شده‌ی توسط کوئری در حین جمع آوری اطلاعات آماری» قسمت قبل را هم مدنظر داشته باشید).

سپس مجددا یک پنجره‌ی new query دیگر را باز می‌کنیم:
WHILE 1 = 1
BEGIN
    SELECT
        [s].[StateProvinceName],
        [s].[SalesTerritory],
        [s].[LatestRecordedPopulation],
        [s].[StateProvinceCode]
    FROM [Application].[Countries] [c]
        JOIN [Application].[StateProvinces] [s]
        ON [s].[CountryID] = [c].[CountryID]
    WHERE [c].[CountryName] = 'United States';
END
این کوئری شبیه به رویه‌ی ذخیره شده‌ای است که ایجاد کردیم؛ اما یک کوئری Ad Hoc و غیر پارامتری می‌باشد.

کوئری‌های هر دو پنجره را به صورت مجزایی اجرا کنید. سپس در قسمت management->extended events، بر روی سشن QueryPerf کلیک راست کرده و گزینه‌ی View live data را انتخاب کنید:


این زنده‌ترین خروجی یک سشن رخ‌دادهای بسط یافته‌است. کار کردن با آن نسبت به روشی که در قسمت قبل بررسی کردیم، ساده‌تر و سریعتر است و همچنین گزارش آن به صورت خودکار تولید می‌شود.

یک نکته: در اینجا در قسمت Details، اگر بر روی هر ردیف کلیک کنید، امکان انتخاب و نمایش آن در لیست بالای صفحه توسط گزینه‌ی Show Column in table وجود دارد.

در آخر در قسمت management->extended events، بر روی سشن QueryPerf کلیک راست کرده و گزینه‌ی Stop Session را انتخاب کنید. اکنون اگر به پوشه‌ی C:\Temp\QueryPerf مراجعه کنید، فایل xel حاوی اطلاعات این گزارش را نیز می‌توانید مشاهده نمائید (به ازای هربار اجرای این سشن، یک فایل جدید را تولید می‌کند).


 این فایل توسط Management Studio قابل گشودن و بررسی است و دقیقا همان نمای گزارش live data را به همراه دارد.
‫۵ سال و ۳ ماه قبل، یکشنبه ۹ تیر ۱۳۹۸، ساعت ۱۵:۴۰
وحید نصیری وحید نصیری
نظرات مطالب
خلاصه‌ای در مورد SQL Server CE
وضعیت SQL Server CE در سال 2018
توسعه‌ی فعال این بانک اطلاعاتی مدت‌ها است که توسط مایکروسافت خاتمه یافته‌است. پشتیبانی رسمی آن در تاریخ 2016 -07-12 پایان یافته و پشتیبانی تمدید شده‌ی آن در تاریخ 2021-07-13 پایان می‌یابد و بجای آن LocalDB برای کارهای سبک توصیه می‌شود. همچنین اگر هدف شما کار با بانک‌های اطلاعاتی Embedded است، بهتر است از SQLite استفاده کنید. جالب است بدانید هرچند EF Core از SQL Server CE پشتیبانی می‌کند، اما پروایدر آن توسط مایکروسافت تهیه نشده‌است. برعکس SQLite پشتیبانی کاملی را توسط مایکروسافت پیدا کرده‌است؛ چه از لحاظ پروایدر EF Core و چه از لحاظ پروایدر ADO.NET.
‫۵ سال و ۱۰ ماه قبل، سه‌شنبه ۲۰ آذر ۱۳۹۷، ساعت ۱۶:۱۲