Printable View
Hi
I have a question about performance and good coding in general.
I have made a function that connects to a MySQL db, makes a query with passed string argument, return the result, and closes the connection to db.
My program calls this function up to three times in a row when a requirement is met.
Do I gain much performance if I change the code to just connect once, do my queries, then disconnect from db? Does it make any difference?
Why not time it? There is the clock() function, or OS-specific functions, if you would provide your OS.
But I would guess that it is slower to connect and disconnect all the time, seeing as the database will have to do extra work everytime.
I would agree that there is an overhead. But sometimes the overhead of doing something is so small that it doesn't matter, at other times it's a large proportion.Quote:
Originally Posted by Elysia
There are two ways to find out:
1. Using profiling/logging to determine how much time is spent in what part of the code.
2. Experimenting by implementing the two solutions and measuring the overall throughput/time for a specific set of operations.
You also need to make sure that you make the test with representative data - doing a search that combines 6 different tables, returning a 1000 entries out of a few million possibles would be dramatically different from a "find user with ID number 61234" in a single table, with a few hundred entries. In the former case, the overhead of connecting to the database is probably minimal, whilst in the latter, it is proportionally larger because the request for data itself is MINIMAL.
--
Mats
Yes, I agree, hence the time it solution ;)
When possible, implement some kind of caching. It does not need to be extremely complex. Just consider the scenario where you would connect, read, disconnect in a loop, in contrast to the connecting once, reading a whole lot and then parsing it in your code, and separating it, then closing.
Connect/disconnect and read as few times as possible, and as much data as you can and is viable in the situation. E.g., do not read the whole database, just because you can, but if you know you'll be getting many entries from one table, read the whole table once and parse it in the code.
OK thanks everybody!
I thought about it and decided to connect once, do my stuff in the db and disconnect. But, now only my first query completes and I don't understand what is wrong. I have had a few beers now but I don't think that is the problem! My functions are like below. I did not write all variables for the db connection etc.
If I have the following in main(), the second query prints no result.Code:int connectodb() {
conn = mysql_init (NULL);
if (conn == NULL){
return 1;
}else{
if (mysql_real_connect (
conn, /* pointer to connection handler */
def_host_name, /* host to connect to */
def_user_name, /* user name */
def_password, /* password */
def_db_name, /* database to use */
0, /* port (use default) */
NULL, /* socket (use default) */
0) /* flags (none) */
== NULL)
{
return 1;
}else{
return 0;
}
}
}
int queryOneRecord(char *qstring) {
if(mysql_query(conn, qstring) == 0) {
res = mysql_use_result(conn);
if ((row = mysql_fetch_row(res)) != NULL) {
dbresult = malloc(strlen(row[0]));
strcpy(dbresult,row[0]);
return 0;
} else {
return 1;
}
} else {
return 1;
}
}
If I close the connection and open it again between those queries, they both print results! Why?Code:int main()
{
char *dbresult;
char *sql_query;
char query[1024];
int idnr = 2;
if(connectodb() ==0) {
sql_query = "SELECT employeenr FROM employees WHERE id = %d";
sprintf(query, sql_query, idnr);
if(queryOneRecord(query) == 0 || dbresult != "") {
printf("%s\n",dbresult);
free(dbresult);
}
sql_query = "SELECT employeenr FROM employees WHERE id = %d";
sprintf(query, sql_query, idnr);
if(queryOneRecord(query) == 0 || dbresult != "") {
printf("%s\n",dbresult);
free(dbresult);
}
mysql_close(conn);
}
return 0;
}
OK, maybe it was the beer! Seems OK now! haha