There is a business, three layer to iterate over the query, found very time consuming

The first layer, iterate through each time the user login

The second floor, from each login, traverse the page to browse

The third layer, from the inside of the each page, the page of every details.

Feel very time consuming, so each elder, do you have any better idea to realize the above I need

Can put the content of the second layer, in particular, in the first layer as a field in storage, to reduce the circulation obtain database

CodePudding user response:

I don't want to dig into your logic, just want to ask, what purpose is business background demand?
Query the user action log, what are the action records?

If so, don't say in the query, not a condition, if there are millions of data, to check with traversal cycle, it't ranging up to a dead end?

CodePudding user response:

Such as query customers a month to browse footprint
I points and two tables to record a table record (LoginData) user login, and then another table (ActionData) record all browse the footprint, and associated login ID, meaning a login will correspond to track multiple actions. Structure about like this:

CodePudding user response:

Demo:

 
The create table LoginData (
LoginID int, 
LoginDt datetime 
) 

The create table ActionData (
ActID int, 
LoginID int, 
Itemno nvarchar (30), - module project [' building dynamic] [' households form details'] etc. 
Limno nvarchar (60), specific details 
 -BrowDt time - browse the length of the 
) 

Declare @ LoginID int=XXXX - login ID 
@ BeginDt nvarchar (10)='XXXXXXX, login - starting date 
='XXXXXXX' @ EndDt nvarchar (10) -- the deadline 

Select a... , 
B... 

The from LoginData a 
Left the join ActionData b on a. oginID=b.L oginID 
Where LoginID=@ LoginID 
And the convert (date, LoginDt) between @ BeginDt and @ EndDt 

Is the result?