query tuning

I am not very good at writing queries - but I need help
tuning this one. Can someone help make this better. It's
retrieving from 3 different tables. Thank you.
Mary...
Select distinct CaseNo, courtCode, courtType, Amount,
DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
CoDLastName, CoDFirstName, CoDMidName,
CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
PLastName, PFirstName, PMidName, PstAddress, PCity,
PState, PZip,
'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
FilingType End), CourtCode, efxCourtCode, ResearcherNo,
JudgmentDate,
SatisfiedDate, c.Efx_prt, c.EFX_action_code,
c.Efx_status_code from tblCases with (Index = idx_BBB),
BBB_Client_CourtCodes,
EFXNDR_RecordType c WHERE PostedDate = '20040524' and
c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
DType = 'I' and (CoDType = '' or CoDType = 'I') and
DStAddress <>'' and DStAddress is not null and DCity <>''
and DCity is not null and
DState <>'' and DState is not null and CourtCode = CC and
CourtType = CT and
RT = isnull(nullif((Case When DispositionType='ES'
Then 'JS' Else DispositionType End),''),
(Case When FilingType='EV' Then 'CJ' Else FilingType End))
and efxCourtCode is not null order by CourtCodeHard to say without seeing more information such as DDL (CREATE TABLE
statements, constraints, etc.)
With this little information all I can suggest is to remove the index
hint, so SQL-Server can figure out for itself what the best access path
is.
Also, as a general advice, make sure you defined Primary Keys on all
tables, and that you have indexed all Foreign Keys.
A thing that will not improve performance is to rewrite the statement in
a more readable form, with some consistent spacing, indentation and LFs.
You may also want to switch from "SELECT ... FROM Table1, Table2 WHERE
Table1.something = Table2.something" to "SELECT ... FROM Table1 INNER
JOIN Table2 ON Table1.something = Table2.something", if only just for
readability.
Hope this helps,
Gert-Jan
Mary Lou wrote:
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode
(Please reply only to the newsgroup)|||There are a couple of more things I might add...
SInce you do not have any join criteria you are doing a cross join... Each
court code row is joined with each case... I suspect this is not what you
wish...Unless (CourtCode = CC and
> CourtType = CT ) is part of the join criteria
There probably should be something like... And I would recommend re-writing
this using the ANSI Style Join as below...
from tblCases inner join BBB_Client_CourtCodes on SomcolumnintablesCases =
somecolumninBBB_Client_CourtCodes
inner join EFXNDR_Recordtype C on SomeColumninTablesCases =
somecoluminRecordtype
Amount >= '00000050' or Amount >= 50 - this is also strange... Is the amount
column character or numeric?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mary Lou" <anonymous@.discussions.microsoft.com> wrote in message
news:1c0e501c4524c$d1f4f3e0$a601280a@.phx
.gbl...
> I am not very good at writing queries - but I need help
> tuning this one. Can someone help make this better. It's
> retrieving from 3 different tables. Thank you.
> Mary...
>
> Select distinct CaseNo, courtCode, courtType, Amount,
> DTaxID, DZip, DLastName, DFirstName, DMidName, DSuffix,
> CoDLastName, CoDFirstName, CoDMidName,
> CoDSuffix, CoDTaxID, DStAddress, DCity, DState,
> PLastName, PFirstName, PMidName, PstAddress, PCity,
> PState, PZip,
> 'FilingType'=(Case When FilingType='EV' Then 'CJ' Else
> FilingType End), CourtCode, efxCourtCode, ResearcherNo,
> JudgmentDate,
> SatisfiedDate, c.Efx_prt, c.EFX_action_code,
> c.Efx_status_code from tblCases with (Index = idx_BBB),
> BBB_Client_CourtCodes,
> EFXNDR_RecordType c WHERE PostedDate = '20040524' and
> c.ndr_rt=rt and (Amount >= '00000050' or Amount >= 50) and
> DType = 'I' and (CoDType = '' or CoDType = 'I') and
> DStAddress <>'' and DStAddress is not null and DCity <>''
> and DCity is not null and
> DState <>'' and DState is not null and CourtCode = CC and
> CourtType = CT and
> RT = isnull(nullif((Case When DispositionType='ES'
> Then 'JS' Else DispositionType End),''),
> (Case When FilingType='EV' Then 'CJ' Else FilingType End))
> and efxCourtCode is not null order by CourtCode