SQL question

[Red Squirrel]

When doing a query like this:

update table set field1=3, field2=3 where field3=4;

Is it faster then doing this:

update table set field1=3 where field3=4;
update table set field2=3 where field3=4;

Or does it come up to the same thing, internally? This is assuming the connection is on during this whole time and that field3 is indexed.

I'll be designing a sql table structure for a very large application that has very variable data sets so just brainstorming how I will structure my tables.

 

[KLin]

The first query is more efficient because you're only executing one single statement.

 

[Red Squirrel]

Hmm figured. So in in the back end, it's not broken down to 2 queries as it's updating 2 values?

 

[Markbnj]

Check out the query plan for both statements. It's a useful way to get an idea of what the server is going to do with your command. I don't know if you're on SQL Server, or what, but it you are here's some tips on how to go about it:

http://www.sql-server-performa..._plan_analysis_p1.aspx

 

[awal]

The first statement should be faster.

 

[Red Squirrel]

Thanks, now how about this:

update table set field1=3 where field3=4;

vs

update table set field1=3, field2=3 where field3=4;


So updating 1 field vs updating 2 fields. Is there any speed difference or are they the same? Basically this application will have lot of instances where only individual fields will change at a time so debating on making a very basic table structure like: itemid, fieldname,fieldvalue so 1 item would have multiple rows for each field type. Each item type (100's) have different sets of fields.

Another question, is there a limit of how many tables there can be? Or any performance impacts? I may just go ahead and have 1 table per item type, and SOME types will be able to share the same table structure.

For those curious this is a MMO game environment so for example a special weapon that has various settings will have different fields to be kept track of then another weapon that has no special settings (special ability cool down timers, etc).

Another way I may structure this is have tables for the base properties that are consistent throughout most items/mobs then just have "addon" fields in a seperate table. Could do that too. The way the data will save is during "state saves" and not real time. I also have to keep in mind how fast the entire DB's data can be read, but this will only happen when the server starts up.

 

[awal]

Originally posted by: RedSquirrel
Thanks, now how about this:

update table set field1=3 where field3=4;

vs

update table set field1=3, field2=3 where field3=4;


So updating 1 field vs updating 2 fields. Is there any speed difference or are they the same? Basically this application will have lot of instances where only individual fields will change at a time so debating on making a very basic table structure like: itemid, fieldname,fieldvalue so 1 item would have multiple rows for each field type. Each item type (100's) have different sets of fields.

Statement 1 should be faster, but i doubt you will see any noticeable difference. I'd be hesitant about having a table with many property fields for a single item. If your concern is speed this table will become a hotspot of your application. You might be better off having an intermediate component that keeps state with periodic updates to SQL.

Another question, is there a limit of how many tables there can be? Or any performance impacts? I may just go ahead and have 1 table per item type, and SOME types will be able to share the same table structure.

You probably wont reach the table limit, unless your using a primitive sql backend. If you do your design is probably flawed. There may be performance impacts, but it will be dependent on your hardware & size of the tables.

 

[Fallen Kell]

A lot of this depends on what your database is running. If you are using MySQL, you can use the "explain" function (basically, "explain update table set filed1=3, field2=3 where field3=4" and it will tell you exactly how it is trying to run the command (i.e. what index(s) were used, how many records it believes it would need to scan to get the result, etc...

If you are working with Oracle, if you give it the more complex query, it will test to see if it is optimal, and if not, re-write the query to be more optimized on the fly, essentially re-writing the query to best utilize the Oracle databases engine's strengths if possible.

 

[leoloco]

Originally posted by: RedSquirrel
When doing a query like this:

update table set field1=3, field2=3 where field3=4;

Is it faster then doing this:

update table set field1=3 where field3=4;
update table set field2=3 where field3=4;

Or does it come up to the same thing, internally? This is assuming the connection is on during this whole time and that field3 is indexed.

I'll be designing a sql table structure for a very large application that has very variable data sets so just brainstorming how I will structure my tables.

First query is the most efficient way of updating fields. If you go with second approach it will invoke database operation two times. Which is very expensive in terms of resource and performance consideration.

 

[imported_Dhaval00]

Originally posted by: leoloco

Originally posted by: RedSquirrel
When doing a query like this:

update table set field1=3, field2=3 where field3=4;

Is it faster then doing this:

update table set field1=3 where field3=4;
update table set field2=3 where field3=4;

Or does it come up to the same thing, internally? This is assuming the connection is on during this whole time and that field3 is indexed.

I'll be designing a sql table structure for a very large application that has very variable data sets so just brainstorming how I will structure my tables.

First query is the most efficient way of updating fields. If you go with second approach it will invoke database operation two times. Which is very expensive in terms of resource and performance consideration.

You'd surprised to see the query plan change some 20 million rows down the road. It is completely up to the query optimizers (SQL Server and Oracle) to decide how to execute a particular query. What is efficient today will be obsolete tomorrow. As your tables grow, your queries will need tending to in order to accommodate the 'growth.' It is always a good idea to look at the query execution plan to be exact [even then, it would only be a point-in-time solution, not a perpetual one].

 

[Evadman]

update table set field1=3, field2=3 where field3=4;

That will run with the lowest total resources used, and the lowest total execution time. However, like Dhaval00 said, that may not be the best solution in all instances. The 2nd statement will split the disk writes into 2 separate transactions, which can let other transactions run in the space between the 2 updates. For example updating both columns at once may take 30 seconds total, and updating the 2 columns using 2 separate statements may take 20 seconds each, 40 seconds total. However, between the two 20 second single updates,a bunch of 15ms queries can run.

Determining if that is a good thing, or if it is even required, would generally fall on the process designer, not the DBA. Running as 2 statements could leave the tables in an inconsistent business process state, where the data presented to another system or end user doesn't make any sense. As a DBA, you should make sure that the statements run as fast as possible, balanced against other queries being run. The possible DBA changes would be to horizontally or vertically partition the tables, add/remove indexes, change or make covering indexes, etc.