Actually, Each table has a RecNo field. That field is the Identity Key for each table and it is indexed.

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.

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.

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.

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.

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.

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

/*********************************************************/
/****** 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]

/*********************************************************/

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?

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.

I will pass it on. Thanks.