CODEWITHSUNDEEP
sqlsql-server
mfisher91
mfisher91

Reputation: 807

How can I optimize my varchar(max) column?

I'm running SQL Server and I have a table of user profiles which contains columns for the user's personal info and a profile picture.

When setting up the project, I was given advice to store the profile image in the database. This seemed OK and worked fine, but now I'm dealing with real data and querying more rows the data is taking a lifetime to return.

To pull just the personal data, the query takes one second. To pull the images I'm looking at upwards of 6 seconds for 5 records.

The column is of type varchar(max) and the size of the data varies. Here's an example of the data lengths:

28171
4925543
144881
140455
25955
630515
439299
1700483
1089659
1412159
6003
4295935

Is there a way to optimize my fetching of this data? My query looks like this: 

SELECT * 
FROM userProfile 
ORDER BY id

Indexing is out of the question due to the data lengths. Should I be looking at compressing the images before storing?

Upvotes: 0

Views: 386

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

  1. If it is still possible to take one step back.Drop the idea of Storing images in table. Instead save path in DB and image in folder.This is the most efficient .

  2. SELECT * FROM userProfile ORDER BY id

Do not use * and why are you using order by ? You can order by AT UI code

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If takes time to return data. Five seconds seems a little long for a few megabytes, but there is overhead.

I would recommend compressing the data, if retrieval time is so important. You may be able to retrieve and uncompress the data faster than reading the uncompressed data.

That said, you should not be using select * unless you specifically want the image column. If you are using this in places where it is not necessary, that can improve performance. If you want to make this save for other users, you can add a view without the image column and encourage them to use the view.

Upvotes: 1

Related Questions