Five Ways to Rev up Your SQL Performance
Sometimes all it takes is a little tweak here or there to make your application run much faster. Ah, but the key is figuring out how to tweak it! Sooner or later you'll face a situation where a SQL query in your application isn't responding the way you intended. Either it doesn't return the data you want or it takes entirely too long to be reasonable. If it slows down a report or your enterprise application, users won't be pleased if they have to wait inordinate amounts of time. And just like your parents didn't want to hear why you were coming in past curfew, users don't want to hear why your query is taking so long. ("Sorry, Mom, I used too many LEFT JOINs.") Users want applications to respond quickly and their reports to return analytical data in a flash. I myself get impatient when I surf the Web and a page takes more than ten seconds to load (OK, more like five seconds).
To resolve these issues, it is important to get to the root of the problem. So where do you start? The root cause is usually in the database design and the queries that access it. In this month's column I'll demonstrate four techniques that can be used to either improve your SQL Server™-based application's performance or improve its scalability. I'll examine the use of LEFT JOINs, CROSS JOINs, and retrieving an IDENTITY value. Keep in mind that there is no magic solution. Tuning your database and its queries takes time, analysis, and a lot of testing. While the techniques here are proven, some may work better than others in your application.
五種提高SQL性能的方法
有時(shí)， 為了讓應(yīng)用程序運(yùn)行得更快，所做的全部工作就是在這里或那里做一些很小調(diào)整。啊，但關(guān)鍵在于確定如何進(jìn)行調(diào)整！遲早您會(huì)遇到這種情況：應(yīng)用程序中的 SQL 查詢不能按照您想要的方式進(jìn)行響應(yīng)。它要么不返回?cái)?shù)據(jù)，要么耗費(fèi)的時(shí)間長(zhǎng)得出奇。如果它降低了報(bào)告或您的企業(yè)應(yīng)用程序的速度，用戶必須等待的時(shí)間過(guò)長(zhǎng)，他們就會(huì)很不滿意。就像您的父母不想聽(tīng)您解釋為什么在深更半夜才回來(lái)一樣，用戶也不會(huì)聽(tīng)你解釋為什么查詢耗費(fèi)這么長(zhǎng)時(shí)間。（“對(duì)不起，媽媽，我使用了太多的 LEFT JOIN?！保┯脩粝Ｍ麘?yīng)用程序響應(yīng)迅速，他們的報(bào)告能夠在瞬間之內(nèi)返回分析數(shù)據(jù)。就我自己而言，如果在 Web 上沖浪時(shí)某個(gè)頁(yè)面要耗費(fèi)十多秒才能加載（好吧，五秒更實(shí)際一些），我也會(huì)很不耐煩。
為了解決這些問(wèn)題，重要的是找到問(wèn)題的根源。那么，從哪里開(kāi)始呢？根本原因通常在于數(shù)據(jù)庫(kù)設(shè)計(jì)和訪問(wèn)它的查詢。在本月的專欄中，我將講述四項(xiàng)技術(shù)，這些技術(shù)可用于提高基于 SQL Server? 的應(yīng)用程序的性能或改善其可伸縮性。我將仔細(xì)說(shuō)明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的檢索。請(qǐng)記住，根本沒(méi)有神奇的解決方案。調(diào)整您的數(shù)據(jù)庫(kù)及其查詢需要占用時(shí)間、進(jìn)行分析，還需要大量的測(cè)試。這些技術(shù)都已被證明行之有效，但對(duì)您的應(yīng)用程序而言，可能其中一些技術(shù)比另一些技術(shù)更適用。