Prev: Plain Text In Image Field
Next: Operation between alias
From: Mecn on 10 Jun 2010 17:30
I have sql table (20million records)with a image field A. I need to set up a
where clause like ----
select * from tablea where A(image type) is not null.
that query takes forever.
How do I optimize that query?

Thanks,


From: Erland Sommarskog on 10 Jun 2010 18:01
Mecn (mecn(a)yahoo.com) writes:
> I have sql table (20million records)with a image field A. I need to set
> up a where clause like ---- select * from tablea where A(image type) is
> not null.
> that query takes forever.
> How do I optimize that query?

About how many rows in the table do you expect to have a non-NULL value in
A? If it 16 million rows, and the image columns are big, it will take
some time to return all that data. And your client may choke before the
query completes.

If only a small amount of rows have a value in A, you could add
a computed column:

has_image AS CASE WHEN A IS NULL THEN 0 ELSE 1 END

and then index that column, and run your query over that column.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Plain Text In Image Field
Next: Operation between alias