Sql query timeout, expensive cast operation?

[Ted]

I need some tips to boost the performance on the following query.
The problem is that it times out once in a while, and then again runs
normally in most cases.
The clue is to compare a textual value stored as an image data type, but I
don't know if the cast operation could be done in another way.
Any tips?

SELECT DISTINCT pk_product FROM tbl_product P
JOIN tbl_product_con tent PC ON P.pk_product = PC.fk_product
JOIN tbl_content C ON PC.fk_content = C.pk_content
JOIN tbl_content_rel REL ON C.pk_content = REL.fk_content
JOIN tbl_filegroup FG ON REL.fk_filegrou p = FG.pk_filegroup
JOIN tbl_filegroup_f ile FF ON fg.pk_filegroup = FF.fk_filegroup
JOIN tbl_file F ON ff.fk_file = F.pk_file
WHERE
P.fk_product_ty pe = 45 AND
CAST(CAST(f.dat a AS VARBINARY) AS NVARCHAR(20)) = '1234'
/ted

[Erland Sommarskog]

Ted (te*@organizer. net) writes:

I need some tips to boost the performance on the following query.
The problem is that it times out once in a while, and then again runs
normally in most cases.
The clue is to compare a textual value stored as an image data type, but I
don't know if the cast operation could be done in another way.
Any tips?

SELECT DISTINCT pk_product FROM tbl_product P
JOIN tbl_product_con tent PC ON P.pk_product = PC.fk_product
JOIN tbl_content C ON PC.fk_content = C.pk_content
JOIN tbl_content_rel REL ON C.pk_content = REL.fk_content
JOIN tbl_filegroup FG ON REL.fk_filegrou p = FG.pk_filegroup
JOIN tbl_filegroup_f ile FF ON fg.pk_filegroup = FF.fk_filegroup
JOIN tbl_file F ON ff.fk_file = F.pk_file
WHERE
P.fk_product_ty pe = 45 AND
CAST(CAST(f.dat a AS VARBINARY) AS NVARCHAR(20)) = '1234'

Since you only retrive one column, it's better to use EXISTS:

SELECT P.pk_product
FROM tbl_product P
WHERE P.fk_product_ty pe = 45
AND EXISTS
(SELECT tbl_product_con tent PC
JOIN tbl_content C ON PC.fk_content = C.pk_content
JOIN tbl_content_rel REL ON C.pk_content = REL.fk_content
JOIN tbl_filegroup FG ON REL.fk_filegrou p = FG.pk_filegroup
JOIN tbl_filegroup_f ile FF ON fg.pk_filegroup = FF.fk_filegroup
JOIN tbl_file F ON ff.fk_file = F.pk_file
WHERE P.pk_product = PC.fk_product
AND CAST(CAST(f.dat a AS VARBINARY) AS NVARCHAR(20)) = '1234')

That will save you the sorting operation for the DISTINCT.

Whether the convert operation is the culprit, I don't know, but
I would not really expect so. The timeouts could be due to that
under some circumstances, the optimzer finds a less good plan.
But the reason could also be blocking. Since the timeouts occur
only intermittently, my bets go for blocking.

Then again, the query you posted appears to be a styled version of the
real McCoy. If I were to see the actual code, I might change my bets.

In any case, it's a good idea to examing the query plan for the query.
You can do this from Query Analyzer.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp