we have complaints from users that login time is almost 1 min.
we open MS SQL 'Activity monitor' for HPSM database and see that most time-consuming queries (10-20 sec) are to the <userinfo> like:
SELECT m1."LOGINNAME", m1."CLIENTIP" ,COUNT(*) FROM USERINFOM1 m1
--WHERE ((m1."LOGINNAME" like 'email@example.com'))
GROUP BY m1."LOGINNAME", m1."CLIENTIP"
ORDER BY m1."LOGINNAME", m1."CLIENTIP"
I open this table and it has less than 300 records - it's the number of logged in users.
1. why query to this table is so long and take 10-20 secs?
2. how to fix it?
3. we have tried to add index in dbdict to this table (loginname, clientip columns) but it doesn't help.