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 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 398
1 DamithSL 235
2 OriginalGriff 230
3 Zoltán Zörgő 149
4 Dave Kreskowiak 120
0 OriginalGriff 7,575
1 DamithSL 5,529
2 Sergey Alexandrovich Kryukov 5,279
3 Maciej Los 4,961
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web04 | 2.8.141223.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
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