Page 1 of 1
Slow site? Try adding an Index
Posted: Tue May 11, 2010 1:38 pm
by 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.
Re: IMPORTANT: Slow site? Try adding an Index
Posted: Tue May 11, 2010 1:50 pm
by 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.
Re: IMPORTANT: Slow site? Try adding an Index
Posted: Tue May 11, 2010 1:52 pm
by 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.
Re: Slow site? Try adding an Index
Posted: Tue May 11, 2010 9:03 pm
by 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?
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 6:26 am
by 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.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 8:21 am
by 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.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 8:24 am
by 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
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 8:43 am
by GrfxDan
Thanks for the info Judy. The W2M staff takes very good care of my site and I appreciate it!
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 8:53 am
by 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.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 9:02 am
by 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.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 9:10 am
by michael.p.larsen
Sure. It's a good problem to have
I like your site, btw. Great work!
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 11:23 am
by NC Software
Michael,
What's the URL to your store? I'd love to check it out.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 11:43 am
by michael.p.larsen
Neal - I don't link to the site from here for security reasons. I PM'ed you instead.
Re: Slow site? Try adding an Index
Posted: Wed May 12, 2010 5:16 pm
by jmestep
Michael- they were routines that W2M created.