Finding Not null on multiple columns using COALESCE

Question

I have a query to find not null on 2 columns on a table which is a view , hence it is taking a lot of time for execution. The query is : Query1

SELECT [Table1].M, [[Table1]].B, [Table1].P
FROM [Table1]
WHERE ((([[Table1]].B) Is Not Null) AND (([[Table1]].P) Is Not Null));

Does the below query does the same function as Query1 with faster execution time ?

SELECT [Table1].M, [[Table1]].B, [Table1].P
FROM [Table1]
WHERE COALESCE (([[Table1]].B),([[Table1]].P)) Is Not Null

Any help would be of great help and thanks in advance.

The view query

select dbo.TABLE1.[COL1]
    , dbo.TABLE1.[COL2]
    , RIGHT(dbo.TABLE1.M, 12) as M
    , dbo.TABLE2.[MD]
    , dbo.TABLE1.[COL3]
    , dbo.TABLE1.[COL4]
    , dbo.TABLE3.COL1
    , dbo.TABLE3.[COL2]
    , dbo.TABLE3.[COL3]
    , dbo.TABLE4.[COL1]
    , dbo.TABLE5.[COL1]
    , dbo.TABLE6.[COL1]
    , dbo.TABLE7.[COL1] as [BA]
    , dbo.TABLE8.[COL1]
    , dbo.TABLE3.[COL4]
    , dbo.TABLE3.[COL5]
    , dbo.TABLE3.[COL6]
from dbo.TABLE1
left outer join dbo.TABLE2
    on dbo.TABLE1.M = dbo.TABLE2.M
left outer join dbo.TABLE3
    on dbo.TABLE1.M = dbo.TABLE3.M
left outer join dbo.TABLE5
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE5.OBJ
left outer join dbo.TABLE6
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE6.OBJ
left outer join dbo.TABLE7
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE7.OBJ
left outer join dbo.TABLE4
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE4.OBJ
left outer join dbo.TABLE8
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE8.OBJ
where (
        (
            dbo.TABLE1.[COL1] not in (
                'XX'
                , 'YY'
                )
            )
        and (dbo.TABLE1.COL5 = 'x')
        )

It's probably spending half the time working out all those parentheses. ;) — Adrian Wragg, Nov 04 '14 at 09:19
More seriously, have you tried creating test data and seeing if your premise is correct or not? Run it a few thousand times and check the output and execution times, etc. — Adrian Wragg, Nov 04 '14 at 09:20
I'm an amateur learning my tricks through the forums like this. It would be of great help if I get some ideas how to approach situations like this. Thanks — WiredTheories, Nov 04 '14 at 09:21
Pay more attention to my second comment than my first (although the first *is* a hint towards making it a bit more readable and thus maintainable). Create two loops - one for each - and run each version, see which is faster. — Adrian Wragg, Nov 04 '14 at 09:23

score 3 · Accepted Answer · answered Nov 04 '14 at 09:24

3

No, both queries aren't equivalent.

The WHERE clause in the second one is equivalent to

WHERE [[Table1]].B Is Not Null OR [[Table1]].P Is Not Null

COALESCE will evaluate the first parameter and return it if not null. Otherwise, it will return the second one if not null, and so on, until reaching the last parameter, which will be returned whatever its value. So COALESCE(...) IS NOT NULL needs only one not null value to return true, not all.

answered Nov 04 '14 at 09:24

xlecoustillier

16,183
14
60
85

Thanks for correcting me on that. Is there a way if I could use a function for finding not nulls on multiple columns. – WiredTheories Nov 04 '14 at 09:26
I don't think so. Anyway, I doubt the `NOT NULL` tests are the bottleneck here. What do your indexes look like ? How big is your table ? – xlecoustillier Nov 04 '14 at 09:30
My table is around 3 million record which is a view. I dont have a schema , so should I include WITH SCHEMABINDING fro views. – WiredTheories Nov 04 '14 at 09:44
I don't think so, but I don't know really this option. My bet is an index issue. – xlecoustillier Nov 04 '14 at 09:47
1

The view is formed from multiple left n right joins. So I can't index them I believe. I get an error when I do with Schemabinding for indexing reasons. Cannot create index on view "xxx" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead. – WiredTheories Nov 04 '14 at 09:52
If you're view is based on a particularly complex query, it's not surprising that it takes some time to execute, especially with outer joins. – xlecoustillier Nov 04 '14 at 10:28
I did some reverse engineering and added some indexes on certain fields. I reran the query and it took just 1.32 mins to return 3 million records :). – WiredTheories Nov 04 '14 at 12:53

score 0 · Answer 2 · answered Nov 04 '14 at 09:53

0

I've tried this out on a table in my development DB. Here are the results:

with only PK index: 2 minutes for 4 million selected records out of 8 million table

with index on 3 selected columns (none of them PK) 1.8 seconds.

You might need to do some testing to get the right indexes for your setup but here is the sample of what i changed:

select [col1]
    , [col2]
    , [col3]
from [dbo].[tbl]
where col2 is not null
    and col3 is not null

create nonclustered index [idx_test] on [dbo].[tbl] (
    [col2] asc
    , [col3] asc
    ) INCLUDE ([col1])

answered Nov 04 '14 at 09:53

Tristan

1,004
7
14

1

My table is a view and I'm unable to create joins on them due to having joins in them. I get an error as " Cannot create index on view "xxx" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead " – WiredTheories Nov 04 '14 at 09:56
@Lewan a view isnt a table. a view views tables. make the index on the tables your view is viewing. – Tristan Nov 04 '14 at 10:05
I have indexes on pirmary keys on most tables the view uses. I was just thinking if I could have index views which I can't in my case since my view involves outer joins. I don't want to rewrite several queries to suit this. – WiredTheories Nov 04 '14 at 10:24
@Lewan Can you post a anonymized version of the view definition? – Tristan Nov 04 '14 at 10:25
I'm unable to post the code here. I have included the query in the question. Please check. Thanks – WiredTheories Nov 04 '14 at 10:44
@Lewan Rather than just having indexes on your primary keys, look at the queries you're running and tailor them accordingly; also investigate which field(s) may be best for a clustered index as (IME) that's rarely the primary key. – Adrian Wragg Nov 04 '14 at 12:11
@AdrianWragg I did some reverse engineering and added some indexes on certain fields. I reran the query and it took just 1.32 mins to return 3 million records :). Thanks for idea I appreciate it. – WiredTheories Nov 04 '14 at 12:52

Finding Not null on multiple columns using COALESCE

2 Answers2