HELP WITH OPTIMIZING SP

erkdahl · May 11, 2006

Hi,

I'm quite new to optimizing Stored procedures, I'm currently developing a application who will be added to a website which have 6000+ daily users, and the current SP will be runned one time for each unique user.
This basicly means it will be runned a lot in the beginning when we release the application, and as time goes by the procedure will be only runned for each new user. 1-300 each day.

When i'm monitoring it with Profiler it takes around 1000-10000 ms in duration, around 300-1000 cpu and has aprox 10000 reads and 16 writes.

I guess the temp tables are partly to blame for bad preformance, but i just cant find a way around them.

I know i'm using old style sql and not joins, and i know i should change it, but i dont think that is the problem with this procedure.

Code follows below, here are some comments of the code:
Each user has one or many sessions (in this case the ALWAYS has one)
Each user has responed to a set of questions. (responsevalue)
The responsevalue is stored with a reference to the session id and the item id (question id)
Each question is connected to many faktors with a given calc_value for each connection
Select nr 1 is calculating scores for the faktores by inserting values in a temp table
It looks kind of like this:
user id:faktor id:question id:responsevalue:calcvalue:score:
111122
112313
121144

etc..
the score in each line is calculated by multiplying the responsevalue with the current calcvalue.

Next we need to calculated user-scores on a faktor level. (tscores)

This basicly means you run this algorithm where sum(score) is the sum of all the scores for the user on this faktor:
((sum(score)-average)/std_deviance)*10+50
The variables average, std_deviance is found in a table called konstanter. Each faktor will have many rows in the konstanter table, but only one for each language (spraak).

What i have done so far is the run the first select into a temp table, then inserting and calculation tscores from the temp table directly into the table where i want the scores.

The table Item_response is very large, it contains 300+ lines for each user.
All other tables except user and session is relativly small (<1000).

I think all the tables have good indexes for this operation

Does anyone have some ideas of what i can do to speed it up?

Any help would be greatly appriciated!
Thanks in advance!

create procedure dbo.CALCULATE_SCORES
(
@user_sid int,
@spraak int

)
as
begin

--SELECT nr. 1

select @user_sid as user_sid, qif.faktor_sid,ir.item_sid, responsevalue, calc_value, (calc_value*responsevalue) as score into #tempscores
from item_response ir with (nolock), session s with (nolock), qstnr_item_faktor qif with (nolock), qstnr q with (nolock)
where s.mp_user_sid = @user_sid and s.session_sid = ir.session_sid
and qif.item_sid = ir.item_sid
and qif.qstnr_sid = q.qstnr_sid
and q.language_sid = @spraak
order by qif.faktor_sid

--delete old scores
delete tscores_faktor where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore) select t.user_sid, t.faktor_sid, ((sum(score)-average)/std_deviance)*10+50
from #tempscores t, konstanter k with (nolock) where t.faktor_sid = k.faktor_sid
and k.language_sid = @spraak
group by t.faktor_sid,average, std_deviance, t.user_sid

drop table #tempscores
end

cmdr_skywalker · May 11, 2006

do you have indexes on those ids? can you paste the execution plan for these queries? whats the total size for each table?
your tempdb can contribute to the performance since you're using temporary tables. However, it can only be one of many. but let us start with the execution plan and indexes.

May the Almighty God bless us all!
www.empoweredinformation.com

mmarovic · May 11, 2006

Without looking further, you must drop select .... into #tempTable code and use create table #tempTable and normal insert. The reason is that each time you use select into, tempdb is locked untill data are populated.

FrankKalis · May 11, 2006

Here's an interesting article about what Mirko mentioned:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243586

Btw, do you need the results to be accurate?
The NOLOCK might be responsible for incorrect resultsets. I'm guessing in your rather statistical case that shouldn't cause a big problem.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

cmdr_skywalker · May 11, 2006

Mirko is right, use create table instead of SELECT INTO. By the way, is the locking of tempdb still true with SQL 2000 SP4? I know its true with v6/7.

May the Almighty God bless us all!
www.empoweredinformation.com

Twan · May 11, 2006

Hi ya,

to pick up on what other have said, select into also causes a stored procedure recompile which can easily be avoided...

I've not checked the syntax but what about something like

declare@tempscores table(
faktor_sidint,
item_sidint,
scorenumeric(10,4),

constraint pk_tempscores primary key( faktor_sid, item_sid )
)

insert into @tempscores
selectqif.faktor_sid,
ir.item_sid,
( calc_value * responsevalue )
from item_response ir,
session s,
qstnr_item_faktor qif,
qstnr q
wheres.mp_user_sid = @user_sid
and s.session_sid = ir.session_sid
andqif.item_sid = ir.item_sid
andqif.qstnr_sid = q.qstnr_sid
andq.language_sid = @spraak

--delete old scores
delete tscores_faktor where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(
mp_user_sid,
faktor_sid,
tscore
) select
@user_sid,
t.faktor_sid,
((sum(t.score)-k.average)/k.std_deviance)*10+50
from @tempscores t,
konstanter k
wheret.faktor_sid = k.faktor_sid
andk.language_sid = @spraak
group by
t.faktor_sid

end

with indexes on
konstanter( faktor_sid, spraak ) ideally clustered
session( mp_user_sid, session_sid ) ideally nonclustered
item_response( session_sid, item_sid ) ideally nonclustered
qstnr_item_faktor( item_sid, qstnr_sid, faktor_sid ) ideally nonclustered
qstnr( qstnr_sid, language_sid ) ideally nonclustered

Cheers
Twan

PS also ensure there is an index on tscores_faktor( mp_user_sid ) ideally nonclustered

ranjitjain · May 12, 2006

check this:

create procedure dbo.CALCULATE_SCORES
(@user_sid int,@spraak int)
as
SET NOCOUNT ON
--delete old scores
delete tscores_faktor where mp_user_sid = @user_sid
--insert new scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore)
select , t.faktor_sid, ((sum(score)-average)/std_deviance)*10+50
from konstanter k with (nolock)
INNER JOIN
(select @user_sid as user_sid,qif.faktor_sid,(calc_value*responsevalue) as score
from item_response ir with (nolock) INNER JOIN
session s with (nolock) ON s.session_sid = ir.session_sid
INNER JOIN qstnr_item_faktor qif with (nolock) ON qif.item_sid = ir.item_sid
INNER JOIN qstnr q with (nolock) ON qif.qstnr_sid = q.qstnr_sid
where s.mp_user_sid = @user_sid
and q.language_sid = @spraak
) t ON
t.faktor_sid = k.faktor_sid
WHERE k.language_sid = @spraak
group by t.faktor_sid,average, std_deviance, t.user_sid

ramkumar.mu · May 12, 2006

quote:Originally posted by cmdr_skywalker

Mirko is right, use create table instead of SELECT INTO. By the way, is the locking of tempdb still true with SQL 2000 SP4? I know its true with v6/7.

May the Almighty God bless us all!
www.empoweredinformation.com

instead of using create, use
SELECT * INTO Table2 FROM Table1 WHERE 1=2
and
use a normal insert so that tempdb is not locked for longer time

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

ranjitjain · May 12, 2006

quote:Originally posted by ramkumar.mu

quote:Originally posted by cmdr_skywalker

Mirko is right, use create table instead of SELECT INTO. By the way, is the locking of tempdb still true with SQL 2000 SP4? I know its true with v6/7.

May the Almighty God bless us all!
www.empoweredinformation.com

instead of using create, use
SELECT * INTO Table2 FROM Table1 WHERE 1=2
and
use a normal insert so that tempdb is not locked for longer time

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Do u mean table2 as permanent table or temporary table.
If it is permanet then don't you think will impact log file.

I think erkdahl should check the eg: given by twan or the one i gave.
no need of temp object

Twan · May 12, 2006

in any case you ALWAYS want to explicitly create temp tables, don't rely on select ... into. It is simply lazy and makes the table creation less explicit. You also always want to create all temporary tables at the start of the procedure. This will reduce procedure recompiles

Cheers
Twan

mmarovic · May 12, 2006

Temp table creation is concurency and recompilation issue and should be resolved. However, from single execution performance point of view you will gain the most if you optimize index design as cmdr and Twan already suggested.

Have you looked at index design proposed by Twan?

erkdahl · May 12, 2006

Big thank you to everyone who has spent time on this subject!

First of, i have now modified the procedure to look like twans example.
The indexes suggested was already in place, except the one on the qstnr table, which is very small anyway (max 10 rows).

Statistics from profiler shows the following result:
Difference in reads - writes - cpu = No major difference. Results still hovering around 5-9000 reads, 50-250 writes, cpu 50-200.
Duration: New duration is around 100-200 ms when the procedure is run by it selv, when the procedure is run multiple times in small spaces of time the duration goes up to 1000-6000 ms.
Difference from old design: It seems to be faster when run alone (old 400-600), but when run multiple times its no big difference.

So there seem to be something to gain by using your design, and I am very grateful for that.

I have not yet tried ranjitjain's design, but i will give it a testdrive to see. I will come back to post results for that.

Other questions:
Q: can you paste the execution plan for these queries?
A: yes, but how to i convert the execution plan to a readable format for you guys? I guess i could do a screenshot, but then vital information when hovering over the steps are missing. Any good suggestions?

Q: whats the total size for each table?
A: Session 50-100.000, item_response (14.000.000 ++), qstnr_item_faktor (400), qstnr(10), konstanter (200), tscores_faktor (5-10.000.000)
The DB where i'm running my scripts has only 5000 sessions and 1.500.000 responses, tscores_faktor only 1-2.000.000

