Tag Info

Hot answers tagged

22

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

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 ...


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

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately. Table GroupMemberRoles GroupID FK UserID FK RoleID FK (PK on all three, with perhaps other constraints) Your queries (say, to find the admins of a certain group) should be of the ...


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

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

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

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 ...


4

Based on your comment above, it sounds like when you said everything was stored in separate "databases", you actually meant to say "tables". I'm going to work with that assumption... I'd suggest a simple table that stores who's been invited to which event, and what their response was. invitations ----------- id (primary key for the table) user_ID ...


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

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

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

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

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

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 ...


3

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios? Scenario 1 (INSERT .. SELECT) The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given: ...


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

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 ...


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 ...


1

How about using a scheduled job to take a snap-shot of your users' weight stats and influencing factors into a different (could be a temp-) table, then using those to calculate the new weight (into a different column of that same table). And then update your user table with the complete set of calculated weight stats. You'd avoid all kinds of conflicts ...


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 ...



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