Why is my SP running like a dog?

[1DMF]

I have converted a standard MS access query to a stored procedure but am experiencing diabolical performance.

why is this SP running so badly?

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT dbo.vCaseCheckerMain.Documents_Override, dbo.vCaseCheckerMain.Case_ID, dbo.vCaseCheckerMain.Check_Type, dbo.vCaseCheckerMain.Flag_Date, dbo.vCaseCheckerMain.CNames, dbo.vCaseCheckerMain.Category, dbo.vCaseCheckerMain.Status, dbo.vCaseCheckerMain.CompanyName, dbo.vCaseCheckerMain.FirstName, dbo.vCaseCheckerMain.LastName, dbo.vCaseCheckerMain.[Compliance Officer], dbo.vCaseCheckerMain.Rating, dbo.vCaseCheckerMain.Due_Date, dbo.vCaseCheckerMain.Final_Rating, dbo.vCaseCheckerMain.Assigned, dbo.vCaseCheckerMain.Reason, dbo.vCaseCheckerMain.Documents, dbo.vCaseCheckerMain.Override_By, dbo.vCaseCheckerMain.Override_Reason

FROM dbo.vCaseCheckerMain
GROUP BY dbo.vCaseCheckerMain.Documents_Override, dbo.vCaseCheckerMain.Case_ID, dbo.vCaseCheckerMain.Check_Type, dbo.vCaseCheckerMain.Flag_Date, dbo.vCaseCheckerMain.CNames, dbo.vCaseCheckerMain.Category, dbo.vCaseCheckerMain.Status, dbo.vCaseCheckerMain.CompanyName, dbo.vCaseCheckerMain.FirstName, dbo.vCaseCheckerMain.LastName, dbo.vCaseCheckerMain.[Compliance Officer], dbo.vCaseCheckerMain.Rating, dbo.vCaseCheckerMain.Due_Date, dbo.vCaseCheckerMain.Final_Rating, dbo.vCaseCheckerMain.Assigned, dbo.vCaseCheckerMain.Reason, dbo.vCaseCheckerMain.Documents, dbo.vCaseCheckerMain.Override_By, dbo.vCaseCheckerMain.Override_Reason
HAVING (((dbo.vCaseCheckerMain.Rating) Is Null Or (dbo.vCaseCheckerMain.Rating)<>1))
ORDER BY dbo.vCaseCheckerMain.Documents_Override DESC , dbo.vCaseCheckerMain.Case_ID;
END

Thanks
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[bborissov]

Why you have GROUP BY?
You didn't use ANY aggregate functions (or at least I didn't saw such functions).
Change HAVING to WHERE.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

Try this:

SELECT dbo.vCaseCheckerMain.Documents_Override,
       dbo.vCaseCheckerMain.Case_ID,
       dbo.vCaseCheckerMain.Check_Type,
       dbo.vCaseCheckerMain.Flag_Date,
       dbo.vCaseCheckerMain.CNames,
       dbo.vCaseCheckerMain.Category,
       dbo.vCaseCheckerMain.Status,
       dbo.vCaseCheckerMain.CompanyName,
       dbo.vCaseCheckerMain.FirstName,
       dbo.vCaseCheckerMain.LastName,
       dbo.vCaseCheckerMain.[Compliance Officer],
       dbo.vCaseCheckerMain.Rating,
       dbo.vCaseCheckerMain.Due_Date,
       dbo.vCaseCheckerMain.Final_Rating,
       dbo.vCaseCheckerMain.Assigned,
       dbo.vCaseCheckerMain.Reason,
       dbo.vCaseCheckerMain.Documents,
       dbo.vCaseCheckerMain.Override_By,
       dbo.vCaseCheckerMain.Override_Reason
FROM dbo.vCaseCheckerMain

WHERE (dbo.vCaseCheckerMain.Rating IS NULL  OR
      (dbo.vCaseCheckerMain.Rating)<>1)

ORDER BY dbo.vCaseCheckerMain.Documents_Override DESC ,
         dbo.vCaseCheckerMain.Case_ID;

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

A group by without any aggregates is the same as distinct. Perhaps that is why the group by was there.

I notice that the table name starts with a "v". Is this a view? If so, can you post the view definition?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

1. it was a straight copy from the access query (SQL view) to the SP, was this wrong?

2. yes the table is a view... (I used the view designer in SQL 2005 studio)

