Solved

Tuning sql question

Posted on 2015-02-08
30
95 Views
Last Modified: 2015-03-03
Hi Experts,

I have the following Sql statement that takes forever to execute.
 Select * from view_frmOrientationSchedule where
exists
(Select 1 from dbo.NotesTbl 
 INNER JOIN dbo.View_EmployeesLastNoteID ON dbo.NotesTbl.ID = dbo.View_EmployeesLastNoteID.LastID
 Where  NotesTbl.EmployeeID = view_frmOrientationSchedule.EmployeeID and Date >= '02/01/15')
 

Open in new window

I guess the reason is because the Notestbl is very large (over 1.5 Mil rec.).
What are my options?

The intention of the query is to select records of all Employees who's last note were entered in a specific date range.

P.S. I know if would be possible to join the notes table to the main table (view_frmOrientationSchedule) it would probably solve the issue, however this option is currently not relevant.
0
Comment
Question by:bfuchs
  • 12
  • 11
  • 5
  • +2
30 Comments
 
LVL 10

Expert Comment

by:Jeffrey Dake
ID: 40597691
Do you have any indexes on the date column. Since you are selecting recent data, having an index on date could possibly help greatly
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40597698
You are joining views together,  this can be quite inefficient depending on the query used by each view. I suspect the one proving the "latest" noteid may be a performance problem.

Have you looked at the query for each view?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40597704
you could provide an explain plan for the query?  (A .sqlplan file not a screenshot please)
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40597817
I don't think you need the subselect:
SELECT view_frmOrientationSchedule.* 
FROM view_frmOrientationSchedule 
INNER JOIN dbo.NotesTbl ON NotesTbl.EmployeeID = view_frmOrientationSchedule.EmployeeID
INNER JOIN dbo.View_EmployeesLastNoteID ON dbo.NotesTbl.ID = dbo.View_EmployeesLastNoteID.LastID
WHERE Date >= '02/01/15'

Open in new window

Also, as requested above, provide the query plan.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40598470
I'm guessing the "Date" column comes from dbo.NotesTbl.  [Please, when you use multiple tables in a single SELECT, use a table alias on every column.]

If so, you should strongly consider clustering the NotesTbl on Date; you can keep a nonclustered/pk index on ID if you need it.

Maybe something like below, to limit the number of rows to be considered as quickly as possible.  Would need to see the table, view and index definitions to be more sure, and the query plan as well, as others have noted.


SELECT fos.*
FROM (
    SELECT EmployeeID, Date, ID
    FROM dbo.NotesTbl
    WHERE
        Date >= '20150201'
) AS nt
INNER JOIN dbo.View_EmployeesLastNoteID elni ON
    elni.LastID = nt.ID
    --AND elni.EmployeeID = nt.EmployeeID --redundant, but add if it helps performance
INNER JOIN view_frmOrientationSchedule fos ON
    fos.EmployeeID = nt.EmployeeID
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40599266
Ok Experts,

I finnaly got hold of the sqlplan (attached copy).
Let me know if that helps.

Thanks
Copy-of-OrientSchedule.sqlplan
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40599326
I think I hit this one right; it's a pretty common setup.

Try clustering the tblNotes table by Date, then using the query above.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40599402
@Jeffrey Dake,
Actually I realized there is no index on the Date field in Notestbl, guess will try that first.

@PortletPaul,
below is the SQL providing the latest NoteID, are they any improvements to be made?
SELECT     EmployeeID, MAX(ID) AS LastID
FROM         dbo.NotesTbl
GROUP BY EmployeeID

Open in new window


@Vitor Montalvão,
Yours also takes very long to execute.

@ScottPletcher,
Your version (when including the commented block) works.
Re clustered index, we have currently an clustered index on the EmployeeID column, as most of the time its sorted and filtered by that, would you advise to change to the date column? what are the ramifications of such a move?

@Scott, Vitor,
 The reason I mentioned in the post that joining is not relevant is as follows:
