Tag Info

Hot answers tagged

19

Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? In any performance scenario, you have to test and measure the solutions to see which is faster. That said, it's almost always the case that a joined result set from a properly ...


8

Technically instance resides in memory and database (physical files) resides on disk. There can be an instance without a database (an example is a started instance right before you create a database). There also can be a database without an instance (example of this is database files residing on disk but instance is not running, or files copied to some other ...


7

You should use a lookup table Not all clients will use the ENUM in the application You will get a reporting or MIS or Excel app connecting at some point How can do "NOT EXIST" otherwise? You will be asked this You won't know about client enum changes Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause ...


7

Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views. Triggers are often the only option when you're enhancing a 3rd party ...


5

The design choices you describe are not directly related to normalization. I agree there should be a lookup table. I think an OrderStatusID value would increase redundancy. The status (text) value presumably already satisfies many of the qualities of a good key: unique, stable, narrow, familiar to users, etc. Referential integrity can be applied to VARCHAR ...


4

Have you investigated the MERGE command in SQL 2008? Here is a basic example: merge YourBigTable ybt using (select distinct (RecordID) from YourOtherTable) yot on yot.Recordid = YBT.RecordID when NOT matched by target then insert (RecordID) values (yot.DeviceID) ; This is basically an "UPSERT" command. Update if it exists, insert ...


4

100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately. Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new question ...


4

Overall, triggers have a silent and hidden impact on your data modification performance. I have seen environments where cascading errors were caused by updating other tables from within a trigger and the updated tables had triggers. As far as auditting is concerned, a trigger may be used, but if you have the Enterprise edition of SQL Server 2008, or ...


4

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration. I am assuming that: you will have separate application for each industry, they have not very much in common (they really cover different business aspects) I think that one of acceptable solutions in ...


4

While 100,000 transactions per minute sounds like a lot, it's generally classed as a "moderate" transaction rate (100,000 tx/min / 60 sec/min = 1,666.7 tx/sec). There are systems out there running typical RDBMS systems (not in-memory) that can handle well over 10x that transaction rate (sustained) on a single box. Granted, these are well-tuned systems at ...


3

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away ...


3

Of course, I didn't measure any performance with these You put together some good sample code. Did you look at the timing in SQL Fiddle? Even some brief unscientific performance testing will show that query three in your demonstration takes about the same amount of time to run as either query one or two separately. Combined one and two take about ...


3

I have just the situation described above by Mark Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API. My example: a c# application written by another developer using LLBL.gen as the ORM. Any change to the data structure that you want the C# code to use implies the ...


3

In Oracle triggers should be avoided in most situations because they divide a portion of the logic into a separate code area that is harder to debug and only runs as side effect, not directly. Legitimate exceptions include instead of triggers, integrating with third party code, and setting complex default values. The latter may be the biggest argument ...


3

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column. Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are ...


3

Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ... If you feel intimidated by SQL, an ORM can help ...


2

The quick and dirty argument that should come up against such policy is the implementation of business logic. Mundane auditing of data traffic is only one good use of triggers. Maintenance of those triggers for business logic makes the use of triggers take on a life of its own, as if it is part of the software lifecycle. For some databases like Oracle, ...


2

Update is not as fast. The trick is to achieve a fast insert is to disable the indexes while data is being inserted. Consider using this: -- disable indexes ALTER INDEX [index_name] ON dbo.import_table DISABLE -- ... disable more indexes -- don't use delete if you don't care about minimal logging. truncate is faster TRUNCATE TABLE dbo.import_table -- ...


2

First off, taking a step back, do you really need to solve the inventory management problem on the front end? Since you're selling large volumes of a relatively small set of products, it should be relatively easy to manage your inventory so that you are never out of stock or, if you are, it doesn't prevent you from fulfilling orders. There is a great deal ...


2

Everything I've ever learned says "never say never". There will always be cases where you have to go against any established convention and there's just no way around it. Often times it seems that these situations are caused by older, existing systems; third parties; and architecture already in place. While better solutions may be clear and obvious, the ...


2

If you actually plan to query these attributes separately (such as "Find all form elements with height = 20 and colour = #FF3377") then it is probably better to store them in some kind of queryable attributes table. If you are only plan to use a set of attributes as a whole, and never query inside it, never update/insert/delete individual attributes for ...


2

For this scenario, the database server should not be publicly exposed, for obvious security reasons. You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API. This separation (decoupling) allows for all sorts of good things to ...


2

That's a big topic. The tactics depend in part on how big a change you're making. The strategy is that every move to production breaks nothing. Loosely speaking, that means you add "new things" to the database, alter all application code to use the new things instead of the old things, then, if prudent, remove the old things. That equates to one to ...


1

If you move these additional costs to ShoeInventory then they will be associated with your ShoeInventory entity. This entity also has a relationship with ShoeSize. If I understand your schema (and I might not, so correct me if I'm wrong), the implication of this is that different sizes for the same shoe type could potentially have different sub-costs. This ...


1

The best approach is to model each independent data item as its own predicate (column). If you are sure that each of your status codes will remain binary, then use a bit column for each. Bit is an efficient data type for storage. Note, however, that you shouldn't try to filter (WHERE) on a bit field if you can help it. Bit is not generally something you ...


1

You should check out VoltDB. It can handle extremely high transaction rates (>100K txns/sec/server) with read or write-heavy workloads, full ACID-compliance, including full synchronous (immediate) durability and multi-statement transactions (e.g. check if seat is still available, then assign ticket in the same transaction). Full disclosure: I work for ...


1

Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one Something along the lines: update real_table set ... where exists (select 1 from staging_table where ...) insert into ... select from stage_table left join real_table where real_table.some_col is NULL delete real_table ...


1

There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier. This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy. ...


1

If you're searching documents, a text engine like solr could be your friend. Combine that with ajax for real-time document discovery.



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