Thread: Connecting and disconnecting from DB: good coding?

  1. #1
    Registered User
    Join Date
    Mar 2009
    Posts
    7

    Connecting and disconnecting from DB: good coding?

    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?

  2. #2
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Location
    Inside my computer
    Posts
    24,654
    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.
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  3. #3
    Kernel hacker
    Join Date
    Jul 2007
    Location
    Farncombe, Surrey, England
    Posts
    15,677
    Quote Originally Posted by Elysia View Post
    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.

    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
    Last edited by matsp; 03-13-2009 at 09:20 AM.
    Compilers can produce warnings - make the compiler programmers happy: Use them!
    Please don't PM me for help - and no, I don't do help over instant messengers.

  4. #4
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Location
    Inside my computer
    Posts
    24,654
    Yes, I agree, hence the time it solution
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  5. #5
    Ex scientia vera
    Join Date
    Sep 2007
    Posts
    477
    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.
    "What's up, Doc?"
    "'Up' is a relative concept. It has no intrinsic value."

  6. #6
    Registered User
    Join Date
    Mar 2009
    Posts
    7

    Post

    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.

    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 have the following in main(), the second query prints no result.
    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;
    }
    If I close the connection and open it again between those queries, they both print results! Why?

  7. #7
    Registered User
    Join Date
    Mar 2009
    Posts
    7
    OK, maybe it was the beer! Seems OK now! haha

Popular pages Recent additions subscribe to a feed