I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.
I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same priviledge to send and view
In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldnt also keep messages for more than two days,
meaning every sent message should be deleted after two days.
Below are the sample tables that represents the logics above
Now, if I am to implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
Please is there a way to handle this, scalability and performance wise?
Don't you think this would give my queries more load using four tables and doing many JOINs. Cant we have a simple way to solve this, which will increase speed. That even means, at every insert into the message table, there should be the same insert into the unread table, when all members are online
Look into partitioning your table if you need more speed. If that's not enough, then you'd more be thinking about client-side processing. Do you need to "know" on the server whether a user read one of the messages?
If not, then you might be helped quickly with a XMPP-server. The local client could then keep track of the messages it receives, and which ones are read.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
Delete the recipient-table, and add a table like below;
Add a record to that table when the user read a message set 'IsDeleted' to false.
When user delete a message, set IsDeleted to true.
When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the MessageStatusPerUser table and Messages marked 'IsDeleted' as false
messages not appearing in MessageStatusPerUser table are unread.
messages appearing in MessageStatusPerUser table and flagged IsDeleted as false are read.
messages appearing in MessageStatusPerUser table and flagged IsDeleted as true are deleted messages.
I have a package to import data from Excel to SQL tables. I have created and depolyed the package on production server say "ProdServer". Also I have created job on the same server and it runinng the package successfully. Even I run it manually it is running properly. But problem here is when I am connecting to SQL installed on the production server from my local machine using windows authentication and trying to run the package manually it gives below error.
Executed as user: SERVERNAME\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:41:10 AM Error: 2014-07-15 07:41:17.24 Code: 0xC0202009 Source: ImportMasterData Connection manager "PracticeMapping" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. End Error Error: 2014-07-15 07:41:17.27 Code: 0xC020801C Source: Load Practice Get Practice  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "PracticeMapping" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-07-15 07:41:17.29 Code: 0xC004701A Source: Load Practice SSIS.Pipeline Description: component "Get Practice" (69) failed the pre-execute phase and returned error code 0xC020801C. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:41:10 AM Finished: 7:41:17 AM Elapsed: 6.692 seconds. The package execution failed. The step failed
So in short when I run the packgae direclty from production server it works fine but from local machine or any other machine by connecting to the SSMS and running manually, it get failed. Please help me guys on this issue. Its very very urgent.
Need to write extract file providing 2 employee emergency contacts with one phone number each in one record. Going to order by emergency contact name when selecting which contact and sort by phone type when selecting phone. I'm at a loss at how to do this and would appreciate any help
WITH cteOrderedContacts As
-- Generate a row number for each employee, ordered by contact name:
ROW_NUMBER() OVER (PARTITIONBY EmpNum ORDERBY EmpEmergencyContact) As RN
C1.EmpEmergencyContact As EmergencyContact1,
C1.Relation As Relation1,
C1.PhType As PhType1,
C1.Phone As Phone1,
C2.EmpEmergencyContact As EmergencyContact2,
C2.Relation As Relation2,
C2.PhType As PhType2,
C2.Phone As Phone2
cteOrderedContacts As C1
LEFTJOIN cteOrderedContacts As C2
ON C2.EmpNum = C1.EmpNum -- Record must be for the same employee;And C2.RN = C1.RN + 1-- Get the next record in the sequence;WHERE-- Only return odd-numbered rows, as the even-numbered rows-- will be included in the previous row:
(C1.RN & 1) = 1ORDERBY
I should hope that you have translation tables for Relation and PhType -- you can add some sort of prioritization there to better control ordering.
ID Abbr Name Priority
1 SP Spouse 1
2 FR Friend 3
3 CH Child 2
ID Abbr Name Priority
1 Cell Mobile 1
2 Work Office 3
3 Home Home 2
Such translation table also allow you to use referential integrity and can reduce the amount of space used by the database (not so much a concern these days).
Also, it allows your application code to use an enumeration rather than hard-coded string values.
Then follow Richard Deeming's advice.
You'll never get very far if all you do is follow instructions.
I trying to design a query that returns products ordered based on a self-reference foreign key (just like a linked list). I tried to make a recursive query (CTE), but i couldn't return the records as the table below.
I'm trying to understand this function.
Someone wrote this piece of code and I would like to use to do something simular.
On a daily basis we read .txt files into our SQL database through SSIS.
The data that's going through is growing fast and takes a long time to process. (for example a year of data is read every day into the db)
I want to make a dayset so that we only read one single day into the DB on top of the existing data and not the whole year.
Someone wrote this peace of code:
Select * , ROW_NUMBER() OVER (PARTITIONBY PK_Assignment ORDERBYDATE, TIME) as LineNbr
I was wondering what the Partition By statement means.
My guess was that you have to place the fields there that makes the record unique.
Can someone help me out with this because I read so many things on the internet and still I don't understand.
Also I don't have much experience with SQL, just the basics.