SELECT     dbo.Business_Register.CNames, dbo.Business_Register.Documents_Override, dbo.Business_Register.Category, dbo.Business_Register.Status, 
                      dbo.Business_Register.Documents, dbo.Case_Checking.Case_ID, dbo.Case_Checking.Reason, dbo.Case_Checking.Check_Type, 
                      dbo.Case_Checking.Flag_Date, dbo.Case_Checking.Assigned_By, dbo.Case_Checking.Assigned_Date, dbo.Case_Checking.Assigned, 
                      dbo.Case_Checking.Override_By, dbo.Case_Checking.Override_Reason, dbo.Members.FirstName, dbo.Members.LastName, 
                      dbo.Members.CompanyName, dbo.Case_Checking.[Compliance Officer], dbo.Case_Checking.Rating, dbo.Case_Checking.Final_Rating, 
                      dbo.Case_Checking.Due_Date, dbo.Business_Register.Ext_Retire, dbo.Business_Register.Client_Status, dbo.Business_Register.Repay_Method, 
                      dbo.Business_Register.App_Fee, dbo.Business_Register.Comp_Fee, dbo.Business_Register.Prod_Status, dbo.Business_Register.Sub_Date, 
                      dbo.Business_Register.Rep_Policy, dbo.Business_Register.Trust, dbo.Business_Register.Prod_Type, dbo.Business_Register.Rec_Type, 
                      dbo.Business_Register.Entry_Date
FROM         dbo.Case_Checking INNER JOIN
                      dbo.Business_Register ON dbo.Case_Checking.Case_ID = dbo.Business_Register.Rec_ID INNER JOIN
                      dbo.Members ON dbo.Business_Register.Adv_MemNo = dbo.Members.ID

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

There is nothing "obviously" wrong with this. Hmmm.....

Can you show us the execution plan for this stored procedure?


First, set the output to text. Right click in the query window, Click "Results To" -> "Results To Text"

Set ShowPlan_Text on
go
Exec YourStoredProcedureNameHere
go
Set ShowPlan_Text off

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

ok, plan attached, hope that's what you meant?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Not exactly, but close enough.

I think your problem is that the where clause is not selective enough to effectively use an index.

Notice the bottom right corner? There are 2 clustered index scans. Index scans are generally bad. Index seeks are good. There are 2 (which get merged) based on the where clause the 6% one is probably from the Is NULL check and the 81% is probably from the <> 1 check.

If you add an index to the case checking table that has Rating in it, you may get better performance. In fact, I would suggest an index on (caseid, rating) added to the case_checking table.

This may not help. If it doesn't then you should remove the index.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Where has my plan gone?

Anyway, you say add an index to the case_checking table, it has one on the PK (Case_ID) - clustered, am i simply adding Rating to this existing index?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

well I added an index to the rating column, re-attached my SP via a pass-through query to the form and it ground to a halt.

When the form finaly loaded the data, if i changed one of the drop down combo boxes to filter the record set, OMG , sooo sloooow.

Put back to a straight query against the view and it speeded up no-end the filter then runs well fast.

I don't know if the fact the form is a continuos form with conditional formating on it makes any difference?

If I manually just run the pass-through query the records are displayed almost instantly , so I think using an SP to obtain records sets bound to a form in MS Access doesn't work very well.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Try adding "WITH RECOMPILE" to your stored procedure so it looks something like this:

CREATE PROCEDURE YourProcedureName
  @Var1 int,
  @Var2 VarChar(20)
[!]WITH RECOMPILE[/!]
AS
 .. your code here

If this works for you, let me know and I will explain why.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

nope, no change still painfully slow!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

just to put this into perspective. Once the form has loaded the entire record set (regardless of datasource), changing the drop down to perform a filter for exactly the same selection runs as follows...

Using the Stored Procedure as the record source : 58 seconds to complete filter.

Using standard query against the table 'view' as the record source : 1-2 seconds to perform filter.

Something isn't right?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Can you run profiler to see what Access is sending to the database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

How do I do that?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jrbarnett]

The instructions here are for SQL 2008 developer, so they may vary slightly.

Start -> Programs -> Microsoft SQL Server (your version) -> Performance Tools -> SQL Server Profiler.

File menu -> New trace
Login to SQL server with your credentials when prompted.
Give the trace a sensible name
use template Standard (default) - we can refine this later fi needed
Click the Run button at the bottom of the windows
Now go into Access and run your query and Profiler should populate with the SQL that is being sent to SQL server. If other people are using databases hosted on it at the time there may be other data showing up in the trace, but hopefully it won't be too bad.

Secondly, try and run your stored procedure from Access.

Once this has finished, be sure to click the red stop button in the toolbar as running traces imposes extra workload on the database engine.
Look at the code - hopefully it will be a single select statement from your view or similar.

Eliminate all the bumpf about existing connections, and try and locate the record (or records) in the result set that relate to running your query from Access (or attempts to run your stored procedure here).

John

 

[1DMF]

Cant see 'Performance Tools' I have SQL 2005.

This is what I have...

Analysis Services has - Deployment Wizard.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Are you using SQL Express? I think Profiler may not be available with that version. I know it exists for SQL2005 standard.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

It is meant to be SQL 2005 Standard Edition, though I didin't install it, the web hosting compnay did.

where do I find my version to confirm this?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Select SERVERPROPERTY('Edition')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom