超时SQL选择与或与CLOUSE组 [英] getting time out sql select with or with out group by clouse

问题描述

我现在有一张桌子

 创建  TABLE  [dbo].[EmailData](
    [ID] [ int ]  IDENTITY ( 1 ， 1 )不  NULL ，
    [电子邮件] [ nvarchar ]( 255 )>不  NULL ，
    [域] [ nvarchar ]( 255 )>不  NULL ，
    [状态] [真实] 不  NULL  ，
    [日期] [日期时间]  NULL ，
  CONSTRAINT  [PK_EmailData]  PRIMARY   KEY  集群
(
    [电子邮件]  ASC 
)与(PAD_INDEX = 关闭，STATISTICS_NORECOMPUTE = 关闭，IGNORE_DUP_KEY = 关闭，ALLOW_ROW_LOCKS = 打开，ALLOW_PAGE_LOCKS =  ON ) ON  [ PRIMARY ]
)打开 [ PRIMARY ] 

现在的问题是，假设我的表emaildata中有1亿条记录，状态为0、1、2、3、4

和我开SQL选择查询

就像

 选择状态，COUNT(状态)作为总计 EmailData 组 依据状态

即使我将记录分为100,000条，我也要超时

 选择状态，COUNT(状态)作为总计 EmailData 其中 id 之间 和 ">组 依据状态

我大部分时间都在超时，甚至从sql server management studio中触发与使用sqlserver 2008企业版相同的命令.

所以请帮帮我

我在此使用的连接字符串是

 Provider = SQLOLEDB;数据源= LOCALHOST \ SQL2008;初始目录= dbEmailData;集成安全性= SSPI;连接超时=  600 ;  pre> 
解决方案
 
您是否尝试过设置命令超时以及连接?

如果您在SSMS中运行此程序: http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio  [ http://msdn. microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx  [CREATE TABLE [dbo].[EmailData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Email] [nvarchar](255) NOT NULL,
    [Domain] [nvarchar](255) NOT NULL,
    [Status] [real] NOT NULL,
    [Dated] [datetime] NULL,
 CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED
(
    [Email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now the problem is this suppose i have 100 million records in my table emaildata with status 0,1,2,3,4

and i fire sql select query

like

select Status,COUNT(status) as counttotal from EmailData group by Status

i am getting time out even if i breaks the records into 100,000 records

select Status,COUNT(status) as counttotal from EmailData where id between 0 and 100000 group by Status

i am getting time out most of the time, even firing the same command from sql server management studio as we are using sqlserver 2008 enterprises.

so please help me

connection string i am using in this is

Provider=SQLOLEDB;Data Source=LOCALHOST\SQL2008;Initial Catalog=dbEmailData;Integrated Security=SSPI;Connect Timeout=600;

解决方案

Have you tried setting the Command Timeout as well as the Connect?
If you are running this in SSMS: http://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio[^]
If you are usign this from .NET code: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx[^]

The defined data type of status is real in EmailData and smallint in Status. I assume EmailData has a reference to Status and therefor just change it to Int for both tables. Also, your foreign key looks completely bogus because Status is referencing Status. SQL Management Studio can create a diagram for you and it would make it visual for you.

Good luck!

Thanks Mr. Ram
I need to change the data index and cluster keys
I just applied cluster to ID and made email is unique + indexed
so now i can fire the query in breaking the rows as per id blocks mode.

这篇关于超时SQL选择与或与CLOUSE组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！