I have a message system, where people insert their message to the database, and others pick up their messages by selecting messages that have their username as the reciever ID.
I have just intoduced bulk message sending - sending to multiple users. This requires many usernames being entered into the recievername column, seperated by a comma.
Is there a way to select messages if the column name contains their username, rather than is their username? Because there may be more than one name, I would like everyone that has their name in the column to be able to retrieve the message, even
if they arent the only reciever..
Just make sure that you don't get partial matches you don't intend.
Incidentally, the right way to do this in a relational database would have been to have one table for the message and another for the message recipients. That way, you would get to make use of indexes instead of having to scan thru the entire table.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
For example, if someone had a username of jon123, would the operator be able to distinguish between them and jon12345, or would it think they are the same? Ideally the operator would be used to find if the column contained ' jon123 ' with a space either
side, as I can create spaces between each username and the comma that seperated them.. would the LIKE operator do this?
A message could be sent to anything between 10 and 200 users.. This wouldn't work?
I didn't say your approach would not WORK. I said it would not SCALE.
Here is why:
At 1000 messages on file and 20 users, each user has to read and parse 1000 message address strings to find their 50 messages.
That's 20,000 data record reads for them to get their answers.
At 10,000 messages on file and 200 users, it will take 2,000,000 data record reads for each user to find their messages.
At 100,000 messages on file and 2000 users, it will take 200,000,000 data record reads for each user to find their messages.
As the number of messages and users increases, the load on the system will also increase. The system will get slower and slower until it finally fails.
This is because you are seaching thru the address and cannot use any database indexes to speed things up.
If your database tables had one table for the message and another for each message recipient, indexed by user, each user would only have to look at the records in the message_recipient table (plus a few index records behind the scenes as index overhead).
At small numbers of users and messages, your approach would be minutely faster - but no human would notice. At large numbers of messages and users, this approach would give good performance much longer.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
Marked as answer by jbear123 on Nov 29, 2007 09:09 AM
Sorry, but that WHERE clause is really muddled up.
I'm confused as to the sample column value, did the message get sent to the same person (bezlan) twice?
And you did not answer my question. :) What value are you passing in as the @receivername value?
The purpose of the LIKE operator is to allow for partial matches. If you insist that it is also = to a specific value, then it can't work as a partial match. That's because a partial match has to have wildcard characters in it.
Wildcard characters are % and _
% means any combination of values of any length. _ means any one character's worth of values.
So, like '%bezlan%' would find match any occurance of bezlan in the string.
If you are passing in the user's name you want to find messages for, what's the point of joining to Profile("UserName")? If Profile("UserName") knows the user that is logged in, what's the point of using @receivername? Seems to me that only one of those
values is needed.
Is Profile() a sql database function in your database? I don't recognize it. Or is it a C# or VB method? Because if it's a C# or VB function, it has no business in the middle of a SQL statement.
If that's correct, then
WHERE [receivername] LIKE '% ' + somevalue + ' %'
Replace somevalue with @receivername if you decide to use that as your parameter or Profile('UserName') if that is a sql database function.
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
Marked as answer by jbear123 on Nov 29, 2007 09:09 AM
jbear123
Member
558 Points
1256 Posts
Selecting data wheere values contain something
Nov 17, 2007 03:49 PM|LINK
Hi,
I have a message system, where people insert their message to the database, and others pick up their messages by selecting messages that have their username as the reciever ID.
I have just intoduced bulk message sending - sending to multiple users. This requires many usernames being entered into the recievername column, seperated by a comma.
Is there a way to select messages if the column name contains their username, rather than is their username? Because there may be more than one name, I would like everyone that has their name in the column to be able to retrieve the message, even if they arent the only reciever..
Please ask if more explanation is needed!
Thanks,
Jon
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 17, 2007 07:08 PM|LINK
use the LIKE operator instead of the = operator.
Just make sure that you don't get partial matches you don't intend.
Incidentally, the right way to do this in a relational database would have been to have one table for the message and another for the message recipients. That way, you would get to make use of indexes instead of having to scan thru the entire table.
jbear123
Member
558 Points
1256 Posts
Re: Selecting data wheere values contain something
Nov 17, 2007 10:47 PM|LINK
Hi, thanks for the reply.
Re: partial matches-
How would I protect against this?
For example, if someone had a username of jon123, would the operator be able to distinguish between them and jon12345, or would it think they are the same? Ideally the operator would be used to find if the column contained ' jon123 ' with a space either side, as I can create spaces between each username and the comma that seperated them.. would the LIKE operator do this?
Thanks for any advice!
Jon
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 17, 2007 11:49 PM|LINK
If you can "safely know" that the data will always be in this format: " blahblah@blah.com , blah@blah.com , ack@ack.biz ",
then a search for "% blah@blah.com %" will work.
Note the leading and trailing spaces. Otherwise, you would have to check for "blah@blah.com %" and "% blah@blah.com %" and "% blah@blah.com".
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 17, 2007 11:50 PM|LINK
How many messages and how many users are you talking about for this message system?
The method you are proposing will not scale up, so the numbers matter.
jbear123
Member
558 Points
1256 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 09:54 AM|LINK
A message could be sent to anything between 10 and 200 users.. This wouldn't work?
I have tried replacing = with LIKE and it doesn't seem to pick up the usernames anyway?
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 02:57 PM|LINK
Show me a sample string you are searching thru, and the like clause you are using to search thru it.
How many messages will be in the database?
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 03:10 PM|LINK
I didn't say your approach would not WORK. I said it would not SCALE.
Here is why:
At 1000 messages on file and 20 users, each user has to read and parse 1000 message address strings to find their 50 messages.
That's 20,000 data record reads for them to get their answers.
At 10,000 messages on file and 200 users, it will take 2,000,000 data record reads for each user to find their messages.
At 100,000 messages on file and 2000 users, it will take 200,000,000 data record reads for each user to find their messages.
As the number of messages and users increases, the load on the system will also increase. The system will get slower and slower until it finally fails.
This is because you are seaching thru the address and cannot use any database indexes to speed things up.
If your database tables had one table for the message and another for each message recipient, indexed by user, each user would only have to look at the records in the message_recipient table (plus a few index records behind the scenes as index overhead). At small numbers of users and messages, your approach would be minutely faster - but no human would notice. At large numbers of messages and users, this approach would give good performance much longer.
jbear123
Member
558 Points
1256 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 04:38 PM|LINK
Hi,
I see what you mean - thanks for the advice. I will keep it on hold until user numbers and messages reach high numbers.
Here is the select for the messages:
SELECT [message], [senddate], [subject], [messageid], [sendername] FROM [Messages] WHERE ([recievername] LIKE @recievername)
and @recievername = Profile("UserName")
UserName is just the current users username as on their profile.
An example recievername column value would be:
bezlan , bezlan
david wendel...
All-Star
15865 Points
2243 Posts
Re: Selecting data wheere values contain something
Nov 18, 2007 05:23 PM|LINK
Sorry, but that WHERE clause is really muddled up.
I'm confused as to the sample column value, did the message get sent to the same person (bezlan) twice?
And you did not answer my question. :) What value are you passing in as the @receivername value?
The purpose of the LIKE operator is to allow for partial matches. If you insist that it is also = to a specific value, then it can't work as a partial match. That's because a partial match has to have wildcard characters in it.
Wildcard characters are % and _
% means any combination of values of any length. _ means any one character's worth of values.
So, like '%bezlan%' would find match any occurance of bezlan in the string.
If you are passing in the user's name you want to find messages for, what's the point of joining to Profile("UserName")? If Profile("UserName") knows the user that is logged in, what's the point of using @receivername? Seems to me that only one of those values is needed.
Is Profile() a sql database function in your database? I don't recognize it. Or is it a C# or VB method? Because if it's a C# or VB function, it has no business in the middle of a SQL statement.
If that's correct, then
Replace somevalue with @receivername if you decide to use that as your parameter or Profile('UserName') if that is a sql database function.