Image column - How to put a where clause

Question

Image column - How to put a where clause

badkow

Right there with Babe

Points: 775
More actions
January 17, 2010 at 4:24 pm

#71893

I have an image column(FileBlob) in a large table with about 50,000 records. Now I have a query which needs to retrieve only those rows that have an image i.e. FileBlob is not NULL.
The problem is the execution plan shows that the image column is being retrieved to do the filtering and so, that is causing heavy reads on my system.
Any solutions?
Create table dbo.AppTable
(ID int,
FileBlob image,
ModifiedDate datetime,
CONSTRAINT [PK_AppTable]
PRIMARY KEY CLUSTERED ([ID])
)
on Primary;
Indexes:
Has clustered index on ID
Query Used:
select ModifiedDate
from AppTable
where id = @rid
and fileblob is not null
BitBucket- Thanks for responding. I tried to upload what I can.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

badkow

It would help those who want to help you to have some further information. Can you post the table definition, perhaps some sample data (does not have to be real or in any way compromise your organizations data or security) as well as your Select statement.

In other words try to follow the link in my signature block

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ykottu SSC Rookie Points: 47 More actions · Answer 2

I think the problem is in that it is trying to retrieve the entire image when its check the not null constraint

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Is there any way to run a checksum on blobs?

If so just add a calculated column as checksum. Figure out what checksum of null is then compare to that value.

Plan B would be to add a tinyint flag Like ImageIsSet and check against that. Of cours with that option you need to change all the programs accessing that table...

badkow Right there with Babe Points: 775 More actions · Answer 4

That was the first thought that came to my mind. I could add a persisted computed column that does a textvalid(imagecolumn) and stores it in a separate column of data type bit but the problem is SQL Server 2000 does not have persisted computed columns and either way, I don't think making changes is an option for me.

The only way out seems to be to have an indexed view that stores this information but we generally keep away from indexed views and I was wondering if someone on here had a good solution. 🙂

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

WHERE ImageBlob > 0x00

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

badkow Right there with Babe Points: 775 More actions · Answer 6

sounds very interesting!!! let me login to my business machine and see if that reduces the execution plan.

🙁

Aww man.. it says "the text, ntext, and image data types cannot be compared or sorted except when using the IS NULL or LIKE operator

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 7

Sorry about that... that's why I only store file names for images.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

badkow Right there with Babe Points: 775 More actions · Answer 8

I know but I am having to work with what is there. When SQL Server parses the query, it is retrieving all the images that satisfy the first condition i.e. id = @rid and retrieving the FILEBLOBs of all those rows so it can filter using the NOT NULL operator;

The performance as a result is very slow. Its a very interesting problem though.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Yes you have persisted columns in sql 2000, they just don't have that name. All you have to do is put the column in an index and it'll persisted there. Which is exactly what you need here.

check this out :

SELECT DATALENGTH(NULL)

SELECT DATALENGTH(CAST(NULL AS IMAGE))

SELECT DATALENGTH(CAST(0X00 AS IMAGE))

The first 2 return null, #3 returns 1. You could put a where condition on the DATALENGTH persisted column < 2 and that should fetch all nulls. You would need to test this on a single pixel image tho to confirm you considered everything.

badkow Right there with Babe Points: 775 More actions · Answer 10

Persisted computed columns are different from what you are talking about.

You are talking about Non-Clustered indexes (correct me if I am wrong.) They are only pointers to clustered index. If you look at the execution plan that is an attachment to the opening post, you will see that FileBlob (the image column) is being retrieved to check for the IS NOT NULL condition in the where clause and this is degrading the performance.

A persisted computed column will only be useful if it stores information about which row contains an image (like a bit column, containing TEXTVALID(Image)).

Maybe I misunderstood you, can you tell in detail your solution.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Please add a calculated as checksum or textisvalid(blob image).

add that column in the index of your current query. When the index is created the value will be calculated AND SAVED in the index.

Run the query with the current where condition is see how many pages are accessed.

Then run the version with the new indexed column. If everything is done right, you'll get a dramatic difference in page reads.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 12

Now, there's an idea. Thanks, Remi.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Jeff Moden (1/18/2010)
Now, there's an idea. Thanks, Remi.

Yup, haven't used that one in a while, but that's a perfect exemple of how it could be used.

Of course now with 2008, that discussion is a better known fact (wasn't quite a widespread knowledge back with 2000).

badkow Right there with Babe Points: 775 More actions · Answer 14

Thanks a lot! That works perfectly. You are the man. :hehe: