There's a healthy debate out there between surrogate and natural keys: SO Post 1 SO Post 2 My opinion, which seems to be in line with the majority (it's a slim majority), is that you should use surrogate keys unless a natural key is completely obvious and guaranteed not to change. Then you should enforce uniqueness on the natural key...
Using SQL Server Management Studio. How can I test the performance of a large select (say 600k rows) without the results window impacting my test? All things being equal it doesn't really matter, since the two queries will both be outputting to the same place. But I'd like to speed up my testing cycles and I'm thinking that the output ...
To All, We are wanting to use Extended Properties as part of our database documentation. One of my co-workers mentioned that on a prior project they used Extended Properties and it impacted database performance. I have been searching the internet for any corroborating information in regard to this. I've not found anything. Has anyon...
Hello I have changed auto_increment value to 1000000 of a MyISAM table. Does this affect read/write performance? I thought that db engine may look for IDs before ID 1000000 everytime a list of rows is requested. ...
just want to verify that database partition is implemented only at the database level, when we query a partitioned table, we still do our normal query, nothing special with our queries, the optimization is performed automatically when parsing the query, is that correct? e.g. we have a table called 'address' with a column called 'country...
I currently have the below tables representing a bus network mapped in hibernate, accessed from a Spring MVC based bus route planner I'm trying to make my route planner application perform faster, I load all the above tables into Lists to perform the route planner logic. I would appreciate if anyone has any ideas of how to speed my per...
On Ruby on Rails, say, if the Actor model object is Tom Hanks, and the "has_many" fans is 20,000 Fan objects, then actor.fans gives an Array with 20,000 elements. Probably, the elements are not pre-populated with values? Otherwise, getting each Actor object from the DB can be extremely time consuming. So it is on a "need to know" b...
I've been stuck in a MsSql/MySql world now for a few years, and I've decided to spread my wings a little further. At the moment I'm researching which DBMS is good at things needed when archiving data. Eg. lots of writes and low reads. I've seen the NoSQL crusade, but I have a very RDBMS mindset, so I'm a bit skeptical. Anyone have any ...
I also have a very large table in SQL Server (2008 R2 Developer Edition) that is having some performance problems. I was wondering if another DBMS would be better for handling large tables. I'm mainly only considering the following systems: SQL Server 2008, MySQL, and PostgreSQL 9.0. Or, as the referenced question above eludes to, is ...
I have 3 tables Person, Names, and Notes. Each person has multiple name and has optional notes. I have full text search on some columns on names and notes (see below), they are working perfectly if the word i search with is in the result set or is in the db, this is for custom function,php, and psql. The problem now is that when the word...
I'm trying to 'bin' all of my customers based on the number of items purchased, and display the counts of each bin. I'm trying to see how many people(account_id) purchased one item, how many purchased two items, all the way through nine items, and then ten or more. Here's the query I'm using - for what its worth, I'd expect the query t...
Is there a faster way to update the oldest row of a MySQL table that matches a certain condition than using ORDER BY id LIMIT 1 as in the following query? UPDATE mytable SET field1 = '1' WHERE field1 = 0 ORDER BY id LIMIT 1; Note: Assume the primary key is id and there is also a index on field1. We are updating a single row. We are ...
I have a buddy who runs a web app for people listing cars for sale. There are a few thousand clients who use it, and each client has hundreds and sometimes thousands of rows in the database (some have been on for 5 years with hundreds of cars selling each month, and 10s of rows per sale (comments, messages, etc)). He has run this system ...
I fear I don't know what I'm doing. 1: I have a table called ticket which has a column called total. When the total is updated I want to keep a record of it (old total, etc), so I decided to remove the total column and create a table called ticket_total with columns ticket_id, total, and datetime (the most recent of course is the "curr...