Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 22, 2000

Case Study: Storing, Querying, and Analyzing Performance: Pt. 3

By Bruce Szabo

Querying Performance

Using a novel method of storing data in a database has allowed survey data of different types to be held in a single table. This allows the data to be queried and cross-referenced using fields from the single table or when looking for specific company information the query can join two tables. The last step of any new methodology should be a look at the performance of the methodology.

In most business environments it has been my experience that people overbuy on their hardware, this has lead me to be a tad lackadaisical when it comes to trying to refine the performance of applications. In doing this article it became evident a performance analysis was in order. The original method used to analyze performance was an ASP page determining how long it takes to query for specific articles. Because the focus of these articles is SQL I decided to figure out how to analyze performance on the SQL server.

In order to analyze the methodology discussed in the previous two articles I created two tables and populated them with 5,000 records each. The first table has two columns, an ID column and an integer column (referred to as the integer table) while the second table has 33 columns an ID column and 32 logical columns (referred to as the logical table). Using Transact-SQL I wrote the following queries to test performance. Each query is followed by the results.

Click here for code example 1.

Click here for code example 2.

The Queries follow the same format. Two variables are declared which were used for the start and stop times of the query. The start variable (@start) was set to the current time (getdate()) before the select statement. When the query finishes the end variable (@finished) is set to the current time. Taking the difference between these two values in the final select statement yields the length of time it took for the query. As one can see from the results the logical bit query took less time. The result was expected as it makes sense finding a logical value directly will be quicker than performing a calculation on each integer. The 3 millisecond difference, however, seems negligible for ease of use of this table.

When two bits where queried the results were more dramatic as it took 33 milliseconds for the integer query to run and only 16 milliseconds to query two bits from the table with logical fields. From the query perspective it would make sense to use a table with logical fields to store the data from the checkbox responses on the survey.

It can be argued, however, that the space required to store the information is as important as the response time for the query. The space required for the 5000 records of the integer table was 12 pages while it took 17 pages to store the same information in the logical table. In SQL 7.0 a page is 8k in size meaning the storage size would translate to 96k (8k * 12 pages) and 136k (8k * 17 pages) pages, respectively. If I add another 5000 records the numbers were similar the integer table required 184k (8k * 23 pages) and 264 k (8k * 33 pages).

In this 3 part series I hope to have presented a method to translate data so it can be stored in a database (Storing Responses). Part 2 of this series discussed querying the data from the database (Querying Responses) while the performance of the table in the database is discussed in the final installment.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date