slow Stored procedure result

Question

4.00/5 (2 votes)

See more:

SQL-Server-2005

Sir This is my stored procedure in sql server 2005
which retrieve 20000 rows result but it working slow.
but my another sp which retrieve 40000 rows result working nice .

any mistake or tips regarding my stored procedure is acceptable.

please give me idea.

SQL

ALTER PROCEDURE [dbo].[SearchCatalogOrUrlInformation] 
	
@username int,
@urlorcatalog varchar(100)
AS

BEGIN  

SET NOCOUNT ON;
DECLARE 
@M_username int,
@M_urlorcatalog varchar(100)

SET @M_username=@username	
SET @M_urlorcatalog=@urlorcatalog

    
SELECT  COMPANY.urlorcatalog AS 'Source',COMPANY.companyname AS 'Companies',CATEGORY.categoryname AS 'Category',REGION.regionname AS 'Region',usr.name AS 'User',count(email.email) AS 'Collection',COMPANY.dataentrytype as 'Type', COMPANY.date AS 'Date'

FROM dbo.companyinfo COMPANY

INNER JOIN dbo.emailinfo email ON COMPANY.dataID = email.DataID
INNER JOIN dbo.userinfo usr ON COMPANY.userid = usr.ID
LEFT OUTER  JOIN dbo.category CATEGORY ON COMPANY.category=CATEGORY.ID 
LEFT OUTER  JOIN dbo.regioninfo REGION ON COMPANY.region=REGION.ID 

 WHERE
 COMPANY.userid=@M_username AND COMPANY.urlorcatalog LIKE '%'+ @M_urlorcatalog + '%'

GROUP BY
 
COMPANY.urlorcatalog,COMPANY.companyname,CATEGORY.categoryname,REGION.regionname,COMPANY.dataentrytype,COMPANY.date,usr.name
  
ORDER BY  COMPANY.date DESC


END

Posted 25-Aug-11 0:13am

udusat13

Updated 25-Aug-11 0:17am

v3

Add a Solution

Comments

Prerak Patel 25-Aug-11 6:14am

Use code block for code segments.

Herman<T>.Instance 25-Aug-11 6:23am

do both stored procedures use left outer joins?

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)

Dylan Morley · Answer 1 · 2011-08-25T00:27:00

Solution 1

Take your SQL statement and put it in a new query window

Then choose Query -> Display Estimated Execution Plan

If you don't understand query plans, have a read of SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]

This will help you understand where your query is taking the time to execute. Maybe a new index is required. Do all of your tables have clustered indexes?

LIKE in the WHERE clause can sometimes causes isues, have a look here

http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx[^]

Again though, see how the query plan statistics are being used? Get to know this tool, it will help you write efficient SQL and tune your statements

Posted 25-Aug-11 0:27am

Dylan Morley

Comments

udusat13 25-Aug-11 6:54am

please view my sp once and check any mistake

thanks.............

Dylan Morley 25-Aug-11 6:55am

No obvious mistake, would need to see the query plan to know where the most time is being spent.

udusat13 25-Aug-11 7:02am

http://www.codeproject.com/KB/database/sql-tuning-tutorial-1.aspx

i have read this for execution plan but i am getting confused how to implement it

Dylan Morley 25-Aug-11 7:12am

In SQL Studio, choose 'New Query' from the toolbar

Paste your SQL statement into query window.

Now choose Query -> Display Estimated Execution Plan

Look at the results

S Douglas · Answer 2 · 2011-08-25T14:28:00

Without knowing the table structure / execution plan (likely missing an index) here is one way to try and speed up the query. This builds a limit list of based on the company. With the thought that it will cut down on the number of rows to be considered in the where clause as a result of the left joins.

SQL

DECLARE 
@M_username int,
@M_urlorcatalog varchar(100)
 
SET @M_username=@username	
SET @M_urlorcatalog=@urlorcatalog
 
-------------------------------------------------
-- Limit list of just the users / URL Catalogs --
-------------------------------------------------
SELECT
	c.dataID
	, c.userid
	, c.companyname
	, c.urlorcatalog
        , c.dataentrytype
	, c.[Date]
INTO #Company
FROM 	companyinfo c
WHERE
 COMPANY.userid=@M_username AND 
 COMPANY.urlorcatalog LIKE '%'+ @M_urlorcatalog + '%'
	
	
SELECT  
	COMPANY.urlorcatalog AS 'Source'
	, COMPANY.companyname AS 'Companies'
	, CATEGORY.categoryname AS 'Category'
	, REGION.regionname AS 'Region'
	, usr.name AS 'User'
	, count(email.email) AS 'Collection'
	, COMPANY.dataentrytype as 'Type'
	, COMPANY.date AS 'Date'
FROM #Company COMPANY
	INNER JOIN dbo.emailinfo email ON COMPANY.dataID = email.DataID
	INNER JOIN dbo.userinfo usr ON COMPANY.userid = usr.ID
	LEFT OUTER  JOIN dbo.category CATEGORY ON COMPANY.category=CATEGORY.ID 
	LEFT OUTER  JOIN dbo.regioninfo REGION ON COMPANY.region=REGION.ID  
GROUP BY
 COMPANY.urlorcatalog
 , COMPANY.companyname
 , CATEGORY.categoryname
 , REGION.regionname
 , COMPANY.dataentrytype
 , COMPANY.date,usr.name 
ORDER BY  COMPANY.date DESC

DROP TABLE #Company