By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,012 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

Sql query timeout, expensive cast operation?

P: n/a
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_content 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_filegroup = FG.pk_filegroup
JOIN tbl_filegroup_file FF ON fg.pk_filegroup = FF.fk_filegroup
JOIN tbl_file F ON ff.fk_file = F.pk_file
WHERE
P.fk_product_type = 45 AND
CAST(CAST(f.data AS VARBINARY) AS NVARCHAR(20)) = '1234'
/ted
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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_content 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_filegroup = FG.pk_filegroup
JOIN tbl_filegroup_file FF ON fg.pk_filegroup = FF.fk_filegroup
JOIN tbl_file F ON ff.fk_file = F.pk_file
WHERE
P.fk_product_type = 45 AND
CAST(CAST(f.data 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_type = 45
AND EXISTS
(SELECT tbl_product_content 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_filegroup = FG.pk_filegroup
JOIN tbl_filegroup_file 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.data 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.