This is to be used as a part of users selection to filter, while in the same time users can select a variety of other options to be filtered, and changing the record source each time depending on option selected to filter would really complicate the programming.
Besides in many cases, the original form is based on a table or view that is editable, and if I am forced to change the recordsource to a read only recordset would pose an issue..
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599501
try using ROW_NUMBER() OVER() instead of View_EmployeesLastNoteID
SELECT
      FOS.*
FROM (
      SELECT
            EmployeeID , [Date] , ID
      FROM (
            SELECT
                  EmployeeID , [Date] , ID
                , ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY [Date] DESC) AS RN
            FROM dbo.NotesTbl
            WHERE Date >= '20150201'
            ) X
      WHERE RN = 1
      ) AS NT
INNER JOIN view_frmOrientationSchedule FOS ON FOS.EmployeeID = NT.EmployeeID

Open in new window


Note I have ordered by [Date] DESC instead of by the ID assuming you are looking for the latest date.
Changing that order to [ID] DESC might be faster depending on what you do with the indexes.

There isn't anything "wrong" with the View_EmployeesLastNoteID, but if it can be avoided then avoid it. Using Scott's suggestion to reduce the volume of notes records being assessed in the query I have simply reduced that further by using row_number().

{+EDIT}
A much simpler approach might be to simply get the employeeid like this:
SELECT
      FOS.*
FROM (
            SELECT DISTINCT
                  EmployeeID
            FROM dbo.NotesTbl
            WHERE Date >= '20150201'
      ) AS NT
INNER JOIN view_frmOrientationSchedule FOS ON FOS.EmployeeID = NT.EmployeeID

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40599521
>> @ScottPletcher,
 Your version (when including the commented block) works.
 Re clustered index, we have currently an clustered index on the EmployeeID column, as most of the time its sorted and filtered by that, would you advise to change to the date column? what are the ramifications of such a move?
<<

Interesting.  Typically people will search for some limited period of time as well.

If you mostly sort / group on EmployeeID, then even with the increased fragmentation of EmployeeID, you might still be better off clustering on it.

It would be best if we could double-check by looking at the actual SQL stats for the table.  If you want to do that, please run the code below and post the results:


USE [<your_db_name_here>]

SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = 'NotesTbl' --'%'=all tables.
--SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        dps.row_count,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        user_seeks, user_scans, ca1.max_days_active, unique_compiles,
        last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,
        system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID() --only current db
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

PRINT 'Midpoint @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    CASE WHEN i.name LIKE ca2.table_name + '%'
         THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1, 200)
         ELSE i.name END AS index_name,
    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
    ca2.table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date
        ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
    )
    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    db_name, table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40599606
@PortletPaul,
Tested yours and they work, however I'm wondering how can I apply these while preserving the beginning of my SQL statement?
Select * from view_frmOrientationSchedule where...

Open in new window


@ScottPletcher,
Attached results.
notestbl2.csv
notestbl1.csv
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 200 total points
ID: 40599627
You're right, based on the stats, your current clustered index is quite correct.

But the stats suggest adding one nonclustered index:

CREATE NONCLUSTERED INDEX <index_name_goes_here>
ON NotesTbl ( [Date], [ID] )
INCLUDE ( [EmployeeID], [Initial] )
WITH ( FILLFACTOR = 99 )
ON [PRIMARY]
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599660
>>" I'm wondering how can I apply these while preserving the beginning of my SQL statement?"

What is magical about the beginning of that query that needs retention? and:

You should NOT USE "select *" in a production query (we use it just as a form of shorthand expecting you to fill out the details)

So, with respect, I cannot see the point.

{+edit} does this help?
SELECT
      view_frmOrientationSchedule.*
FROM view_frmOrientationSchedule
INNER JOIN(
            SELECT DISTINCT
                  EmployeeID
            FROM dbo.NotesTbl
            WHERE Date >= '20150201'
          ) AS NT ON view_frmOrientationSchedule.EmployeeID = NT.EmployeeID

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40599953
@ScottPletcher,
Not sure I understand what is the intended of this index, which fields will be included as it listed 4 fields? and this would help in this case too, or I should regardless add a non clustered index on the Date field?

