CODEWITHSUNDEEP
sqlperformanceselectoptimizationdb2
stack_none_overflow
stack_none_overflow

Reputation: 21

my SELECT statement is very slow

My table contain 97 column with data type varchar(200) about 80% and bigint,char.

select count(*) from mytable it return 4500 rows

I am testing with 100 rows with SQL statement

select * from mytable .It use time about 2.3 min

I think,it is too very slow if return all record

please recommend me for the way to up speed my query and result.

my db server is db2 9.5

Upvotes: 1

Views: 3136

Answers (2)

gh9
gh9

Reputation: 10693

You need to isolate what is slow, the query or displaying the query. I am assuming it is a combination of both.

When you have 97 columns each being a varchar(200) sql cannot store all the info on the same row in a page . The worst case scenario is 97 * 202 (i used 202 instead of 200 because sql needs to store the length of a varchar in each column and i believe it stores it as two bytes) 19594 which is much larger than the row on a page can handle. TL;DR; normalize your table and have the columns broken up into logical units on another table.

Also doing a SELECT * FROM X will result in a full table scan which is much slower then querying on an indexed column.

If you must do a SELECT * FROM X and you have to have 97 columns please change the column data types to be a smaller data type (int,tinyint,char(10)) this will optimize the storage and should speed up the query.

Upvotes: 0

Declan_K
Declan_K

Reputation: 6826

The first thing you should do to improve the performance is stop using SELECT *. Rewrite the query to get the columns you need.

the second thing to do is to add a WHERE clause. Do you really need to return all rows for the table?

Third thing is indexes, does you table have any, can you use indexed columns in your WHERE statement, etc.

What is the reason not to use select *?

http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx

Upvotes: 1

Related Questions