Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xpandre
Posting Yak Master
212 Posts |
 Posted - 2008-11-18 : 09:21:32
|
| Hi,I have couple of tables, table1 and table2Structure : Table1 : create table Table1 (id int identity(1,1), modified_date datetime)alter table Table1 add constraint pk1 primary key(id)create table Table2(id int identity(1,1),clid int)alter table Table2 add CONSTRAINT fk1 foreign key (clid) references Table1(id)create index cl_i1 on Table2 (clid)table1 has 20 million rows...while table2 has 40 million.I run this query:select claim1.id, claim1.modified_date from claim1,call1where claim1.id = call1.claimid and modified_date < getdate()-5select claim1.id, claim1.modified_date from claim1 inner join call1on claim1.id = call1.claimid where modified_date < getdate()-5It always goes fo the index scan, and since there are around 40 million records, it takes around couple of minutes to execute.I tried adding this index:create index i1 on table1(id,modified_date)Still it goes in for a index scan. Is there any way I can make the query do an index seek?Since table2 would have much more records than table1, is ther anyway I can position the tables in the join syntax for better performance?ThanksSam |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-21 : 10:43:39
|
| I was able to get an index seek by adding "and table1.id >0" into query but there was only a slight improvement in performance ( I put 1 million rows into each table). I also got a slight improvement putting function into a variable "set @t = getdate()-5" and use variable in Where Statement. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-21 : 11:09:40
|
1. You should index claimid column too in call1 table.2. Reverse order for the id, modified_date index.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Query Issue..