Any improvement possible in the given query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Any improvement possible in the given query

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-12-04 : 02:06:31

Declare @StartDate DATETIME
Declare @EndDate DATETIME

Set @StartDate = '2012/10/01'
Set @EndDate = '2012/10/31'

Select tt.MyTableID_PK
, tt.Firstname
, tt.Lastname
, tt.Contact1
, tt.Email
, tt.TotalFeeCharged
, Case
When ct.CaseTypeId = 1 Then 'Traffic'
Else 'Criminal'
End As 'CaseType'
, t.RecDate As LeadDate
, (Select Min(ttp1.RecDate)
From MyOtherDB.dbo.tblTicketsPayment ttp1
Where ttp1.MyTableID = tt.MyTableID_PK
And ttp1.PaymentVoid = 0
And ttp1.isRemoved = 0) As HireDate
From MyOtherDB.dbo.tblTickets tt
Inner Join MyOtherDB.dbo.CaseType ct
On ct.CaseTypeId = tt.CaseTypeId
Inner Join tblcontactus t
On ( ( ISNULL(tt.Contact1, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact1, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Contact2, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact2, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Contact3, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact3, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Email, '') = ISNULL(t.EmailAddress, '')
And ISNULL(tt.Email, '') != ''
And ISNULL(t.EmailAddress, '') != '' ) )
And (Select Min(ttp.RecDate)
From MyOtherDB.dbo.tblTicketsPayment ttp
Where ttp.MyTableID = tt.MyTableID_PK
And ttp.PaymentVoid = 0
And ttp.isRemoved = 0) >= t.RecDate
And t.RecDate Between @StartDate And @EndDate
Where ISNULL(tt.Email, '') Not Like '%Test%'

It is the lehgacy app query which we have to maintain and database is sql server 2005

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-04 : 03:02:09

the catch all type of query may result in bad plan and can cause performance issues in case of large datasets.
Can you explain your requirement in words?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources