August 12, 2009 at 1:44 pm
I have a table named Policies that contains approx 600,000 records...
When I try to run a simple select * query directly against the table, it takes over an hour to return all records...
The table has:
* 55 columns
* 17 FOREIGN KEYS
* 2 compound UNIQUE KEYS (PolicyID_ClientID & PolicyID_PolicyNumber)
* 1 PRIMARY KEY (PolicyID)
* 13 CONSTRAINTS
* 3 TRIGGERS (INSERT / UPDATE / DELETE)
* 13 INDEXES (clustered, non-clustered)
It looks like the statistics were last updated on 8/9/09.
Since this is a simple table query, and I'm not performing any JOINS, I don't understand why it is taking to long to return the data... Is this a network bandwith issue? How can I tell where the bottleneck is?
Thanks!
Josh
***** UPDATE *****
Here is the table creation script. Not sure if this will help, but its worth a shot.
/****** Object: Table [dbo].[Policies] Script Date: 08/12/2009 15:08:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Policies](
[PolicyID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ClientID] [int] NULL,
[FormID] [int] NULL,
[IssuingCompanyID] [int] NULL,
[BillingCompanyID] [int] NULL,
[Premium] [money] NULL,
[PolicyNo] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOBs] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffDate] [datetime] NULL,
[ExpDate] [datetime] NULL,
[CovEffDate] [datetime] NULL,
[CovExpDate] [datetime] NULL,
[EffTime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExpTime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CovEffTime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CovExpTime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeZoneId] [int] NULL,
[StageId] [int] NULL,
[StatusId] [int] NULL,
[ModeId] [int] NULL,
[ChangeId] [int] NULL,
[ParentId] [int] NULL,
[PolicyTypeId] [int] NULL CONSTRAINT [DF_Policies_PolicyTypeId] DEFAULT (0),
[BillTypeId] [int] NULL,
[BillMethodId] [int] NULL,
[Remark] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HistoryNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[History] [bit] NULL CONSTRAINT [DF_Policies_History] DEFAULT (0),
[ActivityPending] [bit] NULL CONSTRAINT [DF_Policies_ActivityPending] DEFAULT (0),
[LastUpdatedById] [int] NULL,
[LastUpdated] [datetime] NULL CONSTRAINT [DF_Policies_LastModified] DEFAULT (getdate()),
[DateCreated] [datetime] NULL CONSTRAINT [DF_Policies_DateCreated] DEFAULT (getdate()),
[Scripts] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClaimCount] [int] NULL CONSTRAINT [DF_Policies_ClaimCount] DEFAULT (0),
[RestrictToAssignedUser] [bit] NULL CONSTRAINT [DF_Policies_RestrictToAssignedUser] DEFAULT (0),
[PrevStatusId] [int] NULL,
[MemberKeyId] [int] NULL,
[SourceKeyId] [int] NULL,
[RetailAgentID] [int] NOT NULL CONSTRAINT [DF_Policies_RetailAgent] DEFAULT (0),
[Nonrenewal] [bit] NOT NULL CONSTRAINT [DF_Policies_Nonrenewal] DEFAULT (0),
[CovEffDatePrior] [datetime] NULL,
[CovExpDatePrior] [datetime] NULL,
[LegacyID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EnterpriseID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReplicationID] [uniqueidentifier] ROWGUIDCOL NULL CONSTRAINT [DF_Policies_ReplicationID] DEFAULT (newid()),
[OriginDate] [datetime] NULL,
[PolicyDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrimaryState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientAccessEnable] [bit] NULL CONSTRAINT [DF_Policies_ClientAccessEnable] DEFAULT (0),
[RetailAgentAccessEnable] [bit] NULL CONSTRAINT [DF_Policies_RetailAgentAccessEnable] DEFAULT (0),
[CarrierAccessEnable] [bit] NULL CONSTRAINT [DF_Policies_CarrierAccessEnable] DEFAULT (0),
[PremiumChangeAmt] [money] NULL,
[PolicyTermGroupID] [int] NULL,
[PolicyGroupID] [int] NULL,
[EnterpriseApplicationID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Policies] PRIMARY KEY NONCLUSTERED
(
[PolicyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ_Policies_PolicyClient] UNIQUE CLUSTERED
(
[PolicyID] ASC,
[ClientID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UQ_Policies_PolicyNumber] UNIQUE NONCLUSTERED
(
[PolicyID] ASC,
[PolicyNo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Org911_Data]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_BillMethods] FOREIGN KEY([BillMethodId])
REFERENCES [dbo].[BillMethods] ([BillMethodID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_BillMethods]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_BillTypes] FOREIGN KEY([BillTypeId])
REFERENCES [dbo].[BillTypes] ([BillTypeID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_BillTypes]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_CarrierNames_BillingCompany] FOREIGN KEY([BillingCompanyID])
REFERENCES [dbo].[CarrierNames] ([CarrierNameID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_CarrierNames_BillingCompany]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_CarrierNames_IssuingCompany] FOREIGN KEY([IssuingCompanyID])
REFERENCES [dbo].[CarrierNames] ([CarrierNameID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_CarrierNames_IssuingCompany]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([ClientID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_Clients]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_People] FOREIGN KEY([LastUpdatedById])
REFERENCES [dbo].[People] ([PeopleID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_People]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_Policies_MemberKeyID] FOREIGN KEY([MemberKeyId])
REFERENCES [dbo].[Policies] ([PolicyID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_Policies_MemberKeyID]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_Policies_ParentID] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Policies] ([PolicyID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_Policies_ParentID]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_Policies_SourceKeyID] FOREIGN KEY([SourceKeyId])
REFERENCES [dbo].[Policies] ([PolicyID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_Policies_SourceKeyID]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_PolicyChange] FOREIGN KEY([ChangeId])
REFERENCES [dbo].[PolicyChange] ([PolicyChangeId])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyChange]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_PolicyMode] FOREIGN KEY([ModeId])
REFERENCES [dbo].[PolicyMode] ([PolicyModeId])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyMode]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_PolicyStage] FOREIGN KEY([StageId])
REFERENCES [dbo].[PolicyStage] ([PolicyStageId])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyStage]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_PolicyStatus_PrevStatus] FOREIGN KEY([PrevStatusId])
REFERENCES [dbo].[PolicyStatus] ([PolicyStatusId])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyStatus_PrevStatus]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_PolicyTypes] FOREIGN KEY([PolicyTypeId])
REFERENCES [dbo].[PolicyTypes] ([PolicyTypeID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_PolicyTypes]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_RetailAgent] FOREIGN KEY([RetailAgentID])
REFERENCES [dbo].[RetailAgent] ([RetailAgentID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_RetailAgent]
GO
ALTER TABLE [dbo].[Policies] WITH NOCHECK ADD CONSTRAINT [FK_Policies_TimeZones] FOREIGN KEY([TimeZoneId])
REFERENCES [dbo].[TimeZones] ([TimeZoneID])
GO
ALTER TABLE [dbo].[Policies] CHECK CONSTRAINT [FK_Policies_TimeZones]
GO
ALTER TABLE [dbo].[Policies] WITH CHECK ADD CONSTRAINT [FK_Policy_PolicyGroup] FOREIGN KEY([PolicyGroupID])
REFERENCES [dbo].[PolicyGroup] ([PolicyGroupID])
GO
ALTER TABLE [dbo].[Policies] WITH CHECK ADD CONSTRAINT [FK_Policy_PolicyTermGroup] FOREIGN KEY([PolicyTermGroupID])
REFERENCES [dbo].[PolicyTermGroup] ([PolicyTermGroupID])
August 12, 2009 at 1:49 pm
If you want to test for a network/display type issue, then change your SELECT query to a SELECT .. INTO #temp and time that. If it is significantly faster, then you've probably got a problem with you network or your local display speed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 1:54 pm
Are you returning ALL the rows and ALL the columns? If not, what is your WHERE clause? Have you looked at your execution plan?
Have you checked blocks? SELECT * FROM sys.dm_exec_requests AS DER check wait_type and last_wait_type.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2009 at 1:57 pm
RBarryYoung (8/12/2009)
If you want to test for a network/display type issue, then change your SELECT query to a SELECT .. INTO #temp and time that. If it is significantly faster, then you've probably got a problem with you network or your local display speed.
Or you could go to Query -> Query Options -> Results and check the discard results after execution setting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2009 at 1:58 pm
How big is each row (in bytes)? What type of network are you on 10MB/100MB/1000MB? Are connected to the network via a switch or hub?
August 12, 2009 at 2:03 pm
Jack Corbett (8/12/2009)
Are you returning ALL the rows and ALL the columns? If not, what is your WHERE clause? Have you looked at your execution plan?Have you checked blocks? SELECT * FROM sys.dm_exec_requests AS DER check wait_type and last_wait_type.
Unfortunately, I don't have SHOWPLAN rights to the DB in question (don't ask me why).
The query is simply: SELECT * FROM Policies.
When I run the query you listed, I only get 1 record returned...
wait_type = NULL and last_wait_type = MISCELLANEOUS
August 12, 2009 at 2:08 pm
Lynn Pettis (8/12/2009)
How big is each row (in bytes)? What type of network are you on 10MB/100MB/1000MB? Are connected to the network via a switch or hub?
I'm sorry, I'm not really DBA type, so I'm not sure how to tell how big the row is in bytes... Should I post the CREATE TABLE script?
I am on my company's intranet. I'm connected to a wall socket via CAT-5 cable... that's all I know...
JOsh
August 12, 2009 at 2:11 pm
Jack Corbett (8/12/2009)
RBarryYoung (8/12/2009)
If you want to test for a network/display type issue, then change your SELECT query to a SELECT .. INTO #temp and time that. If it is significantly faster, then you've probably got a problem with you network or your local display speed.Or you could go to Query -> Query Options -> Results and check the discard results after execution setting.
I tried this and it does not seem to run any faster (although I terminated execution after about 15 minutes...)
So does this mean its NOT a network bandwidth issue?
Thanks!
Josh
August 12, 2009 at 2:16 pm
I'm going to go out on limb here, but the problem isn't your query. It is the amount of data you are trying to pull down to you desktop system. Do you really need to pull down all 600,000+ records and all 55 columns?
Just curious, but what are you trying to accomplish?
August 12, 2009 at 2:23 pm
Jack Corbett (8/12/2009)
RBarryYoung (8/12/2009)
If you want to test for a network/display type issue, then change your SELECT query to a SELECT .. INTO #temp and time that. If it is significantly faster, then you've probably got a problem with you network or your local display speed.Or you could go to Query -> Query Options -> Results and check the discard results after execution setting.
Hmmm, hadn't thought of that. I wonder if this is implemented on the client-side or the server-side though?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 2:28 pm
jbarker4682 (8/12/2009)
Lynn Pettis (8/12/2009)
How big is each row (in bytes)? What type of network are you on 10MB/100MB/1000MB? Are connected to the network via a switch or hub?I'm sorry, I'm not really DBA type, so I'm not sure how to tell how big the row is in bytes... Should I post the CREATE TABLE script?
I am on my company's intranet. I'm connected to a wall socket via CAT-5 cable... that's all I know...
JOsh
you say are on the company intranet....are you accessing via LAN or WAN? (are you at a remote office ?)
what type of data do all these columns hold...is it just numbers or is there a lot of text as well?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2009 at 2:30 pm
jbarker4682 (8/12/2009)
Jack Corbett (8/12/2009)
RBarryYoung (8/12/2009)
If you want to test for a network/display type issue, then change your SELECT query to a SELECT .. INTO #temp and time that. If it is significantly faster, then you've probably got a problem with you network or your local display speed.Or you could go to Query -> Query Options -> Results and check the discard results after execution setting.
I tried this and it does not seem to run any faster (although I terminated execution after about 15 minutes...)
So does this mean its NOT a network bandwidth issue?
Well, I'm not sure if this is implemented before or after the network transmission, so I'd suggest trying the SELECT .. INTO first before we try to call it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 3:20 pm
Are there any open transactions? Something holding some rows "open"? This should be trying to put some find of table lock down on the table, so something preventing that lock from happening would delay the process.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply