在SQL Server 2014中写入基础表后立即查询视图

我有一个问题,如果我写一个表(使用 Linq-to-sql),它是视图的依赖,然后马上转过来查询该视图来检查写入的影响(使用新的连接DB,因此是新的数据上下文),写入的影响不会立即显示,但最多需要几秒钟才能显示.这只会偶尔发生(每10万次写入可能是10-20次).

这是视图的定义：

CREATE VIEW [Position].[Transactions]
WITH SCHEMABINDING
AS
(
  SELECT
    Account,Book,TimeAPIClient AS DateTimeUtc,BaseCcy AS Currency,ISNULL(QuantityBase,0) AS Quantity,ValueDate AS SettleDate,ISNULL(CAST(0 AS tinyint),0) AS TransactionType
  FROM Trades.FxSpotMF
  WHERE IsCancelled = 0

  UNION ALL

  SELECT
    Account,QuoteCcy AS Currency,ISNULL(-QuantityBase * Rate,ExecutionTimeUtc AS DateTimeUtc,ISNULL(CAST(1 AS tinyint),1) AS TransactionType
  FROM Trades.FxSpotManual
  WHERE IsCancelled = 0

  UNION ALL

  SELECT
    Account,ISNULL(SpotQuantityBase,SpotValueDate AS SettleDate,ISNULL(CAST(2 AS tinyint),2) AS TransactionType
  FROM Trades.FxSwap

  UNION ALL

  SELECT
    Account,ISNULL(-SpotQuantityBase * SpotRate,ISNULL(ForwardQuantityBase,ForwardValueDate AS SettleDate,ISNULL(-ForwardQuantityBase * ForwardRate,c.Book,TimeUtc AS DateTimeUtc,Currency,ISNULL(Amount,SettleDate,ISNULL(CAST(3 AS tinyint),3) AS TransactionType
  FROM Trades.Commission c
  JOIN Trades.Payment p
    ON c.UniquePaymentId = p.UniquePaymentId
    AND c.Book = p.Book
)

而这是Linq-to-sql 生成的用于写入其中一个基础表的查询：

INSERT INTO [Trades].[FxSpotMF] ([UniqueTradeId],[BaseCcy],[QuoteCcy],[ValueDate],[Rate],[QuantityBase],[Account],[Book],[CounterpartyId],[Counterparty],[ExTradeId],[TimeAPIClient],[TimeAPIServer],[TimeExchange],[TimeHandler],[UniqueOrderId],[IsCancelled],[ClientId],[SequenceId],[ExOrdId],[TradeDate],[OrderCycleId],[CycleIndex])
  VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22)

这是Linq-to-sql产生的查询查询效果的查询：

SELECT
  SUM([t0].[Quantity]) AS [Item2],[t0].[Currency] AS [Item1]
FROM [Position].[Transactions] AS [t0]
WHERE ([t0].[Book] = @p0)
AND ([t0].[DateTimeUtc] < @p1)
GROUP BY [t0].[Currency]

此外,这是生成写入(使用F#类型提供程序)的Linq-to-sql 代码：

type Schema = Microsoft.FSharp.Data.TypeProviders.DbmlFile<"TradeDb.dbml",ContextTypeName="TradeDb"> 

use db = new Schema.TradeDb(connectionString)
let trade = new Schema.Trades_FxSpotMF()
(* omitted: set object properties corresponding to column values here... *)
db.Trades_FxSpotMF.InsertOnSubmit(trade)
db.SubmitChanges()

而这是生成读取的相应Linq-to-sql：

use db = new Schema.TradeDb(connectionString)
query { for t in db.Position_Transactions do
        where ( t.Book = book &&
                t.DateTimeUtc < df.MaxExecutionTimeExcl
              )
        groupBy t.Currency into group
        let total = query { for x in group do sumBy x.Quantity }
        select (group.Key,total)
      }
|> Map.ofSeq

我会以为System.Data.Linq.DataContext.SubmitChanges()只会在写入事务完成后返回,并且视图的任何后续查询都必须包含写入的效果…我缺少/做错了什么？

解决方法

我终于得到了这样的结论：数据库写入在自己的线程中完成,主线程在检查结果之前等待所有写入线程完成.但是,代码中有一个错误,检查所有线程是否完整,导致主线程过早地进行检查.

原文链接：https://www.f2er.com/mssql/76169.html

在SQL Server 2014中写入基础表后立即查询视图

解决方法

猜你在找的MsSQL相关文章