×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

SQL Server performance question

A SQL runs very slow: select * from t1 inner join t2 on t1.col1=t2.col1 where t2.col2=@v1, the variable @v1 is assigned an INT value right before. Then I changed it using a dynamic SQL:
SET @sql='select * from t1 inner join t2 on t1.col1=t2.col1 where t2.col2='+convert(varchar,@v1), exec (@sql). after the change, it runs much much faster. Did someone can help to explain why? Thanks. Note, in real job, the table joining is much more complicated than I write here, so the performance improvement is significant.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / SQL Server performance question
    A SQL runs very slow: select * from t1 inner join t2 on t1.col1=t2.col1 where t2.col2=@v1, the variable @v1 is assigned an INT value right before. Then I changed it using a dynamic SQL:
    SET @sql='select * from t1 inner join t2 on t1.col1=t2.col1 where t2.col2='+convert(varchar,@v1), exec (@sql). after the change, it runs much much faster. Did someone can help to explain why? Thanks. Note, in real job, the table joining is much more complicated than I write here, so the performance improvement is significant.
    • Press Ctrl+L in your SQL Analyzer (SQL Management Studio) to check the Execution Plan and you will know the reason.
      • I did see the Execution plan for the SQL statement. I notice a part of Bookmark Lookup changed significately. Before the change, it was around 50%, after, it's around 0%. much more IO cost. I don't know why?
    • 我不用SQL SERVER但可以帮你分析一下
      你在t2.col2上有个索引但数据分布不太均匀,有的值很多有的少,你要用的恰好是比较少的,也就是索引能发挥作用的那些值。
      如果用静态SQL,带有变量v1,SQL优化器生成执行计划的时候不知道你变量的值,它的判断是不用这个索引更有效。用了动态SQL, 变量变为常量,优化器就能正确使用索引了。
      你如果想用静态动态SQL可以加提示(hints)使用指定的索引。
      因为没有看到完整的SQL这仅仅是我的猜测。
      • 正解
      • You might be right. There is an index in the column. However, I added an index hint as t2 (index=IX_t2), but it still takes much longer then the dynamic SQL. Am I right adding the index hint like that?
        • 你可以比较一下加hints前后的执行计划,如果它没有按你的提示生成那可能是语法没写对,这个我就不懂了,去请教楼上的专家吧
        • 好像T-SQL 的hint 是这样的 SELECT * FROM table WITH(INDEX(IDX_xxx)) ... IDX_xxx 是index的name
        • newkid和bdbs确实专家,学习了。
          • You guys are so good. I have even not heard this kind of thing before.
            Questions:
            1. What kind of work would require this kind of queries?
            2. What kind of level (in terms of job position) need to master this kind of knowledge?
            3. Does PL/SQL have same concepts, such as dynamic SQL, hints, etc?
            4. How many years do I need to match your guys' skills?