Tag Info

Hot answers tagged

24

As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services. If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM ...


12

The reason to use BLOBs is quite simply manageability - you have exactly one method to back and restore up the database, you can easily do incremental backups, there is zero risk of the image and its meta data stored in DB tables ever getting out of sync, you also have one programming interface to run queries or load/save images, so you don't need to give ...


12

I don't use blobs -- mostly from a backup and restore perspective, as I don't want the blob data slowing down my backups. I don't store a full URL, however ... I only store the filepath below a certain point, and build the path as I have more than one way in which people & programs access my files (FTP, HTTP, local directory, NFS mounted directories). ...


11

Paul Randal provides some sample DBs that are actually corrupt: http://www.sqlskills.com/BLOGS/PAUL/post/Conference-corruption-demo-scripts-and-example-corrupt-databases.aspx Good exercise to go through working with DBCC commands and such on SQL Server.


9

You have to use InnoDB. Here is why : The major advantages of InnoDB over MyISAM InnoDB caches data and index pages, whereas MyISAM only caches index pages. InnoDB is designed for ACID-compliant transactions InnoDB is designed for row-level locking, MyISAM uses table-level locking. InnoDB is designed for Multiversion Concurrency Control, critical for ...


9

It is legacy from SQL Server 2000 0 and 100 were different back then 100 meant "fill all pages including all b-tree index levels" 0 meant "leave some space at higher levels in the b-tree index" Since SQL Server 2005, both mean "fill all pages including all b-tree index levels" Quotes from BOL (My bold) SQL Server 2000: A fill factor value of 0 ...


8

I'm a big fan of storing the "reference" copy of the image in the database -- from a managability/disaster recovery standpoint this is really the way to fly. Now, you can still do lots of things to serve the image out of the filesystem for most applications so you are not putting that much pressure on the database server itself to do things it doesn't ...


8

If you are working with linux, storing the images in the filesystem and not in the database has significant better performance, see this excerpt of Brad Ediger's book Advanced Rails.


7

Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete In addition to comments and other answers: some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc) avoiding composite keys forces you to always join intermediate ...


7

You are confusing various concepts here, the major error being primary key != clustered key. Because of this misunderstanding, the majority of the guidance is incorrect. To be brutally honest, you are probably not well placed to be writing these guidelines. Primary key != clustered key Unique identifiers make for very poor clustered keys SQL Server is not ...


7

Refer the the Concepts Guide - Overview of Views for this sort of question: Overview of Views A view is a logical representation of one or more tables. In essence, a view is a stored query. [...] Characteristics of Views Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a ...


6

First, the costs are not supposed to directly relate to the execution time. They're strictly relative; a plan which costs more should take longer to actually execute. You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time. For 99% of users, there's only three cost ...


6

Starting with MySQL 5.6, the performance_schema instruments Table I/O, and computes aggregated statistics by table, and by index. See table performance_schema.table_io_waits_summary_by_index_usage: http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table Finding the least recently used index ...


6

Is this consistent, time after time? I see a CPU difference which could be compile time. Are there any LINQ settings that affect this? Edit: Capture the plans in Profiler Are you sure the SQL is the same in Profiler?


6

Data alignment and storage size Actually, the overhead per tuple is 24 byte for the tuple header plus 4 byte for the item pointer. More details in the calculation in this related answer: Use GIN to index bit strings Also read about the basics of data alignment in this related answer on SO. We have three columns for the primary key: PRIMARY KEY ...


5

How exactly would you 'release' the memory back? As a general rule SQL Server will steal all the memory from the OS and then use it for it's own purpose. See Dynamic Memory Management: The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs... The instance then continues to acquire ...


5

This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you: Can you help me with my capacity planning? How do ...


5

I expect the analysis is carried out serially, one query at a time, as doing so in parallel would be unreliable. DTA produces recommendations by creating hypothetical indexes and evaluating the impact on a query's execution plan. If analysis were carried out on multiple queries at a time, the index created for one query could influence the analysis of ...


5

If you're a developer and you have a DBA team I strongly suggest you look into booking some time with them to go over tuning. As a DBA (who does dev and architecture too!) supporting thousands of users I can honestly say that I will always make time to meet with developers that are serious about learning how to improve their SQL skills. An email here or ...


5

Some things you can look at... Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is). Try turning on IO Stats to see just how much IO the FK lookups are taking. What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)? Lets start here and see where we go.


5

The biggest reason to have a large table_cache is so that LOCK_open mutex is not hot. MySQL prior to 5.5 has a lot of contention when you are trying to open/close tables, so you want to restrict doing this as much as possible, i.e. have a large table cache. So you don't care about any particular ratio of hits to misses (infact you should ignore ratios ...


5

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique. The side effect of this is that queries via primary and unique keys are usually "fast". Now if you haven't defined primary or unique keys on your tables, You don't have a relational table but you have junk (OK, ...


5

Based on your stated requirements, your model is in pretty good shape. Here are some suggestions for improvement: You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly. This would be very bad! If you look at common authentication databases, passwords are stored in encrypted form. You often ...


5

I will tell you my experience in defining the terms of "large", "big", "a lot": a lot is a database that takes around 400 GB for the data of a full month (custom logging information from all our web apps) big is a table in this database that contains much of this space :-).. about half of this size (200 GB a table, from what I kinda remember) large is the ...


5

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, ...


5

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL. You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple ...


5

Short Answer: Yes. There are many reasons but the few that stick to mind: 1.) Trust but verify - SQL cares a lot about its environment, the hardware or virtualized system it is on. When I help a company with SQL on VM issues it is normally a misconfigured VM. In many cases the idea of SQL on VM is about to be thrown away. 2.) DBAs should look at memory ...


4

I'm not much of a fan of storing images in the database. In a small app with a few users, it seems like an easy solution, but as you start to scale, it makes things more difficult. My preference is to start out storing images in a folder on the web server, but keep the path in an easily accessible configuration so that when I need to, I can quickly move ...


4

+1 for the answers from @CadeRoux and @ChrisS, they make valid points. Your comments to those answers highlight that this is essentially a proof-of-concept venture at this stage and you want to minimise your capital investment. If that's the case, forget spending $1000s on hardware and licenses, rent. You don't appear to need to store a vast quantity of ...


4

I want to know how a new partitioned table will accommodate future years? I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years. I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name ...



Only top voted, non community-wiki answers of a minimum length are eligible