- OS : Windows Server 2012 R2 ( 64bit )
- RAM : 16.00GB
- CPU : Intel(R) Xeon(R) CPU E5-2609 @ 2.40GHz
- SQL : Windows SQL Server 2012 Standard
Brief Database Data Information
- Among more than 10 tables, one of them has varbinary(max) type column
- The table has more than 1m records and each column has thumbnail data that takes up about 20k
Brief Table Spec
- Table name : RegisteredThumbnail
- Column name : UserId, ThumbData, Created, Updated
- Column type : int, varbinarymax, datetime, datetime
- Used space info : row : 1,034,300 | reserved : 34,092,160 KB | data : 34,054,872 KB | index_size : 31040 KB | unused : 6248 KB
SELECT * FROM RegisteredThumbnail WHERE UserId = 512315
This query takes about 6:45 minutes to fetch the expected row.
To overcome this, is indexing the only option for me?
Changing the way image data is stored by replacing the binary data to image url as a string data will help a lot?
Since this is a currently operating system, changing the column is not a good idea though.
Any ideas would be greatly appreciated.
No indexes have been configured yet.