SQL Query in a variable as RecordSet

[BShadow]

Hello,

I've programmed a server which uses a database to get and store information about the users. The server supports multiple connections and data is saved alot.

Because of the CPU use of the server with every huge action a user does, I'm thinking about changing the updating and retreiving to and from the database (MS Access) to using variables, and saveing the data to the database in a interval of 15 minutes. The loss of data in a server crash is acceptable, crashes are not common (anymore ).

Now, I have a function called SQL(strQuery) as RecordSet which I call to get the database information. Updating is done local:

Dim RS as RecordSet
 
Set RS = SQL("SELECT * FROM Users")
RS!UserName = "Guest"

Now I was wondering when I create a variable like

Dim Users as RecordSet

And I copy the whole Users table from the database into this variable, how I can select a few records from the variable as a new recordset.

So my SQL function could do a routine on the variable 'Users' and the rows left over after a selection routine could be returned as the SQL() so I don't have to update all the code elsewhere in the server.

However, this setting of the SQL() must be done ByRef because, when someone would so Set RS as SQL("SELECT * FROM Users") and change the RS, the corresponding field in the Users variable (used by the SQL() function) should also change to the new value.

Is this possible, if please can someone show me how?

Thank you for your time...

[Gaffer]

What exactly does your function SQL() do? And are you using DAO or ADO (I suspect the former...)

[BShadow]

Yes, I use DAO.

My SQL function:

Function SQL(strQuery as String, Optional Execute as Boolean) as RecordSet
 
  If Execute then
    DB.Execute strQuery
  Else
    SQL = DB.OpenRecordSet(strQuery, dbopenDynaSet)
  End If
 
End Function

Calling the SQL function like

Set RS = SQL("SELECT * FROM Users")

Would set the local RS variable with the result of the SELECT SQL Query.

[Ecniv]

In the case of the users why are you doing it this way ?

On a splash screen of the db loading you could grab the user (or log them in) then hold the current user in global variables for use when changing tables etc..


Or did I miss the point completely?


Vince

[BShadow]

uhhh, it's a server. The User uses a client to connect to the server and perform actions. The user logs into the server via it's client, so nothing can be done serverside at the spash-screen time

The problem is that I dynamically update the database. So that when a user changed his/her setting, the database gets updated inmediately. This takes alot of CPU power to query the database and update, so I want every thing to load into the memory and update the database in some interval (server side)...

[Gaffer]

I'm confused. What exectly are you doing with rs!UserName ? Are you updating field username with the user's userID? Is it something lie this:

User log into server
You have a log-in tabel called Users, which you update every time a user logs in.


IF this is an updating/append issue, then you don't even need to create a recordset IMO...

[BShadow]

Okay, the situation is this (server side)

- incomming connection (winsock) -> connection handling blah blah blah
- user sends login and password
- server retreives password from database for this user if equal, then user has access

Now the user does some actions. My server is a MUD server, for clarification. So the user can move, pick up etc.

When a user moves, the Location field in the User tabel bust be updated to the new location of the user.

Set RS = SQL("SELECT Location FROM Users WHERE UserName='Guest'")
  'RS has the location as a recordset
 
RS.Field("Location").value = NewLocation 'Or RS!Location = NewLocation
 
Set RS = Nothing

Calling the SQL function here sets the SQL function to the recordset correcponding to the SELECT query. The local variable RS is set to this value (recordset) of the SQL() function and has the recordset retreived.

Next the RS.Field("Location").value is updated and because the RS has a recordset from the database, the database is updated.

Now, what I want is not the database to be updated in this way, but a public variable containing all records from the Users table.

So at startup, the server places all records from the table Users into a variable (as recordset) called Users.

'Users as recordset
'DB as Database and the database is opend (connected)
 
Set Users = DB.OpenDatabase("SELECT * FROM Users", dbOpenDynaset)

Here the variable Users gets all recordsets from the database table Users.

I want this to be in the memory all the time and be updated withoud the database actually be updated. I'll create a time that with an interval of 15 minutes saves the contents of the recordset variable Users to the actual database. This way the database is not updated all the time...

Is this possible? If so how?

[Gaffer]

Well yes you can - you can declare your recordset globally and open it at startup. This recordset will persist throughout the life of your module. You then have a function on closeure of your form which updates your recordset.

But how do you handle multiple instances? Are you sure you even need a recordset at all? There are few SQL statemetns as effective as:

db.execute ("update users set location = '" & NewLocation & "' where UserName = 'Guest'")

very quick....

[Ecniv]

I see.

So if the users were to move around a lot in the 15 mins only at the time of the 15mins expires would they be stored to the tables.

You said it was held on the server, so you are basically making a copy of the tables in an array then updating from the arry every 15 mins.

Your users must move a lot then...


Yeah this is possible, but as gaffer said, perhaps you should look at executing queries first.

Or try both ways and see which is better.


The array must be set up before anyone logs in and held open all the time though. Not sure if this is a problem or not...


Vince

[BShadow]

Originally posted by Gaffer
Well yes you can - you can declare your recordset globally and open it at startup. This recordset will persist throughout the life of your module. You then have a function on closeure of your form which updates your recordset.

But how do you handle multiple instances? Are you sure you even need a recordset at all? There are few SQL statemetns as effective as:

db.execute ("update users set location = '" & NewLocation & "' where UserName = 'Guest'")

very quick....

Yes this is possible, the global variable idea, but I use the 'SELECT Location FROM Users' and for example 'SELECT Password FROM Users'. This will then be placed in the same global variable (RS). Doesn't that cinflict, because I havn't updated it whithin the 15 mins, and so the prrevious data is lost.

The SQL function does support the Execute thing. For more clear code I'm using the RS.Fields("Location").value = "..." etc. I could make the Execute string more clearly though.

Using the Execute, does not help my with my problem, that is the memory based recordsets.

I was thinking, maybe it's possible to load the whole database into the memory? Or clone? Or I don't know Just update it every 15 mins to the file itself...

[BShadow]

Originally posted by Ecniv
I see.

So if the users were to move around a lot in the 15 mins only at the time of the 15mins expires would they be stored to the tables.

You said it was held on the server, so you are basically making a copy of the tables in an array then updating from the arry every 15 mins.

Your users must move a lot then...

Yeah this is possible, but as gaffer said, perhaps you should look at executing queries first.

Or try both ways and see which is better.

The array must be set up before anyone logs in and held open all the time though. Not sure if this is a problem or not...

Vince

Yes
And Yes, the users move alot, but I also have NPC's and they move too

The best thing is to make the variable not a array, but a recordset, but I can not manipulate that, or so my knowladge tells me...
I was looking online, I have no idea what tabledef does, but maybe that is a was into the solution. Of maybe using the Fillcach, but where can I save Cach, and can I manipulate the cach as a recordset?

[Ecniv]

Create an array of string - put all the tables info in it.

Update the arrray when users change things.

Every 15 mins open the records and update from the array - by this I mean open all the records and loop through updating changes from the array to the tables.


Vince

[BShadow]

Problem with that is that the variable, in this case an array is no RecordSet and all the code troughout the sever is based on handeling a recordset. else I could just update that code But that is to much, last option

[BShadow]

Originally posted by Gaffer
Well yes you can - you can declare your recordset globally and open it at startup. This recordset will persist throughout the life of your module. You then have a function on closeure of your form which updates your recordset.

But how do you handle multiple instances? Are you sure you even need a recordset at all? There are few SQL statemetns as effective as:

db.execute ("update users set location = '" & NewLocation & "' where UserName = 'Guest'")

very quick....

Gaffer, you're right when asking what to do with multiple instances. How can this be handled?

[BShadow]

*bump*

[Gaffer]

Originally posted by BShadow
Yes this is possible, the global variable idea, but I use the 'SELECT Location FROM Users' and for example 'SELECT Password FROM Users'. This will then be placed in the same global variable (RS). Doesn't that cinflict, because I havn't updated it whithin the 15 mins, and so the prrevious data is lost.

Just use select * from users where username = upircurrentuser. Then you can update that user's details whenever you want. I don't understand why you think you need to open an rs for <select location from users> and then for <select password from users>. As long as you open your recordset specific for a user, you should have no conflict problems.

The SQL function does support the Execute thing. For more clear code I'm using the RS.Fields("Location").value = "..." etc. I could make the Execute string more clearly though.

This is not about clarity of code. This is about performance. Basically, opening an recordset, moving to a specific record, changin a specific field withing that trecordset record, and then updating it take far much mor time than a strightforward UPDATE statement.

Using the Execute, does not help my with my problem, that is the memory based recordsets.

I was thinking, maybe it's possible to load the whole database into the memory? Or clone? Or I don't know Just update it every 15 mins to the file itself...

See above


Maybe I am oversimplifying your problem, but if it a matter of updating a users table with current user information, you would probably need the population of Belgium to be working on your program before you see any significant slowdown when using a standard update statement

[swatty]

Originally posted by Gaffer


Maybe I am oversimplifying your problem, but if it a matter of updating a users table with current user information, you would probably need the population of Belgium to be working on your program before you see any significant slowdown when using a standard update statement

Belgium may be a little country , but it is well populated.
An update statement wouldn't give a slowdown a select on the whole table however ...

[Gaffer]

Originally posted by swatty
Belgium may be a little country , but it is well populated.

...my point exactly...

[swatty]

On the other hand you could have the whole population of England working on your program without any slowdown visible.

But therfore it isn't a good way to get all users if U only want to update one.

[BShadow]

LOL! Hehehehe

AnyWay...

Originally posted by Gaffer
[B]Just use select * from users where username = upircurrentuser. Then you can update that user's details whenever you want. I don't understand why you think you need to open an rs for <select location from users> and then for <select password from users>. As long as you open your recordset specific for a user, you should have no conflict problems.

yes, you're right. It was a wrong example. Ofcourse I have more tables, so when I do a 'SELECT * FROM Users' and set it in the RS var and then I need a name of a room with 'SELECT * FROM Rooms' then this is set in the RS too... Or I should make as much RS'ses as tables. Or maybe Dim RS() as RecordSet Could that work?

Type DatabaseTables
    Users As Recordset
    Rooms As RecordSet
    ...
End Type
 
Dim RS As DatabaseTables

Could this work? Then I could put all the tables in just one variable. The only thing is that I have to fix my code to use the variable.

But What if I do a "SELECT User.Username, Rooms.RoomName FROM USERS, ROOMS WHERE Users.Username = 'Guest' AND Rooms.Location=Users.Location"

What would that give me? I can't do this with just one open recordset.

What I want here is a function (probably of my own) to handle suchs requests and send me a proper recordset as a result.
Maybe someting like

Function DoSQL(Str as String) as Recordset
    'Search the open recordset Users
    'Search the open recordset Rooms
    'Select the corresponding SELECT fields and create a new recordset with these fields only.
 
    DoSQL.Fields.Append (Field.Name = "UserName")
    DoSQL.Fields("UserName").Value = RS.Users.Fields("UserName").Value
 
    DoSQL.Fields.Append (Field.Name = "RoomName")
    DoSQL.Fields("RoomName").Value = RS.Rooms.Fields("RoomName").Value
End Function

Could this be possible? This way the doSQL function returns a normal recordset iwth the appropriate fields and records and the main server code can use it unchanged... No database SQL query is done and no updateing is done...

However, I don't know if these values when changed outside the DoSQL function also changes in the Main RS variable...

Am I any clear?

[BShadow]

*bump*

[Ecniv]

So the db is on the server which the user connects to.

And you want to hold the current location of all users held by the db on it. Accessible for users/reporting to state where they are at a certain time, stored into the tables every 15 mins to try to stop network traffic (although you are still connecting to the server anyhow.. and incurring network traffic then).


I am going to have to ask why you'd need to keep the locations of people if they are going to change a lot (hotdesks in 20 rooms?).

