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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy