如何优化以下查询？ [英] How to optimize following query ?

问题描述

我的程序中有一个类型需要1秒才能执行..我被告知要优化这个查询..我该怎么办...







这是sp



-------- ----------------

I have a type in my procedure which takes 1sec to execute .. i have been told to optimize this query .. how can i do this..



Here are sp

------------------------

ELSE IF(@in_type = 5) --Used in Catalogcl class 

BEGIN

    SELECT d.in_catalog_id, a.in_product_id, a.vc_product_name, a.vc_product_image_large,

    (CASE a.bt_product_image_exist WHEN 1 THEN a.vc_product_image_small WHEN 0 THEN '/product_small_images/comingsoon1.gif' END) AS vc_product_image_small,

  dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) AS mn_sale_price,

    d.vc_catalog_name + ' ' + a.vc_product_name AS vc_product_full_name

    FROM dbo.product a

    INNER JOIN dbo.catalog_product b ON a.in_product_id = b.in_product_id

    INNER JOIN dbo.website_catalog c ON b.in_catalog_id = c.in_catalog_id

    INNER JOIN dbo.catalog d ON c.in_catalog_id = d.in_catalog_id

    WHERE a.bt_active = @bt_active AND b.bt_active = @bt_active AND c.bt_active = @bt_active AND d.bt_active = @bt_active

    AND c.in_website_id = @in_website_id AND d.in_catalog_sub_type_id = @in_catalog_sub_type_id

    AND (@bt_best_sellers = 0 OR b.bt_best_sellers = @bt_best_sellers)

    AND dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) > 0

    ORDER BY a.vc_product_name

END

请建议

Please suggest

推荐答案

您好，

您在查询中使用的功能是catalog_fnc_get_sale_price，如果你可以扩展函数，在查询中它肯定会提高查​​询速度。对于每一行，在您的情况下调用函数。您可以使用执行计划验证它

Hi,
You are using function "catalog_fnc_get_sale_price" in your query , if you can expand the function , within the query it can surely in increse speed of query. AS for each row, function is being called in your case. you can verify it by using execution plan

与他人同意，查询看起来不错。您可以查看其他改进方法。

优化数据访问的十大步骤SQL Server：第一部分（使用索引） [ ^ ]

在SQL Server中优化数据访问的十大步骤：第二部分（重新考虑TSQL并应用最佳实践） [ ^ ]

在SQL Server中优化数据访问的十大步骤：部分III（应用高级索引和非规范化） [ ^ ]

优化SQL Server中数据访问的十大步骤：第四部分（诊断数据库性能问题） [ ^ ]

优化SQL Server中数据访问的十大步骤：第五部分（优化数据库文件并应用分区） [ ^ ]

Agree with others, the query looks good. You may look at other ways to improve things.
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

正如Amol_B建议的那样，确保你在select和where子句中调用的函数是有效和最优化 - 这是你最明显的惩罚。



接下来打开SSMMS中的实际执行计划（假设是sql server）并在结果中查找缺少的索引。



如果那里什么都没有，那就回去问问老板他为什么这么做nks它需要进行光学处理。

As Amol_B suggested make sure that the function you are calling in both the the select and where clause is both valid and optomised - it is your biggest obvious penalty.

Next turn on actual execution plan in SSMMS (assumes sql server) and look for missing indexes in the result.

If there is nothing there then go back and ask your boss why he thinks it needs optomising.

这篇关于如何优化以下查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！