gpt4 book ai didi

c# - 使用游标从使用 C# 的 SQL Server 读取时间序列数据?

转载 作者:太空狗 更新时间:2023-10-30 01:25:40 25 4
gpt4 key购买 nike

我有一个包含时间序列数据的大型数据库(5000 万行)。 [datetime] 列上有一个聚集索引,可确保表格始终按时间顺序排序。

将表的行逐行读取到 C# 应用程序中的最高效方法是什么?

最佳答案

你应该试试这个并找出答案。我刚刚做了,没有发现任何性能问题。

USE [master]
GO
/****** Object: Database [HugeDatabase] Script Date: 06/27/2011 13:27:50 ******/
CREATE DATABASE [HugeDatabase] ON PRIMARY
( NAME = N'HugeDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\HugeDatabase.mdf' , SIZE = 1940736KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'HugeDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\HugeDatabase_log.LDF' , SIZE = 395392KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE [HugeDatabase]
GO
/****** Object: Table [dbo].[HugeTable] Script Date: 06/27/2011 13:27:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HugeTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PointInTime] [datetime] NULL,
PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_HugeTable_PointInTime] ON [dbo].[HugeTable]
(
[PointInTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

填充:

DECLARE @t datetime
SET @t = '2011-01-01'

DECLARE @i int
SET @i=0

SET NOCOUNT ON

WHILE (@i < 50000000)
BEGIN
INSERT INTO HugeTable(PointInTime) VALUES(@t)
SET @t = DATEADD(ss, 1, @t)

SET @i = @i + 1
END

测试:

using System;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApplication1
{
internal class Program
{
private static void Main()
{
TimeSpan firstRead = new TimeSpan();
TimeSpan readerOpen = new TimeSpan();
TimeSpan commandOpen = new TimeSpan();
TimeSpan connectionOpen = new TimeSpan();
TimeSpan secondRead = new TimeSpan();

try
{

Stopwatch sw1 = new Stopwatch();
sw1.Start();
using (
var conn =
new SqlConnection(
@"Data Source=.\sql2k8r2;Initial Catalog=HugeDatabase;Integrated Security=True"))
{
conn.Open(); connectionOpen = sw1.Elapsed;

using (var cmd = new SqlCommand(
"SELECT * FROM HugeTable ORDER BY PointInTime", conn))
{
commandOpen = sw1.Elapsed;

var reader = cmd.ExecuteReader(); readerOpen = sw1.Elapsed;

reader.Read(); firstRead = sw1.Elapsed;
reader.Read(); secondRead = sw1.Elapsed;
}
}
sw1.Stop();
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{

Console.WriteLine(
"Connection: {0}, command: {1}, reader: {2}, read: {3}, second read: {4}",
connectionOpen,
commandOpen - connectionOpen,
readerOpen - commandOpen,
firstRead - readerOpen,
secondRead - firstRead);

Console.Write("Enter to exit: ");
Console.ReadLine();
}
}
}
}

关于c# - 使用游标从使用 C# 的 SQL Server 读取时间序列数据?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6468925/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com