Click here to Skip to main content
15,906,645 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 21-Aug-18 2:26am
v3
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?"

 
Share this answer
 
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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900