Speed Up SELECT DISTINCT Queries
The reason the second example runs slightly quicker is that the EXISTS clause will cause a name to be returned when the first book is found, and no further books for that author will be considered (we already have the author's name, and we only want to see it once)
Read the Article
|
Eliminate the Use of Temporary Tables For HUGE Performance Gains
As queries become more complex, temporary tables are used more and more. While temporary table may sometimes be unavoidable, they can often be sidestepped by using derived tables instead. In brief, a derived table is the result of using another SELECT statement in the FROM clause of a SELECT statement. By using derived tables instead of temporary tables, we can boost our application's performance. Let's find out more.
Read the Article
|
Not All SQL Server Indexes Are Created Equal
If you have much experience with indexes at all, you are probably already familiar with the difference between clustered and non-clustered indexes. But this article is not about them. This article is about whether or not the SQL Server Query Optimizer will use your carefully crafted indexes. You may not be aware of this, but just because a column has an index doesn't mean the Query Optimizer will use it. As you can imagine, creating an index that never will be used is a waste of time, and in the worst cases, it can even reduce the performance of your application. Let's learn why.
Read the Article
|
Getting The Best From The SQL Server Index Tuning Wizard
The Index Tuning Wizard is a handy tool shipped as part of the SQL Server 7.0 and SQL Server 2000 Profiler that can analyze a set of SQL queries and suggest index changes that could improve their performance. Here, I will take a look at how to get the best results out of using the Index Tuning Wizard.
Read the Article
|
SQL Server Full Text Search Optimization
Full text indexing is a great feature that solves a long running database problem, the searching of textual data columns for specific words and phrases in SQL Server databases.
Read the Article
|
Using XML To Supercharge Website Performance
With the advent of XML, we have the ability to supercharge our websites with huge performance increases. XML allows us to separate code, content, and presentation. Web developers now have three separate files which in turn will create the HTML that we output. Since the content is in a distinct file separate from the code and presentation, we now have the ability to create static content that is updated based upon changing business needs. The strategy is to have processes that create static content periodically, rather than every time a page is requested, substantially boosting performance of our web pages, and reducing the load on the database.
Read the Article
|
The Importance of E-Commerce, DSS and OLTP Database End-User Service Level Analysis
This is an interesting article from the president of SQL Power Tools, which produces tools for SQL Server (and other databases) that can be used to measure database end-user service levels. If you don't know what an end-user database service level is, then you need to read this article. Before reading this article, I was unfamiliar with this important concept.
Read the Article
|
Use OPENXML to Minimize SQL Server Database Calls
OPENXML is a new function added to SQL Server 2000 that provides a rowset view over an XML document. Since a rowset is simply a set of rows that contain columns of data, OPENXML is the function that allows an XML document to be treated in the familiar relational database format. It allows for the passing of an XML document to a T-SQL stored procedure for the updating of data.
Read the Article
|
Avoiding Bottlenecks with Temporary Tables
Were you aware that the act of populating a SQL Server temporary table can cause system-wide performance bottlenecks on your server? Problems can occur both with SQL Server 6.5 and 7.0 in different ways, and in this article I will discuss how best to avoid them.
Read the Article
|
Start Preparing Today for the New SQL Server 2000 Enterprise Certification Exam
I has fortunate enough to be invited to take the beta test for this exam last November, and while I cannot tell you what's on the test (I signed a non-disclosure agreement), I can at least provide you some guidelines on how to prepare for the test.
Read the Article
|
