Optimising a proc

[chrissparkle]

I've got a dating site which is starting to buckle under some heavy load. I have a few problem areas but this code in particular is slow.

SELECT Smiles.Viewed,Smiles.SID, Members.MemberID, Members.Nickname, Members.Age, Members.Country, Members.Region, Members.Gender,
Members.Looking, Members.FriendshipSex, Members.DatingSex, Members.Lage1, Members.Lage2, Members.Hage1, Members.Hage2, Members.Title, Members.DateJoined, Members.Updated, Members.Photo, members.online, members.regionN
FROM Smiles WITH (NOLOCK)
INNER JOIN Members WITH (NOLOCK)
ON Smiles.MemberID = members.memberid
Where Smiles.SmileID = @MemberID
Order by Smiles.Viewed,  Smiles.SID DESC

Is there some thing obvious that I'm doing wrong here - is there a way to optimise this code so that it will compute faster? The table isn't exactly huge - the Smiles table has 55,000 rows, the members table has about 100,000.

 

[vbkris]

try splitting the SQL:
from what i see, you want to pick only those ids that match in your parameter in the Smiles table.

therefore a better apporach:


SELECT FilteredTable.Viewed,FilteredTable.SID, Members.MemberID, Members.Nickname, Members.Age, Members.Country, Members.Region, Members.Gender,
Members.Looking, Members.FriendshipSex, Members.DatingSex, Members.Lage1, Members.Lage2, Members.Hage1, Members.Hage2, Members.Title, Members.DateJoined, Members.Updated, Members.Photo, members.online, members.regionN

(select * from Smiles WITH(NOLOCK) where Smiles.SmileID = @MemberID) FilteredTable
INNER JOIN Members WITH (NOLOCK)
ON FilteredTable.MemberID = members.memberid
Order by FilteredTable.Viewed, FilteredTable.SID DESC


this way, you get the filtered rows first THEN do the join.

the next thing that you will have to look into is your query plan. see if you can add indexes to the table in the Viewed column (as there is an order by there).

i am assuming that the IDs are indexes (clustered or otherwise)...

Known is handfull, Unknown is worldfull

 

[chrissparkle]

I'm a bit new to SQL and I'm a little unsure of what exactly an index is and how and when and why I should use it. My smiles table for instance has a primary key which increments with each row. That is all I've done with that table. it has two INT columns (who received the smile and who sent it) and the date it was sent.

Can you explain how I would add an index to this?

 

[vbkris]

indexes have to be looked into in this case from these points:

POINT 1
Smiles.MemberID = members.memberid

The above join has 2 fields. PK fields can be left as they are indexed by default. You must make sure that the other field has a Foriegn Key.

POINT 2
Order by FilteredTable.Viewed, FilteredTable.SID DESC

Order by orders the set based on the data right? an index fastens up the process.


An index is:

http://www.odetocode.com/Articles/70.aspx

this article is pretty good...

Known is handfull, Unknown is worldfull

 

[chrissparkle]

That article was very very helpful - thank you.

In my original SQL I had

Where Smiles.SmileID = @MemberID

To see if understand - might an index be useful on my 'smileid' column? I have unique primary key in my smiles table, but a popular page on my site is this page where the user checks what smiles they've received. would making an index on this column speed up the query?

 

[vbkris]

is SmileID a Primarey Key / Forgien Key???

Known is handfull, Unknown is worldfull

 

[chrissparkle]

smileID is neither.

 

[vbkris]

then i sugest that you create an index on it.

but please note the following:
If that field changes too often then indexing it will prove to be an overhead.

Known is handfull, Unknown is worldfull

 

[chrissparkle]

changes in what way.. there's a fair amount of inserts going into that table (as its people sending smiles to each other). Should i be creating a foreign key on this field?

 

[vbkris]

if it maps to a primary key (that is an ID on another table) then defenitley YES. that itself will solve a lot of issues...

Known is handfull, Unknown is worldfull

 

[chrissparkle]

I've just created the foreign key on my SmileID column linking to MemberID in the members table. Is there anything else I need to change in the proc/sql or is that it as far as creating a foreign key goes?

 

[vbkris]

nope. try the same SQL now...

Known is handfull, Unknown is worldfull

 

[chrissparkle]

I didn't know about the foreign key thing - I didn't know it was something you cuold actually tell SQL server. I thought it was just the term for two ID's in different tables relating to each other. I've got about 20 other tables which I better go and put foreign keys on! Fantastic! Thanks for your help.

 

[vbkris]

so it increased the speed???

Known is handfull, Unknown is worldfull

 

[SQLSister]

Foreign keys are not the same things as indexes. Foreign keys ensure that data that is not inthe primary table cannot be entered into the secondary table. For instance you can't enter a customer order for a customer that does not already exist. It is necessary tor maintaining data integrity.

When a primary key is created, it creates an index as prt of the process. The same does not happen for a foreign key. Any field you are using as a foreign key will need a separate index created for it to imporve performance.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[chrissparkle]

so adding a foreign key won't improve the general performance of the query? is it purely for referential integrity?

 

[vbkris]

>>so adding a foreign key won't improve the general performance of the query? is it purely for referential integrity?

yes it is. therefore my question of:
>>so it increased the speed???

but joining tables on referential integrity will be faster than normal joins (without the referential integrity)...

Known is handfull, Unknown is worldfull