Q:Btw, do you need the results to be accurate? The NOLOCK might be responsible for incorrect resultsets.
A: Data for qstnr_item_faktor, qstnr, konstanter will not change more than once a month. When this happens the applikation goes offline. session and item_responses only allows for inserts and all the inserts for the given user has been completed when this procedure runs. So even though the data has to be accurat I have choosen to go with the nolock option to allow inserts from other users to happen while this calculation is running.

One thing that worries me is that other simple and fast running queries seems to block up and stack up when this procedure is run at the same time. A query with an average duration of 0-10 ms suddenly takes 1000 ms when run right after this procedure.
Even with nolock on all the tables involved.

Thanks again for all your help! I will come back with an update after trying ranjitjain's design.

Adriaan · May 12, 2006

For a textual execution plan, change "Results in Grid" to "Results in Text" on the Query menu of Query Analyzer.

Would it make a difference if you create a temp table where you store the base information for the calculations, then insert from the temp table into the target table? It might also make a difference to do the calculations on the temp table before copying the data into the target table, instead of doing the calculation during the copy action.

erkdahl · May 12, 2006

Hello again!

I have now tried ranjitjain's design and here are the results.
Maybe i have forgotten to mention, but when i run the test i launching 100 users at a random time within 2 mins. These users do a full application run, they post all the responses and has a calculated score in the end.
This means the results may not be correct with a sample as small as this. If lucky those users may calculate the score at completly different times and by that giving me the impression that the procedure is running faster.

However, the results so far shows that ranjitjains design has a avg duration of 270 -600 ms while twans is at 150-400 ms.
Reads, writes, cpu no big difference. And when run multiple times within small spaces of time the procedure takes long on both. 1-3000 ms.

Thanks to both of you guys, but i guess i will try to work with twans example!

ranjitjain · May 12, 2006

Hi,
Thanks for posting your test results.
So the table variables are faster then derived tables.
lets consider twan example.
can you check the code below which i have commented and i think that column is irrelevant here.

declare@tempscores table(
faktor_sidint,
--item_sidint,
scorenumeric(10,4),
constraint pk_tempscores primary key( faktor_sid, item_sid )
)

insert into @tempscores
selectqif.faktor_sid,
--ir.item_sid,
( calc_value * responsevalue )
from item_response ir,
session s,
qstnr_item_faktor qif,
qstnr q
wheres.mp_user_sid = @user_sid
and s.session_sid = ir.session_sid
andqif.item_sid = ir.item_sid
andqif.qstnr_sid = q.qstnr_sid
andq.language_sid = @spraak

--delete old scores
delete tscores_faktor where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(
mp_user_sid,
faktor_sid,
tscore
) select
@user_sid,
t.faktor_sid,
((sum(t.score)-k.average)/k.std_deviance)*10+50
from @tempscores t,
konstanter k
wheret.faktor_sid = k.faktor_sid
andk.language_sid = @spraak
group by
t.faktor_sid

end

erkdahl · May 12, 2006

If you remove the item_sid column from the table variable, you will have a primary key of only faktor_sid and then there will be a problem when inserting rows with the same faktor_sid. (There will be 40 rows in table variable for each faktor_sid).
A solution may be to not putting a primary key on the table variable. Is that advisable?
If so, do you think i will gain any preformance by doing it?

erkdahl · May 12, 2006

Here is a text dump of the execution plan after implementing twans design. StmtText ------------------------------ SET STATISTICS PROFILE ON (1 row(s) affected) StmtText ----------------------- SET SHOWPLAN_ALL ON (1 row(s) affected) StmtText ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- exec calculate_faktor_tscores 10000 create procedure dbo.CALCULATE_FAKTOR_TSCORES -- | created by Erik 06 07 05 | -- ( @user_sid int ) as begin -- kalkuler faktortscores for brukeren declare@tempscores table ( faktor_sid int, item_sid int, score numeric(10,4) , primary key(faktor_sid,item_sid) ) declare@spraak int --henter spraak variabel select @spraak = language_sid from web_user with(nolock) where mp_user_sid = @user_sid (2 row(s) affected) StmtText -------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) (1 row(s) affected) StmtText ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- insert into @tempscores select qif.faktor_sid, ir.item_sid, ( calc_value * responsevalue ) from item_response ir with(nolock), session s with(nolock), qstnr_item_faktor qif with(nolock), qstnr q with(nolock) wheres.mp_user_sid = @user_sid and s.session_sid = ir.session_sid andqif.item_sid = ir.item_sid andqif.qstnr_sid = q.qstnr_sid andq.language_sid = @spraak (1 row(s) affected) StmtText -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />@tempscores), SET<img src='/community/emoticons/emotion-6.gif' alt='' />@tempscores.[score]=[Expr1004], @tempscores.[item_sid]=[ir].[ITEM_SID], @tempscores.[faktor_sid]=[qif].[FAKTOR_SID])) |--Top(ROWCOUNT est 0) |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1004]=Convert([qif].[CALC_VALUE]*Convert([ir].[RESPONSEVALUE])))) |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID])) |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID])) | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID])) | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[PK__ITEM_RESPONSE__7F2BE32F] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD) | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FAKTOR].[PK__QSTNR_ITEM_FAKTO__25518C17] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD) |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) (10 row(s) affected) StmtText ------------------------------------------------------------ delete tscores_faktor where mp_user_sid = @user_sid (1 row(s) affected) StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Clustered Index Delete(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[MP_USER_SID]=[@user_sid])) (1 row(s) affected) StmtText ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ insert into tscores_faktor(mp_user_sid,faktor_sid,tscore) select @user_sid, t.faktor_sid, ((sum(t.score)-k.average)/k.std_deviance)*10+50 from @tempscores t, konstanter k with(nolock) wheret.faktor_sid = k.faktor_sid andk.language_sid = @spraak group by t.faktor_sid, k.average, k.std_deviance (1 row(s) affected) StmtText ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |--Assert(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1011]) AND ([Expr1010] IS NULL)) then 0 else If (NOT([Pass1013]) AND ([Expr1012] IS NULL)) then 1 else NULL)) |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[t].[faktor_sid] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[t].[faktor_sid]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1012] = [PROBE VALUE])) |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1010] = [PROBE VALUE])) | |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[IP_SCORE]=NULL, [TSCORES_FAKTOR].[PREF_TSCORE]=NULL, [TSCORES_FAKTOR].[TSCORE]=[Expr1003], [TSCORES_FAKTOR].[FAKTOR_SID]=[t].[faktor_sid], [TSCORES_FAKTOR].[MP_USER_SID]=RaiseIfNull([@user_sid]))) | | |--Top(ROWCOUNT est 0) | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1003]=Convert(([Expr1002]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50))) | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1002]=If ([Expr1020]=0) then NULL else [Expr1021])) | | |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt='' />[t].[faktor_sid], [k].[AVERAGE], [k].[STD_DEVIANCE]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1020]=COUNT_BIG([t].[score]), [Expr1021]=SUM([t].[score]))) | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt='' />[t].[faktor_sid] ASC, [k].[AVERAGE] ASC, [k].[STD_DEVIANCE] ASC)) | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[FAKTOR_SID])) | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER].[PK__KONSTANTER__19DFD96B] AS [k]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[LANGUAGE_SID]=[@spraak])) | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />@tempscores AS [t]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[t].[faktor_sid]=[k].[FAKTOR_SID]) ORDERED FORWARD) | |--Row Count Spool | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) |--Row Count Spool |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FAKTOR].[PK__FAKTOR__01142BA1]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FAKTOR].[FAKTOR_SID]=[t].[faktor_sid]) ORDERED FORWARD) (16 row(s) affected) StmtText ---------------------------------- SET STATISTICS PROFILE OFF (1 row(s) affected)

erkdahl · May 12, 2006

After running a new test i'm getting results of an average duration of 195 ms on the procedure.
Thats a remarkeble difference from the first draft!
But still a couple of the executions has duration up to max 1600 ms, and dragging some other usally fast running inserts on the item_response table down on the way.

Twan · May 12, 2006

Hi ya,

what is the index on the konstanter table? It looks like it is clustered on language_sid? Could you confirm that you have the indexes I mentioned in the post above?

for those queries that don't run efficiently, could you perhaps post their showplan? and also the messages when you run the queries with set statistics time on and set statistics io on

Cheers
Twan

mmarovic · May 12, 2006

Can you please try implementation bellow:

