How to Tune Slow SQL Strings?

You should check the indexes.

On fields where there are a lot of different values and which are needed to search for should be indexed.

Fields on which you do not search or which have only very few different values should not be indexed.

In some cases a compound index may be necessary, check in such case also the order of the fields in the index.

Hopes this helps

1) Tables should have primary key (clustered better)
2) See the relantionships between tables. Foreign keys should be created.
3) Only use distinct keyword if you really need it.
4) Prefix all the columns with correct table name (orgname, username, firstname, surname don't have prefix)
5) If still slow, then create indexes to improve your query execution time (at least the tblvisit.entrytime looks like will need one. tbluser.username too)

I could see that you repeated one filter: (tbluser.active = 1)
It isn't necessary to write it twice! :)

Please try

select username, userid, firstname, surname, email,
(select top 1 entrytime from tblVisit where UserID = U.UserID) as Entrytime    
from tbluser U
where companyid =  8 AND active = 1
AND NOT EXISTS (
      select 1 from tblvisit
      where UserID = U.UserID
      and tblvisit.entrytime between '8/Feb/2005' and '15/Feb/2005')
order by 1

1. You don't need the distinct
2. Avoid Not IN / IN Lists  and Use NOT EXISTS / EXISTS instead
3. look at the query plans generated
4. use profiler/ index analyser to tune problem queries.  

select U.username, U.userid, U.firstname, U.surname, U.email
, max(entrytime) as Entrytime    
from tbluser as U
Inner Join JOIN tblvisit as V
 ON U.UserID=V.UserID
Inner Join tblOrganisation as O
 on companyid = O.id
where U.companyid =  8 AND U.active = 1
AND Not Exists  
(select userid from tblvisit as V1
      where V1.entrytime between '8/Feb/2005' and '15/Feb/2005'
          and V1.UserName=U.userid)
  and U.active = 1 and O.active = 1
 group by orgname, username, U.userid, firstname, surname, U.email
order by  username



I don't believe there is a problem with using Between since SQL can easily convert that into a range select....
you should avoid joining tables via functions where possibvle...

hth

A quick suggestion to try... and the syntax is not tested.  I believe the NOT IN (SELECT ... ) is executed every time for each row in the top query.  Use a table variable.  Also, try to not to us IN use EXISTS and NOT EXISTS.  I have more but I'm not sure what docs came from other pay sites.



declare @tableVar table (tUserid      [int])
Insert Into @tablevar
(select userid from tblvisit where tblvisit.entrytime between '8/Feb/2005' and '15/Feb/2005')



select distinct tbluser.username, tbluser.userid, tbluser.firstname, tbluser.surname, tbluser.email, max(entrytime) as Entrytime    from tbluser JOIN tblvisit ON tblUser.UserID=tblvisit.UserID JOIN tblOrganisation on companyid = tblorganisation.id where tbluser.companyid =  8 AND tbluser.active = 1 AND tbluser.userid NOT EXISTS

(SELECT tUserid FROM @tableVar)

  and tbluser.active = 1 and tblorganisation.active = 1  group by orgname, username, tbluser.userid, firstname, surname, tbluser.email order by  username