August 13, 2008 at 7:35 am
Here is the query
select email_address from mailing_list
This is the table script.
USE [SUN_HERALD_CAMPAIGN]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mailing_list](
[mailing_id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[email_address] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[first_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_added] [datetime] NULL CONSTRAINT [DF_mailing_list_date_added] DEFAULT (getdate()),
[date_last_sent] [datetime] NULL,
[bounce] [int] NOT NULL CONSTRAINT [DF_mailing_list_bounce] DEFAULT (0),
[softbounce] [int] NOT NULL CONSTRAINT [DF_mailing_list_softbounce] DEFAULT (0),
[unsubscribe] [int] NOT NULL CONSTRAINT [DF_mailing_list_unsubscribe] DEFAULT (0),
[source] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[opt_in] [int] NOT NULL CONSTRAINT [DF__mailing_l__opt_i__04EFA97D] DEFAULT (0),
[BAD_FORMAT] [int] NOT NULL CONSTRAINT [DF__mailing_l__BAD_F__0F6D37F0] DEFAULT (0),
CONSTRAINT [PK_mailing_list] PRIMARY KEY CLUSTERED
(
[mailing_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
When I try and run that query, after 14 minutes i cancelled the query and only 20% of the rows had been returned. Can anyone offer a suggestion how to speed this up?
August 13, 2008 at 7:49 am
How big is the table?
What indexes do you have defined?
August 13, 2008 at 8:15 am
Since there's no where clause, SQL has to read all the rows of the table. Indexes aren't going to help much. At best, you could change the exec plan from a clustered index (table) scan to an index scan.
How many rows in the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 8:22 am
USE [SUN_HERALD_CAMPAIGN]
GO
/****** Object: Index [IX_all] Script Date: 08/13/2008 10:21:03 ******/
CREATE NONCLUSTERED INDEX [IX_all] ON [dbo].[mailing_list]
(
[mailing_id] ASC,
[email_address] ASC,
[first_name] ASC,
[last_name] ASC,
[city] ASC,
[state] ASC,
[zip] ASC,
[phone] ASC,
[date_added] ASC,
[date_last_sent] ASC,
[bounce] DESC,
[softbounce] DESC,
[unsubscribe] DESC,
[opt_in] DESC,
[BAD_FORMAT] DESC
) ON [PRIMARY]
USE [SUN_HERALD_CAMPAIGN]
GO
/****** Object: Index [IX_EMAIL] Script Date: 08/13/2008 10:21:15 ******/
CREATE NONCLUSTERED INDEX [IX_EMAIL] ON [dbo].[mailing_list]
(
[email_address] ASC,
[bounce] DESC,
[softbounce] DESC,
[unsubscribe] DESC,
[opt_in] DESC
) ON [PRIMARY]
USE [SUN_HERALD_CAMPAIGN]
GO
/****** Object: Index [IX_PHONE] Script Date: 08/13/2008 10:21:26 ******/
CREATE NONCLUSTERED INDEX [IX_PHONE] ON [dbo].[mailing_list]
(
[phone] ASC
) ON [PRIMARY]
USE [SUN_HERALD_CAMPAIGN]
GO
/****** Object: Index [PK_mailing_list] Script Date: 08/13/2008 10:21:39 ******/
ALTER TABLE [dbo].[mailing_list] ADD CONSTRAINT [PK_mailing_list] PRIMARY KEY CLUSTERED
(
[mailing_id] ASC
) ON [PRIMARY]
It has over 6 million rows, and is updated not very often.
August 13, 2008 at 8:27 am
I do realize now that the IX_EMAIL index had extra columns I did not realize were included in the script.
August 13, 2008 at 8:44 am
6 million rows at say an average of 20 characters each (rough guess) means that query is pulling around 200 MB of data from the database and across the network. It's probably going to scan the IX_EMAIL index, which is about as optimal as it's going to get.
What's the network speed? If you do SELECT Count(EmailAddress) FROM mailing_list, how long does that query take?
What's the query for? What is going to be done with 6 million email addresses?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 8:53 am
Thanks, I completely forgot about that. it's just a bandwidth issue, I'll run this remotely and I'll be fine. I keep thinking this is a legitimate business I work for with broadband!:w00t:
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply