Click here to Skip to main content
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 22:38pm
Edited 8-Nov-12 23:16pm
v3
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
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  

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 480
1 Tadit Dash 305
2 Sergey Alexandrovich Kryukov 250
3 Peter Leow 190
4 OriginalGriff 163
0 Sergey Alexandrovich Kryukov 9,435
1 OriginalGriff 5,498
2 Peter Leow 4,230
3 Maciej Los 3,540
4 Abhinav S 3,353


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