@PortletPaul,
You should NOT USE "select *" in a production query
I thought that's only true when selecting  from a table, while if using a view tailored to your current needs, this should not be an issue.

The reason I want to retain the original SQL and only add filters to the where clause is as follows:
We have continuous forms that have dual purposes, they are mainly used as reports, and can also be used for add/edit data.
The top section is reserved for filtering options, and users have a variety of options to select from.
Now in this case, user can select employees that have last notes entered in a certain date range, as in this example, but they are not limited to that, they can also select for example employees that are affiliated with certain facilities (a one to many relationship to EmployeesFacilitiesTbl), and they can select employees which have experience in certain fields (again one to many relationship) and so on.., and they can combine all those selections at once.
Taking this into account, you can understand why its easier for me if all filters are build as part of the where clause, and not changing the original source.

below is an extraction of the code used to build the filter.
    If Not IsNull(frm.EmployeesLanguage) Then
        s = s & " AND " & sTbl & "EmployeeID IN (SELECT EmployeeID FROM EmployeesLanguages WHERE Language = '" & frm.EmployeesLanguage & "')"
    End If
    
    If IsNull(frm.ComboFacility) And Not IsNull(frm.ComboOrFacility) Then
        frm.ComboFacility = frm.ComboOrFacility
        frm.ComboOrFacility = ""
    End If
    If frm.OptionAllFacilities = True Then
        If Not IsNull(frm.ComboFacility) Then
            s = s & " And " & sTbl & "EmployeeID in (Select EmployeeID from view_EmployeeFacilityMainID"
            s = s & " Where MainFacilityID = " & frm.ComboFacility.Column(2) & ")"
        End If
    Else
        If Not IsNull(frm.ComboOrFacility) Then
            s = s & " AND " & sTbl & "EmployeeID IN (SELECT EmployeeID FROM EmployeesFacilitiestbl WHERE FacilitieID in (" & frm.ComboFacility & "," & frm.ComboOrFacility & "))"
        ElseIf Not IsNull(frm.ComboFacility) Then
            s = s & " AND " & sTbl & "EmployeeID IN (SELECT EmployeeID FROM EmployeesFacilitiestbl WHERE FacilitieID = " & frm.ComboFacility & ")"
        End If
    End If
    
    If Not IsNull(frm.LastNoteFrom) Or Not IsNull(frm.LastNoteTo) Or Not IsNull(frm.LastNoteInitial) Then
        If Not IsNull(frm.LastNoteFrom) Then s2 = s2 & " And Date >= '" & frm.LastNoteFrom & "'"
        If Not IsNull(frm.LastNoteTo) Then s2 = s2 & " And Date <= '" & frm.LastNoteTo & "'"
        If Not IsNull(frm.LastNoteInitial) Then s2 = s2 & " And Initial = '" & frm.LastNoteInitial & "'"
        s = s & " And " & sTbl & "EmployeeID in (Select NotesTbl.EmployeeID from dbo.NotesTbl "
        s = s & " INNER JOIN dbo.View_EmployeesLastNoteID ON dbo.NotesTbl.ID = dbo.View_EmployeesLastNoteID.LastID"
        s = s & " Where " & Mid(s2, 5) & ")"
    End If
    
        
    If Len(s) > 0 Then
        GetSqlFromEmpMoreFilter = s
    End If

Open in new window


Another point is that fact the form (which this filter applies to) might be used for data entry, and changing the source would most likely make it read only.

Let me know if there is a way to accommodate your version with this route?

I guess the part you are replacing View_EmployeesLastNoteID with something more efficient, can definitely be applied here in some way.

(Attached a pic of what the screen to filter looks like).
untitled.bmp
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599970
>>"only true when selecting  from a table"
I disagee, table or view, you should specify the fields.

So the reason you wish to retain the original query is to enable edits (that's the "magic" I didn't understand)

You can continue to use EXISTS but it might not be quite as efficient. e.g.
SELECT
      view_frmOrientationSchedule.*
FROM view_frmOrientationSchedule
WHERE EXISTS (
            SELECT null
            FROM dbo.NotesTbl as nt
            WHERE Date >= '20150201'
            AND view_frmOrientationSchedule.EmployeeID = NT.EmployeeID
          )
;

Open in new window


I assume this will help you keep the query editable.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:bfuchs
ID: 40599986
@PortletPaul,

In case both include the same columns, Select * and Select field a, b and c, what is the reason not to use Select * ?

Your latest SQL did not look for the latest note, its simply looking for records in the notes table.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40600005
@PortletPau
And what about the other point, re the programming issue, do you have an easy way of accomplishing it?
Here is a typical scenario where user selects multiple choices, how would I program that in your version?
 Select * from view_frmOrientationSchedule where view_frmOrientationSchedule.EmployeeID IN (SELECT ID FROM Employeestbl WHERE  State in ('NY')) AND view_frmOrientationSchedule.EmployeeID IN (SELECT EmployeeID FROM EmployeesLanguages WHERE Language = 'Spanish') AND view_frmOrientationSchedule.EmployeeID IN (SELECT EmployeeID FROM EmployeesFacilitiestbl WHERE FacilitieID = 107) And view_frmOrientationSchedule.EmployeeID in (Select NotesTbl.EmployeeID from dbo.NotesTbl  INNER JOIN dbo.View_EmployeesLastNoteID ON dbo.NotesTbl.ID = dbo.View_EmployeesLastNoteID.LastID Where  Date >= '02/01/15')

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40600016
>>"Your latest SQL did not look for the latest note"
It does not need to.... so far as I can see

the link from view_frmOrientationSchedule into note is by EmployeeID

Your query asks for notes after a certain date. If a person has created 1 or 100 notes after that date, it is still the same person. If a person has not created a note since that date, even if it is their latest note, that note is ignored.

So, there is no need to discover latest note for this exists ()  test, you just need to know if EmployeeIDs have any notes after that date (and if they do one of those notes will be the latest, but that is irrelevant).

---------
I will not attempt to answer the select * issue, it is well covered in many books and other sources.
I will not permit any query into production unless the select clause is fully qualified

We can just disagree on this point if you prefer.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40600023
Ok, we cross posted. I will say this about select *

You attached an image of a form that appears to be in MS Access. I am not very familiar with Access.

For the types of systems I am more familiar with "select *" is a recipe for disaster. Here is a classic example:

CREATE TABLE ABC
(
  PRECIO_LISTA_MN  DECIMAL(12,2),
  PRECIO_LISTA_DL  DECIMAL(12,2),
  FECHAMODIFICO    DATE
)

Then somebody creates a view using this:

create view XYZ as (select * from ABC)

Then somebody adds another field to the table ABC

What do you expect will happens to the view XYZ? (ignoring Access)

answer: nothing.

no big deal you say...

ok, now somebody CHANGES a field in the table ABC. instead of FECHAMODIFICO  we use MODIFIEDON


NOW what happens to view XYZ? (ignoring Access)

answer: it fails
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40600744
>> @ScottPletcher,
 Not sure I understand what is the intended of this index, which fields will be included as it listed 4 fields? and this would help in this case too, or I should regardless add a non clustered index on the Date field?
<<

The index will by keyed by ( [Date], [ID] ) and include ( [EmployeeID], [Initial] ).

It may or may not help in the current query, but the stats indicate that the index above would be useful to a number of queries you've been running.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40601863
@PortletPaul,
It does not need to.... so far as I can see
Actually you might be right will that, and this could very possible be a key to solve this performance issue (in case the View_EmployeesLastNoteID was the culprit as you suspected..).

I sent an email for users asking if they have any reason to look for last note only, waiting for their response.

