May 24, 2017 at 1:50 am
Hi All,
I have a table in which customer profile details such as name, address, dob etc are stored. Whenever customer update the profile the last update date time for corresponding field is updated. e.g. if name is updated then last name change field will be updated with latest date time. Requirement is to get all the profile for which any details have been updated.
I can use OR clause to check multiple condition but I want to explore other options which could perform faster as using OR clause might degrade the query as index might not be used properly.
Any help will be appreciated. Thanks! a lot
May 24, 2017 at 2:34 am
Am I correct in reading that you have a datetime field for every column to store the datetime a field was last updated? So you have a table along the lines of:CREATE TABLE dbo.Customer
(Title varchar(5) NOT NULL,
TitleChanged datetime DEFAULT NULL,
Forename varchar(50) NOT NULL,
ForenameChange datetime DEFAULT NULL,
Surname varchar(50) NOT NULL,
SurnameChange datetime DEFAULT NULL,
CustomerAddress varchar(50) NOT NULL,
CustomerAddressChange datetime DEFAULT NULL,
Postcode varchar(50) NOT NULL,
PostcodeChange datetime DEFAULT NULL)
I'd honestly, reconsider your design if this is the case, as this isn't the ideal way to store this. A lot of people will often store off the old data in a historic table, for example, by using triggers or forcing changes through an application that handles this in the SP. Then you only need to stamp one datefield, and you can tell everytime a record is updated.
Unfortunately, yes, the only way you'll going to be able to check if any of these have been changed is to query them all. So, along the lines of:SELECT *
FROM dbo.Customer
WHERE (TitleChanged >= @DateFrom AND TitleChanged < Dateadd(DAY, 1, @DateTo))
OR (ForenameChange >= @DateFrom AND ForenameChange < Dateadd(DAY, 1, @DateTo))
OR (SurnameChange >= @DateFrom AND SurnameChange < Dateadd(DAY, 1, @DateTo))
OR (TitleChanged >= @DateFrom AND TitleChanged < Dateadd(DAY, 1, @DateTo))
OR (CustomerAddressChange >= @DateFrom AND CustomerAddressChange < Dateadd(DAY, 1, @DateTo))
OR (PostcodeChange >= @DateFrom AND PostcodeChange < Dateadd(DAY, 1, @DateTo));
This query, of course, might not even give you completely accurate results. Say you want to know details of customers that changed address in 2016. If the customer has changed their address this year as well, then the field CustomerAddressChange will contain a date for 2017, so won't show up in your query even though they did change their address in 2016.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 24, 2017 at 3:31 am
Hi Thom A,
Thanks! for the reply! You are correct with your assumption on the design.
This table is question only store the latest profile information and there are other details of profile along with fields mentioned in my post. Hence, if we have only one date time field then we would not be able to identify what details of profile was updated. Moreover, this architecture has been since long time now and change to table schema would have huge impact on entire application.
Coming to query result, I just need to pick the record if any details such as name or address or dob etc. has been updated in last one day. Logic is based on the latest update so I don't worry about historic update. I have written the logic using OR but am not satisfied with the performance. I am looking for other approach to get the similar result with better performance.
May 24, 2017 at 6:08 am
How about a UNION ALL where you hit the table over & over filtering only on each individual datetime?
Not sure that'll be faster, but it's different than an OR statement. Although, it'll require an index on each of those columns for it to work well.
Might want to look into change data capture.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2017 at 11:20 am
devesh.hassani - Wednesday, May 24, 2017 1:50 AMWhy did you fail to post DDL? Your vague narrative is not a good design. This is a basic history table skeleton for property rentals and sales which you can modify when you finally follow the forum rules.CREATE TABLE Occupancy_History
(property_id CHAR(13) NOT NULL
REFERENCES Inventory(property_id),
prev_date DATE NOT NULL,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATE, -- NULL means property_ids current status
property_status VARCHAR(10) NOT NULL
CHECK (property_statusIN ('vacant', 'rented', 'condemned', .. ),
etc.
-- now we add constraints--uniqueness
PRIMARY KEY (property_id, start_date),
UNIQUE (property_id, end_date),
UNIQUE (property_id, prev_end_date),--ordering of events
CHECK (start_date <= end_date),
-- Optionally, CHECK (start_date < end_date
CHECK (prev_end_date <= start_date),
-- Optionally, CHECK (start_date = prev_date + INTERVAL ‘1’ DAY), -- prevents gaps-- self-referencing constraint
CONSTRAINT property_id_prev_end_date
FOREIGN KEY (property_id, prev_end_date)
REFERENCES statusHistory (property_id, end_date)
);The first three uniqueness constraints are fairly obvious. The property_id and the start of a status have to be unique and not NULL-able so we have a natural primary key. The ending dates, current and previous, might have NULLs, so we need to use a UNIQUE constraint.
The next constraints give an ordering to each event, namely, prev_date is on or before start_date, which is on or before end_date. The reason for not putting this into a single BETWEEN predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.
The self-referencing constraint is a trick from Alex Kuznetsov. It says that all the previous status ending dates were really ending dates for some time period. You will want to play with options to get them to fit your own business rules.
It is also a good idea to have a VIEW with the current data:
CREATE VIEW Current_Status (..)
AS
SELECT ..
FROM Occupancy_History
WHERE end_date IS NULL
OR end_date >= CURRENT_TIMESTAMP;You use a BETWEEN predicate to get the appropriate status for particular date.
SELECT ..
FROM Occupancy_History AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP); -- or other known data as needed
Please post DDL and follow ANSI/ISO standards when asking for help.
May 25, 2017 at 11:20 am
devesh.hassani - Wednesday, May 24, 2017 1:50 AMWhy did you fail to post DDL? Your vague narrative is not a good design. This is a basic history table skeleton for property rentals and sales which you can modify when you finally follow the forum rules.CREATE TABLE Occupancy_History
(property_id CHAR(13) NOT NULL
REFERENCES Inventory(property_id),
prev_date DATE NOT NULL,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATE, -- NULL means property_ids current status
property_status VARCHAR(10) NOT NULL
CHECK (property_statusIN ('vacant', 'rented', 'condemned', .. ),
etc.
-- now we add constraints--uniqueness
PRIMARY KEY (property_id, start_date),
UNIQUE (property_id, end_date),
UNIQUE (property_id, prev_end_date),--ordering of events
CHECK (start_date <= end_date),
-- Optionally, CHECK (start_date < end_date
CHECK (prev_end_date <= start_date),
-- Optionally, CHECK (start_date = prev_date + INTERVAL ‘1’ DAY), -- prevents gaps-- self-referencing constraint
CONSTRAINT property_id_prev_end_date
FOREIGN KEY (property_id, prev_end_date)
REFERENCES statusHistory (property_id, end_date)
);The first three uniqueness constraints are fairly obvious. The property_id and the start of a status have to be unique and not NULL-able so we have a natural primary key. The ending dates, current and previous, might have NULLs, so we need to use a UNIQUE constraint.
The next constraints give an ordering to each event, namely, prev_date is on or before start_date, which is on or before end_date. The reason for not putting this into a single BETWEEN predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.
The self-referencing constraint is a trick from Alex Kuznetsov. It says that all the previous status ending dates were really ending dates for some time period. You will want to play with options to get them to fit your own business rules.
It is also a good idea to have a VIEW with the current data:
CREATE VIEW Current_Status (..)
AS
SELECT ..
FROM Occupancy_History
WHERE end_date IS NULL
OR end_date >= CURRENT_TIMESTAMP;You use a BETWEEN predicate to get the appropriate status for particular date.
SELECT ..
FROM Occupancy_History AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP); -- or other known data as needed
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply