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.

LVL 4
tobjectpascalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
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
BlackTigerXCommented:
remember the rule of thumb, always use the right tool for each job
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.