Interesting Design Issue

Question

Interesting Design Issue

mar.ko

Default port

Points: 1451
More actions
September 2, 2015 at 7:16 am

#300161

Call center data - large volume....millions of rows.
When a sale is made, a column value is established with the Lead ID in the source data table.
I need to set an indicator in the official table that holds the call data to indicate it is the first call with the Lead ID present.
This will allow me to relate the calls with the sale....to perform a conversion calculation.
If this indicator is not set, I will over-count the sales because the Lead ID could appear in multiple call rows.
Now I am trying how best to set that indicator:
If I perform an INSERT for new calls, I must outer join the source data table to the call table on which the insert is being performed.
That would have the optimizer looking at all of the rows going back in time...and likely slow down the insert considerably.
From a practical standpoint, I only need to "look-back" about 30 days to see if the LEAD ID was already present.
So I was thinking of joining to a view that limits the look-back to 30 days....but the view would not have an index on the lead ID.
Should I drop and create an indexed view each time the insert is performed ? The 30 day lookback would likely be a little less than 1 million rows.
Any and all suggestions appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

mar.ko (9/2/2015)
Call center data - large volume....millions of rows.
When a sale is made, a column value is established with the Lead ID in the source data table.
I need to set an indicator in the official table that holds the call data to indicate it is the first call with the Lead ID present.
This will allow me to relate the calls with the sale....to perform a conversion calculation.
If this indicator is not set, I will over-count the sales because the Lead ID could appear in multiple call rows.
Now I am trying how best to set that indicator:
If I perform an INSERT for new calls, I must outer join the source data table to the call table on which the insert is being performed.
That would have the optimizer looking at all of the rows going back in time...and likely slow down the insert considerably.
From a practical standpoint, I only need to "look-back" about 30 days to see if the LEAD ID was already present.
So I was thinking of joining to a view that limits the look-back to 30 days....but the view would not have an index on the lead ID.
Should I drop and create an indexed view each time the insert is performed ? The 30 day lookback would likely be a little less than 1 million rows.
Any and all suggestions appreciated.

Done correctly, a view is not any different that a sub-select and will use the indexes of the underlying tables. To be honest, though, doing a view for such a simple thing as limiting the output of a table to 30 days is real overkill unless you think that you'll need to change the 30 day limit to something else in the future for a lot of code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mar.ko Default port Points: 1451 More actions · Answer 2

Thanks Jeff -

you are saying that if the table is properly indexed, I really don't need to use the view which will limit the look-back period and thus the number of rows to search ?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

mar.ko (9/2/2015)
Thanks Jeff -
you are saying that if the table is properly indexed, I really don't need to use the view which will limit the look-back period and thus the number of rows to search ?

Yes... that too!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)