Re the Select *, I didn't mean to disagree in any way..Just was wondering if this can cause performance issues or any of this sort, anyway thanks for elaborating on this issue.

@ScottPletcher,
I will apply that index and let you know if this solves the problem.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602030
>>"I sent an email for users asking if they have any reason to look for last note only, waiting for their response."
that is up to you, but it should not change the technical implementation

It's logic

Person NoteDate
Fred     2015-02-01
Fred     2015-02-02
Fred     2015-02-03
Fred     2015-02-04
Fred     2015-02-05
Fred     2015-02-06
Bill         2015-02-01

Latest after 2015-02-04 =
Fred     2015-02-06

Persons with any notes after 2015-02-04 =
Fred

It does NOT matter if you locate just the last if you are judging from a cutoff date
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40602240
@All Experts,
Actually I am a little confused as before apply any changes I just ran my original SQL and it works fine (it takes a few seconds but that's ok).
Wondering if it has to do with the time users are logged into the database?

@PortletPaul,
I the example posted you're right, however users can select last note between 2/1/15 and 2/5/15, and in this case they don't want include employees with dates entered after 2/5/15.
I am almost positive they will come back saying that they don't want to lose this option as well..
Another option which they currently have is to filter all emp's who's last note was added by certain user, which this will also not function with all notes included.

@Scott,
I tried creating your index and didn't see a diff in performance, maybe its due to the above factor, and will have to try doing the day when many users are connected?
One more point, I didn't see in SSMS where the EmployeeID and Initial are included.
See attached.
Untitled.png
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602261
>>"however users can select last note between 2/1/15 and 2/5/15"
SELECT
      view_frmOrientationSchedule.*
FROM view_frmOrientationSchedule
WHERE EXISTS (
              SELECT NULL
              FROM (
                    SELECT
                          EmployeeID , [Date] , ID
                        , ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY [Date] DESC) AS RN
                    FROM dbo.NotesTbl
                    WHERE Date >= '20150201'
                    ) X
              WHERE RN = 1
              AND X.[Date] < '20150206' --<< new bit, and note it is one day after '20150205'
              AND view_frmOrientationSchedule.EmployeeID = NT.EmployeeID
            )
;

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40602299
@PortletPaul,
I got an error on  view_frmOrientationSchedule.EmployeeID = NT.EmployeeID  and changed for NotesTbl.EmployeeID, but still getting the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "NotesTbl.EmployeeID" could not be bound.
also tried dbo.notestbl.employeeid, same thing.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602316
my bad, give me a moment or two please
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 40602319
copy/paste had led to my alias error - it's looking for the alias of a subquery not the source table
see line 12
SELECT
      view_frmOrientationSchedule.*
FROM view_frmOrientationSchedule
WHERE EXISTS (
              SELECT NULL
              FROM (
                    SELECT
                          EmployeeID , [Date] , ID
                        , ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY [Date] DESC) AS RN
                    FROM dbo.NotesTbl
                    WHERE Date >= '20150201'
                    ) NT                                        --<< LOOKING FOR THIS ALIAS 
              WHERE NT.RN = 1
              AND NT.[Date] < '20150206' --<< new bit, and note it is one day after '20150205'
              AND view_frmOrientationSchedule.EmployeeID = NT.EmployeeID
            )
;

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40602332
This worked and pretty fast, will do additional testing tom when users are logged in & let you know.
Thanks
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40604286
Thanks Experts,

As I mentioned, this problem is not occurring now (and I didn't do any changes yet..), but I guess your suggestions is worth to apply regardless.

I would like to find out more on Scott's suggestion about indexes, how did he figured out which is missing and perhaps I can check it across the database, but since that is not part of this question, I will leave it for another post.

Great Job!
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40643758
@Scott,
If you have a chance, please take a look at the following,
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28627947.html#a40643623
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
sql server query? 6 28
How to manage encyps queries mssql when sending conditioning values 10 22
Sql query 34 20
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now