SQL speed on non-pk'd tables

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/68522?pagenum=1
04 November 2025, 18:46

Topic

SiSL
SQL speed on non-pk'd tables
11 April 2009, 06:57


Greetings,

I'm wondering if there is any way to speed up tables with no-primary keys (you know tables inserting records non-stop and deleting) where int or bigint will be a problem....where you store massive number of records that may change during... eg. like Active_users table

Since yesterday hit over 25K active at same time, 194K overall visit, I think one of reasons for that to update that file while users changing pages.

 

Replies ...

ruirib
11 April 2009, 10:34


I guess the question is how those tables are accessed and what type of performance problems you're experiencing.
SiSL
11 April 2009, 14:07


Well, you know Active_Users table of Snitz. Very same :)
My performance problem is while users of 24K around same periods access data as they change pages, active users change pages as well.. So around 24K queries on a no-PK'd table sadly in less than 20 minutes periods.
ruirib
11 April 2009, 14:11


In what pages does that slow you down? Every page? Active users page?
SiSL
11 April 2009, 14:14


Every page that calls activepages function (you know the function that adds users to list if they are not, or changes their location if they were already in the table)
Not listing or anything.
ruirib
11 April 2009, 14:20


Well I will try to have a look at what it does.
SiSL
11 April 2009, 14:41


Thank you, it is in general, massive files with no PK naturally takes a lot more to operate. I was wondering if there are other options for such "log-style" tables to operate quicklier if given paramaters good enough.
ruirib
15 April 2009, 07:56


Forgot this, Selçuk, sorry. Only remembered it by seeing your latest post. Please bump it if I say nothing else today...
SiSL
15 April 2009, 11:38


Alright, as you wish :)
ruirib
15 April 2009, 12:16


Try adding an index to the MEMBER_ID field and another to the AU_IP field. Both should be non clustering indexes. This should speed getting table data and deleting members, but it may slow adding members to the list...
Let me know if it makes any difference.
SiSL
15 April 2009, 12:19


Thank you, I'll give it a try :)

Edit: Best option would be to wait for daytime where it hits :)
ruirib
15 April 2009, 18:14


Ok, but I am interested in knowing how it goes...
HuwR
15 April 2009, 18:20


i vaguely remember having to do this when I used the AU code on my old portal version
SiSL
15 April 2009, 18:25


My problem was, even I delete records in a cron every 15 minutes, who has last active time more than 15 minutes, there is around avarage 10,000 records.
So this 10,000 people browse pages, in every page, it tries to locate their record based on their MEMBER_ID or IP if they are not logged in. After SELECT, it updates, if can't find the record, it inserts. So almost in every page, 10000 visitors search through 10,000 records to update their locations. Ofcourse, with a auto-increment PK, that'd be as much as fast. But can't use any of that in a table that changes so much.

While SQL server holds just fine at another server, it forces CPU on the web server due to delay.

So I inserted 3 indexes now, 1 MEMBER_ID, 1 AU_IP and 1 for both. There was only 1 for both. Will see tomorrow on the impact :)
ruirib
15 April 2009, 18:34


Having those three indexes is wrong. The one for both if it is (MEMBER_ID, AU_IP) replaces the single one on MEMBER_ID, if it is (AU_IP, MEMBER_ID) replaces the single one on AU_IP.
You need to remember that indexes have a cost, specially when inserting and deleting records, as the indexes themselves need updating. I did have a quick look and as far as I can remember, the two fields are not used at the same time. That being the case, I think you should have just two, single column indexes, from the quick look at the queries I took.
HuwR
15 April 2009, 18:45


it would be advisable to run some sort of profiling to see which is used the most, if the AU code does most of it's queeries against the IP only add it for the IP, or vice versa for the memberid, obviously if the spread is even then add both indexes but like rui said, having the double column index would be an extra unnecesary overhead

you should also adjust the fill factor for the index, the default fill factor is 0, try increasing it to 90 (some trial and eror will be required to get the correct value)
SiSL
15 April 2009, 18:49


Yeah, considering number of members vs. guests, (like 1 member:15 guests ratio) grand majority is checking for IP. So first I'm going to go with two different indexes only and compare with just AU_IP after.
HuwR
15 April 2009, 18:50


you may have missed the addition to my post re fill factor as I edited it the same time you posted smile
SiSL
15 April 2009, 18:54


Oh thanks for that. I was just going to ask about that and if should disable row locks and page locks settings and "automatically recompute statistics"
ruirib
15 April 2009, 18:56


Think you need two. Id is used for joins with the members table, so needed. Also needed for other selects. IP is also needed for selects and deletes.
You may need another index on AU_LASTACTIVETIME, as it used to remove the inactive users.
SiSL
15 April 2009, 19:00


Thanks to you both :) Can't wait for tomorrow to check out results :)

