SQL Quickie

[WPKenny]

Just wondering, years ago I touched very briefly on some SQL stuff and I was just pondering what the command was you used to include in queries that would make the query go slower but placed less strain on the server? Anyone know what I'm on about?

 

[Overdriven]

I'd think a normal INSERT query wouldn't really kill the server (Unless you've got like 300 people on it/depending on what the spec of the server is) unless each INSERT statement is quite large (spoilerish - even though it's quite small) and there's a lot of them, it shouldn't really do much to performance.

Spoiler

INSERT INTO itemtemplate
(ItemTemplate_ID, Id_nb, Name, Level, Durability, MaxDurability, Condition, MaxCondition, Quality, DPS_AF, SPD_ABS, Hand, Type_Damage, Object_Type, Item_Type, Color, Emblem, Effect, Weight, Model, Extension, Bonus, Bonus1, Bonus2, Bonus3, Bonus4, Bonus5, ExtraBonus, Bonus1Type, Bonus2Type, Bonus3Type, Bonus4Type, Bonus5Type, ExtraBonusType, IsPickable, IsDropable, Gold, Silver, Copper, MaxCount, PackSize, Charges, MaxCharges, SpellID, ProcSpellID, Realm, IsTradable, Bonus6, Bonus7, Bonus8, Bonus9, Bonus10, Bonus6Type, Bonus7Type, Bonus8Type, Bonus9Type, Bonus10Type, Charges1, MaxCharges1, SpellID1, ProcSpellID1, PoisonSpellID, PoisonMaxCharges, PoisonCharges, Platinum, CanDropAsLoot, AllowedClasses)
VALUES
("0_Demon_Bound_Bracelet", "0_Demon_Bound_Bracelet", "Demon-Bound Bracelet", 40, 50000, 50000, 50000, 50000, 100, 0, 0, 0, 0, 41, 33, 0, 0, 0, 10, 598, 0, 30, 22, 4, 4, 3, 0, 0, 3, 16, 14, 19, 0, 0, 1, 1, 0, 0, 0, 1, 1, 10, 10, 55443, 0, 0, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, "");

If you're putting in around 1000 inserts of something like the above (in spoiler) it could take a while, but still wouldn't kill performance much... *Waits for somebody with more professional SQL knowledge to respond*

 

[SheepCow]

INSERT LOW_PRIORITY INTO xxx ...

Is the MySQL way of doing things nicely.

 

[WPKenny]

Hmm. Not exactly what I was after. I used to include it in SELECT * FROM .... type queries.

 

[SheepCow]

Erm. Why would you want to do that?

 

[WPKenny]

I was told it was to reduce the strain on the server so the query and the normal operation of it wouldn't overload it and fuck up.

So I'm guessing no one knows what the command is?

 

[GReaper]

You should be creating indexes on tables for queries, you want select statements to run as fast as possible without reading the entire table. Having a low priority shit query doesn't stop it being a waste of resources.

MySQL does offer a high_priority option for select statements, and low_priority/delayed for inserts/updates. Maybe you got them confused?

 

[WPKenny]

I managed to get hold of an old colleague. He says the command was "nolock". Does this sound right?

 

[GReaper]

Locking in SQL Server seems to list it.

If you're not too bothered about consistency then it may help.

 

[Gahn]

I do hope that Select * it's just an example ...
The NOLOCK is good as long as u don't treat data that u MUST be sure that are consistent, cause it completely circumnavigate Sql locking policy.
That said it does give a performance boost.
So it is something along the line of "SELECT [Field1], [Field2] from [Table1] WITH (NOLOCK) WHERE [Field1] = 'xyz' ORDER BY [Field1] asc;".