Click here to Skip to main content
11,491,742 members (64,030 online)
Rate this: bad
good
Please Sign up or sign in to vote.
I have one table now

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;
Posted 8-Nov-12 23:38pm
Edited 9-Nov-12 0:16am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,


i hope it will help you.

Overview of SQL Server 2005/2008 Table Indexing (Part-1)[^]

Thanks,
Adersh Ram
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 476
1 Maciej Los 276
2 Sascha Lefèvre 275
3 Andy Lanng 263
4 OriginalGriff 215
0 Sergey Alexandrovich Kryukov 9,829
1 OriginalGriff 8,530
2 Sascha Lefèvre 3,579
3 Maciej Los 3,332
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100