• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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

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;
var
 LocateSuccess: Boolean;
 SearchOptions: TLocateOptions;
 N,BudCount: Integer;
 Tmp,Tmp2,Buds: String;
begin
If Trim(UserN)='' Then Exit;
SearchOptions := [loCaseInsensitive];
locateSuccess := DataModule1.Table1.Locate('UserName',UserN,SearchOptions);
Try
If LocateSuccess=False Then
   Exit;
 else
  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

james,mark,lisa,stephan,mel,kimmy

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.

0
tobjectpascal
Asked:
tobjectpascal
  • 3
  • 2
1 Solution
 
pilotzCommented:
Hello,
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.

Simonas
0
 
tobjectpascalAuthor Commented:
not exactly, i've given a limit of 200 buddies for 1 user.

mike
a,b,c,d,e,f,g,h,i,h


james
b,e,f,h,j


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.
0
 
pilotzCommented:
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 :)

Simonas
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BlackTigerXCommented:
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
0
 
pilotzCommented:
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.

Simonas
0
 
BlackTigerXCommented:
remember the rule of thumb, always use the right tool for each job
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now