Slow site? Try adding an Index

[michael.p.larsen]

Our site ground to a HALT when we hit about 600 [edit]simultaneous[/edit] users. I checked SQL to see what queries were running and found a TON of calls to the ac_Users table... ALL having the LoweredUserName column in the WHERE clause.

I checked the indexes on the ac_Users table and there was ONLY ONE for the UserId column!

I ADDED an index to the LoweredUserName column and literally watched the CPU utilization DROP INSTANTLY from 95% to 10%.

Moral of the story? ADD this simple solution and your site SHOULD speed up.

[NC Software]

I agree, the typical rule of thumb is if a column is used in a WHERE clause, it should be indexed. I hope AC 8 will also use stored procedures.

[michael.p.larsen]

I've never heard that rule of thumb... but it's set in stone in my brain now! I love it! Thanks for sharing that.

[GrfxDan]

Micheal, forgive me for being ignorant on the matter but are you referring to 600 user accounts or 600 simultaneous users? Also, can you provide details on how to add an index to the LoweredUserName column?

[jmestep]

Dan, this is done within SQL server. I just checked your store and the ac_User table has indexes on the username and loweredusername. At W2M, we run code to create indexes as a part of every site install. I believe there are other routines that are run periodically to optimize database performance.

[michael.p.larsen]

Dan - 600 simultaneous users. We have over 65,000 users in the ac_Users table.

Judy - Are those routines something you created, or did it come with Able? My default install didn't have an index on the LoweredUserName column.

[michael.p.larsen]

Here is the SQL script to create the index:

Code: Select all

CREATE NONCLUSTERED INDEX [ac_Users_LoweredUserName] ON [dbo].[ac_Users] 
(
	[LoweredUserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

[GrfxDan]

Thanks for the info Judy. The W2M staff takes very good care of my site and I appreciate it!

[michael.p.larsen]

Sounds like you are in good hands Dan.

On average, how many simultaneous users would you say you have? 10? 100? 1000?

Just curious.

[GrfxDan]

At peak times I've probably got 30 users at a time. I've got a while before I have to worry about 600 simultaneous users.

[michael.p.larsen]

Sure. It's a good problem to have

I like your site, btw. Great work!

[NC Software]

Michael,

What's the URL to your store? I'd love to check it out.

[michael.p.larsen]

Neal - I don't link to the site from here for security reasons. I PM'ed you instead.

[jmestep]

Michael- they were routines that W2M created.