1

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.

  • 1
    What database & version is it? What is the query and view? – LowlyDBA Dec 11 '14 at 14:15
  • We can't really judge the index without seeing the query, and the indexes on the joined tables. Also what steps have you taken, is there a maintenance plan in place? We need more to go on. – Reaces Dec 11 '14 at 14:35
  • Yes I do Have maintenance Plans that reorganize indexes and updates statistics once a week. I used to have shrink database but I removed that as a part of the MP. The query is like this: Select a,b,c,d,e,f,g,h,i,j from TBLA left outer join TBLB where 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. – Pragya Dec 11 '14 at 17:45
  • John M,It is a Server 2003 and SQL server 2008. the view is simply like this: Create view abcd as Select a,b,c,d,e,f,g,h,i,j from TBLA left outer join TBLB where 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' – Pragya Dec 11 '14 at 17:46
  • 1
    Any chance we can see the execution plan? :D – Ali Razeghi Dec 11 '14 at 22:17
1

Have you checked your actual rows vs estimated rows in the actual execution plan? Also, have you checked the reads from your query compared to the reads from a table made up of just the fields you are selecting on?

Without even seeing any of that I would say you definitely need a non-clustered index. My guess is either one or both of the following is occurring in your query in regards to the questions above:
1) The actual rows are way off from the estimated rows (because the sql optimizer has no good estimates on what is actually in your tables)
2) The IO from your query listed above is at least a magnitude higher than if you just inserted those same 10 fields into another table and ran the same query (if those 10 fields are no larger than your average across all those 99 fields)

Even if you don't use those other 89 fields (since your query looks to only select 10 fields), SQL can only read what is on the page -- all 99 fields. Think of it like this: You have a word doc all your data printed out on paper. All your records are striped in one long string; at the end of the each record (99 fields), you have some delimiter. Then it starts over again with the next record; again all in one long string printed across all these physical piece of paper. Now if you are wanting to read just 10 of those fields, you still have to look at that whole piece of paper and pull out just those 10 fields. This is exactly how SQL Server works. If those 10 fields make up 10% of the actual data length, you are wasting 90% of the paper in his massive book you have printed out. If you create a non-clustered index on just these 10 fields you will save a lot of reading (because now these sheets of paper you have printed out will be completely crammed full of good data and the overall size will be 10% the original book. You will of course want to index on something that limits the most (based on the criteria of your query) and then just throw the other fields into an includes. Also, if those 7 rows you mention in your other query are static values, I would recommend to even go further and do a filtered index on those 7 values OR a filtered index on one of your where criteria. That will shrink your index down even further.

0

Add an index for each A, B, C, D, E, F

Or you could add one composite index on A, B, C, D, E, F

0

you can have a filter index if filter values are static or if not they you can have a composite index based on your filter columns and include rest of columns in it out of 15 col which are in that view if there is not too much insert,update,delete
but as you said there is no other index except clustered then you may probably bear the cost of another index...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.