[PowerPC603]

Hi,

I've got MySQL working but I find it kinda slow.
Using normal queries or threaded makes no difference when inserting 212 vehicles into a database using a loop.

pawn Код:

new Query[600], Disabled;
    for (new i; i < sizeof(AVehicleInfo); i++)
    {
        if (AVehicleInfo[i][VehicleDisabled] == true)
            Disabled = 1;
        else
            Disabled = 0;

        format(Query, sizeof(Query), "INSERT INTO VehicleInfo (ID, Name, Class, Price, MaxFuel, Consumption, RefuelTime, Disabled)");
        format(Query, sizeof(Query), "%s VALUES ('%i', '%s', '%i', '%i', '%f', '%f', '%i', '%i')", Query,
        AVehicleInfo[i][VehicleModel], AVehicleInfo[i][VehicleName], AVehicleInfo[i][VehicleClass], AVehicleInfo[i][VehiclePrice],
        AVehicleInfo[i][VehicleMaxFuel], AVehicleInfo[i][FuelConsumption], AVehicleInfo[i][RefuelTime], Disabled);

        mysql_function_query(SQL_db, Query, true, "", "");
    }

This code takes about 6 seconds to execute.
It's inside my OnFilterscriptInit callback for now for testing purposes.
The gamemode starts immediately, but shutting it down immediately after starting it takes 6 seconds, due to the queries waiting to be executed.
Also, inside the database, the contents are inserted slowly as well.
If I used

pawn Код:

mysql_query(SQL_db, Query);

And added GetTickCount() before the loop and after the loop, the result was 5.6 seconds for this loop to execute and starting the server was delayed as well due to non-threaded queries where OnFilterScriptInit had to wait until all queries were processed.

I really thought MySQL would be fast. I find it rather slow, compared to saving the same data into a file.
Inserting this data into MySQL takes 5.8 seconds.
Saving the same data to a file takes only 5 milliseconds (this file is about 42 kilobytes in size and saves 1000 times faster than storing the same data into MySQL).
Executing the format-lines without sending a query takes 1 millisecond.

pawn Код:

new Query[600], Disabled, StartTime, EndTime;
    StartTime = GetTickCount();
    for (new i; i < sizeof(AVehicleInfo); i++)
    {
        if (AVehicleInfo[i][VehicleDisabled] == true)
            Disabled = 1;
        else
            Disabled = 0;

        format(Query, sizeof(Query), "INSERT INTO VehicleInfo (ID, Name, Class, Price, MaxFuel, Consumption, RefuelTime, Disabled)");
        format(Query, sizeof(Query), "%s VALUES ('%i', '%s', '%i', '%i', '%f', '%f', '%i', '%i')", Query,
        AVehicleInfo[i][VehicleModel], AVehicleInfo[i][VehicleName], AVehicleInfo[i][VehicleClass], AVehicleInfo[i][VehiclePrice],
        AVehicleInfo[i][VehicleMaxFuel], AVehicleInfo[i][FuelConsumption], AVehicleInfo[i][RefuelTime], Disabled);

        mysql_query(SQL_db, Query);
//      mysql_function_query(SQL_db, Query, true, "", "");
    }
    EndTime = GetTickCount();
    printf("Saving the data to MySQL takes %i milliseconds", EndTime - StartTime);

    StartTime = GetTickCount();
    for (new i; i < sizeof(AVehicleInfo); i++)
    {
        if (AVehicleInfo[i][VehicleDisabled] == true)
            Disabled = 1;
        else
            Disabled = 0;

        format(Query, sizeof(Query), "INSERT INTO VehicleInfo (ID, Name, Class, Price, MaxFuel, Consumption, RefuelTime, Disabled)");
        format(Query, sizeof(Query), "%s VALUES ('%i', '%s', '%i', '%i', '%f', '%f', '%i', '%i')", Query,
        AVehicleInfo[i][VehicleModel], AVehicleInfo[i][VehicleName], AVehicleInfo[i][VehicleClass], AVehicleInfo[i][VehiclePrice],
        AVehicleInfo[i][VehicleMaxFuel], AVehicleInfo[i][FuelConsumption], AVehicleInfo[i][RefuelTime], Disabled);
    }
    EndTime = GetTickCount();
    printf("Executing the formats takes %i milliseconds", EndTime - StartTime);

    StartTime = GetTickCount();
    FileVehicleInfo_Save();
    EndTime = GetTickCount();
    printf("Saving the data to file takes %i milliseconds", EndTime - StartTime);

The output of this is:

Код:

Saving the data to MySQL takes 5845 milliseconds
Executing the formats takes 1 milliseconds
Saving the data to file takes 5 milliseconds

Is this normal?
If it is, I might just dump MySQL alltogether.
I got ALOT more data to dump into databases, 212 vehicles is nothing compared to the rest.
This would take several minutes to just store all data into the database.

This is my function to save the same data to a file.

pawn Код:

// This function will save the VehicleInfo datafile
FileVehicleInfo_Save()
{
    // Setup local variables
    new File:DFile, LineForFile[100];

    // Open the VehicleInfo file for writing
    DFile = fopen(FileVehicleInfo, io_write);

    // Loop through all vehicle-models
    for (new Slot; Slot < sizeof(AVehicleInfo); Slot++)
    {
        fwrite(DFile, "[VehicleModel]\r\n"); // Save the header of this vehiclemodel

        format(LineForFile, 100, "VehicleName %s\r\n", AVehicleInfo[Slot][VehicleName]);
        fwrite(DFile, LineForFile);
        format(LineForFile, 100, "VehicleClass %i\r\n", AVehicleInfo[Slot][VehicleClass]);
        fwrite(DFile, LineForFile);
        format(LineForFile, 100, "VehicleModel %i\r\n", AVehicleInfo[Slot][VehicleModel]);
        fwrite(DFile, LineForFile);
        format(LineForFile, 100, "VehiclePrice %i\r\n", AVehicleInfo[Slot][VehiclePrice]);
        fwrite(DFile, LineForFile);

        format(LineForFile, 100, "VehicleMaxFuel %f\r\n", AVehicleInfo[Slot][VehicleMaxFuel]);
        fwrite(DFile, LineForFile);
        format(LineForFile, 100, "FuelConsumption %f\r\n", AVehicleInfo[Slot][FuelConsumption]);
        fwrite(DFile, LineForFile);
        format(LineForFile, 100, "RefuelTime %i\r\n", AVehicleInfo[Slot][RefuelTime]);
        fwrite(DFile, LineForFile);
        if (AVehicleInfo[Slot][VehicleDisabled] == true)
            fwrite(DFile, "VehicleDisabled Yes\r\n");
        else
            fwrite(DFile, "VehicleDisabled No\r\n");

        fwrite(DFile, "[/VehicleModel]\r\n\r\n"); // Save the footer of this vehiclemodel
    }

    // Close the file
    fclose(DFile);
}

[Memoryz]

If you are just updating existing vehicle info, do not use INSERT, use UPDATE.

[PowerPC603]

I'm dumping the data into the database for the first time, so I have to use INSERT.

I haven't tested reading yet.

[iZN]

You must be doing something wrong. I never had any issue with the MySQL speed, it is always like in a blink except some loading stuff like Houses, Vehicles, Races.

Unthreaded vs. Threaded queries.
http://forum.sa-mp.com/showpost.php?...54&postcount=6

[gtaplayer1]

Quote:

Originally Posted by PowerPC603 Hi, ... Using normal queries or threaded makes no difference when inserting 212 vehicles into a database using a loop. ...

I agree something must be wrong with your mysql server's configuration or something.

When you tried using threaded queries, did your server freeze execution the whole time it was dumping data?

EDIT:
Is your MySQL server running locally or on a remote server?

[Michael@Belgium]

Indeed strange that you use the latest version of the MySQL plugin and you say it's slow. I'm using R6-2 and it isn't slow. But on the other side, I don't have to insert +200 rows at once... Not sure how to fix this.

[PowerPC603]

Quote:

Originally Posted by gtaplayer1 I agree something must be wrong with your mysql server's configuration or something. When you tried using threaded queries, did your server freeze execution the whole time it was dumping data? EDIT: Is your MySQL server running locally or on a remote server?

The MySQL server is runnong on my own computer (localhost).
Threaded queries didn't freeze OnFilterScriptInit, non-threaded did.

Just ran another test to do the same when the data was already in place.
The ID field is primary key, so the queries will be sent to MySQL, but the data won't be overridden as the data already exists.
This only took 71 milliseconds.

Perhaps MySQL is increasing filesize of the database everytime when a new record is being added, this might be what takes so long.

I'm using BlueG's plugin R34.
For the server, I'm using MySQL Community Server 5.6.15.

[InfiniTy.]

Something must be wrong with your mysql (either the service itself or the plugin somehow)..

pawn Код:

main() {
    g_con = mysql_connect("127.0.0.1","root","test","");
    new tick = GetTickCount();
    for(new i;i != 1000000;i++) {
        mysql_function_query(g_con,"INSERT INTO `asd` (`1`,`2`,`3`,`4`,`5`,`6`,`7`) VALUES (1,'ddddddd',3,4,5,'aaaaaaaaaaa',7)",false,"","");
    }
    printf("took %d milliseconds",GetTickCount() - tick);
}

I used this code and i got in the console
"took 4376 milliseconds"
And if i try with only 200 it takes just 1 millisecond !
And also..the server shouldn't be hanged while inserting the data..that's weird(atleast with r7+)
It may take a while to insert those 1000000 that i tested it with but the server isn't hanged,it works just fine.
Did you try running it from the gamemode ?

[PowerPC603]

The server isn't frozen when using mysql_function_query.
This is instant as well as the server starts as fast as without using MySQL.
Only the queries take so much time to be processed in the background.

To time it right (waiting until queries are actually executed), I used mysql_query instead.

Try your same code this way (with your 1 million queries):
- start server
- end server immediately using CTRL C on the console window

You'll see it takes a while to shutdown as the queries are still being processed in the background.
Empty your database first so the data can be inserted.


I'll try updating the database with the same values when the data is already in place to see how long that takes.
So the UPDATE queries should overwrite all data instead of adding it.

[InfiniTy.]

Quote:

Originally Posted by PowerPC603 -

Yeah,i know it takes some time to insert the querys in the background,but it shouldn't take that long for 200 querys..