February 11, 2005 at 5:43 am
I have a very large table (circa 12 million recs) and I want to perform repeated queries on a subset of the contents. The table contains a RecordID (Type INT Identity field and also set to be the primary key).
I have already created a second table containing a list of all the RecordIDs of the subset I want to work with (circa 400K records) from the results of an earlier query.
My problem is that when I perform an inner join between my two tables, SQL insists on performing an Index Scan on the larger table instead of an index seek based on the smaller one. Thus I am getting absolutely no performance benefit from using a second table to define the record subset.
I can find no logical reason why SQL should think that the index scan is more efficient ofer an index seek. Can anyone advise please?
February 11, 2005 at 6:33 am
See if the PK is a clustered index as well...... if so it's logical it will fall back to an index scan...
February 12, 2005 at 8:22 am
Can you post your query
February 12, 2005 at 9:22 am
3.2 Place the index and data files on different disks(RAID10). Place the log file on a different disk(RAID 1) . Place the tempdb on its own drive.
3.3 Depends. Weekly full, daily differential and hourly log. (This would vary on your SLA)
3.4 You can use the rebuildm.exe utility.
3.5 Back up the current log file and then perform a restore.
3.6 Try to reset status using sp_resetstatus and if this doesn't work, you can put the database in emergency mode and pull out all the data.
3.7 truncate. you cannot restore the log after that point.
3.8 Auto Close,
3.9 backup database customer to disk = 'c:\mssql7\backup\customer.bak'
3.10 backup log customers with truncate_only
3.11 sp_lock
3.12 sysusers, sysxlogins, sysobjects.
February 12, 2005 at 9:22 am
Sorry, please ignore the above post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply