RSS
 

Posts Tagged ‘sql’

Damn work! :)

10 Feb

Not much has gone on lately, except for the bad week start and then work has gone crazy, so I haven’t done much lately on my hobbies… Haven’t played SWTOR in ages, so I still haven’t had the time to finish all the questing so I can do an “early impressions” review…

Anyway, I took the opportunity to read up a lot on shading systems, specially multi-materials on a deferred rendering context, and I decided that when I have the chance, I’ll implement something like this: Anisotropic Lighting, combining a 3d texture to store the maps and a material id lookup (since I still have component free on the G-Buffer). Still not an ideal solution (since I can’t parameterized that much the materials, beyond the lookup… for example, I can’t parameterize rim-lighting with a falloff parameter, I have to fix that so I can generate the lighting lookup table). Anyway, it should give me some more flexibility in having different materials, specially on Grey

grey_anim03

The hair and fur shading seem “incorrect”, and this hopefully will allow me to fix it (with double-sided lighting for the hair and some anisotropy on the fur).

There’s a new blog post on Spellcaster Studios, showing some props and base textures for the terrain tiles, besides some explanation on how we’re going to make the terrain in the first episode.

Another thing that I’ve been working was with some SQL queries for work: I had a query that basically inserted some rows into a database, but it could only do that if there wasn’t some other rows there yet. Basically, I need to queue some reset commands, but could only do that if there wasn’t any pending reset commands…

First version just iterated and checked in PHP, and took about 15 seconds…

Then we built a query that did all that in one SQL query, and it took about 9 seconds (because it had to do a SELECT query for every hit in 1400+ rows)… Still too slow…

Finally, we just designed a series of statements with a different point of view… Instead of adding the new data and before adding it check if it was already present in a specific table (a query), we created a temporary table that had all the pending requests, and we just checked if the data we wanted to insert was in that table or not (a “static” structure)… Boom, down to 0.005 ms per query…

For fun, here’s the code:

CREATE TEMPORARY TABLE TempTable(
   key VARCHAR(50) NOT NULL
) ENGINE=MEMORY;

INSERT INTO TempTable(mac)
SELECT data FROM MainTable WHERE ((type=5) OR ((type=7) AND (error LIKE 
'processing%')));

INSERT INTO MainTable(id,user,status,type,data,error,comment,time) SELECT 
NULL,1,1,5,OtherTable.key,'','Device reset request (type=5)',NOW() 
FROM Services
INNER JOIN Addresses
ON Services.address=Addresses.id
INNER JOIN Devices
ON Services.device=Devices.id
INNER JOIN OtherTable
ON Devices.parameters=OtherTable.key
WHERE (Addresses.location=40) AND (Services.type=1) AND OtherTable.key 
NOT IN(SELECT key FROM TempTable);

DROP TABLE TempTable;

So, even something like SQL can be optimized! Smile (I really hate SQL and database programming, but this kind of things are quite neat!)