Meanwhile found out Lastactivetime of my db is nvarchar(100) for some weird reason and ip is also 100 chars long. That will save me some space reducing them to 14 and 16

Actually I guess I'm going to go ahead and Modify Active Users functions not to update/insert Query String and User Agent... Since that's something that does not matter much.
HuwR
15 April 2009, 19:06


should disable row locks and page locks settings and "automatically recompute statistics"
I would leave the locking up to SQL, unless you are experiencing memory problems due to large amounts of locks, in which case you coud try turning off row locks but leaving page locks on (broader locking but less memory)

You should leave the auto compute statistics on
HuwR
15 April 2009, 19:07


be carefull reducing the size of the IP field, the IP field can often have multiple IP's in it if the users is being proxied
SiSL
15 April 2009, 19:09


Hmm.... Right, so 50 should be okay like default now?
HuwR
15 April 2009, 19:11


yes 50 should be ok, you can probably make it smaller, but definately not as small as 16 smile
ruirib
15 April 2009, 19:15


Last active time needs to be only 14, will save a lotta space and the index will be lighter too.
SiSL
16 April 2009, 14:16


I sadly, could not be able to drop down CPU levels for sure, but...
YOU ROCK GUYS, each page dropped more than 1 seconds avarage rendering time if stop timer does not lie :) That's like 100% extra performance. I also added some other indexes on other "massive storage" tables, that definitely rocks.. Like it dropped to 0,5 from 1,5 or so...

Thanks a lot :)
ruirib
16 April 2009, 14:52


Glad to know that the suggestions improved page generation times.
You know, the default Snitz indexing structure is rather poor and it may be worth, as Huw suggested and if you have that possibility, to run the SQL Server profiler and record a representativa session and then submit the results to the Database Engine Tuning Advisor. It could really represent a major improvement in performance for your forum.
SiSL
16 April 2009, 15:13


Doing that at this moment, nice hour of 22:00, avaragely loaded time and took a sample of 100 mb's. Hoping to get more improvement that way :)

Thanks again :)
ruirib
16 April 2009, 17:45


Ok, would be interesting to know the recommendations from the Tuning Advisor, if you don't mind posting them.
SiSL
17 April 2009, 01:12


I got an error on Advisor telling me no permission for SHOWPLAN...
And I have absolutely no idea how to grant that to BUILTIN/Administrators

ruirib
17 April 2009, 05:21


Code:
GRANT SHOWPLAN To username
SiSL
17 April 2009, 11:10


And any idea how to do that for BUILTIN/Administrators group? Since it does not allow me to do like that
ruirib
17 April 2009, 11:23


I find it very strange that it won't allow you to do that for an administrator. What server roles does your user have assigned?
SiSL
17 April 2009, 11:29


Well, I login with Windows Authentication. It allows me to do anything on Server, however, I can't do any SHOWPLAN with that, so I have to grant this Windows user. I didnt really understand, how come Windows Authentication Administrator can't have SHOWPLAN access for Tuning Advisor...
So I added a showplan grant to one of my ASP login, trying that way. Improvement says 94% so far, but no idea what it will do in the end eheh
ruirib
17 April 2009, 11:34


Ok, let us know how it goes...
SiSL
17 April 2009, 16:17


Well, it gave me like 1000 index results to add, not sure if I should heh...
ruirib
17 April 2009, 19:37


I would generate a script to create the current database structure and then would apply the results. My experience with the Tuning Advisor has been very positive.
If you "record" the existing structure, you can get back to it if needed. I would be highly surprised if the advice was bad. Give it a go, man, but get the existing structure, indexes, constraints and all, before you apply the changes recommended by the advisor.
SiSL
18 April 2009, 18:16


Applied them directly. Now I'm having an easier ride. It added around 2 or 3 indexes to my forum tables, but sure huge number of indexes to my other non-forum tables. Definitely seeing the improvement. 94% as it said on Tool :)
ruirib
18 April 2009, 19:38


Could you post the definitive forum table indexing structure? It would be interesting to know what was advised...
SiSL
18 April 2009, 20:13


Sure thing, I'm going to give 3 main tables right ahead:

For TOPICS table, I got following (apart from the originals):

Index 1: CAT_ID, TOPIC_ID, FORUM_ID, T_AUTHOR, T_DATE (all Asc)

Index 2: T_LAST_POST (Desc), T_AUTHOR, T_STATUS, FORUM_ID (Asc)

Index 3: T_STATUS (Asc)


For REPLY Table (apart from Original ones)

Index 1: R_AUTHOR, REPLY_ID, TOPIC_ID, R_STATUS (all Asc)

For MEMBERS

Index 1: MEMBER_ID Asc <-- I dont know why it has given, since there is already unique clustered one

Index 2: M_STATUS, M_POSTS Asc




ruirib
18 April 2009, 20:50


Thanks :).
© 2000-2021 Snitz™ Communications