Email Database Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Email Database Design

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-02 : 08:10:22

Scott writes "I've inherited a database and I need to improve the performance of one main query. I'll describe the problem in terms of an email database (not the real application--but close enough).

There are two important tables. The EmailTable looks something like:

CREATE TABLE EmailTable
(
emailID int, -- unique, non-clustered index
emailDate datetime, -- non-unique, clustered index
language nchar(2), -- en = english, de = german, etc.
subject nvarchar(250),
.
.
.
)

Each row includes several nvarchar's and a table entry can be fairly large (avg 1100 bytes).

There is a second table

CREATE TABLE EmailAttributes
(
emailID int, -- foreign key pointer to EmailTable
attribute int
)

Each Email may have several attributes assigned to it (the statistics I looked at said there is around 4 attributes per email (out of several hundred possible), i.e., there are about 4 entries in EmailAttributes for each entry in Emailtable. I currently have only one unique, clustered key on EmailAttributes (combined columns emailID + attribute)

This is a web-based system. Users will login to "page though" their emails. They provide a list of languages they are interested in (typically 1 to 12) and a set of attributes. A stored procedure is called to get the next "page" (20 emails to a web page) of items. It is passed a semicolon separated list of languages and a semicolon separated list of attributes along with the emailID of the last email in the web page that was previously retrieved. I parse the semicolon separated lists into single column table variables. The sproc needs to find the next 20 (where "next" is the next emails following the previous "last one" and is based on date/time). Each email must have at least one of the attributes provided (i.e., it's an "or") and the email language must match one in the provided list.

The sproc is executing very slow. Rather than trying to show it (since it is also long), I'm wondering what other people have done in similar situations. Do I have the right indexes? Should I split the EmailTable into two tables (one with search terms and one with details--making the indexes smaller and more efficient)? Is there some clever way to better represent the data to make it more quickly accessible?"

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 08:14:44

Hi Scott, Welcome to SQL Team!

I investigate the logical I/O on each SELECT in an SProc, and work hard on the ones where the figure is too high and where I feel I have a chance of improving it!


-- See http://www.sql-server-performance.com/statistics_io_time.asp for more details

-- Clear cache (for level playing field 
--    - only if wanting to check PHYSICAL performance rather than LOGICAL performance)
-- Do **NOT** run these on a Live server
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources