Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Query Optimization

Author  Topic 

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-06-28 : 20:28:03
Hi,

Please advise how i can optimise this query:

insert @tblKeyword
(
Keyword,
IDSource,
SourceID,
KeywordTemplateID
)
select
dbo.ufn_ppcConvertToKeyword(pm.SYSProductName, default, default, default, default),
'PRD',
min(p.SYSProductID),
@DefaultPRDTemplateID
from tblSYSProduct p
join tblSYSProductName pm on p.SYSProductID = pm.SYSProductID and pm.UpdateDate>=@StartTime
where p.SYSProductDisabledCode = 'E' and pm.LanguageCode = 'EN' and p.WebSiteID = @WebSiteID and
not exists (
select *
from tblKeyword k1
where k1.Keyword = dbo.ufn_ppcConvertToKeyword(pm.SYSProductName, default, default, default, default)
and k1.WebSiteID = @WebSiteID and k1.KeywordTemplateID = @DefaultPRDTemplateID
)
and len(ltrim(rtrim(pm.SYSProductName))) > 0
group by dbo.ufn_ppcConvertToKeyword(pm.SYSProductName, default, default, default, default)


mk_garg

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-28 : 20:40:34
1) should us DDL and sample data for all tables involved, and be sure to include all indexes

2) explain to us what this INSERT is doing

3) we need the definition of the user defined functions used as well

4) this is only inserting rows into a table variable, technically no results are produced here ... are you sure that this step is the bottleneck? What happens to the table variable after this step?



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
   

- Advertisement -