Query Performance and Index

Dear Forum,
Please help me modify this query for optimum performance and suggest indexes
to create. Thanks. James
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
Hi James
I added parenthesis to your query:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,
MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND
BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
(BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
GROUP BY
BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
Please let me know if this is correct.
If it is correct, then there is no need of changing the query, your query
looks perfect.
There might be Primary Keys declared on the tables that are used here.
Else create Index on the columns that are involved in the where clause.
please let me know your comments
thanks and regards
Chandra
"James Juno" wrote:

> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
>
|||Adding to my previous post, what i feel is, the first OR should be replace
with AND:
SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition,
BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
BBNew.dbo.fffCourtCodes.MST_COURT_CODE
AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode AND
BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
BBNew.dbo.fffCourtTypes.CTdefinition,
BBNew.dbo.fffRecordTypes.RTdefinition
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
|||Chandra,
This is great. Thank you so much. I haven't tried it yet - but it looks good.
James.
"Chandra" wrote:
[vbcol=seagreen]
> Hi James
> I added parenthesis to your query:
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,
> MIN(substring(BBNew.dbo.fffCASES.JudgmentDate,1,6) ),
> MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes,
> BBNew.dbo.fffRecordTypes, BBNew.dbo.fffCases
> WHERE BBNew.dbo.fffCases.CourtCode = BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND
> BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> (BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode
> OR BBNew.dbo.fffCases.StateFips LIKE :Param_State
> OR BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate)
> GROUP BY
> BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition, BBNew.dbo.fffRecordTypes.RTdefinition
> Please let me know if this is correct.
> If it is correct, then there is no need of changing the query, your query
> looks perfect.
> There might be Primary Keys declared on the tables that are used here.
> Else create Index on the columns that are involved in the where clause.
> please let me know your comments
> thanks and regards
> Chandra
> "James Juno" wrote:
|||To avoid confusion, use ansi join instead old style. Also use alias to make
the statement more readable.
SELECT
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition,
c.StateFips,
MIN(substring(c.JudgmentDate, 1,6)),
MAX(substring(c.JudgmentDate, 1,6))
FROM
BBNew.dbo.fffCases as c
inner join
BBNew.dbo.fffCourtCodes as cc
on c.CourtCode = cc.MST_COURT_CODE
inner join
BBNew.dbo.fffCourtTypes as ct
on c.CourtType = ct.CTcode
inner join
BBNew.dbo.fffRecordTypes as rt
on c.FilingType = rt.RTcode
WHERE
c.StateFips LIKE :Param_State
OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
GROUP BY
c.StateFips,
cc.MST_COURT_NAME,
ct.CTdefinition,
rt.RTdefinition;
Be sure to have an index by:
- BBNew.dbo.fffCases.PostedDate <-- clustered one
- BBNew.dbo.fffCases.StateFips
- BBNew.dbo.fffCases.CourtCode
- BBNew.dbo.fffCases.CourtType
- BBNew.dbo.fffCases.FilingType
- BBNew.dbo.fffCourtCodes.MST_COURT_CODE
- BBNew.dbo.fffCourtTypes.CTcode
- BBNew.dbo.fffRecordTypes.rt.RTcode
AMB
"James Juno" wrote:
[vbcol=seagreen]
> Chandra,
> This is great. Thank you so much. I haven't tried it yet - but it looks good.
> James.
> "Chandra" wrote:
|||Alejandro,
Great. Thank you.
James
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> To avoid confusion, use ansi join instead old style. Also use alias to make
> the statement more readable.
> SELECT
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition,
> c.StateFips,
> MIN(substring(c.JudgmentDate, 1,6)),
> MAX(substring(c.JudgmentDate, 1,6))
> FROM
> BBNew.dbo.fffCases as c
> inner join
> BBNew.dbo.fffCourtCodes as cc
> on c.CourtCode = cc.MST_COURT_CODE
> inner join
> BBNew.dbo.fffCourtTypes as ct
> on c.CourtType = ct.CTcode
> inner join
> BBNew.dbo.fffRecordTypes as rt
> on c.FilingType = rt.RTcode
> WHERE
> c.StateFips LIKE :Param_State
> OR c.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY
> c.StateFips,
> cc.MST_COURT_NAME,
> ct.CTdefinition,
> rt.RTdefinition;
> Be sure to have an index by:
> - BBNew.dbo.fffCases.PostedDate <-- clustered one
> - BBNew.dbo.fffCases.StateFips
> - BBNew.dbo.fffCases.CourtCode
> - BBNew.dbo.fffCases.CourtType
> - BBNew.dbo.fffCases.FilingType
> - BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> - BBNew.dbo.fffCourtTypes.CTcode
> - BBNew.dbo.fffRecordTypes.rt.RTcode
>
> AMB
>
> "James Juno" wrote:
|||In general, it is a good practice to create a Primary Key constraint on
all tables (which will automatically create a unique index), and to
create indexes on foreign key constraints.
You did not post any DDL, so the keys and indexes cannot be reviewed.
But the query would benefit if all join columns were indexed.
Gert-Jan
James Juno wrote:
> Dear Forum,
> Please help me modify this query for optimum performance and suggest indexes
> to create. Thanks. James
> SELECT BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition,
> BBNew.dbo.fffCases.StateFips,MIN(substring(BBNew.d bo.fffCASES.JudgmentDate,
> 1,6)), MAX(substring(BBNew.dbo.fffCASES.JudgmentDate, 1,6)) FROM
> BBNew.dbo.fffCourtCodes, BBNew.dbo.fffCourtTypes, BBNew.dbo.fffRecordTypes,
> BBNew.dbo.fffCases WHERE BBNew.dbo.fffCases.CourtCode =
> BBNew.dbo.fffCourtCodes.MST_COURT_CODE
> AND BBNew.dbo.fffCases.CourtType = BBNew.dbo.fffCourtTypes.CTcode AND
> BBNew.dbo.fffCases.FilingType = BBNew.dbo.fffRecordTypes.RTcode OR
> BBNew.dbo.fffCases.StateFips LIKE :Param_State OR
> BBNew.dbo.fffCases.PostedDate BETWEEN :Param_StartDate AND :Param_EndDate
> GROUP BY BBNew.dbo.fffCASES.StateFips, BBNew.dbo.fffCourtCodes.MST_COURT_NAME,
> BBNew.dbo.fffCourtTypes.CTdefinition,
> BBNew.dbo.fffRecordTypes.RTdefinition
|||Gert-Jan,
I got the script that works. Thanks for your contribution.
James
"Gert-Jan Strik" wrote:

> In general, it is a good practice to create a Primary Key constraint on
> all tables (which will automatically create a unique index), and to
> create indexes on foreign key constraints.
> You did not post any DDL, so the keys and indexes cannot be reviewed.
> But the query would benefit if all join columns were indexed.
> Gert-Jan
>
> James Juno wrote:
>