I know this falls into the black arts area of programming, but this is suddenly an area that I need to strengthen in my professional life. There are a couple of topics were this subject has been touched on but I'm not sure the item has been really addressed about how to become one with the system. For example: I became a phenomenally better C++ programmer when I began to understand how the compiler worked, I became a better software engineer when I understood how people worked. They are different study paths.
我知道这属于黑人艺术领域的编程,但这突然成为我职业生涯中需要加强的领域。这个主题有几个主题已被触及,但我不确定该项目是否真正涉及如何与系统成为一个。例如:当我开始理解编译器如何工作时,我成为了一个非常好的C ++程序员,当我理解人们如何工作时,我成为了一名更好的软件工程师。他们是不同的学习路径。
What I'm asking is if you were to put a self help course together to become better at not just writing syntactically correct SQL, and well normalized databases, but fast optimized ones as well. What would you focus on, who would you talk to, who would you read, and how would you get there?
我要问的是,如果你将自助课程放在一起,不仅要编写语法正确的SQL,以及规范化的数据库,而且要快速优化的数据库。你会关注什么,你会和谁说话,你会读谁,你会如何到达那里?
9 个解决方案
#1
3
Learn BerkeleyDB. It will force you to learn the low level details of how databases work (to an extent). As someone with multiple years of SQL Server behind me, learning BDB took me to a new level. There is a ton of knowledge that translates across multiple platforms (i.e. locality of data, effects of various file formats, disk cluster specifics, etc).
学习BerkeleyDB。它将迫使您学习数据库如何工作的低级细节(在一定程度上)。作为有多年SQL Server背后的人,学习BDB把我带到了一个新的水平。有大量知识可以跨多个平台进行转换(即数据的位置,各种文件格式的效果,磁盘簇细节等)。
I'll warn you that it's not for the faint of heart.
我会警告你,这不适合胆小的人。
Also, make sure you pick up a book on BDB if you want to attempt this. Learning strictly from the reference material doesn't work so well.
另外,如果你想尝试这个,请确保你在BDB上拿一本书。严格学习参考资料并不能很好地学习。
#2
2
I looked a long time for resources to help me understand exactly how the query optimizer worked, and how to really make sense of the query plans. I finally found this:
我花了很长时间寻找资源来帮助我准确理解查询优化器的工作原理,以及如何真正理解查询计划。我终于找到了这个:
T-SQL Querying, by Itzik Ben-Gan
由Itzik Ben-Gan进行的T-SQL查询
For Sql Server 2005, I haven't found anything else that gets close.
对于Sql Server 2005,我还没有发现其他任何接近的东西。
#4
1
Best 3 tips from my own experience:
根据我自己的经验提供最好的3个提
- Index all columns you are going to join tables on
- Specify the fields you are actually going to retrieve, rather than "SELECT * FROM..."
- Use SQL aggregate functions rather than rolling your own. Common mistake: Opening a recordset and using a "Count" property of the recordset, rather than using "SELECT COUNT(*)..."
索引要加入表的所有列
指定实际要检索的字段,而不是“SELECT * FROM ...”
使用SQL聚合函数而不是滚动自己的函数。常见错误:打开记录集并使用记录集的“Count”属性,而不是使用“SELECT COUNT(*)...”
I know this isn't a full fledged resource or anything, but it will get you started.
我知道这不是一个完整的资源或任何东西,但它会让你开始。
Good web resource: http://www.sqlteam.com/tag/sql-server-performance-tuning
良好的网络资源:http://www.sqlteam.com/tag/sql-server-performance-tuning
#5
1
The Guru's Guide books are quite a good intermediate-advanced level SQL Server resource. In particular, the third one in this series has a fairly detailed architectural dissection of SQL Server 2000, which might give you the sort of insight you're looking for.
Guru的Guide书籍是一个相当不错的中级 - 高级SQL Server资源。特别是,本系列的第三篇文章对SQL Server 2000进行了相当详细的架构剖析,可能会为您提供所需的洞察力。
#6
1
I am just learning more SQL for my job (I knew a little before, but am working on something which requires a lot of database interaction). I'm reading:
我正在为我的工作学习更多的SQL(我之前知道一点,但我正在处理需要大量数据库交互的事情)。我正在阅读:
SQL for Smarties by Joe Celko, and the SQL Cookbook by Anthony Molinaro. If you were choosing one, I'd go with SQL for Smarties, as it has more theory in it and gives a broader view, whereas the cookbook is more "here is a specific problem and how you would solve it in each of the major db applications"
Joe Celko的SQL for Smarties和Anthony Molinaro的SQL Cookbook。如果你选择了一个,我会选择SQL for Smarties,因为它有更多的理论,并提供更广泛的视图,而烹饪书更“这是一个特定的问题,你将如何在每个主要解决它数据库应用“
#7
1
I would check out The Art of SQL By Stephane Faroult and Peter Robson from O'Reilly. This gives a great lookinto principles of data normalization and thus optimization.
我会查看来自O'Reilly的Stephane Faroult和Peter Robson的SQL的艺术。这样可以很好地了解数据规范化的原理,从而优化。
#8
1
You need to learn to read the query plan. You need to know how to pseudo-code the inner workings of an inner loop, merge join, and a hash join, amongst others.
您需要学习阅读查询计划。您需要知道如何伪内部循环的内部工作,合并连接和散列连接等。
Query planning is a black art, but reading the plans is not so bad. Look at the plans for everything you are going to run (make sure that you have a representative size sample of data and current stats.) You might be able to make sense of what the black art is producing. Looking at the query plans quickly lets you know more about how the database works, and when it is going to do something less than efficient.
查询计划是一种黑色艺术,但阅读计划并不是那么糟糕。查看您要运行的所有内容的计划(确保您拥有代表性的大小数据样本和当前统计数据。)您可能能够了解黑色艺术所产生的内容。快速查看查询计划可以让您更多地了解数据库的工作方式,以及何时执行的操作不是那么有效。
Take those inefficient points, and look at them. Look at the method it chose. An index may or may not change the plan (if it doesn't help, you can always drop it.) Learn the differences between B-Tree, R-Tree, and bit mask indexes, not just when they are recommended, but also how they are structured and how they work, so you will know WHY they are recommended.
拿那些效率低下的点来看看它们。看看它选择的方法。索引可能会也可能不会更改计划(如果它没有帮助,您可以随时删除它。)了解B-Tree,R-Tree和位掩码索引之间的差异,不仅仅是在推荐它们时,还要它们是如何构建的以及它们如何工作,因此您将知道为什么建议它们。
The only real way I have found to learn it is to do it.
我发现学习它的唯一真正方法就是去做。
The only exception to the rule is Oracle 9 and newer sometimes chooses Merge Join Cartesian for no apparent reason. Once again, black art.
该规则的唯一例外是Oracle 9,而更新的有时候选择Merge Join Cartesian是没有明显原因的。黑色艺术再一次。
#9
1
SQL Server Query Performance Tuning Distilled by Sajal Dam is a good book.
Sajal Dam蒸馏的SQL Server查询性能调优是一本好书。
From my experience several things besides what have already been said come to mind. First avoid cursors at all costs especially in inserts, deletes, updates or triggers! Replace subqueries with derived tables. Denormalize if need be, especially if you need to join to two or three other large tables just to get something that you will need in every query such as client id. Write sargable queries (google this if you don't know what that is.) Check out whether a temp table, table variable or derived table will be faster. Avoid the use of functions.
根据我的经验,除了已经说过的内容之外,我还会想到几件事。首先不惜一切代价避免使用游标,特别是在插入,删除,更新或触发器中!用派生表替换子查询。如果需要,则规范化,特别是如果您需要加入两个或三个其他大型表,只是为了获得您在每个查询中需要的内容,例如客户端ID。编写可搜索的查询(如果你不知道那是什么,请谷歌。)检查临时表,表变量或派生表是否会更快。避免使用功能。
#1
3
Learn BerkeleyDB. It will force you to learn the low level details of how databases work (to an extent). As someone with multiple years of SQL Server behind me, learning BDB took me to a new level. There is a ton of knowledge that translates across multiple platforms (i.e. locality of data, effects of various file formats, disk cluster specifics, etc).
学习BerkeleyDB。它将迫使您学习数据库如何工作的低级细节(在一定程度上)。作为有多年SQL Server背后的人,学习BDB把我带到了一个新的水平。有大量知识可以跨多个平台进行转换(即数据的位置,各种文件格式的效果,磁盘簇细节等)。
I'll warn you that it's not for the faint of heart.
我会警告你,这不适合胆小的人。
Also, make sure you pick up a book on BDB if you want to attempt this. Learning strictly from the reference material doesn't work so well.
另外,如果你想尝试这个,请确保你在BDB上拿一本书。严格学习参考资料并不能很好地学习。
#2
2
I looked a long time for resources to help me understand exactly how the query optimizer worked, and how to really make sense of the query plans. I finally found this:
我花了很长时间寻找资源来帮助我准确理解查询优化器的工作原理,以及如何真正理解查询计划。我终于找到了这个:
T-SQL Querying, by Itzik Ben-Gan
由Itzik Ben-Gan进行的T-SQL查询
For Sql Server 2005, I haven't found anything else that gets close.
对于Sql Server 2005,我还没有发现其他任何接近的东西。
#3
#4
1
Best 3 tips from my own experience:
根据我自己的经验提供最好的3个提
- Index all columns you are going to join tables on
- Specify the fields you are actually going to retrieve, rather than "SELECT * FROM..."
- Use SQL aggregate functions rather than rolling your own. Common mistake: Opening a recordset and using a "Count" property of the recordset, rather than using "SELECT COUNT(*)..."
索引要加入表的所有列
指定实际要检索的字段,而不是“SELECT * FROM ...”
使用SQL聚合函数而不是滚动自己的函数。常见错误:打开记录集并使用记录集的“Count”属性,而不是使用“SELECT COUNT(*)...”
I know this isn't a full fledged resource or anything, but it will get you started.
我知道这不是一个完整的资源或任何东西,但它会让你开始。
Good web resource: http://www.sqlteam.com/tag/sql-server-performance-tuning
良好的网络资源:http://www.sqlteam.com/tag/sql-server-performance-tuning
#5
1
The Guru's Guide books are quite a good intermediate-advanced level SQL Server resource. In particular, the third one in this series has a fairly detailed architectural dissection of SQL Server 2000, which might give you the sort of insight you're looking for.
Guru的Guide书籍是一个相当不错的中级 - 高级SQL Server资源。特别是,本系列的第三篇文章对SQL Server 2000进行了相当详细的架构剖析,可能会为您提供所需的洞察力。
#6
1
I am just learning more SQL for my job (I knew a little before, but am working on something which requires a lot of database interaction). I'm reading:
我正在为我的工作学习更多的SQL(我之前知道一点,但我正在处理需要大量数据库交互的事情)。我正在阅读:
SQL for Smarties by Joe Celko, and the SQL Cookbook by Anthony Molinaro. If you were choosing one, I'd go with SQL for Smarties, as it has more theory in it and gives a broader view, whereas the cookbook is more "here is a specific problem and how you would solve it in each of the major db applications"
Joe Celko的SQL for Smarties和Anthony Molinaro的SQL Cookbook。如果你选择了一个,我会选择SQL for Smarties,因为它有更多的理论,并提供更广泛的视图,而烹饪书更“这是一个特定的问题,你将如何在每个主要解决它数据库应用“
#7
1
I would check out The Art of SQL By Stephane Faroult and Peter Robson from O'Reilly. This gives a great lookinto principles of data normalization and thus optimization.
我会查看来自O'Reilly的Stephane Faroult和Peter Robson的SQL的艺术。这样可以很好地了解数据规范化的原理,从而优化。
#8
1
You need to learn to read the query plan. You need to know how to pseudo-code the inner workings of an inner loop, merge join, and a hash join, amongst others.
您需要学习阅读查询计划。您需要知道如何伪内部循环的内部工作,合并连接和散列连接等。
Query planning is a black art, but reading the plans is not so bad. Look at the plans for everything you are going to run (make sure that you have a representative size sample of data and current stats.) You might be able to make sense of what the black art is producing. Looking at the query plans quickly lets you know more about how the database works, and when it is going to do something less than efficient.
查询计划是一种黑色艺术,但阅读计划并不是那么糟糕。查看您要运行的所有内容的计划(确保您拥有代表性的大小数据样本和当前统计数据。)您可能能够了解黑色艺术所产生的内容。快速查看查询计划可以让您更多地了解数据库的工作方式,以及何时执行的操作不是那么有效。
Take those inefficient points, and look at them. Look at the method it chose. An index may or may not change the plan (if it doesn't help, you can always drop it.) Learn the differences between B-Tree, R-Tree, and bit mask indexes, not just when they are recommended, but also how they are structured and how they work, so you will know WHY they are recommended.
拿那些效率低下的点来看看它们。看看它选择的方法。索引可能会也可能不会更改计划(如果它没有帮助,您可以随时删除它。)了解B-Tree,R-Tree和位掩码索引之间的差异,不仅仅是在推荐它们时,还要它们是如何构建的以及它们如何工作,因此您将知道为什么建议它们。
The only real way I have found to learn it is to do it.
我发现学习它的唯一真正方法就是去做。
The only exception to the rule is Oracle 9 and newer sometimes chooses Merge Join Cartesian for no apparent reason. Once again, black art.
该规则的唯一例外是Oracle 9,而更新的有时候选择Merge Join Cartesian是没有明显原因的。黑色艺术再一次。
#9
1
SQL Server Query Performance Tuning Distilled by Sajal Dam is a good book.
Sajal Dam蒸馏的SQL Server查询性能调优是一本好书。
From my experience several things besides what have already been said come to mind. First avoid cursors at all costs especially in inserts, deletes, updates or triggers! Replace subqueries with derived tables. Denormalize if need be, especially if you need to join to two or three other large tables just to get something that you will need in every query such as client id. Write sargable queries (google this if you don't know what that is.) Check out whether a temp table, table variable or derived table will be faster. Avoid the use of functions.
根据我的经验,除了已经说过的内容之外,我还会想到几件事。首先不惜一切代价避免使用游标,特别是在插入,删除,更新或触发器中!用派生表替换子查询。如果需要,则规范化,特别是如果您需要加入两个或三个其他大型表,只是为了获得您在每个查询中需要的内容,例如客户端ID。编写可搜索的查询(如果你不知道那是什么,请谷歌。)检查临时表,表变量或派生表是否会更快。避免使用功能。