使用游标使用C#从SQL Server读取时间序列数据?
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了使用游标使用C#从SQL Server读取时间序列数据?,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2839字,纯文字阅读大概需要5分钟。
内容图文
我有一个包含时间序列数据的大型数据库(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#从SQL Server读取时间序列数据?全部内容,希望文章能够帮你解决使用游标使用C#从SQL Server读取时间序列数据?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。