Click here to Skip to main content
15,880,891 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Below is my query:-

This query is written for those users who did not log-in to the system between 1st July to 31 July.
However when we run the query in query analyzer then it's taking more than 2 minutes. But in application side giving error as 'Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding'.

Below query takes start date as 1st July 2022 and get all the users and add those users into temp table called '@TABLE_TEMP' and increases to next date.

Again while loop runs and fetch users for 2nd July and so on until it reaches to 31st July.

Can anyone help on this to optimize the query using CTE or any other mechanism? How can we avoid While loop for better performance.


DECLARE @TABLE_TEMP table
    (
        Row int IDENTITY(1,1),
        [UserId] int,
        [UserName] nvarchar(100),       
        [StartDate] nvarchar(20),
        [FirstLogin] nvarchar(20),
        [LastLogout] nvarchar(20)       
    )

DECLARE @START_DATE datetime = '2022-07-01';
DECLARE @END_DATE   datetime = '2022-07-31';
DECLARE @USER_ID nvarchar(max) = '1,2,3,4,5,6,7,8,9';
DECLARE @QUERY nvarchar(max) = '';

WHILE(@START_DATE < @END_DATE OR @START_DATE = @END_DATE)
                BEGIN               
                    SET @QUERY ='SELECT 
                                    s.userid as [UserId],
                                    s.username  as [UserName],                                  
                                    '''+ CAST(@START_DATE as nvarchar)  +''' as [StartDate],
                                    MAX(h.START_TIME) as [FirstLogin],
                                    MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) as [LastLogout]                    
                                FROM USER s 
                                LEFT JOIN USER_LOGIN_HISTORY h ON h.userid = s.userid                                                       
                                LEFT JOIN TEMP_USER_INACTIVATION TUI ON TUI.userid = s.userid AND ('''+ CAST(@START_DATE as nvarchar)  +''' BETWEEN ACTIVATED_DATE AND DEACTIVATD_DATE)
                                WHERE s.userid in ('+ @USER_ID +') 
                                AND h.userid  NOT IN (SELECT userid FROM USER_LOGIN_HISTORY WHERE CAST(START_TIME AS DATE)  = '''+ CONVERT(nvarchar,(CAST(@START_DATE AS DATE))) +''')                                                                           
                                AND ACTIVATED_DATE IS NOT NULL 
                                GROUP BY s.userid,h.userid,s.username,s.last_seen_time
                                HAVING CAST(MAX(ISNULL(h.END_TIME, s.LAST_SEEN_TIME)) AS DATE) <>  '''+ CONVERT(nvarchar,(CAST(@START_DATE AS DATE)))  + '''
                                order by [User Name]'

                    INSERT INTO @TABLE_TEMP
                    EXEC(@QUERY)   
                    SET @START_DATE =DATEADD(DD,1,@START_DATE)          
                END


What I have tried:

I have tried with CTE. But no luck.. Even tried to add index on table variable [@TABLE_TEMP].
Posted
Updated 3-Aug-22 7:30am
Comments
[no name] 3-Aug-22 11:52am    
"Query literals"? While loops? "Start" by figuring out how to eliminate those.
Member 15627495 3-Aug-22 12:47pm    
you have made the choice to use a so heavy Date Formating : ex : 2022-07-01... It's a string type.

by unix time you'll have a 'big int' type to process,
you'll --> earn lot of time <-- with all your Date fields in unix time.

It's a db design error you're facing.

in sql use : try 'create view' before going on a request,
'VIEWS' are lightweight instead of classic 'select'.
It will be easier to proceed 'views' after.

to avoid 'execution time out', and because the lots of datas involved,
use 'buffering'/ more temp tables and explode your single big query.
the db engine will thanks you.
CHill60 4-Aug-22 3:48am    
Quote: "you have made the choice to use a so heavy Date Formating : ex : 2022-07-01... It's a string type."
No, it's not. It's a date, actually a datetime as is clearly indicated
DECLARE @START_DATE datetime = '2022-07-01';
It is absolutely good practice to use the correct data types.
I also disagree with your comments about Views - they are no more lightweight than a classic select if not written properly. "Views are generally used to focus, simplify, and customize the perception each user has of the database" - Views - SQL Server | Microsoft Docs[^]
Quote: "It's a db design error you're facing." - in what way? Tables look to be reasonably normalized from what we can see in the question.
Member 15627495 4-Aug-22 4:01am    
about the Date :
it's really a string but formatted as 'YYYY_MM_DD'
I go on 'it's a string', because when comparing, the engine have to care with very big datas, conversions , casts.
the unix time is 'number' and really gain at time speed processing. ( adding , comparing , fetching and so and so )

about 'views' :
you said it, views 'simplifies' what is a query return.
How a computer simplifies datas and containers ??? by lightweight , less structure , less 'caching' , less handles ressources ... so gain for speed with views.
I didn't advise about views without knowing.

----
about the db design error :
I was closing the note about 'unix time' , and so columns type choice for this db.
CHill60 4-Aug-22 4:15am    
A Date in SQL is not a string. It is a Date. It might look like a string when being assigned like that, but it is still a Date, no casts or conversions are required at all. It is actually stored as a 3-byte integer. You will gain no speed whatsoever by using "unix time"

Views are just that - views of the underlying data tables. They are essentially just queries that create virtual tables from the persisted data below. They do not necessarily simplify the data returned - particularly if they use joins to amalgamate data. They can make queries faster to write but only indexed views can really aid performance and you failed to mention that in your inaccurate comments

1 solution

The logic is quite obvious (in pseudo-code):
SQL
--get all users who did not log-in into system in specific period of time
SELECT ...
FROM UsersTable
EXCEPT or NOT IN
(
  SELECT DISTINCT users 
  FROM LogInTable
  WHERE LogInDate BETWEEN @StartDate AND @EndDate
)


But if you want ot get a LogInDate too:
SQL
SELECT U.UserId, L.LogInDate
FROM UsersTable AS U
LEFT JOIN 
(
  SELECT UserId, LogInDate
  FROM LogInTable 
  WHERE LogInDate BETWEEN @StartDate AND @EndDate
) AS L ON L.UserId = U.UserId


When user did not log-in into system at specific date, you'll get NULL in field L.LogInDate.

You haven't provide sample data, so, i can't help you more...

I hope, you got it now.

Good luck!
 
Share this answer
 
v4

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