December 3, 2004 at 3:34 am
My problem is as follows:
I have four views which are all simple select * from table statements against tables from another database.
The result of this is used to populate a datamart with an insert into. I.E. The datamart is emptied before each run.
Insert into target
(select viewa.column, viewb.column, viewc.column, viewd.column
from viewa inner join on a=b, b= c, c = d ..all the views linked together.
I have indexes in the joins but they are only scanned in the execution plan.
I want to know is:
1. Is it worth even trying to use indexes if I am selecting so many rows and there is no where clause in existence.
2. If I do use views, should I be looking to use indexed views. Would it help in this scenario.
As a side issue, I always though that if you joined views indexes are never used. However, as I stated earlier, the indexes are being scanned when looking at the execution plan.
December 3, 2004 at 6:16 am
#1 Indexes are always good. I have ran execution of some queries that took 10 minutes and when I changed WHERE and forced it to use the proper index took 10 seconds.
#2 IF the query is already using the indexes I don't think would help. Plus if the underlying table schema ever changes you will need to rebuild these views..
#3 I think it really depends on how the views are built and if they are designed in such a way to use the indexes (proper order, etc..)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 3, 2004 at 7:48 am
If you are selecting all or most of the data from the table, the indexes don't help because a scan will occur. Chcejing fragmentation often can speed this up.
I agree with AJ though. Indexes are always good. You might start using these tables in other ways.
December 3, 2004 at 9:15 am
Thanks for the replies. More or less what I thought.
As the tables are only used as in a staging area, I won't bother adding any indexes in from now on as they take 45 minutes.
I am not going to gain 45 minutes in execution with index scans instead of table scans.
My remit was to reduce load time, so hey, I chopped off a fair bit with the minimal amount of actual work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply