help speeding up query..... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help speeding up query.....

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-09-28 : 11:33:32

I have this query that was running at a decent speed, but now has totally bogged down. The execution time just tested was 1 minute 20 seconds, and the immediate re run was 54 seconds.

I know its a slow query, but as of now its timing out all my pages. I'm not sure whats caused it to run slower except more records in the DB.

Does anyone have any ideas on speeding this up? I am willing to modify the query so that the whole "UserDetails" table is not scanned and possible just the first X amount of records. (100k or so) as I think this would possibly bring me back to where I was before....

Any suggestions greatly apprecated!

Thanks again
Mike123

CREATE PROCEDURE dbo.select_Cities_Distinct_MostPopular_Top100

AS SET NOCOUNT ON

select top 100 ltrim(city) as city, count(*) as_count, UD.stateProvID, SP.stateProv from Userdetails UD

JOIN tblStateProv SP on UD.stateProvID = SP.stateProvID

where patindex('%[^a-zA-Z0-9 ]%', city ) = 0 and city <> '' and UD.stateProvID <> '1' and UD.stateProvID <> '64'
group by city, ud.stateProvID, sp.StateProv order by as_count desc, city asc

GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 12:07:30

Did you look at the query plan?

Also, please post the DDL of the tables, AND all of the indexes and contraints.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 12:15:49

Well I get a cluster index scan on this. We should look at correcting that


USE Northwind
GO

SELECT patindex('%[^a-zA-Z0-9 ]%', ShipCity ), ShipCity FROM Orders
WHERE patindex('%[^a-zA-Z0-9 ]%', ShipCity )=0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 12:20:47

Just a real shot in the dark


SELECT TOP 100 
	  LTRIM(city) AS city
	, stateProvID
	, stateProv 
	, COUNT(*)    AS as_count
  FROM ( 
	  SELECT  city
		, UD.stateProvID
		, SP.stateProv 
	    FROM  Userdetails  UD
	    JOIN  tblStateProv SP 
	      ON  UD.stateProvID = SP.stateProvID
	     AND  city <> '' 
	     AND  UD.stateProvID <> '1' 
	     AND  UD.stateProvID <> '64'
	) AS XXX
   WHERE  patindex('%[^a-zA-Z0-9 ]%', city ) = 0 
GROUP BY  city
	, ud.stateProvID
	, sp.StateProv
ORDER BY  as_count desc
	, city asc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-09-28 : 12:41:49

Hi Brett,

Thanks for the quick response!.. I tested the new query but no real difference on execution time. Here is the table structure as you requested, I hope its what you need. If I can provide anything else please let me know

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStateProv]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStateProv]
GO

CREATE TABLE [dbo].[tblStateProv] (
[StateProvID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[StateProv] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateProvAbbr] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUserDetails] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateProvID] [tinyint] NULL ,
[CountryID] [tinyint] NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenderID] [tinyint] NULL ,
[Age] [tinyint] NULL ,
[Date] [datetime] NULL ,
[LastLoggedIn] [datetime] NULL ,
[LastUpdated] [smalldatetime] NULL ,
[LoginCount] [int] NULL ,
[Active] [tinyint] NULL ,
[emailNotification] [tinyint] NULL ,
[IMpermissionID] [tinyint] NULL ,
[IP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forum_Level] [tinyint] NULL ,
[Forum_Posts] [int] NULL ,
[ForumPic] [uniqueidentifier] NULL ,
[Forum_Pic] [int] NULL ,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblUserDetails] WITH NOCHECK ADD
CONSTRAINT [PK_tblUserDetails] PRIMARY KEY CLUSTERED
(
[UserID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblUserDetails] ADD
CONSTRAINT [DF_tblUserDetails_LoginCount] DEFAULT (0) FOR [LoginCount],
CONSTRAINT [DF_tblUserDetails_emailNotification] DEFAULT (1) FOR [emailNotification],
CONSTRAINT [DF_tblUserDetails_IMpermissionID] DEFAULT (1) FOR [IMpermissionID],
CONSTRAINT [DF_tblUserDetails_Forum_Level] DEFAULT (1) FOR [Forum_Level],
CONSTRAINT [DF_tblUserDetails_Forum_Pic] DEFAULT (0) FOR [Forum_Pic],
CONSTRAINT [DF__tblUserDet__GUID__230A1C49] DEFAULT (newid()) FOR [GUID],
CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED
(
[NameOnline],
[EmailAddress]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblUserDetails_1] UNIQUE NONCLUSTERED
(
[NameOnline],
[EmailAddress]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblUserDetails_2] UNIQUE NONCLUSTERED
(
[NameOnline]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblUserDetails31] ON [dbo].[tblUserDetails]([UserID], [NameOnline]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblUserDetails33] ON [dbo].[tblUserDetails]([GUID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblUserDetails26] ON [dbo].[tblUserDetails]([EmailAddress], [NameOnline], [Password]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblUserDetails39] ON [dbo].[tblUserDetails]([Active]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_589245154_49A_1A] ON [dbo].[tblUserDetails] ([GUID], [UserID]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_589245154_1A_2A_49A] ON [dbo].[tblUserDetails] ([UserID], [NameOnline], [GUID]) ')
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_589245154_1A_49A_2A] ON [dbo].[tblUserDetails] ([UserID], [GUID], [NameOnline]) ')
GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 12:49:33

Add this


CREATE INDEX [tblUserDetailsXX] ON [dbo].[tblUserDetails]([City], [StateProvID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

PS. My Rewrite is was realized by the optimizer like your anyway...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-09-28 : 13:05:24

Brett,

You saved my world today! .. Query is down from 1:20 to just 4 seconds...

Thanks once again :)

mike123

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-28 : 13:22:51

a Pitcher of margarita's and we're even.

I looked at your query plan and saw an index scan on your table. I noticed you did not have an index for your predicates.

Do you know how to look at your query's plan?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-09-28 : 16:05:43

Why

where patindex('%[^a-zA-Z0-9 ]%', city ) = 0

rather than

WHERE city NOT LIKE '%[^a-zA-Z0-9 ]%'

it might just use the index (now there is one!) more efficiently [although I doubt it]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources