Database

Member 12069869 wrote:

Can MySql handle such large data?

Member 12069869 wrote:

each row have about 20 fields/colums

Public Shared Function load_subscribers( _
        ByVal pPageIndex As Integer,
        ByVal pPageSize As Integer,
        ByRef pResults As List(Of ws_subscribers_list)) As Integer

        Dim pValue As Integer = 0
        Dim context As New hx5Context()

        If (pPageIndex = 1) Then

            pResults = _
            (
                From cs In context.crm_Subscribers
                Order By cs.Name
                Take (pPageSize)
                Select New ws_subscribers_list With
                {
                    .SubscriberID = cs.SubscriberID,
                    .CustomerID = cs.CustomerID,
                    .Name = cs.Name,
                    .EmailAddress = cs.EmailAddress,
                    .Source = cs.Source,
                    .Subscribe = cs.Subscribe,
                    .DateCreated = cs.DateCreated,
                    .Status = cs.Status
                }
            ).ToList()

        Else

            pResults = _
            (
                From cs In context.crm_Subscribers
                Order By cs.Name
                Skip ((pPageIndex - 1) * pPageSize)
                Take (pPageSize)
                Select New ws_subscribers_list With
                {
                    .SubscriberID = cs.SubscriberID,
                    .CustomerID = cs.CustomerID,
                    .Name = cs.Name,
                    .EmailAddress = cs.EmailAddress,
                    .Source = cs.Source,
                    .Subscribe = cs.Subscribe,
                    .DateCreated = cs.DateCreated,
                    .Status = cs.Status
                }
            ).ToList()

        End If

        pValue = pResults.Count()

        Return pValue

    End Function

Afzaal Ahmad Zeeshan wrote:

Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread.

1. Thread 1 locks X;
2. Thread 2 locks Y;
3. Thread 1 tries to acquire a lock on Y - waits for thread 2 to release;
4. Thread 2 tries to acquire a lock on X - waits for thread 1 to release;

Aliyu Usman wrote:

please helo me with this issue

SELECT
ADMISSION_NO,
FULL_NAME,
EXAMS,

CASE 

WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'

WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'

WHEN EXAMS BETWEEN 55 AND 59 THEN 'C'
WHEN EXAMS BETWEEN 50 AND 54 THEN 'CD'

WHEN EXAMS BETWEEN 45 AND 49 THEN 'D'
WHEN EXAMS BETWEEN 40 AND 44 THEN 'E'

WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'

END AS GP

FROM  R_Upload_2

Aliyu Usman wrote:

WHEN EXAMS BETWEEN 40 AND 0 THEN 'F'

BETWEEN 0 AND 40

CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT)
INSERT INTO tblTest VALUES(1,'AAA',22),(1,'AAA',22),(2,'BBB',33),(2,'BBB',33),(2,'BBB',33),(3,'CCC',44),(4,'DDD',55)

--create an alternate table to store the duplicate records
CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT)

-- insert those duplicate records to this new table
INSERT INTO tblTestDuplicate
SELECT *
FROM tblTest
GROUP BY ID,NAME,AGE
HAVING COUNT(ID) > 1

--delete the duplicate records from the original table
DELETE FROM
tblTest 
WHERE  ID IN (SELECT ID FROM tblTestDuplicate)

--insert all the records into the original table
INSERT INTO tblTest
SELECT * 
FROM tblTestDuplicate

--Project the new records
SELECT *
FROM tblTest
ORDER BY ID


--clean up
DROP TABLE tblTestDuplicate
DROP TABLE tblTest

ID	NAME	AGE
1	AAA	    22
2	BBB	    33
3	CCC	    44
4	DDD	    55

total records belonging to each division

;WITH CTE AS(
SELECT  
	1 AS EMPNUM
	,'File1'AS FILENAME
	,'Name1' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	2 AS EMPNUM
	,'File2'AS FILENAME
	,'Name2' AS EMPNAME
	,'Division2' AS Division UNION ALL
SELECT  
	3 AS EMPNUM
	,'File12'AS FILENAME
	,'Name12' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	4 AS EMPNUM
	,'File14'AS FILENAME
	,'Name14' AS EMPNAME
	,'Division1' AS Division UNION ALL
SELECT  
	5 AS EMPNUM
	,'File21'AS FILENAME
	,'Name21' AS EMPNAME
	,'Division2' AS Division UNION ALL
SELECT  
	6 AS EMPNUM
	,'File3'AS FILENAME
	,'Name3' AS EMPNAME
	,'Division3' AS Division) -- table create and data insertion part

-- Query starts
SELECT
	X.*	
FROM(
	SELECT 
		EMPNUM = ISNULL(CAST(t.EMPNUM AS VARCHAR(10)), ' ')	
		,FILENAME = ISNULL(CAST(t.FILENAME AS VARCHAR(10)), ' ')		
		,EMPNAME = ISNULL(CAST(t.EMPNAME AS VARCHAR(10)), ' ')			
		,Division = CASE 
						WHEN t.Division IS NULL AND t.EMPNUM IS NULL THEN 'Total :-'
						WHEN t.EMPNUM IS NULL THEN 'Total ' + t.Division + ' Count :-'
						ELSE t.Division END	
		,DivisionCount = COUNT(t.Division)
	FROM   CTE t	
	GROUP BY ROLLUP(t.Division,t.EMPNUM,t.EMPNAME,t.FILENAME))X
WHERE (LEN(X.FILENAME) > 0 AND LEN(X.EMPNAME) > 0)
OR	(LEN(X.FILENAME) = 0 AND LEN(X.EMPNAME) = 0 AND LEN(X.EMPNUM) = 0)

EMPNUM	FILENAME	EMPNAME	Division         	     DivisionCount
1	       File1	Name1	Division1	                1
3	       File12	Name12	Division1	                1
4	       File14	Name14	Division1	                 1
 	 	 	                Total Division1 Count :-	3
2	       File2	Name2	Division2	                 1
5	       File21	Name21	Division2	                 1
 	 	 	                 Total Division2 Count :-	2
6	       File3	Name3	Division3	                1
 	 	 	                 Total Division3 Count :-	1
 	 	 	                 Total :-	                 6