This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / SQL Server performance question
-marsher(LP3A);
2008-5-22
{517}
(#4462325@0)
-
Press Ctrl+L in your SQL Analyzer (SQL Management Studio) to check the Execution Plan and you will know the reason.
-bdbs(不多不少);
2008-5-22
(#4462361@0)
-
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?
-marsher(LP3A);
2008-5-22
(#4462390@0)
-
我不用SQL SERVER但可以帮你分析一下你在t2.col2上有个索引但数据分布不太均匀，有的值很多有的少，你要用的恰好是比较少的，也就是索引能发挥作用的那些值。
如果用静态SQL，带有变量v1，SQL优化器生成执行计划的时候不知道你变量的值，它的判断是不用这个索引更有效。用了动态SQL, 变量变为常量，优化器就能正确使用索引了。
你如果想用静态动态SQL可以加提示(hints)使用指定的索引。
因为没有看到完整的SQL这仅仅是我的猜测。
-newkid(newkid);
2008-5-22
{367}
(#4462400@0)
-
正解
-bdbs(不多不少);
2008-5-22
(#4462455@0)
-
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?
-marsher(LP3A);
2008-5-22
(#4462457@0)
-
你可以比较一下加hints前后的执行计划，如果它没有按你的提示生成那可能是语法没写对，这个我就不懂了，去请教楼上的专家吧
-newkid(newkid);
2008-5-22
(#4462481@0)
-
好像T-SQL 的hint 是这样的 SELECT * FROM table WITH(INDEX(IDX_xxx)) ... IDX_xxx 是index的name
-canadiantire(轮胎 - favete lingui);
2008-5-23
(#4463203@0)
-
newkid和bdbs确实专家，学习了。
-canadiantire(轮胎 - favete lingui);
2008-5-23
(#4463211@0)
-
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?
-newbuddie(就要登陆);
2008-5-25
{284}
(#4465619@0)