optimzing a query in sql , help!

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

SQL Server Development (2000)

optimzing a query in sql , help!

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-09-29 : 13:33:28

Hi Guys ,

I'm asking help optimzing a SP i wrote , this SP is used many times and causes cpu overload. (SQL is sql 2005.)

I have following table (TABLE has 500k RECORDS and grows by 10k per day)

m_id | m_from_username | m_to_username | m_to_folder
1 | 100 | 5000 | 0
2 | 101 | 22 | 0
3 | 107 | 3000 | 1
4 | 25 | 10 | 2

CREATE PROCEDURE [dbo].[MessagesExist]
(
	@FromUsername int,
	@ToUsername int
) AS
 IF EXISTS(
	SELECT
		m_id
	FROM
		Messages
	WHERE
		@FromUsername = m_from_username AND @ToUsername = m_to_username
	)
	SELECT  CAST(1 AS BIT)
ELSE
	SELECT CAST(0 AS BIT)

Thank you very Much!
Shar

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 13:46:16

If that proc is slow then you don't have an index on m_from_username, m_to_username. Add the index and that will be fast every time.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-09-29 : 14:42:09

thx!

i'm not really a pro in sql , by index do you mean identity column?
Currently "m_id" is the identity column.

if you meant something different with "index" , i would be glad if you can explain or reffer me to some articles .

THANK YOU

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 17:20:01

No, index and identity are very different. Take a look at the CREATE INDEX statement.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-09-29 : 17:48:40

Ok , meanwhile i read alot of material regrading Index's.
http://www.sql-server-performance.com/optimizing_indexes.asp
http://www.databasejournal.com/features/mssql/article.php/1443581

maybe other users will wanna take a look into it also

Thanks for opening my eyes , i understand this is a very impotrant subject.

I added a index to the table for m_from_username called IX_Messages.

What i'm not sure about is, should i create another index for the m_to_username
or should i add the m_to_username column to the IX_Messages index.

Hope you can help me get some deeper understanding in the field.

THANKS!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-09-29 : 18:00:35

i have another query killing my cpu , running on the same table

SELECT
		m_id			AS Id,
		m_from_username	AS FromUsername,
		m_to_username		AS ToUsername,
		m_to_folder		AS ToFolder,
	
	FROM Messages
	AND (@ToUsername IS NULL OR @ToUsername = m_to_username)
	AND (@ToFolder = 0 OR @ToFolder = m_to_folder)
	ORDER BY m_is_read DESC, m_timestamp DESC

do you recommand to create a index from this also or the IX_MESSAGES one should be enough.

thx!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 18:03:55

quote:
I added a index to the table for m_from_username called IX_Messages.

What i'm not sure about is, should i create another index for the m_to_username
or should i add the m_to_username column to the IX_Messages index.

It's difficult to say without knowing more about your data, but in this case I'd say you probably want to add m_to_username to the same index.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 18:07:52

quote:
Originally posted by shaharru

i have another query killing my cpu , running on the same table

SELECT
m_id AS Id,
m_from_username AS FromUsername,
m_to_username AS ToUsername,
m_to_folder AS ToFolder,

FROM Messages
AND (@ToUsername IS NULL OR @ToUsername = m_to_username)
AND (@ToFolder = 0 OR @ToFolder = m_to_folder)
ORDER BY m_is_read DESC, m_timestamp DESC

do you recommand to create a index from this also or the IX_MESSAGES one should be enough.

thx!

You probably need to add a second index for this, by m_to_username, m_to_folder, but now it starts to get tricky because depending on the number of records for each combination you may also need one on m_is_read, m_timestamp.

Given that you're just discovering indexes, you should read some more

about the Index Tuning Wizard - it will suggest all the indexes for you.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-09-30 : 14:20:03

i will read more for sure.

still , i would appreciate if you can help me with this:

SELECT
		m_id			AS Id,
		m_from_username	AS FromUsername,
		m_to_username		AS ToUsername,
		m_to_folder		AS ToFolder,
	
	FROM Messages 
        WHERE
	@ToUsername = 56443
	AND @ToFolder = 1
	ORDER BY m_is_read DESC, m_timestamp DESC

i've added two index's to the table:

index1 = m_from_username + m_to_username
index2 = m_to_username + m_to_folder.

i've looked at the time & cpu this is the resluts:

before the index is was added cpu was ~400.

does this looks reasonble time & cpu?
can i some how make it better?

THX!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-30 : 15:29:32

CPU is not the most important thing to look at. You want to look at the execution plan and the number of reads, which you can do better in Query Analyzer. You want to see index seeks that are looking at a small number of rows rather than table scans which look at every row in the table. There is a graphical Execution Plan, and you can turn on a text based version with various SET statements. Take a look at these articles
http://www.sqlteam.com/item.asp?ItemID=6891
http://www.sql-server-performance.com/query_analyzer_tips.asp

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-10-02 : 12:21:43

Hi !

I have run the Database Tuning Advisor.
The DTA reccomanded to create , 7 new index's and 25 new statistics.

1. Should i follow the DTA instructions by clicking on "Apply Reccomandation" , or those reccomandation's should be deeply analyzed first?

2. CREATE STATISTICS :
the DTA reccomanded to create 25 new staticstics.
Is there a good point in creating addtional statistics other than the statistics , that are automatically created ,when adding a new index ?

an example of the create statistics from the dta:
CREATE STATISTICS [_dta_stat_213575799_13_7_6_9_21_15] ON [dbo].[Users]([u_lastonline], [u_birthdate2], [u_birthdate], [u_active], [u_deleted], [u_profilevisible])

THANKS!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2006-10-03 : 12:03:54

anyone ?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-03 : 14:14:45

I follow the DTA recommendations and then only analyze further if I have a query that still doesn't seem to be performing well.

The statistics are going to help the query optimizer to pick the best execution plan so they are useful to the optimizer even for non-index columns because the optimizer needs to be able to accurately assess the impact of not using an index at all (which for some queries is the right thing to do).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources