Performance issue… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance issue…

Hi I’m working on a rather large multi-function-project, and the part of it I’m working on now is a system that can help our customers sending newsletters to their subscribers. A customer can have one or more lists of subscribers, and each list can contain a variable number of fields. There can be a couple of millions subscribers all in all. So we have a customer who can log into the system. Then we have a list that the customer owns, and on that list is a number of subscribers. Some lists may have only 3 fields, like firstname, surname, and email. Others may have maybe 30. I could just reserve 40 fields on subscriber table just to make sure, but that would make a lot of NULL values on the lists, because most lists would use less than 10 fields. It wouldn’t make the system nice and dynamic either. Then I made a table of fields and a table of field values. The fields are related to the list, and the field values are related to the fields and the subscribers, which again are related to the list. That way every value is a record in the field values table. Then for instance, I can get the first 10 subscribers from a certain list, ordered by first name, like this: SELECT TOP 10 * FROM (
SELECT tblSubscriber.UID, tblSubscriber.dtmCreated,
(SELECT strValue FROM tblStrFieldValue WHERE IDField = 1 AND UIDSubscriber = tblSubscriber.UID) AS [First name],
(SELECT strValue FROM tblStrFieldValue WHERE IDField = 2 AND UIDSubscriber = tblSubscriber.UID) AS [Last name]
FROM tblSubscriber
WHERE tblSubscriber.UIDList = ‘{7A7440D0-A174-4AF6-99FA-0ECA6F293652}’) DERIVEDTBL
ORDER BY [First name] I thought this was a good idea, but when I had made a couple of million test records, I found out that it was hard to index properly, and it seems like no matter what I do, I can’t get the performance right. Another issue that I have thought of is simply creating a new table for each list. That way I only have the fields in the list that I need; no more and no less. Problem here is just that it’s hard to make the system make the best of indexing at create-time. Does anyone have a genious suggestion? Thanks in advance,
How about execution plan and indexes involved in SARG? _________
Satya SKJ