Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql sp inner join slow performance

Posted on 2011-04-30
8
Medium Priority
?
472 Views
Last Modified: 2012-05-11
sql - the following SP works fine -- but really when adding the 2 inner joins ... the reason to add them is to cross-reference Agent/Discount column data.

why is this is ?   can this be speed up?  (without the inner joins -- the search returns back almost instantly ... when adding inner joins ... more than 10 seconds).
SELECT 
T3.[DistributorDisplayName] AS 'Agent',
T2.[Discount] AS 'Agent Discount ( % )',
T1.[MSISDN], 
T1.[Comments] AS 'Voucher', 
cast( Cast(isnull(T1.[ChargeableAmount], 0) AS DECIMAL(18,2)) / 100000 AS decimal(18,2))  'Top-Up Amount ( £ )' ,
cast( Cast(isnull(T1.[EndingBalance], 0) AS DECIMAL(18,2)) / 100000 AS decimal(18,2))  'Ending Bal ( £ )',
T1.[TransactionTime]

FROM [TDRData] T1
INNER JOIN [EMUK_BackOffice].[dbo].[Vouchers] T2 ON T2.[SerialNumber]  = T1.[Comments] COLLATE Latin1_General_CI_AS
INNER JOIN [EMUK_BackOffice].[dbo].[Distributors] T3 ON T2.[Distributor_ID] = T3.[Distributor_ID]

WHERE ((T1.[COSID] = 95) 
     AND (T1.[TransactionKey] = 1) 
     AND (T1.[TransactionTime] >= @TransactionTime)  
     AND (T1.[TransactionTime] <= @TransactionTime2))

ORDER BY T1.[TransactionTime] DESC

Open in new window

0
Comment
Question by:amillyard
8 Comments
 
LVL 4

Expert Comment

by:RGBDart
ID: 35497318
look at query execution plan in SSMS, and if it suggests you to create indexes - do so.
0
 
LVL 4

Expert Comment

by:musalman
ID: 35497348
try :

WHERE ((T1.[COSID] = 95)
     AND (T1.[TransactionKey] = 1)
     AND (T1.[TransactionTime] BETWEEN @TransactionTime)  AND @TransactionTime2))
0
 

Author Comment

by:amillyard
ID: 35497378
'look at query execution plan in SSMS, and if it suggests you to create indexes - do so. ' -- where is the feature please in SSMS ? under Stored Procedures?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35497473
in query => include actual execution plan, and run the query.
you will get a execution plan, which will show how the query is executed?
0
 

Author Comment

by:amillyard
ID: 35497530
angelIII: apologies - 'actual execution plan' -- what is this ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35497590
it's a graphical display of how the query is executed, showing index access vs full table access, and how "joins" are executed internally.
this helps to tune the query, showing where the query is behaving badly
0
 

Author Comment

by:amillyard
ID: 35497633
angelIII: within the SSMS - where is the option please?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35497884
in the query menu....
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 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