Tag Info

Hot answers tagged

17

Should I start indexing right from the start or when performance problem arises? Indexing strategy tends to evolve as usage patterns emerge. That said, there are also strategies and design guidelines that can be applied up front. Choose a good clustering key. You can usually determine the appropriate clustered index at design time, based on the ...


14

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out ...


13

Oracle: Since Oracle doesn't index entries where all indexed columns are null, you can use a function-based unique index: create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N'))); create unique index idx on foo(case when chk='Y' then 'Y' end); This index will only ever index a single row at most. Knowing this index fact, you can ...


9

SQL Server: How to do it: The best way is a filtered index. Uses DRI SQL Server 2008+ Computed column with uniqueness. Uses DRI See Jack Douglas' answer. SQL Server 2005 and before An indexed/materialised view which is like a filtered index. Uses DRI All versions. Trigger. Uses Code, not DRI. All versions How not to do it: Check constraint with a ...


9

I have been working with Pentaho for about a year now. Pentaho is a full Open Source suite for Business Intelligence. It's strenght is that it relies on independently managed project : Pentaho Data Integration (Kettle) ->ETL Pentaho Report Designer (PRD) -> Report designer Mondrian -> R-OLAP cube and much more.. You can use them as a whole (Pentaho ...


9

I think this is a case of structuring your database tables correctly. To make it more concrete, if you have a person with multiple addresses and you want one to be the default, I think you should store the addressID of the default address in the person table, not have a default column in the address table: Person ------- PersonID Name etc. DefaultAddressID ...


9

There are 2 bits of information. This means 2 fields. It is that simple. In practical terms, selecting 2 columns is zero extra complexity. Having to read a large field to parse one bit out is unnecessary complexity and performance overhead. A couple of SO questions on storing salts with hashes http://stackoverflow.com/q/1219899/27535 ...


8

There's really risks associated with both approaches: Option a) Index from the start, but not realize you have created a number of indexes which are never used. These add some overhead (most noticeably to queries that modify data, but also with optimization of SELECT statements trying to identify the best index). You will need to discipline yourself to ...


7

Most don't? Bummer. I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration. In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of ...


6

If I want to move record 0 to the start, I have to reorder every record No, there's a simpler way. update your_table set order = -1 where id = 0; If I want to insert a new record in the middle, I have to reorder every record after it That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to ...


5

The answer to your question is "no." And no, I don't think it will ever be possible at least in any helpful way. Relational databases are designed to operate over sets of tuples. Non-relational databases are not. This means that one of the key tradeoffs is being able to have very fast access (with "good enough" consistency controls) to single pieces of ...


5

In general, the wider the table the slower the query. But slower doesn't necessarily mean slow. The number of joins probably has more to do with the use of id numbers than anything else. Natural keys can speed performance dramatically when the important information is carried in the key. (This happens more often than you might think.) The M:N table is the ...


5

The two things I deal with every day. Disaster recovery. Performance tuning. (Both for individual queries, and for the dbms itself.) Your disaster recovery plan needs to be scripted, tested, and practiced. I'm using scripted in the sense of something an actor would follow, not something written in Python. It should tell everyone who needs to be ...


5

Possible approaches using widely implemented technologies: 1) Revoke 'writer' privileges on the table. Create CRUD procedures that ensure the constraint is enforced at transaction boundaries. 2) 6NF: drop the CHAR(1) column. Add a referencing table constrained to ensure its cardinality cannot exceed one: alter table foo ADD UNIQUE (bar); create table ...


5

This kind of problem is another reason why I asked this quiestion: Application Settings in Database If you have an application setting table in your database you could have an entry that would reference the ID of the one record you want to be considered 'special'. Then you would just look-up what the ID is from your settings table, in this way you dont ...


5

I think you're not understanding what is meant by datawarehouse. It's not a tool. Or an application. Or a database. It doesn't mean "big database". You said, we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. MSSQL wasn't just the storage for the DWH, it is the DWH. A datawarehouse is a database which is specifically ...


5

In SQL Server we can rollback DDL statements, it's not using auto commit at the end of the statement. In other DBMS I don't know, but I remember that in Oracle one can't do the same. I believe it's specific to each DBMS, not sure what would the SQL standard say about this, but I'm sure no producer implements 100% the standard. There's a similar question on ...


4

For those who use MySQL, here is an appropriate Stored Procedure: DELIMITER $$ DROP PROCEDURE IF EXISTS SetDefaultForZip; CREATE PROCEDURE SetDefaultForZip (NEWID INT) BEGIN DECLARE FOUND_TRUE,OLDID INT; SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE; IF FOUND_TRUE = 1 THEN SELECT ID INTO OLDID FROM PostalCode ...


4

I'm inclined to agree with @Catcall, database recovery should be top of the list. The implications of both backup and recovery options are typically the most poorly understood outside of a DBA team and the most likely to result in disaster. Ensure you have defined and agreed (by technical and non-technical management) RPO (Recovery Point Objective) and RTO ...


4

In addition to Mark's answer You can get a feel by having realistic test data at expected quantities. I've seen many, many (too many) cases where a query runs OK with a 1000 rows but not the million in production. If you can, work on a copy of production later on, Of course, I've seen the odd problem only in production because of usage patterns when ...


3

Just to add a few things. Temporary indexes are a terrible idea.. unless the index is on a temp table. Indexes take up much more dataspace (as well as other overhead) than people realize. Therefore, create them conservatively. This is my approach. Similar to Mark, make indexes where they make sense, but don't overdue it. You don't have to wait until ...


3

In SQL Server 2000 and over you can use Indexed Views to implement complex (or multi-table) constraints like the one you're asking for. Also Oracle has a similar implementation for materialized views with deferred check constraints. See my post here.


2

I will try to answer only the first question. If you can estimate even roughly from the beginning how many records you'll have in your tables after a certain amount of time, than I'd say it's better to start from the beginning to design some indexes. Try to use some test tools or test scripts that will automate as many calls as possible for the application ...


2

Standard Transitional SQL-92, widely implemented e.g. SQL Server 2000 and above: Revoke 'writer' privileges from the table. Create two views for WHERE chk = 'Y' and WHERE chk = 'N' respectively, including WITH CHECK OPTION. For the WHERE chk = 'Y' view, include a search condition to the effect that its cardinality cannot exceed one. Grant 'writer' ...


2

Generally, ordering is done according to some information in the records, title, ID, or whatever is appropriate for that particular situation. If you do need a special ordering, using an integer column isn't as bad as it might seem. For example, to make room for a record to go into 5th place, you could do something like: update table_1 set place = place + ...


2

With 7 books, my guess would be that 7 joins is faster than GROUP BY / HAVING. But it depends on the DBMS, the version, the optimizer's settings, the database settings, the RAM you have, the hard disks' performance, the indexes fragmentation, the overall pressure on the server and possibly several other parameters. Even more, even all the previous are set ...


1

…it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?) Who cares? These numbers are only there for the computer to deal with so it doesn't matter how many fractional digits they have or how ugly they appear to us. Using decimal values means that to move item F between items J and K all you need ...


1

You have to decide whether you want your world to be simple for the programmer or useful for the user. The most useful scenario is the one which treats things that happen in the real world most directly. In your case that is your second scenario: One table for check-ins and check-outs with different rows for each. The reason that this scenario is the most ...


1

Option #3 NULLs are your friend. After you select the platform, study how NULLs work for the platform and embrace them. In this case they accurately reflect the absence of a check-in. In Oracle NULLS are not indexed, so you could create a function based index that swaps the NULL state giving you a very small index that contains only the entries not ...


1

What are the pros and cons of above schema? The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as emplpoyee { user_id, employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE") organization_id, ...



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