I am currently running a stored procedure that is set up to return results using one of fourvarying queries depending on what variables are sent in with the call to the procedure. For the most part, the sproc executes reasonably fast (under 2 seconds via query analyzer). There are four joins in each query and the where clause in each is testing against three different freetext searches (ie searching for the submitted text within three different indexed columns in separate tables).
What I'm wondering is whether it would be more efficient (faster) to split the sproc into 4 versions that I call specifially from the application depending on the final query required.
Page returns from the searches being executed sometimes exceed 6 seconds (about 6 times in an hour) and sometimes extend as long as 20 seconds + (unacceptable). These delays may be due to other issues/conflicts; I just want to be sure my sql is as efficient as it can be while I also look into other areas of slowdown.
The application is a coldfusion (cfmx7) web app running on a W2000 server with IIS5.0. I pass four variables to the sproc in each call. The state of the variables sent determines which query is run withing the sproc.
Any help, suggestions appreciated.
billbiv
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
>What I'm wondering is whether it would be more efficient (faster) to split the sproc into 4 versions that I call specifially >from the application depending on the final query required.
if you can hanle this at the front end(depending on the variale, call appropriate sp), then this will e better
Now four joins won't always reduse the performance...
Post the SPfor more help
create proc sp_select_products_like_se
-- searchItem may be single word or phrase.
-- searchItem is the text entered
-- mfg code of '00' means none entered
-- catID of 0 means none entered
as
--------------------------
if @mfgID = '00'
--------------------------
if @catID > 0 --- execute phrase, cat
begin
SELECT TOP 100
web_product_number,
mfgid_code,
db_product_title,
mfgsubgroup_name,
web_product_title,
webtext_product_descriptio
mfgid_name
from dbo.category_relations_t
JOIN dbo.cssi_category_t ON dbo.cssi_category_t.cscat_
JOIN dbo.db_t ON dbo.web_data_t.web_product
LEFT JOIN dbo.web_text_t ON dbo.web_text_t.webtext_web
JOIN dbo.mfg_id_t ON dbo.web_data_t.web_mfgid_c
LEFT JOIN dbo.mfg_subgroup_t ON dbo.web_data_t.web_mfgsubg
LEFT JOIN
FREETEXTTABLE(db_t,db_prod
ON db_t.db_product_number = KEY_TBL_1.[KEY] LEFT JOIN
FREETEXTTABLE(mfg_subgroup
ON mfg_subgroup_t.mfgsubgroup
LEFT JOIN
FREETEXTTABLE(web_text_t,w
ON web_text_t.webtext_web_pro
WHERE dbo.category_relations_t.c
and (
(FREETEXT (db_product_title, @searchItem) OR
FREETEXT (mfgsubgroup_name, @searchItem) OR
FREETEXT (webtext_product_descripti
)
ORDER BY KEY_TBL_1.RANK DESC, KEY_TBL_2.RANK DESC, KEY_TBL_3.RANK DESC
end
else -- execute 1, phrase only
begin
SELECT TOP 100
web_product_number,
mfgid_code,
db_product_title,
mfgsubgroup_name,
web_product_title,
webtext_product_descriptio
mfgid_name
FROM (dbo.web_data_t JOIN dbo.db_t ON (dbo.web_data_t.web_produc
LEFT JOIN dbo.web_text_t ON dbo.web_text_t.webtext_web
JOIN dbo.mfg_id_t ON dbo.web_data_t.web_mfgid_c
LEFT JOIN dbo.mfg_subgroup_t ON dbo.web_data_t.web_mfgsubg
LEFT JOIN
FREETEXTTABLE(db_t,db_prod
ON db_t.db_product_number = KEY_TBL_1.[KEY]
LEFT JOIN
FREETEXTTABLE(mfg_subgroup
ON mfg_subgroup_t.mfgsubgroup
LEFT JOIN
FREETEXTTABLE(web_text_t,w
ON web_text_t.webtext_web_pro
WHERE (FREETEXT (db_product_title, @searchItem) OR
FREETEXT (mfgsubgroup_name, @searchItem) OR
FREETEXT (webtext_product_descripti
AND web_display_flag = 1
ORDER BY KEY_TBL_1.RANK DESC, KEY_TBL_2.RANK DESC, KEY_TBL_3.RANK DESC
end
else --mfg is defined
if @catID > 0 -- execute 3: phrase, mfg, cat
begin
SELECT TOP 100
web_product_number,
mfgid_code,
db_product_title,
mfgsubgroup_name,
web_product_title,
webtext_product_descriptio
mfgid_name
--from category_relations_t first
FROM dbo.category_relations_t
JOIN dbo.cssi_category_t ON dbo.cssi_category_t.cscat_
join dbo.web_data_t on dbo.category_relations_t.c
JOIN dbo.db_t ON dbo.web_data_t.web_product
LEFT JOIN dbo.web_text_t ON dbo.web_text_t.webtext_web
JOIN dbo.mfg_id_t ON dbo.web_data_t.web_mfgid_c
LEFT JOIN dbo.mfg_subgroup_t ON dbo.web_data_t.web_mfgsubg
LEFT JOIN
FREETEXTTABLE(db_t,db_prod
ON db_t.db_product_number = KEY_TBL_1.[KEY]
LEFT JOIN
FREETEXTTABLE(mfg_subgroup
ON mfg_subgroup_t.mfgsubgroup
LEFT JOIN
FREETEXTTABLE(web_text_t,w
ON web_text_t.webtext_web_pro
WHERE dbo.category_relations_t.c
AND mfgid_code = @mfgID
AND web_display_flag = 1 AND
(FREETEXT (db_product_title, @searchItem) OR
FREETEXT (mfgsubgroup_name, @searchItem) OR
FREETEXT (webtext_product_descripti
ORDER BY KEY_TBL_1.RANK DESC, KEY_TBL_2.RANK DESC, KEY_TBL_3.RANK DESC
end
else -- cat not defined, exectute 2: phrase, mfg
begin
SELECT TOP 100
web_product_number,
mfgid_code,
db_product_title,
mfgsubgroup_name,
web_product_title,
webtext_product_descriptio
mfgid_name
--from mfg_id_t
FROM dbo.mfg_id_t
JOIN dbo.web_data_t ON dbo.web_data_t.web_mfgid_c
LEFT JOIN dbo.mfg_subgroup_t ON dbo.web_data_t.web_mfgsubg
LEFT JOIN dbo.web_text_t ON dbo.web_text_t.webtext_web
LEFT JOIN
FREETEXTTABLE(db_t,db_prod
ON db_t.db_product_number = KEY_TBL_1.[KEY] LEFT JOIN
FREETEXTTABLE(mfg_subgroup
ON mfg_subgroup_t.mfgsubgroup
LEFT JOIN
FREETEXTTABLE(web_text_t,w
ON web_text_t.webtext_web_pro
WHERE mfgid_code = @mfgID AND web_display_flag = 1
AND
(FREETEXT (db_product_title, @searchItem) OR
FREETEXT (mfgsubgroup_name, @searchItem) OR
FREETEXT (webtext_product_descripti
ORDER BY KEY_TBL_1.RANK DESC, KEY_TBL_2.RANK DESC, KEY_TBL_3.RANK DESC
end
I had thought there might have been a way to combine the queries, I see now that is not fesible.
Unless you were planning to execute the queries separately, I would leave them exactly as it is. In other words, it the four stored procedures were only going to be called by this stored procedure, than I see no advantage, whatsoever.
Unrelated, but I would lose the "TOP 100"
Business Accounts
Answer for Membership
by: acperkinsPosted on 2006-02-24 at 18:01:11ID: 16043970
Perhaps it would help if you posted your stored procedure as it stands now.