I have a table TRANSACTIONS. It is kinda very big so there is part of it:
CREATE TABLE [dbo].[TRANSACTIONS]
(
[transaction_ID] UNIQUEIDENTIFIER NOT NULL,
[transaction_number] INT IDENTITY (1000, 1) NOT NULL,
[type_id] INT NOT NULL,
[source_currency_ID] INT NOT NULL,
[target_currency_ID] INT NOT NULL,
[source_value] NUMERIC (14, 6) NOT NULL,
[target_value] NUMERIC (14, 6) NOT NULL,
[user_account_source] NVARCHAR (500) NULL,
[user_account_target] NVARCHAR (500) NULL,
[user_wmid] VARCHAR (12) NULL,
[user_email] NVARCHAR (51) NULL,
[create_date] DATETIME CONSTRAINT [DF__transacti__creat__589C25F3] DEFAULT (getdate()) NOT NULL,
[card_ID] INT NULL,
[payment_ID] NVARCHAR (80) NULL,
[enable_automation] BIT CONSTRAINT [DF__transacti__enabl__3AA1AEB8] DEFAULT ((1)) NOT NULL,
[client_ID] UNIQUEIDENTIFIER NULL,
[partner_ID] INT NULL,
[payment_type_ID] INT NOT NULL,
[language_ID] INT NULL,
[member_id] NUMERIC (18) NULL,
[desk_id] INT NULL,
[source_member_discount] NUMERIC (18, 3) NULL,
[member_scores] INT NULL,
[source_eps_fee_from_customer] NUMERIC (18, 6) NULL,
[source_eps_fee] NUMERIC (18, 6) NOT NULL,
[source_eps_fee_actual] NUMERIC (18, 6) NULL,
[target_eps_fee] NUMERIC (18, 6) NOT NULL,
[target_eps_fee_actual] NUMERIC (18, 6) NULL,
[target_exchanger_fee] NUMERIC(18, 6) NOT NULL DEFAULT 0,
[exchange_rate] NUMERIC (20, 9) NULL,
[source_master] BIT NULL,
[manual_rate] NUMERIC (20, 9) NULL,
[cb_cross_rate] NUMERIC (20, 9) NULL,
[source_profit] NUMERIC (18, 6) NULL,
[partner_reward] NUMERIC (18, 6) NULL,
[source_service_profit] NUMERIC (18, 6) NULL,
[is_individual] BIT NULL,
[member_discount] NUMERIC (18) NULL,
[scores] INT NULL,
[return_url] VARCHAR (300) NULL,
[err_return_url] VARCHAR (300) NULL,
[add_scores] BIT NULL,
[mm_transaction_id] NVARCHAR (80) NULL,
[lp_transaction_id] NVARCHAR (50) NULL,
[masterbank_auth_ID] NVARCHAR (50) NULL,
[cardtype] NVARCHAR (50) NULL,
[cl_num] INT NULL,
[cl_date] NVARCHAR (50) NULL,
[limit_check_day] BIT NULL,
[limit_check_month] BIT NULL,
[rapida_temp] NVARCHAR (50) NULL,
[ecard_id] BIGINT NULL,
[card_payment_complete] VARCHAR (4) CONSTRAINT [DF_TRANSACTIONS_card_payment_complete] DEFAULT ('NO') NULL,
[profit_in_rub] NUMERIC (18, 4) NULL,
[cb_rub_rate] NUMERIC (18, 4) NULL,
[check_wmid] VARCHAR (12) NULL,
[partner_rate] NUMERIC (18, 4) NULL,
[wm_desc] VARCHAR (500) NULL,
[without_real] BIT NULL,
[netex_point] UNIQUEIDENTIFIER NULL,
[secret_key] INT NULL,
[miniport_transfer] BIT NULL,
[miniport_hold] BIT NULL,
[hold_time] DATETIME NULL,
[is_from_widget] BIT NULL,
[partner_reward_currency_id] INT NULL,
[user_phone] NVARCHAR (50) NULL,
[project_ID] INT NOT NULL DEFAULT 2,
...
From which I compose data I need by SELECT:
return query
.Where(x => x.CurrentStatus == TransactionStatus.PaymentSent)
.GroupBy(x => new { SourceCurrency = x.SourceCurrency, TargetCurrency = x.TargetCurrency })
.Select(x => new ProfitabilityReportEntry
{
SourceCurrencyCode = x.Key.SourceCurrency,
TargetCurrencyCode = x.Key.TargetCurrency,
TransactionsCount = x.LongCount(),
TotalProfitRub = x.Sum(y => y.ProfitInRub),
AverageProfitRub = x.Average(y => y.ProfitInRub),
AverageAmountRub = x.Average(y => y.SourceValue * y.CbRubRate),
TotalAmountRub = x.Sum(y => y.SourceValue * y.CbRubRate)
})
.ToList();
Class ProfitabilityReportEntry is:
public class ProfitabilityReportEntry
{
public PaymentCurrencyCode SourceCurrencyCode { get; set; }
public PaymentCurrencyCode TargetCurrencyCode { get; set; }
public long TransactionsCount { get; set; }
public decimal? AverageAmountRub { get; set; }
public decimal? AverageProfitRub { get; set; }
public decimal? TotalAmountRub { get; set; }
public decimal? AmountTurnoverPercent { get; set; }
public decimal? TotalProfitRub { get; set; }
public decimal? ProfitPercent { get; set; }
}
In mapped class Transaction (to TRANSACTIONS) properties which sum and average in query above are all decimal too.
And my query results in the following SQL statement:
select transactio0_.source_currency_ID as col_0_0_, transactio0_.target_currency_ID as col_1_0_,
cast(count(*) as BIGINT) as col_2_0_,
cast(sum(transactio0_.moneyback_profit_rub) as DECIMAL(19,5)) as col_3_0_,
cast(avg(transactio0_.moneyback_profit_rub) as DECIMAL(19,5)) as col_4_0_,
cast(avg(transactio0_.source_value*transactio0_.cb_rub_rate) as DECIMAL(19,5)) as col_5_0_,
cast(sum(transactio0_.source_value*transactio0_.cb_rub_rate) as DECIMAL(19,5)) as col_6_0_
from TRANSACTIONS transactio0_
where transactio0_.change_date>? and transactio0_.change_date<? and transactio0_.status_id=? group by transactio0_.source_currency_ID, transactio0_.target_currency_ID
The problem is why there are casts, why does NHibernate generate them? How to make NH get rid of them? And I think they influence performance too.
Why summing decimal is cast to decimal? Also, if I use an anonymous type casts remain.
My version of Fluent NHibernate is 2.0.3 and NHibernate 5.2.1
For example source_value has type NUMERIC (14, 6) and cb_rub_rate has type NUMERIC (18, 4). So, what type do you expect to get in result of source_value * cb_rub_rat?
I think, you should make types of fileds equal to each other.
But it does not affect perfomance, I think. All CAST operators used in result experessions, so it executes once.