Ensure that the "where" clause is the same type as the field.
A common mistake is: iBlacklist='3' where iBlacklist is a numeric field, and '3' signifies a text value.
Make sure you know what the execution plan of your query will be.
Avoid full table scans, especially for real-time queries or near real-time queries running every minute.
In queries, exists such as conditions in filters are not efficient.
The exists such as condition is the equivalent of a sub-query in SQL:
select iRecipientId from nmsRecipient where iRecipientId IN (select iRecipientId from nmsBroadLog where (...))
select iRecipientId from nmsRecipient INNER JOIN nmsBroadLog ON (...)
Defining links on the schema will determine the join conditions.
The linked table should have an unique index on the primary key and the join should be on this field.
Define keys on numeric fields instead of string fields.
Use a TEST (or pre-production platform), similar to the PROD environment. Use the same values for the TEST and PROD environments. As much as possible, use the same:
- Operating System.
A feature that works in a TEST environment may not work in a PROD environment where the data may be different. Try to identify the main differences in order to anticipate risks and to prepare solutions.
Large volumes require specific configurations. A configuration that worked for 100,000 recipients may not work for 10,000,000 recipients.
Consider how the system will scale when it goes live. Just because something works on a small scale does not mean that it will be suitable with greater volumes. Testing should be done with similar volumes to the volume in production. You should also evaluate the effect of changes in volumes (number of calls, size of the database) at peak hours, peak days, and across the life of the project.