create procedure dbo.CALCULATE_SCORES
(
@user_sid int,
@spraak int
)
as
begin
--delete old scores
delete tscores_faktor
where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore)
select
@user_sid
qif.faktor_sid,
(sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50
from item_responseir with (nolock)
join session s with (nolock)on s.session_sid = ir.session_sid
join qstnr_item_faktor qif with (nolock)on qif.item_sid = ir.item_sid
join qstnr q with (nolock)on qif.qstnr_sid = q.qstnr_sid
join konstanterk with (nolock)on k.faktor_sid = qif.faktor_sid
where s.mp_user_sid = @user_sid
and q.language_sid = @spraak
and k.language_sid = @spraak
group by qif.faktor_sid
--order by qif.faktor_sid

end

I hope I caught all facts right.
From which table are calc_value and responseValue?
What is clustered index on tscores_faktor table?

Twan · May 13, 2006

Hi ya,

if you're going to try mirko's suggestion which I think is even better than the one I'd suggested, then I'd suggest a minor mod

create procedure dbo.CALCULATE_SCORES
(
@user_sid int,
@spraak int
)
as
begin
--delete old scores
delete tscores_faktor
where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore)
select
@user_sid
qif.faktor_sid,
(sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50
from item_responseir
join session s on s.session_sid = ir.session_sid
and s.mp_user_sid = @user_sid
join qstnr_item_faktor qif
join qstnr q on qif.qstnr_sid = q.qstnr_sid
and q.language_sid = @spraak

join konstanterk on k.faktor_sid = qif.faktor_sid
and k.language_sid = @spraak
on qif.item_sid = ir.item_sid
group by qif.faktor_sid

end

otherwise SQL has a tendency to do the joins first and then the where conditions, which can be very bad for performance

also the nolock option should in my opinion only be used where it is absolutely necessary. I must admit that in over 10 years of working with SQL Server I can probably count on one hand the number of times that I've had to use that in production code

it will still come down to indexes
konstanter( language_sid, faktor_sid ) ideally clustered (can have columns other way around too)
session( mp_user_sid, session_sid ) ideally nonclustered
item_response( session_sid, item_sid ) ideally nonclustered
qstnr_item_faktor( item_sid, qstnr_sid, faktor_sid ) ideally nonclustered
qstnr( qstnr_sid, language_sid ) ideally nonclustered (can have columns other way around too)
tscores_faktor( mp_user_sid ) ideally clustered

The query itself should fly for the number of rows you're talking about. I would have expected sub 100ms

Cheers
Twan

erkdahl · May 13, 2006

You guys are the best! I never expected such massive response!

I havn't tried your latest suggestion twan, but i will try it once the weekend is over and i'm back at work. I will come back with test results then. Same goes for the indexes. I think i got them all, but i will check it again.

One thing i may have forgot to mention is that this procedure is more than just this code and the test results i'm posting is when the whole procedure is runned. In short terms the procedure has 2 main operations. Calculate scores of faktor (the code you guys have been looking at) and Calculate scores of fasett. The fasett calculation is pretty much exactly the same as the faktor calculation, only with connections to fasett tables instead of faktor tables.

All the changes has been done on both parts of the procedure, so the test results should be accurate in regards to the percentage gained compared to the old one, but in reality the reads, writes, duration, etc is half of what i have posted for just this bit of code.

I will come back with results on monday! Cheers!

erkdahl · May 15, 2006

Ok, here is the results from trying some new designs.
First of, I've upped the test scenario to 300 users to do a full application run. The users is started randomly within 2 minutes and each user has completed all task within 20 secs. (When launched we expekt at max 6000 users doing the same within 2 hours. Each user will take 10-30 mins completing the run).

I tried mmarovic's example with some modifications from twan, and implemented them on the code for the whole procedure.
What happened was this:
Reads increased from 7000 to 10000
Writes decreased slightly (average of 20 -> average of 14)
Cpu increased from average 120 to 180
Duration increased from avg of 180 ms to 400 ms.

So from what i can see the first suggestion with table variables is still the fastest.

Next up the indexes, here is what twan suggested:
konstanter( language_sid, faktor_sid ) ideally clustered (can have columns other way around too)
Konstanter (faktor_sid, language_sid) non clustered
Konstanter (fasett_sid, language_sid) non clustered
(There can be only one clustered and konstanter contains data of both faktor and fasett. One of whom will always be NULL.

session( mp_user_sid, session_sid ) ideally nonclustered
Session(mp_user_sid, session_sid) non clustered.

item_response( session_sid, item_sid ) ideally nonclustered
item_response(session_sid, item_sid) non clustered.
(Session_sid and item_sid are the primary keys of this table which also makes the a clustered index right?)

qstnr_item_faktor( item_sid, qstnr_sid, faktor_sid ) ideally nonclustered
qstnr_item_faktor(item_sid, qstnr_sid, faktor_sid) non clustered.
(Item_sid, qstnr_sid and faktor_sid are the primary keys of this table which also makes the a clustered index right?)

qstnr( qstnr_sid, language_sid ) ideally nonclustered (can have columns other way around too)
qstnr(qstnr_sid, language_sid) non clustered.

tscores_faktor( mp_user_sid ) ideally clustered
tscores_faktor(faktor_sid, tscore) non clustered.
(mp_user_sid, faktor_sid are the primary keys and makes them a clustered index right?)
I need the faktor_sid, tscore index for another procedure so i cant remove that. But should i create the table using non clustered primary keys and create a clustered mp_user_sid index? Does the index really matter when inserting rows?

Ok, so for those with special interest here is the whole procedure including the fasett bit. (Just to get the whole picture).

create procedure dbo.CALCULATE_TSCORES
(
@user_sid int,
@spraak int
)
as
begin
set nocount on
declare@tempfaktor table (
faktor_sid int,
item_sid int,
score numeric(10,4) ,
primary key(faktor_sid,item_sid)
)
declare@tempfasett table (
fasett_sid int,
meansnudd decimal(8,4) ,
primary key(fasett_sid)
)

--deleting old scores
delete tscores_faktor where mp_user_sid = @user_sid
delete tscores_fasett where mp_user_sid = @user_sid

--inserting in table variables
insert into @tempfaktor select qif.faktor_sid,
ir.item_sid,
( calc_value * responsevalue )
from item_response ir with(nolock),
session s with(nolock),
qstnr_item_faktor qif with(nolock),
qstnr q with(nolock)
wheres.mp_user_sid = @user_sid
and s.session_sid = ir.session_sid
andqif.item_sid = ir.item_sid
andqif.qstnr_sid = q.qstnr_sid
andq.language_sid = @spraak

insert into tscores_faktor(mp_user_sid,faktor_sid,tscore)
select @user_sid,
t.faktor_sid,
((sum(t.score)-k.average)/k.std_deviance)*10+50
from @tempfaktor t,
konstanter k with(nolock)
wheret.faktor_sid = k.faktor_sid
andk.language_sid = @spraak
group by
t.faktor_sid,
k.average,
k.std_deviance

--Part 2. The fasett bit. Basicly the same, except the calculations are done slightly different.
-- The dbo.get_turned_score returns the correct score to use given the variables inserted.

--inserting fasett temp scores in fasett table variable
insert into @tempfasett select
qif.fasett_sid,
avg(cast(dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid) as decimal(5,2)))
from
item_response ir with(nolock)
inner join
session s with (nolock)
on s.session_sid = ir.session_sid
inner join
qstnr_item_fasett qif with (nolock)
on qif.item_sid = ir.item_sid
inner join
qstnr q with (nolock)
on qif.qstnr_sid = q.qstnr_sid
where
s.mp_user_sid = @user_sid
and q.language_sid = @spraak
group by qif.fasett_sid
order by qif.fasett_sid

--For all the scores in the table variable where there is found konstants we insert in tscores_fasett using this line
insert into tscores_fasett(mp_user_sid, fasett_sid, tscore)
select @user_sid,
t.fasett_sid,
((t.meansnudd-k.average)/k.std_deviance)*10+50
from @tempfasett t
inner join
konstanter k with (nolock)
on k.fasett_sid = t.fasett_sid
where
k.language_sid = @spraak
group by
t.fasett_sid,
average,
std_deviance,
t.meansnudd

--For all the scores in the table variable where there is no konstant we have to search the lookup table for the right tscore
insert into tscores_fasett(mp_user_sid, fasett_sid, tscore)
select @user_sid,
t.fasett_sid,
l.tscore
from @tempfasett t
inner join
lookup l with (nolock)
on t.fasett_sid = l.fasett_sid
where
l.language_sid = @spraak
and t.meansnudd between l.min_value and l.max_value

end

So, what do you think?
Is there a way I can gain even more preformance?
Maybe its all down to my fairly amaturish way with indexes?
I'm still using the with (nolock) option. Even though i cant see any difference in preformance with or without it.
One other thing to point out is that all the tests are run on a SQL-server with limited resources (bad hardware), and the production server will be much faster.

mmarovic · May 15, 2006

I would still like to try my original implementation. While I agree with Twan that putting language_sid = @spaark in the join clause might potentialy improve execution plan, I am afraid that putting the condition s.mp_user_sid = @user_sid there could made it worst. My guess is that this condition filters out the most rows from resultset so instead as a join condition it should be used in where clause. However I can only guess without seeing the exectuin plan.

mmarovic · May 15, 2006

Here is the version I would like to be tested:

create procedure dbo.CALCULATE_SCORES
(
@user_sid int,
@spraak int
)
as
begin
--delete old scores
delete tscores_faktor
where mp_user_sid = @user_sid

--insert new scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore)
select
@user_sid
qif.faktor_sid,
(sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50
from session s with (nolock)
inner loop join item_responseirwith (nolock)on s.session_sid = ir.session_sid
join qstnr_item_faktor qif with (nolock)on qif.item_sid = ir.item_sid
join qstnr q with (nolock)on qif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak
join konstanterk with (nolock)on k.faktor_sid = qif.faktor_sid and k.language_sid = @spraak
where s.mp_user_sid = @user_sid
group by qif.faktor_sid
option (force order)

end

mmarovic · May 15, 2006

About indexes:

They look fine. If session_sid is primary clustered key on the session table, then you don't need session_sid as the second column of index on mp_user_sid. However, I don't expect dramatic impact of removing that column from the index. Also that second column may be usefull for different query (if there are where conditions on both columns).

erkdahl · May 15, 2006

Ok. Here is code, executementplan and results from your suggestion: First of, results: Reads avg 11500 writes avg 35 Cpu avg 161 Duration avg 933 ms I still like the results from table variable procedure better... The new code: <pre id="code"> create procedure dbo.CALCULATE_TSCORES ( @user_sid int, @spraak int ) as begin set nocount on delete tscores_faktor where mp_user_sid = @user_sid delete tscores_fasett where mp_user_sid = @user_sid insert into tscores_faktor(mp_user_sid, faktor_sid, tscore) select @user_sid, qif.faktor_sid, ((sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50 from sessions with (nolock) inner loop join item_responseirwith (nolock) on s.session_sid = ir.session_sid join qstnr_item_faktor qif with (nolock) on qif.item_sid = ir.item_sid join qstnr q with (nolock) on qif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join konstanterk with (nolock) on k.faktor_sid = qif.faktor_sid and k.language_sid = @spraak where s.mp_user_sid = @user_sid group by qif.faktor_sid, k.average, k.std_deviance option (force order) insert into tscores_fasett(mp_user_sid, fasett_sid, tscore) select@user_sid, qif.fasett_sid, ((avg(cast(dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid) as decimal(5,2)))-k.average)/k.std_deviance)*10+50 from session swith (nolock) inner loop join item_response ir with (nolock) ons.session_sid = ir.session_sid join qstnr_item_fasett qif with (nolock) on qif.item_sid = ir.item_sid joinqstnrq with (nolock) onqif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join konstanter k with (nolock) on k.fasett_sid = qif.fasett_sid and k.language_sid = @spraak where s.mp_user_sid = @user_sid group by qif.fasett_sid, k.average, k.std_deviance option (force order) insert into tscores_fasett(mp_user_sid, fasett_sid, tscore) select @user_sid, qif.fasett_sid, l.tscore from sessionswith(nolock) inner loop joinitem_responseirwith(nolock) on s.session_sid = ir.session_sid join qstnr_item_fasett qifwith(nolock) on qif.item_sid = ir.item_sid joinqstnrqwith(nolock) on qif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join lookuplwith(nolock) on qif.fasett_sid = l.fasett_sid andl.language_sid = @spraak wheres.mp_user_sid = @user_sid and cast(dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid) as decimal(5,2)) between l.min_value and l.max_value option (force order) end </pre id="code"> Executementplan: <pre id="code"> StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ --------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------ exec calculate_tscores 10000,1 1 1 0 NULL NULL 1 NULL NULL NULL NULL NULL NULL NULL NULL EXECUTE 0 NULL create procedure dbo.CALCULATE_TSCORES -- | created by Erik 06 07 05 | -- ( @user_sid int, @spraak int ) as begin -- kalkuler faktortscores for brukeren set nocount on --slett gammel data 2 2 1 NULL NULL 4 NULL NULL NULL NULL NULL NULL NULL NULL SETON 0 NULL delete tscores_faktor where mp_user_sid = @user_sid 3 3 1 NULL NULL 5 NULL 5.0 NULL NULL NULL 1.6460078E-2 NULL NULL DELETE 0 NULL |--Clustered Index Delete(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[MP_USER_SID]=[@user_sid])) 3 4 3 Clustered Index Delete Delete OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[MP_USER_SID]=[@user_sid]) NULL 5.0 1.0041578E-2 4.9999999E-6 4 1.6460078E-2 NULL NULL PLAN_ROW 0 1.0 delete tscores_fasett where mp_user_sid = @user_sid --sett inn ny data 4 5 1 NULL NULL 6 NULL 76.278488 NULL NULL NULL 1.6624641E-2 NULL NULL DELETE 0 NULL |--Clustered Index Delete(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[MP_USER_SID]=[@user_sid])) 4 6 5 Clustered Index Delete Delete OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[MP_USER_SID]=[@user_sid]) NULL 76.278488 1.0042201E-2 7.6278491E-5 4 1.6624641E-2 NULL NULL PLAN_ROW 0 1.0 insert into tscores_faktor(mp_user_sid, faktor_sid, tscore) select @user_sid, qif.faktor_sid, ((sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50 from sessions with (nolock) inner loop join item_responseirwith (nolock) on s.session_sid = ir.session_sid join qstnr_item_faktor qif with (nolock) on qif.item_sid = ir.item_sid join qstnr q with (nolock) on qif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join konstanterk with (nolock) on k.faktor_sid = qif.faktor_sid and k.language_sid = @spraak where s.mp_user_sid = @user_sid group by qif.faktor_sid, k.average, k.std_deviance option (force order) 5 7 1 NULL NULL 9 NULL 176.92372 NULL NULL NULL 0.1452508 NULL NULL INSERT 0 NULL |--Assert(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 0 else If (NOT([Pass1016]) AND ([Expr1015] IS NULL)) then 1 else NULL)) 5 8 7 Assert Assert WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 0 else If (NOT([Pass1016]) AND ([Expr1015] IS NULL)) then 1 else NULL) NULL 176.92372 0.0 2.5795476E-5 32 0.1452508 NULL NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1015] = [PROBE VALUE])) 5 9 8 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1015] = [PROBE VALUE]) [Expr1015] = [PROBE VALUE], [Pass1016] = [PASSTHRU VALUE] 143.3082 0.0 6.6558697E-4 32 0.145225 [Expr1013], [Pass1014], [Expr1015], [Pass1016] NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE])) 5 10 9 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE]) [Expr1013] = [PROBE VALUE], [Pass1014] = [PASSTHRU VALUE] 159.23134 0.0 7.3954114E-4 32 0.13748905 [qif].[FAKTOR_SID], [Expr1013], [Pass1014] NULL PLAN_ROW 0 1.0 | |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[IP_SCORE]=NULL, [TSCORES_FAKTOR].[PREF_TSCORE]=NULL, [TSCORES_FAKTOR].[TSCORE]=[Expr1006], [TSCORES_FAKTOR].[FAKTOR_SID]=[qif].[FAKTOR_SID], [TSCORES_FAKTOR].[MP_USER_SID]=RaiseIfNull([@user_sid]))) 5 11 10 Clustered Index Insert Insert OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FAKTOR].[PK__TSCORES_FAKTOR__1BC821DD]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FAKTOR].[IP_SCORE]=NULL, [TSCORES_FAKTOR].[PREF_TSCORE]=NULL, [TSCORES_FAKTOR].[TSCORE]=[Expr1006], [TSCORES_FAKTOR].[FAKTOR_SID]=[qif].[FAKTOR_SID], [TSCORES_FAKTOR].[MP_USER_SID]=RaiseIfNull([@user_sid])) NULL 176.92372 1.0041578E-2 1.7692371E-4 31 0.13030592 [qif].[FAKTOR_SID] NULL PLAN_ROW 0 1.0 | | |--Top(ROWCOUNT est 0) 5 12 11 Top Top NULL NULL 176.92372 0.0 1.7692371E-5 16 0.12008742 [qif].[FAKTOR_SID], [Expr1006] NULL PLAN_ROW 0 1.0 | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50))) 5 13 12 Compute Scalar Compute Scalar DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50)) [Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50) 176.92372 0.0 1.7692371E-5 16 0.12006973 [qif].[FAKTOR_SID], [Expr1006] NULL PLAN_ROW 0 1.0 | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1005]=If ([Expr1017]=0) then NULL else [Expr1018])) 5 14 13 Compute Scalar Compute Scalar DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1005]=If ([Expr1017]=0) then NULL else [Expr1018]) [Expr1005]=If ([Expr1017]=0) then NULL else [Expr1018] 176.92372 0.0 1.3523122E-3 46 0.12005203 [qif].[FAKTOR_SID], [k].[STD_DEVIANCE], [k].[AVERAGE], [Expr1005] NULL PLAN_ROW 0 1.0 | | |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID], [k].[AVERAGE], [k].[STD_DEVIANCE]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1017]=COUNT_BIG([qif].[CALC_VALUE]*Convert([ir].[RESPONSEVALUE])), [Expr1018]=SUM([qif].[CALC_VALUE]*Convert([ir].[RESPONSEVALUE])))) 5 15 14 Stream Aggregate Aggregate GROUP BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID], [k].[AVERAGE], [k].[STD_DEVIANCE]) [Expr1017]=COUNT_BIG([qif].[CALC_VALUE]*Convert([ir].[RESPONSEVALUE])), [Expr1018]=SUM([qif].[CALC_VALUE]*Convert([ir].[RESPONSEVALUE])) 176.92372 0.0 1.3523122E-3 46 0.12005203 [qif].[FAKTOR_SID], [k].[STD_DEVIANCE], [k].[AVERAGE], [Expr1017], [Expr1018] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID] ASC, [k].[AVERAGE] ASC, [k].[STD_DEVIANCE] ASC)) 5 16 15 Sort Sort ORDER BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID] ASC, [k].[AVERAGE] ASC, [k].[STD_DEVIANCE] ASC) NULL 189.14888 1.1261261E-2 2.3317432E-3 42 0.11869972 [ir].[RESPONSEVALUE], [qif].[FAKTOR_SID], [qif].[CALC_VALUE], [k].[STD_DEVIANCE], [k].[AVERAGE] NULL PLAN_ROW 0 1.0 | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER] AS [k])) 5 17 16 Bookmark Lookup Bookmark Lookup BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER] AS [k]) [k].[STD_DEVIANCE], [k].[AVERAGE] 189.14888 6.2500001E-3 2.0806378E-4 180 0.10510672 [ir].[RESPONSEVALUE], [qif].[FAKTOR_SID], [qif].[CALC_VALUE], [k].[STD_DEVIANCE], [k].[AVERAGE] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID])) 5 18 17 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FAKTOR_SID]) NULL 189.14888 0.0 7.9064234E-4 158 9.8648652E-2 [ir].[RESPONSEVALUE], [qif].[FAKTOR_SID], [qif].[CALC_VALUE], [Bmk1004] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID])) 5 19 18 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID]) NULL 189.14888 0.0 7.9064234E-4 129 7.6393433E-2 [ir].[RESPONSEVALUE], [qif].[FAKTOR_SID], [qif].[CALC_VALUE] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID])) 5 20 19 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID]) NULL 189.14888 0.0 1.0651865E-3 105 5.4138217E-2 [ir].[RESPONSEVALUE], [qif].[QSTNR_SID], [qif].[FAKTOR_SID], [qif].[CALC_VALUE] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID])) 5 21 20 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID]) NULL 254.8293 0.0 3.1955594E-3 63 2.6302356E-2 [ir].[ITEM_SID], [ir].[RESPONSEVALUE] NULL PLAN_ROW 0 1.0 | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 5 22 21 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD .[SESSION_SID] 1.0 6.3284999E-3 7.9603E-5 33 6.4081028E-3 .[SESSION_SID] NULL PLAN_ROW 0 1.0 | | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[PK__ITEM_RESPONSE__7F2BE32F] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD) 5 23 21 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[PK__ITEM_RESPONSE__7F2BE32F] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD [ir].[ITEM_SID], [ir].[RESPONSEVALUE] 254.8293 7.8099817E-3 3.5957672E-4 39 1.6698694E-2 [ir].[ITEM_SID], [ir].[RESPONSEVALUE] NULL PLAN_ROW 0 3.0 | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FAKTOR].[PK__QSTNR_ITEM_FAKTO__25518C17] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD) 5 24 20 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FAKTOR].[PK__QSTNR_ITEM_FAKTO__25518C17] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD [qif].[QSTNR_SID], [qif].[FAKTOR_SID], [qif].[CALC_VALUE] 1.0 6.3284999E-3 7.9603E-5 50 2.6770677E-2 [qif].[QSTNR_SID], [qif].[FAKTOR_SID], [qif].[CALC_VALUE] NULL PLAN_ROW 0 254.8293 | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 5 25 19 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9607002E-5 33 2.1464575E-2 NULL NULL PLAN_ROW 0 189.14888 | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER].[KONSTANT_FAKTOR_LANGUAGE] AS [k]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[FAKTOR_SID]=[qif].[FAKTOR_SID] AND [k].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 5 26 18 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER].[KONSTANT_FAKTOR_LANGUAGE] AS [k]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[FAKTOR_SID]=[qif].[FAKTOR_SID] AND [k].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD [Bmk1004] 1.0 6.3284999E-3 7.9607002E-5 37 2.1464575E-2 [Bmk1004] NULL PLAN_ROW 0 189.14888 | |--Row Count Spool 5 60 10 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 0.0000001 4 6.4082029E-3 NULL NULL PLAN_ROW 0 176.92372 | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 5 61 60 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 6.4081028E-3 NULL NULL PLAN_ROW 0 1.0 |--Row Count Spool 5 62 9 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 0.0000001 4 7.0385248E-3 NULL NULL PLAN_ROW 0 159.23134 |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FAKTOR].[PK__FAKTOR__01142BA1]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FAKTOR].[FAKTOR_SID]=[qif].[FAKTOR_SID]) ORDERED FORWARD) 5 63 62 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FAKTOR].[PK__FAKTOR__01142BA1]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FAKTOR].[FAKTOR_SID]=[qif].[FAKTOR_SID]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 7.037825E-3 NULL NULL PLAN_ROW 0 7.0 insert into tscores_fasett(mp_user_sid, fasett_sid, tscore) select@user_sid, qif.fasett_sid, ((avg(cast(dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid) as decimal(5,2)))-k.average)/k.std_deviance)*10+50 from session swith (nolock) inner loop join item_response ir with (nolock) ons.session_sid = ir.session_sid join qstnr_item_fasett qif with (nolock) on qif.item_sid = ir.item_sid joinqstnrq with (nolock) onqif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join konstanter k with (nolock) on k.fasett_sid = qif.fasett_sid and k.language_sid = @spraak where s.mp_user_sid = @user_sid group by qif.fasett_sid, k.average, k.std_deviance option (force order) 6 64 1 NULL NULL 10 NULL 83.126381 NULL NULL NULL 0.13780145 NULL NULL INSERT 0 NULL |--Assert(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 0 else If (NOT([Pass1016]) AND ([Expr1015] IS NULL)) then 1 else NULL)) 6 65 64 Assert Assert WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 0 else If (NOT([Pass1016]) AND ([Expr1015] IS NULL)) then 1 else NULL) NULL 83.126381 0.0 1.2119825E-5 32 0.13780145 NULL NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1015] = [PROBE VALUE])) 6 66 65 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1015] = [PROBE VALUE]) [Expr1015] = [PROBE VALUE], [Pass1016] = [PASSTHRU VALUE] 67.332367 0.0 3.1272144E-4 32 0.13778932 [Expr1013], [Pass1014], [Expr1015], [Pass1016] NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE])) 6 67 66 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE]) [Expr1013] = [PROBE VALUE], [Pass1014] = [PASSTHRU VALUE] 74.813736 0.0 3.4746827E-4 32 0.12796313 [qif].[FASETT_SID], [Expr1013], [Pass1014] NULL PLAN_ROW 0 1.0 | |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[IP_TSCORE]=NULL, [TSCORES_FASETT].[PREF_TSCORE]=NULL, [TSCORES_FASETT].[TSCORE]=[Expr1006], [TSCORES_FASETT].[FASETT_SID]=[qif].[FASETT_SID], [TSCORES_FASETT].[MP_USER_SID]=RaiseIfNull([@user_sid]))) 6 68 67 Clustered Index Insert Insert OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[IP_TSCORE]=NULL, [TSCORES_FASETT].[PREF_TSCORE]=NULL, [TSCORES_FASETT].[TSCORE]=[Expr1006], [TSCORES_FASETT].[FASETT_SID]=[qif].[FASETT_SID], [TSCORES_FASETT].[MP_USER_SID]=RaiseIfNull([@user_sid])) NULL 83.126381 1.0042201E-2 8.3126382E-5 31 0.12119083 [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | |--Top(ROWCOUNT est 0) 6 69 68 Top Top NULL NULL 83.126381 0.0 8.3126379E-6 16 0.1110655 [qif].[FASETT_SID], [Expr1006] NULL PLAN_ROW 0 1.0 | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50))) 6 70 69 Compute Scalar Compute Scalar DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50)) [Expr1006]=Convert(([Expr1005]-[k].[AVERAGE])/[k].[STD_DEVIANCE]*10+50) 83.126381 0.0 8.3126379E-6 16 0.11105719 [qif].[FASETT_SID], [Expr1006] NULL PLAN_ROW 0 1.0 | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1005]=If ([Expr1017]=0) then NULL else ([Expr1018]/Convert([Expr1017])))) 6 71 70 Compute Scalar Compute Scalar DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1005]=If ([Expr1017]=0) then NULL else ([Expr1018]/Convert([Expr1017]))) [Expr1005]=If ([Expr1017]=0) then NULL else ([Expr1018]/Convert([Expr1017])) 83.126381 0.0 6.1931537E-4 46 0.11104888 [qif].[FASETT_SID], [k].[STD_DEVIANCE], [k].[AVERAGE], [Expr1005] NULL PLAN_ROW 0 1.0 | | |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID], [k].[AVERAGE], [k].[STD_DEVIANCE]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1017]=COUNT_BIG(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID]))), [Expr1018]=SUM(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID]))))) 6 72 71 Stream Aggregate Aggregate GROUP BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID], [k].[AVERAGE], [k].[STD_DEVIANCE]) [Expr1017]=COUNT_BIG(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID]))), [Expr1018]=SUM(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID]))) 83.126381 0.0 6.1931537E-4 46 0.11104888 [qif].[FASETT_SID], [k].[STD_DEVIANCE], [k].[AVERAGE], [Expr1017], [Expr1018] NULL PLAN_ROW 0 1.0 | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] ASC, [k].[AVERAGE] ASC, [k].[STD_DEVIANCE] ASC)) 6 73 72 Sort Sort ORDER BY<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] ASC, [k].[AVERAGE] ASC, [k].[STD_DEVIANCE] ASC) NULL 83.134895 1.1261261E-2 9.2708546E-4 41 0.11042956 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID], [k].[STD_DEVIANCE], [k].[AVERAGE] NULL PLAN_ROW 0 1.0 | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER] AS [k])) 6 74 73 Bookmark Lookup Bookmark Lookup BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER] AS [k]) [k].[STD_DEVIANCE], [k].[AVERAGE] 83.134895 6.2500001E-3 9.1448383E-5 167 9.8241217E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID], [k].[STD_DEVIANCE], [k].[AVERAGE] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID])) 6 75 74 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]) NULL 83.134895 0.0 4.6413697E-4 145 9.1899768E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID], [Bmk1004] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID])) 6 76 75 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID]) NULL 111.03756 0.0 9.2827395E-4 116 7.6189265E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID])) 6 77 76 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID]) NULL 222.0751 0.0 1.0651865E-3 92 5.1175255E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID])) 6 78 77 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID]) NULL 254.8293 0.0 3.1955594E-3 59 2.3339393E-2 [ir].[ITEM_SID], [ir].[SESSION_SID] NULL PLAN_ROW 0 1.0 | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 6 79 78 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD .[SESSION_SID] 1.0 6.3284999E-3 7.9603E-5 33 6.4081028E-3 .[SESSION_SID] NULL PLAN_ROW 0 1.0 | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[session_item] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD) 6 80 78 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[session_item] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD [ir].[ITEM_SID], [ir].[SESSION_SID] 254.8293 6.3284999E-3 3.5957672E-4 35 0.01373573 [ir].[ITEM_SID], [ir].[SESSION_SID] NULL PLAN_ROW 0 3.0 | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FASETT].[iqf_fas] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD) 6 81 77 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FASETT].[iqf_fas] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD [qif].[QSTNR_SID], [qif].[FASETT_SID] 1.0 6.3284999E-3 7.9603E-5 41 2.6770677E-2 [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 254.8293 | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 6 82 76 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9607002E-5 33 2.4085732E-2 NULL NULL PLAN_ROW 0 222.0751 | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER].[KONSTANT_FASETT_LANGUAGE] AS [k]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[FASETT_SID]=[qif].[FASETT_SID] AND [k].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 6 83 75 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[KONSTANTER].[KONSTANT_FASETT_LANGUAGE] AS [k]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[k].[FASETT_SID]=[qif].[FASETT_SID] AND [k].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD [Bmk1004] 1.0 6.3284999E-3 7.9607002E-5 37 1.5246367E-2 [Bmk1004] NULL PLAN_ROW 0 111.03756 | |--Row Count Spool 6 124 67 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 0.0000001 4 6.4082029E-3 NULL NULL PLAN_ROW 0 83.126381 | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 6 125 124 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 6.4081028E-3 NULL NULL PLAN_ROW 0 1.0 |--Row Count Spool 6 126 66 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 0.0000001 4 9.4985189E-3 NULL NULL PLAN_ROW 0 74.813736 |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FASETT].[PK__FASETT__02FC7413]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FASETT].[FASETT_SID]=[qif].[FASETT_SID]) ORDERED FORWARD) 6 127 126 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FASETT].[PK__FASETT__02FC7413]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FASETT].[FASETT_SID]=[qif].[FASETT_SID]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 9.494639E-3 NULL NULL PLAN_ROW 0 38.798439 insert into tscores_fasett(mp_user_sid, fasett_sid, tscore) select @user_sid, qif.fasett_sid, l.tscore from sessionswith(nolock) inner loop joinitem_responseirwith(nolock) on s.session_sid = ir.session_sid join qstnr_item_fasett qifwith(nolock) on qif.item_sid = ir.item_sid joinqstnrqwith(nolock) on qif.qstnr_sid = q.qstnr_sid and q.language_sid = @spraak join lookuplwith(nolock) on qif.fasett_sid = l.fasett_sid andl.language_sid = @spraak wheres.mp_user_sid = @user_sid and cast(dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid) as decimal(5,2)) between l.min_value and l.max_value option (force order) 7 128 1 NULL NULL 11 NULL 19.086903 NULL NULL NULL 0.14279757 NULL NULL INSERT 0 NULL |--Assert(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1012]) AND ([Expr1011] IS NULL)) then 0 else If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 1 else NULL)) 7 129 128 Assert Assert WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />If (NOT([Pass1012]) AND ([Expr1011] IS NULL)) then 0 else If (NOT([Pass1014]) AND ([Expr1013] IS NULL)) then 1 else NULL) NULL 19.086903 0.0 2.7828703E-6 32 0.14279757 NULL NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE])) 7 130 129 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID] IS NULL)OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1013] = [PROBE VALUE]) [Expr1013] = [PROBE VALUE], [Pass1014] = [PASSTHRU VALUE] 15.460391 0.0 7.1804927E-5 32 0.14279479 [Expr1011], [Pass1012], [Expr1013], [Pass1014] NULL PLAN_ROW 0 1.0 |--Nested Loops(Left Semi Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1011] = [PROBE VALUE])) 7 131 130 Nested Loops Left Semi Join WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />[@user_sid] IS NULL), DEFINE<img src='/community/emoticons/emotion-6.gif' alt='' />[Expr1011] = [PROBE VALUE]) [Expr1011] = [PROBE VALUE], [Pass1012] = [PASSTHRU VALUE] 17.178213 0.0 7.9783254E-5 32 0.1358501 [qif].[FASETT_SID], [Expr1011], [Pass1012] NULL PLAN_ROW 0 1.0 | |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[IP_TSCORE]=NULL, [TSCORES_FASETT].[PREF_TSCORE]=NULL, [TSCORES_FASETT].[TSCORE]=[l].[TSCORE], [TSCORES_FASETT].[FASETT_SID]=[qif].[FASETT_SID], [TSCORES_FASETT].[MP_USER_SID]=RaiseIfNull([@user_sid]))) 7 132 131 Clustered Index Insert Insert OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[TSCORES_FASETT].[PK__TSCORES_FASETT__160F4887]), SET<img src='/community/emoticons/emotion-6.gif' alt='' />[TSCORES_FASETT].[IP_TSCORE]=NULL, [TSCORES_FASETT].[PREF_TSCORE]=NULL, [TSCORES_FASETT].[TSCORE]=[l].[TSCORE], [TSCORES_FASETT].[FASETT_SID]=[qif].[FASETT_SID], [TSCORES_FASETT].[MP_USER_SID]=RaiseIfNull([@user_sid])) NULL 19.086903 1.0042201E-2 1.9086903E-5 31 0.12935831 [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | |--Top(ROWCOUNT est 0) 7 133 132 Top Top NULL NULL 19.086903 0.0 1.9086904E-6 16 0.11929701 [qif].[FASETT_SID], [l].[TSCORE] NULL PLAN_ROW 0 1.0 | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />Convert(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID])))>=Convert([l].[MIN_VALUE]) AND Convert(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID])))<=Convert([l].[MAX_VALUE]))) 7 135 133 Filter Filter WHERE<img src='/community/emoticons/emotion-6.gif' alt='' />Convert(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID])))>=Convert([l].[MIN_VALUE]) AND Convert(Convert([dbo].[get_turned_score]([ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID])))<=Convert([l].[MAX_VALUE])) NULL 19.086903 0.0 4.8353485E-4 158 0.1192932 [qif].[FASETT_SID], [l].[TSCORE] NULL PLAN_ROW 0 1.0 | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[LOOKUP] AS [l])) 7 136 135 Bookmark Lookup Bookmark Lookup BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt='' />[Bmk1004]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[LOOKUP] AS [l]) [l].[MAX_VALUE], [l].[MIN_VALUE], [l].[TSCORE] 212.07669 0.025 2.3328436E-4 158 0.11880966 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID], [l].[MAX_VALUE], [l].[MIN_VALUE], [l].[TSCORE] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID])) 7 137 136 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[FASETT_SID]) NULL 212.07669 0.0 1.7276321E-3 145 9.3576379E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID], [Bmk1004] NULL PLAN_ROW 0 1.0 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID])) 7 138 137 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[QSTNR_SID]) NULL 111.03756 0.0 9.2827395E-4 116 7.6189265E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID])) 7 139 138 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[ITEM_SID]) NULL 222.0751 0.0 1.0651865E-3 92 5.1175255E-2 [ir].[ITEM_SID], [ir].[SESSION_SID], [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 1.0 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID])) 7 140 139 Nested Loops Inner Join OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt='' />.[SESSION_SID]) NULL 254.8293 0.0 3.1955594E-3 59 2.3339393E-2 [ir].[ITEM_SID], [ir].[SESSION_SID] NULL PLAN_ROW 0 1.0 | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 7 141 140 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[SESSION].[UserSidSession] AS ), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />.[MP_USER_SID]=[@user_sid]) ORDERED FORWARD .[SESSION_SID] 1.0 6.3284999E-3 7.9603E-5 33 6.4081028E-3 .[SESSION_SID] NULL PLAN_ROW 0 1.0 | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[session_item] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD) 7 142 140 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[ITEM_RESPONSE].[session_item] AS [ir]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[ir].[SESSION_SID]=.[SESSION_SID]) ORDERED FORWARD [ir].[ITEM_SID], [ir].[SESSION_SID] 254.8293 6.3284999E-3 3.5957672E-4 35 0.01373573 [ir].[ITEM_SID], [ir].[SESSION_SID] NULL PLAN_ROW 0 3.0 | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FASETT].[iqf_fas] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD) 7 143 139 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR_ITEM_FASETT].[iqf_fas] AS [qif]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[qif].[ITEM_SID]=[ir].[ITEM_SID]) ORDERED FORWARD [qif].[QSTNR_SID], [qif].[FASETT_SID] 1.0 6.3284999E-3 7.9603E-5 41 2.6770677E-2 [qif].[QSTNR_SID], [qif].[FASETT_SID] NULL PLAN_ROW 0 254.8293 | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 7 144 138 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[QSTNR].[qst_lang] AS [q]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[q].[QSTNR_SID]=[qif].[QSTNR_SID] AND [q].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9607002E-5 33 2.4085732E-2 NULL NULL PLAN_ROW 0 222.0751 | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[LOOKUP].[FASETT_LANGUAGE] AS [l]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[l].[FASETT_SID]=[qif].[FASETT_SID] AND [l].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD) 7 145 137 Index Seek Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[LOOKUP].[FASETT_LANGUAGE] AS [l]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[l].[FASETT_SID]=[qif].[FASETT_SID] AND [l].[LANGUAGE_SID]=[@spraak]) ORDERED FORWARD [Bmk1004] 3.7222462 6.3284999E-3 8.2620529E-5 37 1.5659481E-2 [Bmk1004] NULL PLAN_ROW 0 111.03756 | |--Row Count Spool 7 168 131 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 0.0000001 4 6.4082029E-3 NULL NULL PLAN_ROW 0 19.086903 | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD) 7 169 168 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[WEB_USER].[PK__WEB_USER__778AC167]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[WEB_USER].[MP_USER_SID]=[@user_sid]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 6.4081028E-3 NULL NULL PLAN_ROW 0 1.0 |--Row Count Spool 7 170 130 Row Count Spool Lazy Spool NULL NULL 1.0 0.0 1.496194E-4 4 6.8694437E-3 NULL NULL PLAN_ROW 0 17.178213 |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FASETT].[PK__FASETT__02FC7413]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FASETT].[FASETT_SID]=[qif].[FASETT_SID]) ORDERED FORWARD) 7 171 170 Clustered Index Seek Clustered Index Seek OBJECT<img src='/community/emoticons/emotion-6.gif' alt='' />[hc-match#08].[dbo].[FASETT].[PK__FASETT__02FC7413]), SEEK<img src='/community/emoticons/emotion-6.gif' alt='' />[FASETT].[FASETT_SID]=[qif].[FASETT_SID]) ORDERED FORWARD NULL 1.0 6.3284999E-3 7.9603E-5 29 6.6458089E-3 NULL NULL PLAN_ROW 0 2.0475099 (75 row(s) affected) </pre id="code">

mmarovic · May 15, 2006

quote:I still like the results from table variable procedure better...
That's fine, I just wanted to see what happens when execution plan I had in mind was tested.

I see udf call in another part of the procedure. This is the place where improvements are possible.

erkdahl · May 15, 2006

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mmarovic <blockquote id="quote">quote:<hr height="1" noshade id="quote">I still like the results from table variable procedure better...<hr height="1" noshade id="quote"></blockquote id="quote">That's fine, I just wanted to see what happens when execution plan I had in mind was tested. I see udf call in another part of the procedure. This is the place where improvements are possible. <hr height="1" noshade id="quote"></blockquote id="quote"> Yes i guess thats whats really dragging it down. The problem with it is that each item is connected to the fasett table through the qstnr_item_fasett (qif). So the qif table contains information wheter or not the scorevalue for this spesific item connected to this spesific fasett has to be turned or not. If the score has to be turned, it has to look up the midrange of the item (in the item table) and flip the score accordingly. And since this check and flip has to be done to every item its bound to process slow... <img src='/community/emoticons/emotion-6.gif' alt='' /> And i cant even store the flipped score in the item_response since it changes from fasett to fasett... If you have some suggestions to how i can denormalize or in any other way work around this please let me know. Thanks for all help so far! I've come a long way since the beginning!

Adriaan · May 15, 2006

Instead of the UDF, add a derived table that does the aggregations for you, which also returns all the fields that you're using as parameters - then join on those fields.

Either that, or use a temp table/table variable where you store the aggregations along with the join fields, and include that in your main query.

For the aggregation, it will help performance if you can filter here already.

erkdahl · May 15, 2006

I modified the UDF slightly and halleluja! Reads decreased to 3000, cpu acordingly! Then I tried mirkos code once again, and the reads decreased to 1500! Avg duration mirko-code at 160 ms. Why didn't i think of that before? Thanks for reminding me mirko! I feel so stupid <img src='/community/emoticons/emotion-1.gif' alt='' /> Maybe there is still more to gain? I will try as adriaan suggested to table variable the values. I'll be back to post results from that.

erkdahl · May 15, 2006

ok, i've tried table variables and it seems faster although with more reads than just the modified udf (1500 reads). This has about 3000 reads but runs at speed sub 100ms !
Not constantly, avg is around 150 ms, with some sub 100 and some 1000+

Below is the code to populate the flipscore table variable.
Do you think I will gain something if i modify the column qif.dir from varchar to int describing its direction?

insert into @flipscore(fasett_sid, flipscore)
select qif.fasett_sid,
avg(case
when qif.dir = '-' then ((i.mid_range - ir.responsevalue)+i.mid_range)
when qif.dir = '+' then ir.responsevalue
end)
from session s with (nolock)
inner loop join item_response irwith(nolock)
ons.session_sid = ir.session_sid
joinqstnr_item_fasettqifwith(nolock)
on qif.item_sid = ir.item_sid
joinqstnrqwith(nolock)
on qif.qstnr_sid = q.qstnr_sid
and q.language_sid = @spraak
joinitem iwith(nolock)
on i.item_sid = qif.item_sid
where s.mp_user_sid = @user_sid
group by qif.fasett_sid
option (force order)

Adriaan · May 15, 2006

How are you declaring @flipscore - does it have a PK?

Not sure you gain anything with OPTION (FORCE ORDER) or INNER LOOP JOIN - let SQL work it out.

The only use for qstnr here seems to be to filter, so why not move qstnr from the FROM to the WHERE:
WHERE qif.qstnr_sid IN (SELECT q.qstnr_sid FROM qstnr q WHERE q.language_sid = @spraak)

How many rows in qstnr? And does qstnr.language_sid have an index?

Twan · May 15, 2006

Hi ya,

how large is the entire UDF, are you able to post it completly?

Are you able to run your code from QA with statistics IO and time both turned on, and post the resulting messages?

as adriaan hints, the index on qnstr currently has the columns in the reverse order for what may be more optimal for this query. Try reversing the column order in the nonclustered index to language_id, qnstr_id

Cheers
Twan

erkdahl · May 16, 2006

Q: How are you declaring @flipscore - does it have a PK?
A: PK on fasett_sid.

declare@flipscore table (
fasett_sid int,
flipscore int,
primary key(fasett_sid)
)

Q:How many rows in qstnr?
A: 10-20

Q:And does qstnr.language_sid have an index?
A: Non-clustered index on qstnr(language_sid, qstnr_sid)

Q:how large is the entire UDF, are you able to post it completly?
A: UDF-code (after modifications) 70-80% will have dir = +:

create function dbo.GET_TURNED_SCORE
(
@item_sid int,
@response int,
@dir varchar(2)

)
returns int
begin

if @dir = '+' return @response
declare @midrange int, @jump int

--henter range
select @midrange = mid_range from item where item_sid = @item_sid
set @jump = @midrange - @response
return @midrange + @jump

end

Q:Are you able to run your code from QA with statistics IO and time both turned on, and post the resulting messages?
A: Of course, but do you mean the code with or without the udf? Here is without:
One strange thing is that first time I run it it goes slower than second time. Look at the statistics.

1st run:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TSCORES_FAKTOR'. Scan count 1, logical reads 22, physical reads 2, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 20 ms.
Table 'TSCORES_FASETT'. Scan count 1, logical reads 514, physical reads 71, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 554 ms.
Table 'TSCORES_FAKTOR'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'FAKTOR'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'KONSTANTER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'QSTNR'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'QSTNR_ITEM_FAKTOR'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'ITEM_RESPONSE'. Scan count 1, logical reads 7, physical reads 2, read-ahead reads 5.
Table 'SESSION'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 107 ms.
Table '#0626BC3B'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'ITEM_RESPONSE'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Table 'SESSION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'QSTNR_ITEM_FASETT'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'QSTNR'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'ITEM'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 8 ms, elapsed time = 8 ms.
Table 'FASETT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'TSCORES_FASETT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table '#0626BC3B'. Scan count 36, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'KONSTANTER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'FASETT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'TSCORES_FASETT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'LOOKUP'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table '#0626BC3B'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 2 ms, elapsed time = 2 ms.

2nd run(same values sent to the sp):
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TSCORES_FAKTOR'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'TSCORES_FASETT'. Scan count 1, logical reads 514, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5 ms, elapsed time = 5 ms.
Table 'TSCORES_FAKTOR'. Scan count 0, logical reads 31, physical reads 0, read-ahead reads 0.
Table 'FAKTOR'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'KONSTANTER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'QSTNR'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'QSTNR_ITEM_FAKTOR'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'ITEM_RESPONSE'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Table 'SESSION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 15 ms.
Table '#0FB02675'. Scan count 0, logical reads 172, physical reads 0, read-ahead reads 0.
Table 'ITEM_RESPONSE'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Table 'SESSION'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'QSTNR_ITEM_FASETT'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'QSTNR'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'ITEM'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 13 ms, elapsed time = 13 ms.
Table 'FASETT'. Scan count 36, logical reads 72, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'TSCORES_FASETT'. Scan count 0, logical reads 219, physical reads 0, read-ahead reads 0.
Table '#0FB02675'. Scan count 36, logical reads 72, physical reads 0, read-ahead reads 0.
Table 'KONSTANTER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
Table 'FASETT'. Scan count 49, logical reads 98, physical reads 0, read-ahead reads 0.
Table 'WEB_USER'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'TSCORES_FASETT'. Scan count 0, logical reads 300, physical reads 0, read-ahead reads 0.
Table 'LOOKUP'. Scan count 85, logical reads 1105, physical reads 0, read-ahead reads 0.
Table '#0FB02675'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 10 ms, elapsed time = 11 ms.

SQL Server Execution Times:
CPU time = 20 ms, elapsed time = 12 ms.

mmarovic · May 16, 2006

Try to use outer join instead of udf and see if it helps. Not sure it will, because join is not necessary for 80% rows.

Adriaan · May 16, 2006

The UDF does a simple calculation on a single set of values, so the calculation can easily be done in-line in the main query. Just add the lookup table to your FROM clause.

The calculation goes a little like this - in the "Part 2" section of your script on the previous page -

insert into @tempfasett
select qif.fasett_sid, avg(cast(
dbo.get_turned_score(ir.item_sid, ir.session_sid, qif.qstnr_sid, qif.fasett_sid
CASE WHEN qif.fasett_sid = '+' THEN qif.qstnr_sid ELSE item.mid_range + (item.midrange - qif.qstnr_sid) END
) as decimal(5,2)))
from item_response ir
inner join session s on s.session_sid = ir.session_sid
inner join qstnr_item_fasett qif on qif.item_sid = ir.item_sid
inner join qstnr q on qif.qstnr_sid = q.qstnr_sid
left join item on item.item_sid = ir.item_sid
where s.mp_user_sid = @user_sidand q.language_sid = @spraak
group by qif.fasett_sid
order by qif.fasett_sid

Twan · May 16, 2006

Hi ya,

the difference is mostly due to the delete on the fasett table. In the first call there are physical reads required, the second time there aren't. There is definitely a clustered index on this table with user_id as the first column?

for the fasset inserts, try using a union (or better yet a union all) rather than the two separate inserts?

for the item_response index, add responsevalue to the end of this index. This will cover the query, unless there are other columns out of the item_response table used by the queries (not all columns have the 'alias.' at the front of them which makes it harder to work out)

what are the tables fasett and web_user? I've not seen them in the code? are they part of a view?

some points in general,

- a primary key can be defined as either clustered or non-clustered. You want to make sure that you don't have a clustered primary key as well as a non-clustered index unless you have a specific reason to do so. e.g. you're using the non-clustered index to cover a query and need the clustered index for range queries
- don't put an order by on an insert into...select clause, unless you have an identity column that needs to have a predetermined order
- don't put additional columns in the group by clause. Only put in those columns that make the rows unique. In you example you have things like fasett id followed by average, etc. only put the additional columns if it is possible for a single fasett id to have multiple averages, otherwise all you're achieving is to slow down the group by without any benefit

erkdahl · May 16, 2006

Based on all your feedback, this is the best code i've put together so far:

In this one there is no udf, and the statistics/time io from previous post is from when this code is run:
Looks like the most reads /scans accour in the lookup table. Is there a index i'm missing?
so far lookup has this index: lookup(fasett_sid, language_sid)

create procedure dbo.CALCULATE_TSCORES
(
@user_sid int,
@spraak int
)
as
begin

set nocount on
declare@flipscore table (
fasett_sid int,
flipscore int,
primary key(fasett_sid)
)

--delete old data
delete tscores_faktor where mp_user_sid = @user_sid
delete tscores_fasett where mp_user_sid = @user_sid

--insert faktor scores
insert into tscores_faktor(mp_user_sid, faktor_sid, tscore)
select @user_sid,
qif.faktor_sid,
((sum(calc_value*responsevalue)-k.average)/k.std_deviance)*10 + 50
from sessions with (nolock)
inner loop join item_responseirwith (nolock)
on s.session_sid = ir.session_sid
join qstnr_item_faktor qif with (nolock)
on qif.item_sid = ir.item_sid
join qstnr q with (nolock)
on qif.qstnr_sid = q.qstnr_sid
and q.language_sid = @spraak
join konstanterk with (nolock)
on k.faktor_sid = qif.faktor_sid
and k.language_sid = @spraak
where s.mp_user_sid = @user_sid
group by qif.faktor_sid, k.average, k.std_deviance
option (force order)

-- populate the flipscore table
insert into @flipscore(fasett_sid, flipscore)
selectqif.fasett_sid,
avg(casewhen qif.dir = '-' then ((i.mid_range - ir.responsevalue)+i.mid_range)
when qif.dir = '+' then ir.responsevalue
end)
from session s with(nolock)
join item_response irwith(nolock)
ons.session_sid = ir.session_sid
joinqstnr_item_fasettqifwith(nolock)
on qif.item_sid = ir.item_sid
joinqstnrqwith(nolock)
on qif.qstnr_sid = q.qstnr_sid
and q.language_sid = @spraak
joinitem iwith(nolock)
on i.item_sid = qif.item_sid
where s.mp_user_sid = @user_sid
group by qif.fasett_sid

--insert fasettscores with values in konstant table
insert into tscores_fasett(mp_user_sid, fasett_sid, tscore)
select@user_sid,
f.fasett_sid,
((flipscore-k.average)/k.std_deviance)*10+50
from@flipscoref
join konstanter k with (nolock)
on k.fasett_sid = f.fasett_sid
and k.language_sid = @spraak

--insert fasettscores with values in lookup table
insert into tscores_fasett(mp_user_sid, fasett_sid, tscore)
select @user_sid,
f.fasett_sid,
l.tscore
from@flipscoref
join lookup l with (nolock)
on l.fasett_sid = f.fasett_sid
and l.language_sid = @spraak
where f.flipscore between l.min_value and l.max_value

end

erkdahl · May 16, 2006

Ok, i just read your post twan. Give me some minutes to sort this out and i'll post back with new results.
One question though, and this may be a amatourish one. How to make a clustered primary key non clustered without dropping the whole table?

I have to group by average and std deviance because it is possible to have multiple averages and deviances for each fasett. Though only one pr. language.

Adriaan · May 16, 2006

Depends - you say "most reads/scans" - you mean index scans? Not necessarily bad, but you might consider switching the column order for the index, or adding a separate index on language_sid in lookup.

erkdahl · May 16, 2006

Q: you say "most reads/scans" - you mean index scans?
A: Table 'LOOKUP'. Scan count 85, logical reads 1105, physical reads 0, read-ahead reads 0.
None of the other tables has a scan count and logical read count as high as this.
I will try to experiment with some index changes on this table, maybe a clustered is better than non clustered?

I also tried to to a union all with the two separate inserts into tscores_Fasett.
I did not change any other code, and when i tested it it seems to run slower than the double inserts.
Maybe union puts more pressure on the hardware? My inferior sql-testserver is not particulary well equiped in that department...

Adriaan · May 16, 2006

Does the lookup table have a clustered index?

erkdahl · May 16, 2006

Yes, clustered index on lookup table.

Adriaan · May 16, 2006

Let's see what happens if you change the column order for the index.

erkdahl · May 16, 2006

With reversed index on lookup(language_sid, fasett_sid) clustered, it effectivly decreased the reads in the lookuptable from 1000 to 170.

Twan:
The statistics io output shows reads in WEB_USER, FAKTOR AND FASETT.
The procedure contains no views and i dont really know why those tables have reads...
The web_user table is the parent of session, and contains user information.
Faktor and fasett tables are parrent of the tables containing fasett_sid/faktor_sid and contains basic information about the faktors and fasetts. None of which is included in this procedure so again I dont know why it reads through those tables.

mmarovic · May 16, 2006

Trigger(s)?

mmarovic · May 16, 2006

Actually web_user is checked on insert because of foreign key reference from tscores_faktor.

erkdahl · May 16, 2006

Yeah i guess thats it. same on fasett/faktor when inserting in tscores_faktor, tscores_fasett.

Twan · May 16, 2006

ok,

then could you check that there are the appropriate indexes on fasett/faktor to support the fk relationship, preferably a nonclustered indexes, but clustered would be ok?

did you try adding responsevalue to the index on item_response? This should remove a bookmark lookup for every row...

Cheers
Twan

erkdahl · May 18, 2006

Yes, the item_response table has index with session_sid, item_sid, responsevalue.

Since i've now included the item table as a part of the procedure i need to put a decent index on that one as well. From my point of view it seems logical to create a non-clustered index on item(item_sid, mid_range).
I've also added a non-clustered index on web_user(mp_user_sid)
Does that seem logical or have a missed something important?

Twan · May 18, 2006

Hi ya,

noclustered on item_sid, mid_range sounds sensible provided that there isn't a clustered index on item_sid, or if there is then if the execution plan without the nonclustered index shows a clustered index scan rather than a seek.

same with web_user if there was already a clustered index on mp_user_sid then the nonclustered may not make any difference, again check the execution plan

if you do create a nonclustered index which is a composite index and includes the primary key, then make sure that you make it a unique nonclustered index since it will effect SQL's optimiser

Cheers
Twan

Log in or Sign up

HELP WITH OPTIMIZING SP

erkdahl New Member

cmdr_skywalker New Member

mmarovic Active Member

FrankKalis Moderator

cmdr_skywalker New Member

Twan New Member

ranjitjain New Member

ramkumar.mu New Member

ranjitjain New Member

Twan New Member

mmarovic Active Member

erkdahl New Member

Adriaan New Member

erkdahl New Member

ranjitjain New Member

erkdahl New Member

erkdahl New Member

erkdahl New Member

Twan New Member

mmarovic Active Member

Twan New Member

erkdahl New Member

erkdahl New Member

mmarovic Active Member

mmarovic Active Member

mmarovic Active Member

erkdahl New Member

mmarovic Active Member

erkdahl New Member

Adriaan New Member

erkdahl New Member

erkdahl New Member

Adriaan New Member

Twan New Member

erkdahl New Member

mmarovic Active Member

Adriaan New Member

Twan New Member

erkdahl New Member

erkdahl New Member

Adriaan New Member

erkdahl New Member

Adriaan New Member

erkdahl New Member

Adriaan New Member

erkdahl New Member

mmarovic Active Member

mmarovic Active Member

erkdahl New Member

Twan New Member

erkdahl New Member

Twan New Member

Share This Page