optimize stored procedure

Question

Hi All, I have one question on how to optimized my sp, actually it was a view but I decided to change to sp to make it faster, and it did work. First it took 27 seconds for a view to run and 16 sec for sp. So it is getting better but how to make it even better?

Table that I am running my sp on is 600 records and I am returning around 80 fields and out of 80, 4 fields are images. They are not not too big around 400KB per record.

Any suggestion what can I do.

Answer 1

Have you tried implementing proper indexes for the query being called by the SPROC? Also take a look at the execution plan and see what types of indexes can be best for the query. Lastly, update table and index statistics and check fragmentation on indexes for any performance hits.

Answer 2

you say you tried to use covering indexes, which is a good idea on a table that has 80 columns, including images. However, you said that this didn't work with the images. I would suggest that you split this table apart, even if you just make several 1 to 1 tables. That "*" in select * from ... is not helping much. Only return the columns you need. Also, what about the WHERE? While it is important to only return the columns you need, it is even more critical to only return the rows you need. All the indexes in the world will not help if you have no WHERE clause.

Answer 3

"Table that I am running my sp on is 600 records and I am returning around 80 fields and out of 80, 4 fields are images"

If I were you, I would have put images into file system and their indexed paths in database.

"Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index" as well as non-key (included) columns

Besides, I would have ascertain that images are really cached by your webbrowser