1234     phone     1234567890
1234     fax       0987654321
1234     ssn       123456789

quote:

---

Originally posted by nathans

Guys,
I have a wide table [customer] that holds info such as phone, fax, social_security_number, etc.

When creating a new customer, I want to cross-reference all existing customers on certain fields, say phone, fax, and ssn and insert any matches into a separate table [data_match] with the employee_id, matched_column, matched_value.

I have written the procedure to search the [customer] table for these matches, but with 1 million + rows in the [customer] table this query takes a while, and with adding 1,000 customers per day this solution is not feasible.

SO - I have moved the cross-referenced data out into a name/value table that stores the column_name, and column value.
Account: 1234 would have the following rows for the 3 searched columns:

1234     phone     1234567890
1234     fax       0987654321
1234     ssn       123456789

I indexed the value column, and of course this makes the query lightning fast compared to the other solution... But I dont feel great about duplicating the data. Am I going about this wrong? I can index the other columns in the primary table, but this would really be 8 columns I would be indexing (I have included only 3 in this example).


Thoughts?

---

quote:

---

Originally posted by nathans

I am somewhat hesitant to index those 8 columns and perform the search on the primary transactional table rather than the lookup table. The primary table holds all the data for all customers... I am only interested in searching the last 45 days of data. The lookup table would only hold this 45 day subset of data (< 1 mil rows) whereas the transactional table is ~10 million rows.

---

Select
	CUSTOMER_ID
from
	CUSTOMER_LOOKUP
where
	SSM_CHECKSUM = checksum(@SSN)	and
	SSN = @SSN
union
Select
	CUSTOMER_ID
from
	CUSTOMER_LOOKUP
where
	EMAIL_CHECKSUM = checksum(@EMAIL)	and
	EMAIL = @EMAIL
...and so on...

quote:

---

Originally posted by nathans

Thanks Michael, I should have reviewed my orig post :)

Yes, I am working with a subset of data here, but its still a rather large chunk. Ill be searching email_address, phone, fax, and ssn for both customer / co-customer, so (8) total columns.

Any new ideas, now that you have the full story.

- Nathan

---

quote:

---

Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views that are mapped over relatively static data, process many rows, and are referenced by many queries.

---

quote:

---

Originally posted by nathans

The existing data is not modified often, though new rows are added fairly frequently (1000 rows /day). But like you said, how can we transform the data without the use of the UNION? Indexed views are out?

Im thinking of going with the original solution. I can just create a job to run during off-peak hours to populate the table with 45 days worth of data, then cross reference on that during new customer inserts. At least this solution will allow me the ability to add/remove searched columns, and increase/decrease the day interval by just modifying the job... but I still feel like there is a better way.

thanks again!

---