SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 optimzing a query in sql , help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaharru
Yak Posting Veteran

72 Posts

Posted - 09/29/2006 :  13:33:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/29/2006 :  13:46:16  Show Profile  Reply with Quote
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.
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 09/29/2006 :  14:42:09  Show Profile  Reply with Quote
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

Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/29/2006 :  17:20:01  Show Profile  Reply with Quote
No, index and identity are very different. Take a look at the CREATE INDEX statement.
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 09/29/2006 :  17:48:40  Show Profile  Reply with Quote
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!
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 09/29/2006 :  18:00:35  Show Profile  Reply with Quote
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!
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/29/2006 :  18:03:55  Show Profile  Reply with Quote
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.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/29/2006 :  18:07:52  Show Profile  Reply with Quote
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.
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 09/30/2006 :  14:20:03  Show Profile  Reply with Quote
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!
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/30/2006 :  15:29:32  Show Profile  Reply with Quote
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
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 10/02/2006 :  12:21:43  Show Profile  Reply with Quote
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!
Go to Top of Page

shaharru
Yak Posting Veteran

72 Posts

Posted - 10/03/2006 :  12:03:54  Show Profile  Reply with Quote
anyone ?
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/03/2006 :  14:14:45  Show Profile  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000