| Author |
 Topic  |
|
|
boggyboy
Yak Posting Veteran
USA
57 Posts |
 Posted - 04/23/2012 : 16:40:05
|
To reduce the row count of a fact table, I pull in only rows from the source views that have non-null data. The problem is, when I add a WHERE clause, the query time increases from 2 to 10 minutes. Would it make sense to create a new view that does the filtering ahead of time? I'm unsure if that would equate to a performance gain. Thanks in advance.
Nick W Saban |
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 04/23/2012 : 17:01:16
|
are filtering doing non-null across all columns or on a specific field?
do you have an index on that column?
what type of datatype column is it?
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
 |
|
|
boggyboy
Yak Posting Veteran
USA
57 Posts |
Posted - 04/23/2012 : 17:59:40
|
Thanks for the reply...
I do filtering on 3 fields. I check for null on each. If the source view contains null on all three fields, I wont insert the record into the fact table. The datatype is MONEY, and there are no indexes that I'm aware of.
Nick W Saban |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 04/23/2012 : 23:03:37
|
there you go Nick boggyboy. With no index you are guaranteed 100%. make sure you add index on these columns you are filtering on.
are you doing anything else you are not telling us. confess all your sins. sort by, between, top 100 etc.
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
boggyboy
Yak Posting Veteran
USA
57 Posts |
Posted - 04/24/2012 : 07:01:50
|
Thanks!! My sins are I still am a newbie! My query goes something like this...no fancy betweens or other comparisons.
insert into foo (Field1, field2, field3, field4)
(select field1, field2, field3, field4
from bar
where field2 > 0 or field2 > 0 or field3 > 0)
Nick W Saban |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 04/24/2012 : 11:40:21
|
so yes you will need indexes on those puppies. another thing is why do you have them null? could it possible you can have default value of 0 instead of NULL
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3425 Posts |
Posted - 04/24/2012 : 12:16:35
|
you may want to look into filtered indexes as you have 2008 also.
If you are checking for NULLS (or the absence of NULLS) then that sounds a good candidate for a set of filtered indexes.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
boggyboy
Yak Posting Veteran
USA
57 Posts |
Posted - 04/25/2012 : 08:15:59
|
Awesome! Thank you very much for all the help!
Nick W Saban |
|
|
| |
Topic |
|
SQL View to improve performance?
