SQL Server Performance Forum – Threads Archive

Optimizing a single query. What have i done wrong?

Hi again, I’m still learning how to properly optimize my code and I want to get your opinions on one simple bit of code. There is two tables involved.
TSCORES_FASETT(MP_USER_SID (PFK), FASETT_SID (PFK), TSCORE)
VIEW_REGISTER_FASETT(FASETT_SID (PFK), VIEW_SID(PFK), VERSION(PK)) Indexes:
Both tables have primary keys clustered and no other indexes. Size:
TSCORES_FASETT is currently 10 mill rows and ever increasing.
VIEW_REGISTER_FASETT is about 20 rows and never increasing. The code:
select tf.fasett_sid,
tf.tscore from tscores_fasett tf with (nolock)
joinview_register_fasettvrfwith (nolock)
on tf.fasett_sid = vrf.fasett_sid
and vrf.view_sid in(1,4)
and vrf.version = @version where tf.mp_user_sid = @user_id
This basicly selects the fasett_id and tscore for a spesific user, and only selects those fasetts who is in view_sid 1 and 4 in version x. Then the problem:
Why should this simple bit of code run with reads from 300- 1300?
Shouldn’t this be quite an easy fast running code?
Have i missed some indexes, or structured my code wrong?

So are you querying tables or views? And is MP_USER_SID the first column in any index on TSCORES_FASETT? Perhaps you’re used to Access, where all FK columns get an index without you having to ask for one – that is not the case in SQL Server: you have to create the index yourself.

I think tf.mp_user_sid itself will filter many of the rows,
so you can try keeping that condition after join and vrf table conditions in where clause. Also can check in ITW for any suggestions.

I like the following article for defining sargable and better performing queries. It probably will not help much in this particular query but it is a good resource for any developer. http://www.sql-server-performance.com/transact_sql.asp John John

you cannot possibly be having a problem with this query because your SARG is on the primary key, for a single row, as long as it is indexed, it just doesn’t matter only for queries that must process a significant number of rows does index strategy matter

I was assuming "PFK" meant something different from "PK". If the PK on TSCORES_FASETT covers MP_USER_SID and FASETT_SID, then which column comes first in the definition? If the FASETT_SID comes first, then filtering on MP_USER_SID will not be terribly fast. If you have MP_USER_SID before FASETT_SID in the PK, that might improve performance. If you have a separate index on MP_USER_SID, again that might improve performance.

Thanks for the advice. PFK means its the primary key and also a foreign key.
Mp_user_sid comes first in the definition, then the fasett_sid. "If you have a separate index on MP_USER_SID, again that might improve performance."
The mp_user_sid, fasett_sid clustered index is needed in other queries and cannot be removed or changed. Do you think adding a non clustered index on just mp_user_sid will improve preformance?
I tried to adjust the code according to ranjitains suggestion (code below): But it didn’t really make a difference. Reads around 300. I have to say that the procedure it self runs very fast with both version. My only question is: Is it normal to have 300 reads on such a simple query? select tf.fasett_sid,
tf.tscore
from view_register_fasettvrf with (nolock)
jointscores_fasetttfwith (nolock)
on tf.fasett_sid = vrf.fasett_sid
and tf.mp_user_sid = @user_id where vrf.view_sid in(1,4)
and vrf.version = @version

Oh, if this is of any importance to my question, the query should return about 90-110 rows each time. Anyway, its not a problem procedure, i’m just trying to understand this whole optimizing buisness.

If MP_USER_SID comes first in the PK definition, then it’s already appropriate for your query. Is the PK defined as clustered? If not, then does the table have a clustered index on another (set of) column(s)? Not sure about the number of reads you would expect when filtering on the first column of the PK on a 10 million row table. Might have to do with data distribution (how many rows are matching your current search parameter?). Also, do you have the same data type for both the search parameter @user_id and the MP_USER_SID columns? Implicit conversion can have a big impact on response time.

]]>