So, I've picked up a habit from reverse-engineering List Manager queries of always adding the condition of
ISNULL(c.inactive, 1) = 1
However, scalar functions are a performance disaster on large sets, particularly I imagine when in outside of a select.(with a select I only need to run the function on each returned row, but in a where presumably I have to run it on every row in the table, and T_CUSTOMER is a big table).
Does anyone know if this null condition is a historical thing, and customer records are no longer going to ever have that inactive value? That is, if I updated all rows where inactive is null to 1, could I safely forego using ISNULL on my future queries without worry of a null value slipping back in?
I do not know the answer to your question, but honestly, I have been wondering the same thing myself lately, because I have done the same thing as you in reverse engineering things. We went LIVE with Tessitura in 2010, and not a single one constituent in our database has a NULL value in that column. And no extra back end things were done to make it that way (unless a Tessitura staff member did it themselves during an upgrade or something like that since we are on RAMP). And I would know, I am the only staff member who has or has ever had access to the database.
That's interesting. We have quite a few, but we've been around since 2004 and...
Woah. I swear that the other day when I was looking at this we had tens of thousands of records without inactive set. Now when I query I get none. Crazier: when I look at "Show Query" now, it isn't using the ISNULL(a.inactive, 1) = 1; it just does a.inactive = 1.
Someone at RAMP is screwing with me.
Have you upgraded to V14 yet? We are still on V12.5.1, and I just double-checked the queries that come up with standard List Manager elements, and it still gives me the ISNULL(a.inactive, 1) = 1.
Maybe this IS an upgrade thing; a V14 upgrade thing. Either way... that would seem to imply that you are correct and the ISNULL() should be able to be ditched at this point, no? I suppose a simple confirmation from a Tessitura staff member would not go amiss, but in any case, we are probably pretty safe here.
John A. Moskal II said:not a single one constituent in our database has a NULL value in that column.
The T_CUSTOMER table definition for that column is
[inactive] [int] NOT NULL,
...so I think it's safe to assume that there will be no nulls there.
I see that C_CUSTOMER allows nulls in inactive, which fits with my memories, i.e. I don't think we've had nulls in that column since we converted our data back in 2008.
We upgraded to v14 way back last year in October or November, and to 14.1 about a month ago. I promise I was looking at all this after that. I promise!
Little old me did not bother to check the table definition, though that would have been the solid move. I suppose that pretty much ends the discussion then.
I had hoped to run some analysis on our customers with null values to get a better sense of their age and recency of being touched, but now they're all gone. Anyway, that's my question answered, if obliquely by Tessitura elves.
Tessitura Elf, artist's impression