sql server - Faster way to write specified query -
so in our company have large amount of articles. have customized search function search in these articles, , here piece of code generates part of searchquery needs optimalization.
dtmodif = daldoctype.fetchbyanyidentifier(false, "+") sb.append("(select ( ") each row in dtmodif.rows sb.append("isnull((select -sum(amount) ") sb.append("from sales." & row("tablename") & "detail ") sb.append("where articleid = articles.article.articleid ") sb.append("and dateadd(year,-1,getdate()) < timestamp ") sb.append("),0) ") sb.append("+ ") next dtmodif = daldoctype.fetchbyanyidentifier(false, "-") each row in dtmodif.rows sb.append("isnull((select +sum(amount) ") sb.append("from sales." & row("tablename") & "detail ") sb.append("where articleid = articles.article.articleid ") sb.append("and dateadd(year,-1,getdate()) < timestamp ") sb.append("),0)") sb.append("+ ") next sb = sb.remove(sb.length - 2, 2) sb.append(") )")
i every article stockmodifier positive (so more sold returned), , add negative sum of articles stockmodifier negative. in way can number times article sold. (can negative).
i want know now, there better way structure query? because select on multiple selects, affects performance in large databases.
thanks in advance.
first thing establish bottleneck coming from. so, run query in ssms , include actual execution plan
, find here...
specifically, using this..
.
take @ article, basic analysis of execution plan, identify db issues.
once done, if still experiencing issues, use tool antz profiler profile application. identify issues within code, , assist in pinpointing lines of code heavily used or need refactoring.
if code base looks ok, take @ ui related performance issues, might have 3rd party controls, or binding issues hampering performance.
also, consider environment, running on shared server? server heavily used etc?
Comments
Post a Comment