Click here to Skip to main content
15,890,043 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a problem with my query data ,


it become very slower when i try to access at pag 1200


i also use case when in my query
SQL
Select   Reg_ID,Reg_Service_ID,Reg_Patient_ID,Patient_Name,Doctor_ID,Doctor_Name,
		CASE WHEN EncounterSatuSehatID <>'-' and ConditionID <>'-'
		THEN 'Selesai'
		ELSE
		'Tidak Selesai'
		END AS 'Use Case 1' ,
		EncounterSatuSehatID,
		ConditionID,
		CASE WHEN CompositionID <>'-' and SatuSehatProcedureID <>'-' and ObservationID <>'-'
		THEN 'Selesai'
		ELSE
	    'Tidak Selesai'
		END AS 'Use Case 2',
		Status,
		Poly_Name,
		Reg_OpenDate
		from (
		Select A.Reg_ID,Reg_Service_ID,Patient_Name,Reg_Patient_ID,Doctor_ID,Doctor_Name,Reg_OpenDate,
			CASE WHEN B.EncounterSatuSehatID IS NULL
			THEN '-'
			ELSE 
			B.EncounterSatuSehatID
			END
			EncounterSatuSehatID,
			CASE WHEN C.ConditionID is null
			THEN '-'
			ELSE 
			C.ConditionID END ConditionID
			, CASE WHEN D.CompositionID is null
			THEN '-'
			ELSE 
			D.CompositionID 
			END  CompositionID ,CASE WHEN 
			E.SatuSehatProcedureID is null
			THEN '-'
			ELSE E.SatuSehatProcedureID END
			SatuSehatProcedureID ,
			CASE WHEN 	F.ID is null 
			THEN '-'  
			ELSE 
			F.ID
			END ObservationID,
		CASE WHEN Reg_Active = 1 and Reg_IsCancel =0
			THEN 'Aktif'
		 WHEN ((Reg_Active =1 and Reg_IsCancel =1) OR (Reg_Active=0 and Reg_IsCancel=1))
			THEN 'Batal' 
		ELSE
			 'Selesai'
		END 'Status',
		Poly_Name
		from Table1 A
		left join Table2 B on A.Reg_ID=B.Reg_ID
		left join Table3 C on A.Reg_ID=C.REG_ID
		left join Table4 D on A.Reg_ID=D.Reg_ID
		left join Table5 E on A.Reg_ID=E.RegID
		left join Table6 F on A.Reg_ID=F.RegID
		)A
		order by Reg_OpenDate desc
		 OFFSET (122614-1) *  5 ROWS 
		 		FETCH FIRST 5 ROWS ONLY

that was my query

So i want to access page 122614

and it is very slow

What I have tried:

Is anyone can help me solve this?
Posted
Updated 18-Apr-24 16:59pm
v2
Comments
Richard Deeming 19-Apr-24 3:43am    
"page 122614"

Over one hundred thousand pages?!

How many users do you think are going to sit and scan through that many pages of data to find the row(s) they're looking for?

There must be a better way to present the data to the user. Give them tools to search or summarize the data, rather than trying to speed up loading the data for a process that will take them days/weeks/months to find what they're looking for.

1 solution

There's a wonderful tool in SQL Server that allows you to view the e execution plan for a query. With this, you can analyse to see that you are doing things like indexed queries. We can't help you because we don't have your database schema, and we don't have your data.

It's worth noting that offset becomes more inefficient the bigger the dataset and search it is performing. If you have a numeric index, it is generally more efficient to search using a greater than query.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900