How can I optimize this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I optimize this query?

Author

Topic

rekon32
Starting Member

16 Posts

Posted - 2013-08-23 : 14:05:53

I am making the query below a view. However, the query takes too long as it has millions of records. What can I do to speed it up?


USE GoogleTestBed;
GO

SELECT 
       pr.LoanPurpose,
       pl.plancode,
       ar.ruleid,
       av.adjvalue,
       ar.affectratetype,
       ar.RulePartNo,
       ar.LHSVector,
       ar.FilterID,
       ar.RHSDataType,
       ar.RHSValue1,
       ar.RHSValue2,
       ar.RowID,
       pl.planid,
       pl.Narrative

FROM prodmaster pr
              inner join planmaster pl ON pr.prodid = pl.ProdID
              inner join planxadj pa ON pl.planid = pa.PlanID
              inner join adjrules ar ON pa.RuleID = ar.RuleID
              inner join adjvalues av ON pa.RuleID = av.RuleID;

Data Analyst

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 14:34:34

Do you have any indexes on any of these tables?

If you are selecting all the records - i.e., without any filters or WHERE clauses and if the tables are mostly matching, there is not much you can do to speed it up. If the tables are unbalanced - for example, if you have a lot of records in planmaster, but only a few match the prodid in prodmaster, then an index on prodid for planmaster would help. That is just an example. You should look at your query plan and see what parts of the query are taking a long time.

Also, before you add indexes or buy expensive hardware, make sure that your indexes and statistics are properly maintained.

rekon32
Starting Member

16 Posts

Posted - 2013-08-23 : 14:53:14

Thanks James. I added a where clause to the SP so is going to speed it up quite a bit!

Also,

What am I doing wrong in the SP. Can VARCHAR be declared in SP?


CREATE PROCEDURE DBO.sp_MRS_AdjAutomation 

@bankid NVARCHAR

AS
BEGIN

SELECT 
	pr.LoanPurpose,
	pl.plancode,
	ar.ruleid,
	av.adjvalue,
	ar.affectratetype,
	ar.RulePartNo,
	ar.LHSVector,
	ar.FilterID,
	ar.RHSDataType,
	ar.RHSValue1,
	ar.RHSValue2,
	ar.RowID,
	pl.planid,
	pl.Narrative

FROM prodmaster pr
	inner join planmaster pl ON pr.prodid = pl.ProdID
	inner join planxadj pa ON pl.planid = pa.PlanID
	inner join adjrules ar ON pa.RuleID = ar.RuleID
	inner join adjvalues av ON pa.RuleID = av.RuleID

WHERE pl.lender = @bankid;
END



EXECUTE DBO.sp_MRS_AdjAutomation 'pnc'

rekon32
Starting Member

16 Posts

Posted - 2013-08-23 : 15:02:23

Nevermind i declared bankid where i set it as lender. Sorry!

Data Analyst

rekon32
Starting Member

16 Posts

Posted - 2013-08-23 : 16:08:39

Nevermind i declared bankid where i set it as lender. Sorry!

Data Analyst

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources