I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK.
The Table is updated often and has close to 2 million rows. there are no other non clustered indexes in this table.
I have a view that pulls necessary field from the table and does a left outer join with another table which has only 7 rows. It then adds 4 filters on the where clause. it retrieves about half a million record but takes about a minute.
Is it normal? How can I make it to run for less than five seconds? The Server has 64 GB RAM and 2 processors with 8 cores each. Its Hard Drive Space is about a TB.
Do you think it is improper Indexing? If so How can I figure out which Column to index to make it run faster? I need to keep in mind that there are inserts , updates and deletes on a regular basis.
The query is like this:
SELECT a,b,c,d,e,f,g,h,i,j FROM TBLA LEFT OUTER JOIN TBLB ON TBLA.Objectuid = TBLB.objectuid WHERE TBLA.a NOT LIKE '11%' AND TBLA.b <> '2100' AND TBLA.c <> 'alloc' AND TBLA.d NOT LIKE 'lm-%' AND TBLA.e NOT LIKE 'ap-%' AND TBLA.f >= '2014'
TBLA has 99 columns out of which the view only uses 15. TBLA has a PK which is clustered is a GUID. even when i remove the index it takes the same amount of time.