I don't like DB's but everyone has to use them right?

Posted on 2006-03-26
Last Modified: 2010-04-05
At the moment my server simply stores their username,hashed password, chat room they use, along with their name, nick name where they are from, easy enough, now i've hit a snag (took sometime as well)

the server now has over 8,000 registered users / ids / members (1 person could have 200 id's for example)

now here's the problem, I needed to be able to store data which allowed me to store data dynamically, more than the default string length, so it used a BLOB thing

Function ExtractBuddyList(UserN: String): TBudList;
 LocateSuccess: Boolean;
 SearchOptions: TLocateOptions;
 N,BudCount: Integer;
 Tmp,Tmp2,Buds: String;
If Trim(UserN)='' Then Exit;
SearchOptions := [loCaseInsensitive];
locateSuccess := DataModule1.Table1.Locate('UserName',UserN,SearchOptions);
If LocateSuccess=False Then
  Begin      Buds:=DataModule1.Table1.fieldbyname('BuddyList').AsString;

ok, the code works PERFECTLY there's no problems at all, but lol, everytime i go to extract data, it pulls around 8% of CPU time, combined with all the other checks (as the person logs onto the server) it hits around 20 - 33% of CPU time, ok fine that's not much at all, after all i am only on a 1gig CPU and really a server should be of a nice decent speed, 2.5+gig upwards etc etc..

So this is what i'm wondering..

Would there be any point of me changing from a Paradox Database to say Access or something else?

I would have to be able to store a string > 4k which basically consists of usernames of "buddies"

when the user Joe logs in, the server looks to see the buddylist, oh yes

joe has these buds here


Limiting it to 255 chars would not be possible, i thought of storing a text file for each one of the users but that would be very messy (8,000 files for each user).

Would it be worth the effort to switch to a different DB? or start from fresh using something else...

speed that's what i need lol, and Paradox aint cutting it.

Question by:tobjectpascal

    Expert Comment

    if i understand you correctly, you are storing all buddies in one string for one user in DB. that means that you have bad DB design and operating with such big strings is not very fast. I suggest you to have a one more table (e.g. UsersBuddiesTable) to store user id and buddy id, so you select buddies where user id is equal to specific value. So you don't need to operate with big strings anymore.
    for example you could write similar SQL (this one is for MS SQL but should work on paradox i think): select UserName from UsersTable where exists (select * from UsersBuddiesTable where UserBuddiesTable.UserID = UsersTable.UserID and UserBuddiesTable.UserID="12345") order by UserName;

    thats it

    Anyway if you would like to change the DB server i suggest MS SQL.

    LVL 4

    Author Comment

    not exactly, i've given a limit of 200 buddies for 1 user.



    the problem i had was i need more than 255 chars of space to store all the ids, so the buddylist is stored as a "BLOB Memo" which starts to really get slow once you have around 100 buddies, really slow...., nothing to do with how many buddies but how big the string you pull from it is...

    So yeah, i only have the 1 table, you think i should make another table? or change the DB driver.

    I'm pretty new to DB's still i hate using them and do all i can to avoid using them :) So i'm having trouble working out where i would stick the usernames if not in a blob memo.

    Accepted Solution

    i think you should create one more table (e.g. UserBuddies). as i have mentioned before table should have fields liek this:
    id - int (autoincrement, primary key)
    UserId - int
    BuddyID - int

    ofcourse it would be better if UserId and BuddyID would be references to main YourUsersTable.UserID field.

    the idea is that you can save all buddies to one table. For example we have such records in YourUsersTable:
    id, name
    12345, John
    2, Peter
    3, Marry
    4, Lisa

    so lets sau John knows all others, so in UserBuddies table:
    id, userID, buddieID
    1, 12345, 2
    2, 12345, 3
    3, 12345, 4

    so as you see you can make as much buddies as you want. To get buddies list for user which ID is 12345 SQL query i have written in previous post.

    I hope this makes you clear. For you problem you don't need to change the DB, because problem is in big strings and lag is in your code, but not DB side :)

    LVL 13

    Expert Comment

    I don't think storing a file for each user is necesarily a bad idea, you would just store the path in the database, the load time would be so much faster

    I recommend you don't put all the files in a single directory though, break them down to have a maximum of 3000 files per folder, and keep your file names to be 8.3

    Expert Comment

    maybe sometimes using files is good idea, e.g. liciense or machine specific files. But if you use database, why do you need files ?:) Database is for storing data. Data operations are faster and safer, and i would say more simple and easy to transform, modify and fix if needed.

    LVL 13

    Expert Comment

    remember the rule of thumb, always use the right tool for each job

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
    Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now