Tuning sql question
Hi Experts,
I have the following Sql statement that takes forever to execute.
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_frmOrientationSchedu
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')
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_frmOrientationSchedu
Jeffrey Dake
Do you have any indexes on the date column. Since you are selecting recent data, having an index on date could possibly help greatly
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?
Have you looked at the query for each view?
you could provide an explain plan for the query? (A .sqlplan file not a screenshot please)
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'
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_EmployeesLastNote
elni.LastID = nt.ID
--AND elni.EmployeeID = nt.EmployeeID --redundant, but add if it helps performance
INNER JOIN view_frmOrientationSchedul
fos.EmployeeID = nt.EmployeeID
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_EmployeesLastNote
elni.LastID = nt.ID
--AND elni.EmployeeID = nt.EmployeeID --redundant, but add if it helps performance
INNER JOIN view_frmOrientationSchedul
fos.EmployeeID = nt.EmployeeID
ASKER
Ok Experts,
I finnaly got hold of the sqlplan (attached copy).
Let me know if that helps.
Thanks
Copy-of-OrientSchedule.sqlplan
I finnaly got hold of the sqlplan (attached copy).
Let me know if that helps.
Thanks
Copy-of-OrientSchedule.sqlplan
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.
Try clustering the tblNotes table by Date, then using the query above.
ASKER
@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?
@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..
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@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..
try using ROW_NUMBER() OVER() instead of View_EmployeesLastNoteID
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
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.EmployeeIDNote 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
>> @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_de
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_gr
mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_gr
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)
--AND mid.object_id IN (OBJECT_ID('<table_name_1>
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_spac
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/*,
) 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_stat
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+us
FROM sys.dm_db_index_usage_stat
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
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_de
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_gr
mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_gr
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)
--AND mid.object_id IN (OBJECT_ID('<table_name_1>
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_spac
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/*,
) 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_stat
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+us
FROM sys.dm_db_index_usage_stat
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
ASKER
@PortletPaul,
Tested yours and they work, however I'm wondering how can I apply these while preserving the beginning of my SQL statement?
@ScottPletcher,
Attached results.
notestbl2.csv
notestbl1.csv
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...@ScottPletcher,
Attached results.
notestbl2.csv
notestbl1.csv
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>" 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?
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.EmployeeIDASKER
@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,
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.
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
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 queryI 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 IfAnother 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
>>"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.
I assume this will help you keep the query editable.
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
)
;I assume this will help you keep the query editable.
ASKER
@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.
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.
ASKER
@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?
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')
>>"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_frmOrientationSchedul
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.
It does not need to.... so far as I can see
the link from view_frmOrientationSchedul
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.
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:
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
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
>> @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.
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.
ASKER
@PortletPaul,
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.
It does not need to.... so far as I can seeActually 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.
>>"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
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
ASKER
@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
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
>>"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
)
;ASKER
@PortletPaul,
I got an error on view_frmOrientationSchedul
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.
I got an error on view_frmOrientationSchedul
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.
my bad, give me a moment or two please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked and pretty fast, will do additional testing tom when users are logged in & let you know.
Thanks
Thanks
ASKER
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!
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!
ASKER
@Scott,
If you have a chance, please take a look at the following,
https://www.experts-exchange.com/questions/28627947/SQL-Query-linked-to-Access-Performance-Tuning-ASYNC-NETWORK-IO.html?anchorAnswerId=40643623#a40643623
Thanks
If you have a chance, please take a look at the following,
https://www.experts-exchange.com/questions/28627947/SQL-Query-linked-to-Access-Performance-Tuning-ASYNC-NETWORK-IO.html?anchorAnswerId=40643623#a40643623
Thanks