February 1, 2005 at 6:42 am
I have a table ( 75 million rows ) with a text column sparsely populated ( generally entries are only a hundred characters, max 6k in 3 rows ) , I changed the column to a varchar(7000) as this reduces the overall table storage and makes the database smaller.
It seems that in some circumstances that fairly simple queries against this table run up to 15 times slower with the column as a varchar - there are no full text indexes or searches. All stats etc. up to date indexes same etc. etc. ( same query - in all cases nothing wierd in the query plan )
Any ideas ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 1, 2005 at 3:55 pm
My best guess is that by moving the text into a varchar you increased the number of data pages in your table considerably. This may have caused pretty extensive fragmentation, reducing the effectiveness of your indexes. Have you rebuilt your indexes?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2005 at 5:49 am
Yup clustered indexes rebuilt, stats updated, however it does give me one possible lead
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply