Load Testing Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Load Testing Performance

Author

Topic

ivanho
Starting Member

4 Posts

Posted - 2010-07-03 : 04:34:54

Dear members,

I'm having some issue on the performance for load testing for 1000 Threads.

We do a simple login with a 4 million user records table:

LoginUser.setUsername(rs.getString("username")); LoginUser.setPassword(rs.getString("password"));

if equal encrypted password

getJdbcTemplate().update("UPDATE [user] SET login_attempt = ?, last_login_date = ? where username = ?",

The time use for query and update is quite long. Any idea where we can start to tune a better response time ?

<Time used for query 233441 ms>
<Time used for update 12058 ms>

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-03 : 05:23:58

Table definition? Index definitions? 4 million rows in the users table? That's a lot of users.
Is there blocking?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-04 : 06:09:03

Is there a unique index on Username?
Has the index been rebuilt recently? and the statistics updated?

UPDATE MyTable SET ... WHERE UniqueColumn = SomeValue

is never going to be slow if [UniqueColumn] is indexed and housekeeping done recently (barring blocking, as Gail says)

You don't say what the query part is, but assuming that is using "where username = ?" i.e. the same unique-valued indexed column then that won't be slow either (given the same caveats)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-04 : 08:32:21

I'm going to assume there's no index on username.

--
Gail Shaw
SQL Server MVP

ivanho
Starting Member

4 Posts

Posted - 2010-07-05 : 00:11:03

Hi,

The username is already indexed. Hence we have no idea why it takes so long.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-05 : 01:43:17

quote:
Originally posted by GilaMonster

Table definition? Index definitions? 4 million rows in the users table? That's a lot of users.
Is there blocking?

If you want useful help, give us something to work with. We could guess about the table structure and indexes for weeks and still be wrong.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources