Home extremely slow table operations on a table with few rows but large column contents
Reply: 0

extremely slow table operations on a table with few rows but large column contents

user8891 Published in September 19, 2018, 7:19 pm

I have a table with only 9 rows and 10 columns. Three of these columns are VarBinary and contain about 12-30 MB of data in each column for each row. Now even simple updating of one row takes more than 20 minutes and Sql times out. Even a select statement takes about 40 seconds unless I excluded the Varbinary columns from the query

Any suggestions. Thanks

share|improve this question
  • 2
    the obvious one is to not store 12-30 MB of data in a database row. A more common approach would be to store the metadata (that you need to query) in your database, but then pointers to where the binary data is physically stored (filesystem, cloud storage, etc...). If you don't need to do db-engine operations on the varbinary data, it would probably be better to store it elsewhere. – snow_FFFFFF Nov 14 '17 at 0:38
  • What kind of data it is? If it is some file contents then better you store the file somewhere else and store file path in the database. – Chetan Ranpariya Nov 14 '17 at 0:48
  • Using sql server filestream is another option. – FLICKER Nov 14 '17 at 1:09
  • If you use filestream be sure you know its limitations and design constraints. On SQL Server 2008 R2, you must have the latest service pack or SSMS will lose the filestream attribute and all filestream data when you change the table via the UI. You cannot use mirroring or database snapshots (replication and log shipping work), and depending on the system/db design, you may need to place the filestream on a shared volume (e.g., if you use clustering, which defeats the purpose of using clusters). This page helped me - technet.microsoft.com/en-us/library/bb895334(v=sql.105).aspx – James L. Nov 14 '17 at 1:16
  • Or use FileTable. – Dan Guzman Nov 14 '17 at 3:03

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308516 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO