[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

ORDER BY slowing down query

I have a two queries which are almost identical (except an ORDER BY), but one is timing out each time I try to run it. Could anybody assist?

Here is the query that runs OK (again, the only different between this and the bottom one is the ORDER BY)

SELECT     TOP (20) CallPictures.Picture_ID, CallPictures.Caption, CallPictures.Call_ID, LEFT(dbo.fn_convert_to_title_case(OutletList.Outlet_Name)
                      + ', ' + dbo.fn_convert_to_title_case(OutletList.Outlet_Location), 25) AS Outlet_Location, People.Person_nameFirst, People.Person_nameLast,
                      CAST(DAY(CallBankResponse.Date) AS VARCHAR) + ' ' + CAST(LEFT(DATENAME(MONTH, CallBankResponse.Date), 3) AS VARCHAR) AS Date,
                      CallBankResponse.Date AS Datum
FROM         CallPictures INNER JOIN
                      CallBankResponse ON CallPictures.Call_ID = CallBankResponse.Call_ID INNER JOIN
                      CallBank ON CallBankResponse.Call_ID = CallBank.Call_ID INNER JOIN
                      ActivityOutlets ON CallBank.ActivityOutlet_ID = ActivityOutlets.ActivityOutlet_ID INNER JOIN
                      OutletList ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID INNER JOIN
                      UserList ON CallBank.Username = UserList.User_Username INNER JOIN
                      People ON UserList.Person_ID = People.Person_ID INNER JOIN
                      AccountOutlets ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID INNER JOIN
                      ClientAccounts ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
WHERE     (ClientAccounts.Company_ID = 31)

SELECT     TOP (20) CallPictures.Picture_ID, CallPictures.Caption, CallPictures.Call_ID, LEFT(dbo.fn_convert_to_title_case(OutletList.Outlet_Name)
                      + ', ' + dbo.fn_convert_to_title_case(OutletList.Outlet_Location), 25) AS Outlet_Location, People.Person_nameFirst, People.Person_nameLast,
                      CAST(DAY(CallBankResponse.Date) AS VARCHAR) + ' ' + CAST(LEFT(DATENAME(MONTH, CallBankResponse.Date), 3) AS VARCHAR) AS Date,
                      CallBankResponse.Date AS Datum
FROM         CallPictures INNER JOIN
                      CallBankResponse ON CallPictures.Call_ID = CallBankResponse.Call_ID INNER JOIN
                      CallBank ON CallBankResponse.Call_ID = CallBank.Call_ID INNER JOIN
                      ActivityOutlets ON CallBank.ActivityOutlet_ID = ActivityOutlets.ActivityOutlet_ID INNER JOIN
                      OutletList ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID INNER JOIN
                      UserList ON CallBank.Username = UserList.User_Username INNER JOIN
                      People ON UserList.Person_ID = People.Person_ID INNER JOIN
                      AccountOutlets ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID INNER JOIN
                      ClientAccounts ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
WHERE     (ClientAccounts.Company_ID = 31)
ORDER BY Datum DESC
0
nkewney
Asked:
nkewney
  • 3
1 Solution
 
imran_fastCommented:
You need to have proper index on the table
please create an index on Datum column on the table

create index Ix_tablename on Tablename(datum desc) ;

this will boost the performance
0
 
nkewneyAuthor Commented:
Hi imran_fast,

Unfortunately, this didn't make a difference.

The query completes in less than 1 second without the ORDER BY, but doesn't complete at all with the ORDER BY.

Nick
0
 
imran_fastCommented:
Did you create the index i recommended.
(Datum desc)
0
 
imran_fastCommented:
What else you can do is you can put the query in database engine tuning advisor and see what are the recommendation.
apply those and you will be fine.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now