No indexes in SQL server. Anyone else got same problem? > Forums

Unanswered Active Topics

Forums Search

Forums > General Discussion Forums > ArGoSoft Mail Server .NET Edition

Subject: No indexes in SQL server. Anyone else got same problem?

Prev Next

You are not authorized to post a reply.

Author

Messages

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-13-2008 2:06 PM
Hi I discovered today that the mail server tables don't have any indexes on them. Even the cache and mailboxdata ones. Anyone else found this to be the case? I can't beleave its been designed without the indexes.

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-13-2008 2:25 PM
Actually, Each table has a RecNo field. That field is the Identity Key for each table and it is indexed.

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-13-2008 2:34 PM
Hmm, we I can see its design to be a key (recno field). But there is no index on any of recnos in my database, no fields are set to primary keys. Which is not great to say the least, but there is no indexes on any of the major fields. This is going to have a massive impact on performance when the database grows a little more. I've just added some indexes on some tables for the time being as a quick. Just going to have a look in the forums to see if anyone is have any speed issues (could be related).

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-13-2008 2:49 PM
I have not had any issues with it. My MS-SQL not only runs Mail Server, but the back end DB for this and 5 other websites among other things. I have also taxed the server with nearly 2000+ outbound messages, and recieving my normal load plus bounces and the server performed well. There should be a thread in the Site News forum about that project.

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-13-2008 3:00 PM
2000 messages was not an issue. 10000 for me became bigger problem (newsletter). It will get much worse as it scales if the indexes are not on.

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-13-2008 4:20 PM
I don't see it as issue, but I am no SQL expert nor the developer and I think a lot depends on the controller logic. I have forwarded your comments to the developer (Archie) and I will let you know what he says.

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-13-2008 6:16 PM
Sorry to be so forceful. But I'm telling, not asking. If the mail server is setting up the database with no indexes as standard, it will be a massive problem for anything other than a small database. If there is no index the database has to look at each and every record until it finds the record it wants (not forgetting the records will be out of order). If there is an index, this could be reduced to just a few lookups. On a table with 1000s of records its not an issue. But once your talking about ten of thousands of records or more it will really so down. Don't forgot this compounded by the fact that the database is queried by the mail server many times a second (or it tries to). I've seen instances where a query on a database has been taking minutes reduced to under a second just by adding a index on a field. I've been using Archie's products for quite some years now and he is a great programmer, but if he has missed this it is great mistake. I hope this is corrected. PS. I would be more than happy to publish details of the indexes I've added or even write a script to add the indexes.

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-14-2008 12:24 PM
I would be more than happy to publish details of the indexes I've added or even write a script to add the indexes. I would love to see that and the script. I have communicated with Archie and he will be adding it at some point in the future.

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-18-2008 9:33 PM
Hi Steve, The next post I do will included the code to create the indexes. The indexes are put on just three tables DeliveryQueue, DnsCache, MailboxData. These tables are the ones with the most records and are 'hit' the most times. Without seeing the code I'm working a little blind but its a good start. With some of SQL servers query logging I could optimize the database more. Ideally Primary keys and relationships should be added. But this is really a job for Archie. On another note I discovered that if the DnsCache grows very large (in my case it was tens of thousands of rows when I did the newsletter) if the server is stopped and started it hangs on deleting the old records from DnsCache. Even with the index its still struggling. I suspect this is because of an unoptimized query. I could have a look at how that query is working and maybe offer a solution/ifx if archie is busy. Glenn

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-18-2008 9:33 PM
/*******************************************************/ /** Create a Indexes Recno, **/ /** NextAttemptTime, **/ /** Created, **/ /** in DeliveryQueue Table **/ /*****************************************************/ CREATE NONCLUSTERED INDEX [Recno] ON [dbo].[DeliveryQueue] ( [RecNo] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [NextAttemptTime] ON [dbo].[DeliveryQueue] ( [NextAttemptTime] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [Created] ON [dbo].[DeliveryQueue] ( [Created] 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] /*****************************************************/ /** Create a Indexes Recno, **/ /** DomainName, **/ /** RecordType, **/ /** Expires, **/ /** in DnsCache Table **/ /*****************************************************/ CREATE NONCLUSTERED INDEX [Recno] ON [dbo].[DnsCache] ( [RecNo] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [DomainName] ON [dbo].[DnsCache] ( [DomainName] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [RecordType] ON [dbo].[DnsCache] ( [RecordType] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [Expires] ON [dbo].[DnsCache] ( [Expires] 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] /*****************************************************/ /** Create a Indexes Recno, **/ /** MailboxID, **/ /** in MailboxData Table **/ /*****************************************************/ CREATE NONCLUSTERED INDEX [Recno] ON [dbo].[MailboxData] ( [RecNo] 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] /*****************************************************/ CREATE NONCLUSTERED INDEX [MailboxID] ON [dbo].[MailboxData] ( [MailboxID] 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] /*******************************************************/

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-21-2008 10:07 AM
Thanks I will give it a try. As for the DNS Cache, I thought that was changed in one of the earlier releases. Are you using the latest version?

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

gyroscopes

Posts: 19
Online: User is Offline

ArGoNuke Apprentice

08-21-2008 11:10 AM
I'm on v1.0.5.2. I tend to upgrade on more major releases. If there anything you or archie wants me to look into I try to help. Anything regarding databases I should be able to help. Included triggers, rollbacks, indexes, speed issues etc etc.

SteveT
Forum Administrator

Posts: 2594
Online: User is Offline

ArGoNuke Admiral

08-21-2008 11:13 AM
I will pass it on. Thanks.

Regards, Steve Topilnycky Top Cat Computing http://www.topcatcomputing.com

You are not authorized to post a reply.

Forums > General Discussion Forums > ArGoSoft Mail Server .NET Edition > No indexes in SQL server. Anyone else got same problem?

ActiveForums 3.7