Sql query performance

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

SQL

select S.*,I.SupplierID,I.ModelNumber,I.SerialNumber,i.InvoiceNumber,i.InvoiceDate,i.PONumber,
	 i.PODate,i.ExtendedWarranty,i.FreeServiceCount,i.FreeServiceWarranty,i.PEWarranty,i.SalesNoteNumber
      from SRQMST S LEFT OUTER JOIN InstrumentMST I 
      ON S.InstID=I.InstID 
       order by SRQNumber Desc

What I have tried:

this stored procedure take 18 second to execute..how to improve performance

Posted 20-Aug-18 21:33pm

Member 13956117

Updated 21-Aug-18 2:26am

Nelek

v3

Add a Solution

Comments

CHill60 21-Aug-18 8:54am

Now that you've been back to bump your post can you answer my questions from earlier..."Do you really need the 2 lackhs x how every many rows are on InstrumentMST for each row on srqmst though? Realistically what are you going to do with all that data?"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-08-20T22:06:00

Solution 1

Start here: Performance Tuning SQL Queries | SQL Tutorial - Mode Analytics[^]

Posted 20-Aug-18 22:06pm

OriginalGriff

Simon_Whale · Answer 2 · 2018-08-20T22:56:00

Solution 2

the second part of your query is running a cross join (highlighted in bold) is this something that you intended? as this can cause some over head because of the way that it works.

Declare @InstID int
select @InstID=S.InstID
from SRQMST S
where SRQNumber=@SRQNumber 

if(@InstID<>-1)
  select 
  S.*,I.SupplierID,I.ModelNumber,I.SerialNumber,i.InvoiceNumber,i.InvoiceDate,i.PONumber
,i.PODate,i.ExtendedWarranty,i.FreeServiceCount,i.FreeServiceWarranty,i.PEWarranty,i.SalesNoteNumber

  from SRQMST S,InstrumentMST I
  where S.InstID=I.InstID and SRQNumber=@SRQNumber

Posted 20-Aug-18 22:56pm

Simon_Whale

Updated 20-Aug-18 23:12pm

v2

Comments

Simon_Whale 21-Aug-18 5:18am

Then you need to look at things such as indexing and the other solution that was given to you by OriginalGriff.

CHill60 21-Aug-18 5:19am

I don't agree that it is a cross join - it's a very old-fashioned way of doing an inner join ... see the next line

S.InstID=I.InstID

Simon_Whale 21-Aug-18 5:37am

have to a the obvious question but was that for both tables?

you would need to look at the execution plan for the query that will start to give you some insight.
also how many rows are being returned by this query?

Simon_Whale 21-Aug-18 5:52am

I think you have found a potential solution yourself, can you cut down the number of columns from the srqmst table?

CHill60 21-Aug-18 6:16am

Do you really need the 2 lackhs x how every many rows are on InstrumentMST for each row on srqmst though? Realistically what are you going to do with all that data? Find a way to filter it.