It seems to me that you are trying to use a database to hold peoples location but not use a database except for holding the information once the server is shut down (assuming it is...)


You say you have wrote a routine to get the data and one to store it. Yet you also want to hold an array of information which changes on top of the database and store data to the database.



The options (as I see it and I could be wrong) are :

1. Set up a database and use just that
2. Use an array of data (updated by the admin or when the server is started - store on close down or every 15 mins
3. Just use the database for initial loading of the array and hold the info in the array

Perhaps you should try all the methods - one per day and test network overhead times updating etc and see which is the most viable (asssuming you have the time to).

the RS too... Or I should make as much RS'ses as tables. Or maybe Dim RS() as RecordSet Could that work?

This is mimicing the database but in arrays...

What I want here is a function (probably of my own) to handle suchs requests and send me a proper recordset as a result.
Maybe someting like

visual basic code:--------------------------------------------------------------------------------
Function DoSQL(Str as String) as Recordset
'Search the open recordset Users
'Search the open recordset Rooms
'Select the corresponding SELECT fields and create a new recordset with these fields only.

DoSQL.Fields.Append (Field.Name = "UserName")
DoSQL.Fields("UserName").Value = RS.Users.Fields("UserName").Value

DoSQL.Fields.Append (Field.Name = "RoomName")
DoSQL.Fields("RoomName").Value = RS.Rooms.Fields("RoomName").Value
End Function
--------------------------------------------------------------------------------

This holds the tables open all the time (recordsets held open) but doesn't actually update (no rs.update)...



Vince

[Gaffer]

Yes you can do that. But I have suggested to you already that if all you are doing is updating user information, the quickest and less memory intensive way of doing this is by using stadard DML UPDATE/INSERT queries.

I'll tell you what I use recordsets for, 90% of the time - I use them when I need to apply logic to data on a row-by-row basis, i.e. if it can't be performed in a batch UPDATE basis.

However, as you seem to want to persist with recordsets, I will answer your questions:

1. Using Type - yeah you can use type - but this isn't gonig to solve your speed problem

2. Using rs() - nope, set rs(1) - db.openrecordset isn't going to work

3.

This way the doSQL function returns a normal recordset iwth the appropriate fields and records and the main server code can use it unchanged... No database SQL query is done and no updateing is done...

However, I don't know if these values when changed outside the DoSQL function also changes in the Main RS variable...

No database SQL query and no updating? So what exactly is it supposed to do? even if you construct a recordset from scratch, you have to commit it to the database at some point. And it still doesn't over your problem about simultaneous updates.

Tell me - how many tables do you need to update?

[BShadow]

Hehehe, this is getting to be a long topic, with long replies I like that.

Reply to Ecniv
Okay, Ecniv, it's not the Network traffic, it's the CPU usage... I have alot of fucntions that are called that request data from the database and everytime a SQL query is run and data is returned. Just one query is neglectable, but alot of them again and again is not. I definatetly see full CPU usage when my server is doing it's thing every 10 seconds. The game is dynamic and NPC's must move, links must be checked, rooms must be checked, Items, spells cast and other things must all be checked... Every thing that is checked requires about 1-5 SQL queries. For example When the links are checked, and I have about 200 - 300 links, every link has about 1-2 SQL's I mean, for every link I need data got from about 2 SQL Queries. That is why I want all things to run in the memory, and not be get and put from and into the database itself.

I am going to have to ask why you'd need to keep the locations of people if they are going to change a lot (hotdesks in 20 rooms?).

It's a RPG and players can move from room to room. And I need to know where one is to be able to let other see them...

- Set up a database and use just that
- Use an array of data (updated by the admin or when the server is started - store on close down or every 15 mins
- Just use the database for initial loading of the array and hold the info in the array

The second option is what I want to have. And Yes I want to Mimic the database in arrays

This holds the tables open all the time (recordsets held open) but doesn't actually update (no rs.update)...

Yes, okay, then I'll have a timer that does a rs.update every 15 mins.

Reply to Gaffer
Okay, when I update the database with the UPDATE function, would take less CPU usage? But I have alot of CPU usage when I do the SELECTS, as I have explained to Ecniv I can rebuild all the updating of the recordsets to UPDATE, but somehow I don't think that will solve my porblem entirely. Partly it will I think.

1. Using Type - yeah you can use type - but this isn't gonig to solve your speed problem

2. Using rs() - nope, set rs(1) - db.openrecordset isn't going to work

3.
quote:
--------------------------------------------------------------------------------
This way the doSQL function returns a normal recordset iwth the appropriate fields and records and the main server code can use it unchanged... No database SQL query is done and no updateing is done...

However, I don't know if these values when changed outside the DoSQL function also changes in the Main RS variable...
--------------------------------------------------------------------------------

No database SQL query and no updating? So what exactly is it supposed to do? even if you construct a recordset from scratch, you have to commit it to the database at some point. And it still doesn't over your problem about simultaneous updates.

Tell me - how many tables do you need to update?

Okay, the server create RecordSet variables on startup of all the tables in the database. When I call the DoSQL function, It would search these RecordSet variables and find the appropriate information that I request in the provided string (like "SELECT * FROM Users"). It would get the RSUsers variable which contains the Users Table from the database and return the full recordset.
If I request "SELECT UserName FROM Users" then it would again get the RSUsers variable, take the UserName field and return it by doing something like (Function DoSQL(Str as String) As RecordSet)

DoSQL.Fields.Add "UserName"
DoSQL.Fields("UserName").Value = RSUsers.Fields("UserName").Value

Now the DoSQL function that is returned as a recordset contains only the UserName field and the code that requested the DoSQL function does someting like:
Set RS = DoSQL("SELECT UserName FROM Users")
Now the RS contains the field returned by the DoSQL Function and can handle it in any (valid) way it likes.

Now I hope that when I do
RS.Fields("UserName").Value = "New Name" The RSUsers variabe gets updated too... This ofcourse could only be done if the value given to the returned DoSQL function is by reference to the RSUsers variable, so that when for example I do
Dim Temp as RecordSet
temp = RSUsers
Temp.Fields("UserName").Value = ""
That the field in RSUsers (RSUsers.Fields("UserName").Value) is also "".

No database SQL query and no updating? So what exactly is it supposed to do? even if you construct a recordset from scratch, you have to commit it to the database at some point. And it still doesn't over your problem about simultaneous updates.

The updateing will b done in a timer (or a timer calling a function). This way I could to this every 15 minutes. Update the whole Users table in the database with the RSUsers variable...

Tell me - how many tables do you need to update?

Uhhh, let me see. I'm at work right now, I don't know all the tables in my database, they're alot of them
I think about 6 to 8 tables... Where two of them containing more than 100 entries. Each entry must be checked...

[Ecniv]

I don't think the cpu usage will drop but essentially you need an array for each table which you update (if you don't use the database all the time).

The arrays then get written to the database (for back up purposes) every 15 mins.

This means using methods to find the correct row fast for each array to get the data required. Looping trhough all 100 or so entries you have etc. So the CPU usage won't go down.

You might be able to store the layout of the rpg - the rooms - and things on the clients machine with only the contents and people in the rooms on the server. This would mean that the user gets a copy of the rooms layout once when logging in. This has a pitfall if the rooms are locked to certain levels etc, but I'm sure you can find a way around that.

All the items and people have to be on the server though to be shared around.

I'm not sure there are faster ways of retrieving who is where and with what. Probably depends on the flow of the rpg...


Vince

[BShadow]

Hmm, you have a point whith what you said about searching the arrays...
I though that this was done faster than the letting the database do it and retreiving the result. Going trough all sorts of drivers etc.

Sorting the levels on the clients system gives me another problems like that it will become more cheat sensitive.

So I'm keeping the data server side, for sure.

Hmmm...


The reason I was asking this question is because I though memory based records would be faster than database-based records in updateing and retreiving. Most MUDs work on the same principle... Or so I've heard.

[BShadow]

back from the weekend...



*bump*

[BShadow]

*bump*

[BShadow]

*bump*