Need help to optimize SQL select query with data functions

Question

I implemented a SQL query as mentioned below

SELECT OLT.status,
       OLT.queuedate,
       ( CONVERT(VARCHAR(10), OLT.queuedate, 101)
         + ' '
         + Stuff(RIGHT(CONVERT(VARCHAR(30), OLT.queuedate, 109), 14), 9, 4, ' ')
       )
FROM   onlineresults OLT WITH(nolock)
       LEFT JOIN roadmap CR WITH(nolock)
              ON OLT.roadmapid = CR.content_roadmap_id

The OnlineResults is having only 1,43000 records, but it taking nearly 5 minutes to complete the above query..

do we have any other way to make the query faster by creating indexes

Schema of Onlineresults table

OnlineID (Primary Key)
RoadmapId
StudentId
SectionId
Status
Retries
QueueDate

There are many fields in roadmap table and content_roadmap_id is primary key

Bhuvan, can you use EXPLAIN ANALYZE and paste the result. Also please include the table definitions (if possible, or atleast the relevant fields). — SystemFun, May 26 '14 at 06:09
@Vlad, i updated my question by appending the schema of the table.. what do you mean by Explain Analyze? Is it is a Query Execution Plan in SQL Server — Bhuvan, May 26 '14 at 06:17
In SQL Server you can use Database Engine Tuning Advisor to identify missing indexes — Milen, May 26 '14 at 06:18
@adrianm because i need to display roadmapdate from the roadmap table — Bhuvan, May 26 '14 at 08:45
So you want readers to optimize another query than the one shown. Good luck — adrianm, May 26 '14 at 08:54
@adrianm , no i just removed the column from the select list to make query more readable for readers... its the same query.. — Bhuvan, May 26 '14 at 09:10

score 0 · Answer 1 · edited Nov 13 '18 at 23:42

Yes indexes will effect the result.

You can do something like 1. Check both column have pk or index, if no , then create on it. Always use pk value to filter the data. Also try to filter data as much as possible when your query take too much.

On queuedate column give index with casting result which give faster result. OR create a new column whose value is (CONVERT(VARCHAR(10), OLT.QueueDate , 101)

Add a calculated column to your table and create an index on this column.

ALTER TABLE MyTable
Add Column queuedateCalculated As (CONVERT(VARCHAR(10), OLT.QueueDate , 101)

Then create an index on this.

CREATE INDEX queuedateCalculatedIdx ON MyTable.queuedateCalculated

refer link SQL Server - index on a computed column?

Please check the execution plan for both condition.. here is good article to understand plan with indexes.

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

https://sqlserverfast.com/?s=user+defined+ugly

Or you can create a index view also .

http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer

Instead of this can we create an indexed view? to resolve the issue — Bhuvan, May 26 '14 at 06:43

Need help to optimize SQL select query with data functions

1 Answers1