August 29, 2011 at 12:38 pm
My table has 4000 rows and a simple select * from [DeploymentTarget_Temp] takes more than 2.3 minutes. Can someone help me understand how I can improve performance?
CREATE TABLE [dbo].[DeploymentTarget_Temp](
[DeploymentTargetID] [int] IDENTITY(1,1) NOT NULL,
[DeploymentID] [int] NOT NULL,
[MachineID] [int] NOT NULL,
[StartedAt] [datetime] NULL,
[SuccessfulAt] [datetime] NULL,
[FailedAt] [datetime] NULL,
[DeploymentLog] [text] NULL,
CONSTRAINT [PK_DeploymentTarget_Temp_1] PRIMARY KEY NONCLUSTERED
(
[DeploymentTargetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[DeploymentTarget_Temp] WITH CHECK ADD CONSTRAINT [FK_DeploymentTarget_Temp_Deployment] FOREIGN KEY([DeploymentID])
REFERENCES [dbo].[Deployment] ([DeploymentID])
GO
ALTER TABLE [dbo].[DeploymentTarget_Temp] CHECK CONSTRAINT [FK_DeploymentTarget_Temp_Deployment]
GO
ALTER TABLE [dbo].[DeploymentTarget_Temp] WITH NOCHECK ADD CONSTRAINT [FK_DeploymentTarget_Temp_Machine] FOREIGN KEY([MachineID])
REFERENCES [dbo].[Machine] ([MachineID])
GO
ALTER TABLE [dbo].[DeploymentTarget_Temp] CHECK CONSTRAINT [FK_DeploymentTarget_Temp_Machine]
GO
Thank you!
August 29, 2011 at 12:51 pm
jeeva.nadarajah (8/29/2011)
My table has 4000 rows and a simple select * from [DeploymentTarget_Temp] takes more than 2.3 minutes. Can someone help me understand how I can improve performance?CREATE TABLE [dbo].[DeploymentTarget_Temp](
[DeploymentTargetID] [int] IDENTITY(1,1) NOT NULL,
[DeploymentID] [int] NOT NULL,
[MachineID] [int] NOT NULL,
[StartedAt] [datetime] NULL,
[SuccessfulAt] [datetime] NULL,
[FailedAt] [datetime] NULL,
[DeploymentLog] [text] NULL,
CONSTRAINT [PK_DeploymentTarget_Temp_1] PRIMARY KEY NONCLUSTERED
(
[DeploymentTargetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
See the bolded pieces from your quote. That's your problem... and a LOT of data, in theory. Each Text can hold what... 2 gigs a piece? Round that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 29, 2011 at 12:59 pm
To answer the question that was implied, not asked, there is no index that can help a SELECT * FROM <table> query. Indexes are there to support where clause predicates or joins (or for the more advanced usage, group by and order by)
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 29, 2011 at 12:59 pm
Thanks for the prompt response. I'm looking into whether or not we can change the type for that field without much intrusion.
That's what you meant by by "Round that" ?
August 29, 2011 at 1:06 pm
jeeva.nadarajah (8/29/2011)
Thanks for the prompt response. I'm looking into whether or not we can change the type for that field without much intrusion.That's what you meant by by "Round that" ?
Sorry about that, it's American Slang. It means "Somewhere around that". I didn't remember the actual number off hand.
My guess is you can't, so you're going to want to restrict calls by queries to that table to either not use that column, or only return one row at a time if they do. Because, really, what else are they planning to do with 4,000 rows each one of undetermined document length?
Oh, and this isn't helping either:
TEXTIMAGE_ON [PRIMARY]
By preference you want it in a different filegroup.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 29, 2011 at 1:45 pm
Evil Kraig F (8/29/2011)
Oh, and this isn't helping either:TEXTIMAGE_ON [PRIMARY]
By preference you want it in a different filegroup.
However it's near-impossible to change after table creation. 🙁
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 29, 2011 at 1:50 pm
Thanks Everybody!
Since there is so little data in the table, I've switched the type to be VARCHAR(max) and am refactoring to remove the deploymentLog from queries.
That makes it a Speedy Gonzales. We'll just have to remember the implications of creating text columns.
Thanks for setting me straight on indexes being related to clauses.
j
August 29, 2011 at 2:02 pm
jeeva.nadarajah (8/29/2011)
Thanks Everybody!Since there is so little data in the table, I've switched the type to be VARCHAR(max).
In other words, you've changed almost nothing... 🙂
A varchar(max) and a text are the new and old types for the same thing - a text column stored (mostly) out of row, up to 2 billion characters long.
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 29, 2011 at 2:06 pm
jeeva.nadarajah (8/29/2011)
Thanks Everybody!Since there is so little data in the table, I've switched the type to be VARCHAR(max) and am refactoring to remove the deploymentLog from queries.
That makes it a Speedy Gonzales. We'll just have to remember the implications of creating text columns.
Thanks for setting me straight on indexes being related to clauses.
j
That... fixed... it?! :blink:
As Gail said, you've simply swapped to the newer version of the old issue. It might have caused a defrag/reindex, but... um...
Wow. I'm shocked. :crazy:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 29, 2011 at 2:21 pm
Switching it to varchar(max) didn't slow down anything but not including deploymentlog in select did.
Am going to see what the max length on deploymentlog is and switch it to varchar(x). Let me know if you have better ideas and again thanks for the info.
August 29, 2011 at 2:26 pm
jeeva.nadarajah (8/29/2011)
Switching it to varchar(max) didn't slow down anything but not including deploymentlog in select did.Am going to see what the max length on deploymentlog is and switch it to varchar(x). Let me know if you have better ideas and again thanks for the info.
Ah hah! That makes a ton more sense. 🙂
Nah, just remove that from everyday usage except for single record pulls and you should be okay. When someone asks what the problem is, just tell them it's the equivalent of trying to open up 4000 Word documents. At once.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply