PHPのPostgreSQLの脆弱性EntityFrameworkコアのコアデバッグSQL 1
43131 ワード
イントロ
私のアプリケーションがパフォーマンスの問題を引き起こす場合、私はボトルネックを見つけるために測定する必要があります.
今回はEntityFrameworkコアを使用するコードを測定します.
環境
ベースプロジェクト
カンパニー.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookStoreSample.Models
{
public class Company
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Required]
public string Name { get; set; }
public List<Book> Books { get; set; } = new List<Book>();
}
}
ジャンル.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookStoreSample.Models
{
public class Genre
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Required]
public string Name { get; set; }
}
}
ブック.cs
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookStoreSample.Models
{
public class Book
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Required]
public string Name { get; set; }
[Column(TypeName = "timestamp with time zone")]
public DateTime? PublishDate { get; set; }
[ForeignKey(nameof(Company))]
public int CompanyId { get; set; }
[ForeignKey(nameof(Genre))]
public int GenreId { get; set; }
public Company Company { get; set; }
public Genre Genre { get; set; }
}
}
Bookstoronontext。cs
using Microsoft.EntityFrameworkCore;
namespace BookStoreSample.Models
{
public class BookStoreContext: DbContext
{
public BookStoreContext(DbContextOptions<BookStoreContext> options)
: base(options)
{
}
public DbSet<Company> Companies => Set<Company>();
public DbSet<Genre> Genres => Set<Genre>();
public DbSet<Book> Books => Set<Book>();
}
}
サンプルデータの生成
SQLパフォーマンスを測定するには、サンプルデータを生成します.
ISamplecreator。cs
using System.Threading.Tasks;
namespace BookStoreSample.Samples
{
public interface ISampleCreator
{
Task CreateAsync();
}
}
サンプラcs
using System;
using System.Threading.Tasks;
using BookStoreSample.Models;
namespace BookStoreSample.Samples
{
public class SampleCreator: ISampleCreator
{
private readonly BookStoreContext _context;
public SampleCreator(BookStoreContext context)
{
_context = context;
}
public async Task CreateAsync()
{
using(var transaction = _context.Database.BeginTransaction())
{
try
{
for(var i = 0; i < 1000; i++)
{
_context.Companies.Add(new Company
{
Name = $"Company: {i}",
});
}
for(var i = 0; i < 1000; i++)
{
_context.Genres.Add(new Genre
{
Name = $"Genre: {i}",
});
}
await _context.SaveChangesAsync();
var random = new Random();
for(var i = 0; i < 1000000; i++)
{
_context.Books.Add(new Book
{
Name = $"Book: {i}",
PublishDate = DateTime.Now,
CompanyId = random.Next(999) + 1,
GenreId = random.Next(999) + 1,
Price = 600,
});
}
await _context.SaveChangesAsync();
transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
}
ホームセンター。cs
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using BookStoreSample.Samples;
namespace BookStoreSample.Controllers
{
public class HomeController: Controller
{
private readonly ILogger<HomeController> _logger;
private readonly ISampleCreator _sample;
public HomeController(ILogger<HomeController> logger,
ISampleCreator sample)
{
_logger = logger;
_sample = sample;
}
[Route("Sample")]
public async Task CreateSamples()
{
await _sample.CreateAsync();
}
}
}
出力SQL
EntityFrameworkコアは、CのChorzコードからSQLを生成します.
SQLを測定するには、生成されたSQLを取得します.
“EnableSensitiveDataGogging”で出力できます.
起動。cs
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json;
...
namespace BookStoreSample
{
public class Startup
{
private readonly IConfiguration configuration;
public Startup(IConfiguration config)
{
configuration = config;
}
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers()
.AddNewtonsoftJson(options =>
options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore);
services.AddDbContext<BookStoreContext>(options =>
{
options.EnableSensitiveDataLogging();
options.UseNpgsql(configuration["ConnectionStrings"]);
});
...
}
...
appsettings開発JSON
{
"Logging": {
"LogLevel": {
"Default": "Debug",
"Microsoft": "Information",
"Microsoft.Hosting.Lifetime": "Information"
}
}
}
public async Task<List<SearchedCompany>> SearchCompaniesAsync()
{
return await _context.Companies
.ToListAsync();
}
...
2020-10-06 18:20:17.1528|20101|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT c."Id", c."Name"
FROM "Companies" AS c |url: http://localhost/Company/Search|action: SearchCompany
...
説明、分析
SQLのパフォーマンスを測定するには、説明と解析を使用できます.
EXPLAIN ANALYZE SELECT c."Id", c."Name" FROM "Companies" AS c
結果は実行時間、検索に使用するキーなどです.
これはpgadmin 4の結果です.
例
実行例を2つ試してみます.
サンプル1
SearchedCompanycs
using BookStoreSample.Models;
namespace BookStoreSample.Books
{
public class SearchedCompany
{
public Company? Company { get; set; }
public Book? Book { get; set; }
}
}
BookSearchSample .cs
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using BookStoreSample.Models;
using Microsoft.EntityFrameworkCore;
namespace BookStoreSample.Books
{
public class BookSearchSample: IBookSearchSample
{
private readonly BookStoreContext _context;
public BookSearchSample(BookStoreContext context)
{
_context = context;
}
public async Task<List<SearchedCompany>> SearchCompaniesAsync()
{
return await _context.Companies
.Include(c => c.Books)
.Select(c => new SearchedCompany
{
Company = c,
Book = c.Books
.OrderByDescending(b => b.Id).First(),
})
.ToListAsync();
}
}
}
生成SQL
SELECT c."Id", c."Name", t0."Id", t0."CompanyId", t0."GenreId", t0."Name", t0."Price", t0."PublishDate", b0."Id", b0."CompanyId", b0."GenreId", b0."Name", b0."Price", b0."PublishDate"
FROM "Companies" AS c
LEFT JOIN (
SELECT t."Id", t."CompanyId", t."GenreId", t."Name", t."Price", t."PublishDate"
FROM (
SELECT b."Id", b."CompanyId", b."GenreId", b."Name", b."Price", b."PublishDate", ROW_NUMBER() OVER(PARTITION BY b."CompanyId" ORDER BY b."Id" DESC) AS row
FROM "Books" AS b
) AS t
WHERE t.row <= 1
) AS t0 ON c."Id" = t0."CompanyId"
LEFT JOIN "Books" AS b0 ON c."Id" = b0."CompanyId"
ORDER BY c."Id", t0."Id", b0."Id"
計画時間
実行時間
サンプル2
...
public class SearchedCompany
{
public int CompanyId { get; set; }
public string CompanyName { get; set; } = "";
public Book? Book { get; set; }
}
...
BookSearchSample .cs
...
public async Task<List<SearchedCompany>> SearchCompaniesAsync()
{
return await _context.Companies
.Include(c => c.Books)
.Select(c => new SearchedCompany
{
CompanyId = c.Id,
CompanyName = c.Name,
Book = c.Books
.OrderByDescending(b => b.Id).First(),
})
.ToListAsync();
}
...
生成SQL
SELECT c."Id", c."Name", t0."Id", t0."CompanyId", t0."GenreId", t0."Name", t0."Price", t0."PublishDate"
FROM "Companies" AS c
LEFT JOIN (
SELECT t."Id", t."CompanyId", t."GenreId", t."Name", t."Price", t."PublishDate"
FROM (
SELECT b."Id", b."CompanyId", b."GenreId", b."Name", b."Price", b."PublishDate", ROW_NUMBER() OVER(PARTITION BY b."CompanyId" ORDER BY b."Id" DESC) AS row
FROM "Books" AS b
) AS t
WHERE t.row <= 1
) AS t0 ON c."Id" = t0."CompanyId"
計画時間
実行時間
これらの違いは小さい.しかし、サンプル1の実行時間はサンプル2より2倍遅い.
Reference
この問題について(PHPのPostgreSQLの脆弱性EntityFrameworkコアのコアデバッグSQL 1), 我々は、より多くの情報をここで見つけました https://dev.to/masanori_msl/postgresql-entityframework-core-debugging-sql-1-31d2テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol