April 3, 2008 at 2:49 pm
Hey guys,
I have a query that returns 9000 records of information. Its retrieved two different ways:
1: Get all records that belong to the state "Texas"
2: Get all records that belong the the County "Austin"
I have all 9,000 records assigned to the county "Austin". There are no other records in the database or in Texas right now.
Query 1 returns all 9,000 records to my webpage in roughly 2 seconds.
Query 2 returns all 9,000 records to my webpage in roughly 25 seconds.
Anyways, you can see my weak skilz with query 2. Will the query analyzer help me troubleshoot this? If so, can someone give me the crash course on how to go about troubleshooting this problem?
David
April 3, 2008 at 2:53 pm
Do you have the structures for the table(s), syntax of the queries, sample data, etc?
Hard to help with nothing to see.
April 3, 2008 at 2:55 pm
Yea, it may take me a bit to round it all up.
I also need to note that when running the query directly in ssms it returns the data in under a second but when returned to my webpage it takes over 20 seconds.
April 3, 2008 at 2:58 pm
In Management Studio (I assume you're using that since this is the SQL 2005 forum; if it's actually Query Analyzer, it works the same way), click on the button on the menu that says "Display Estimated Execution Plan" when you put your mouse over it.
Do that for both queries, and use mouse-over to compare the two plans.
You will almost certainly find that one of them is using an "Index Seek", and the other is using a "Table Scan". What you're looking for is, the slow one will have a Scan, the fast one will have a Seek. This could be an "Index Scan", "Clustered Index Scan", or "Table Scan".
What you want to do, if you find that, is probably add an index to the table for the County column.
Books Online has good directions on how to create indexes, plus data on what they do, how they do it, and when to use/not use them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 3:16 pm
LOL...
All of my mine say "Scan". 🙁
I have primary keys for my tables, I don't get it.
April 3, 2008 at 3:18 pm
Can you provide the table structure please?
Odds are your Primary Key is neither the State nor the County?
April 3, 2008 at 3:20 pm
States Table:
CREATE TABLE [dbo].[States](
[State_Code] [int] NOT NULL,
[State_Name] [varchar](50) NOT NULL,
[State_Abbreviation] [varchar](2) NOT NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[State_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'State Code Number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'States', @level2type=N'COLUMN',@level2name=N'State_Code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'State Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'States', @level2type=N'COLUMN',@level2name=N'State_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'State Abbreviation' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'States', @level2type=N'COLUMN',@level2name=N'State_Abbreviation'
April 3, 2008 at 3:20 pm
Counties Table:
CREATE TABLE [dbo].[Counties](
[County_Id] [int] IDENTITY(1,1) NOT NULL,
[County_Name] [varchar](75) NOT NULL,
[County_Code] [int] NOT NULL,
[State_Code] [int] NOT NULL,
CONSTRAINT [PK_Counties] PRIMARY KEY CLUSTERED
(
[County_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Auto generated unique Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Counties', @level2type=N'COLUMN',@level2name=N'County_Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'County Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Counties', @level2type=N'COLUMN',@level2name=N'County_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'County code assigned by state' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Counties', @level2type=N'COLUMN',@level2name=N'County_Code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'State code references state table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Counties', @level2type=N'COLUMN',@level2name=N'State_Code'
GO
ALTER TABLE [dbo].[Counties] WITH CHECK ADD CONSTRAINT [FK_Counties_States] FOREIGN KEY([State_Code])
REFERENCES [dbo].[States] ([State_Code])
GO
ALTER TABLE [dbo].[Counties] CHECK CONSTRAINT [FK_Counties_States]
April 3, 2008 at 3:30 pm
Ok, I went into my database diagrams and updated a few table relationships and it seems to have fixed the delay. However, they still do not say "Seek" instead of "Scan".
David
April 3, 2008 at 4:26 pm
Did you create indexes on the county and state fields? Need indexes to make it work.
I see some foreign keys, but not indexes in the structure above.
What do the SQL queries for by county and by state look like as well?
April 4, 2008 at 5:59 am
You have 9000 rows for the county of Austin and you're filtering by the county of Austin... You're going to get a table scan or index scan no matter what you do. If the database will ultimately have a more distirbuted set of data, then for testing you need to generate a distributed set of data that closely mirrors what you'll see in production. Then your tests will have more meaning. Right now, based on what you've described, scans are the best you can hope for because you're asking it to return all the data in a table. There's nothing to "seek" on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2008 at 7:27 am
grtn316 (4/3/2008)
Ok, I went into my database diagrams and updated a few table relationships and it seems to have fixed the delay. However, they still do not say "Seek" instead of "Scan".David
To get it to say "Seek", and run queries faster, you have to add some indexes.
I'd have to see the actual query to be sure, but most likely, adding an index to county and state will help. If all you'll ever have is Texas, don't bother adding that to the index, since all rows will have that and it won't help. County should be good though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 7:35 am
I guess I must be missing the meaning of index. In my county table I have 1 column called County_Id (primary key, unique identifier), another column with the actual county name, and another column with the state_code (that is from the states table).
Can someone explain to me how to setup an index. Whenever I drill down to the table information and look at "Indexes" It has "PK_Counties (Clustered)". Is this not it?
April 4, 2008 at 7:47 am
That means a default clustered index has been created on the primary key of the table. From your description, I assume on the column CountyId.
However, if you only have a single county worth of information, there is nothing for the query to filter on, nothing for the index to index. Think of it like the alphabetical listing of phone numbers in Outlook and every single name starts with the letter 'R'. There's no way for an index to help you find anything because the only place it can go to is 'R.'
To create other indexes on other columns, the TSQL syntax is
CREATE CLUSTERED/NONCLUSTERED INDEX somename
ON dbo.Table (columna, columnb,etc.)
But again, before you start worrying about creating indexes all over the place, you need a good set of data for your tests in order to determine where, and if, an index will do any good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2008 at 8:02 am
I guess I must be missing the meaning of index. In my county table I have 1 column called County_Id (primary key, unique identifier), another column with the actual county name, and another column with the state_code (that is from the states table).
Can someone explain to me how to setup an index. Whenever I drill down to the table information and look at "Indexes" It has "PK_Counties (Clustered)". Is this not it?
This means you have a clustered index on Counties. It so happens that your clustered index is the primary key to your table. You primary key does not have to be your clustered index. You are only allowed 1 Clustered index per table, as this type of index sorts the data.
As GSquared said, this should be sufficient if you only have Texas data in your table; however, if you have other States/Counties, you should create another index on State and County. You should base your indexes off the selectivity of the data your index represents. It does not matter how good you think your index is because a bad selectiviy percentage means the optimizer will decide not to use the index.
A quick test of your selectivity should be conducted. You can use these queries to get you started.
/*===========================================================
DETERMINE SELECTIVITY FOR COMPOSITE INDEX
===========================================================*/
DECLARE @UniqueValues INT
SELECT DISTINCT State, County
FROM MyTable;
SET @UniqueValues = @@ROWCOUNT;
SELECT (1.0 * @UniqueValues) / count(*) AS [SELECTIVITY]
FROM MyTable;
/*===========================================================
DETERMINE SELECTIVITY FOR NON-COMPOSITE INDEX
===========================================================*/
SELECT (1.0 * COUNT(DISTINCT County)) / count(*)
FROM MyTable;
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply