Stored Procedure,View Working Slow

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hi all,

I am working on website . For administrator, i need to generate report which contains data from 9-10 tables.

I have created stored procedure but it is working very slow, it takes 5 mins to get data in sql server 2005 so in my website, i get timeout exception.
As i said, i fetch data from 9-10 tables so i used inner join for that. is inner join slows down stored procedure?

For this, i tried to work with view. I craeated 5 to 6 views as per my requirement and used those views in one stored procedure. but still its very slow.

Please help me with above scenario..

Below is my stored procedure

SQL

CREATE Procedure getAdminReport   -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012                              
(                                                              
@eid varchar(500),                                      
@from_month int,                                                 
@to_month int,                                                        
@from_year int,                                                          
@to_year int,      
@desg int= null                                                            
)                                                              
as                                                                                                     
                                        
CREATE TABLE #2                                         
(                                         
LIDList Int                                         
)                                         
                                        
-- Optional index on the temp table                                         
CREATE INDEX idx1 ON #2 (LIDList)                                         
INSERT INTO #2                                         
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,                                         
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList                                         
FROM tblToolsStringParserCounter                                         
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +                                         
@eid + ',' , ID - 1, 1) = ','                                         
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0                                         
                                        
                                        
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,                      
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,                                                                  
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,                                                                     
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,                                                                  
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,          
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,                                                                         
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,                                                
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,          
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full                                              
from doc_business_data DB                                                                  
inner join educator_call_dtls EC on DB.call_id=EC.call_id                                                                  
inner join doctor_master DM on DM.doc_id=DB.doc_id                                                           
inner join educator_master ED on ED.eid=EC.eid                                                           
inner join educator_state_master city on city.state_city_id=ED.city_id                      
inner join educator_state_master region on region.state_city_id=city.parent_id                      
inner join educator_state_master st on st.state_city_id = region.parent_id                                                                      
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and                                    
(((year(EC.call_date) > @from_year) OR

Posted 31-Jan-13 21:55pm

Sweetynewb

Updated 1-Feb-13 2:15am

CHill60

v3

Add a Solution

Comments

Rob Branaghan 1-Feb-13 4:09am

Dont use views. Views are slow....very slow....

You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.

Ankur^\m/ 1-Feb-13 8:06am

That is the way to go. i) Evaluate the execution plan. ii) Add necessary indexes.
That could have been an answer as well with some more details.

Rob Branaghan 1-Feb-13 4:11am

Would it be possible to see the SQL, as it would be a lot easier to assist being able to see the SQL.

Meysam Toluie 1-Feb-13 4:11am

Of course, Inner join slows down store procedures.
For suggesting you a solution I need to see your code.

Sweetynewb 1-Feb-13 4:53am

but i need to use inner join to get data

Tejas Vaishnav 1-Feb-13 4:13am

Can you provide your procedure and view code. so any one can review it and if any issue then solved it.

Sweetynewb 1-Feb-13 4:52am

ok.. Please suggest me any other idea if views doesnt work then what need to use? Below is my stored procedure

CREATE Procedure getAdminReport -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012
(
@eid varchar(500),
@from_month int,
@to_month int,
@from_year int,
@to_year int,
@desg int= null
)
as

CREATE TABLE #2
(
LIDList Int
)

-- Optional index on the temp table
CREATE INDEX idx1 ON #2 (LIDList)
INSERT INTO #2
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList
FROM tblToolsStringParserCounter
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +
@eid + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0

select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join doctor_master DM on DM.doc_id=DB.doc_id
inner join educator_master ED on ED.eid=EC.eid
inner join educator_state_master city on city.state_city_id=ED.city_id
inner join educator_state_master region on region.state_city_id=city.parent_id
inner join educator_state_master st on st.state_city_id = region.parent_id
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and
(((year(EC.call_date) > @from_year) OR

Tejas Vaishnav 1-Feb-13 7:38am

please use improve question and past your code in your question body

Irbaz Haider Hashmi 1-Feb-13 5:09am

Here are few things that you can avoid.
- Creating index on hash table might not be necessary here
- using Case in Sum function might not be necessary here
- Move IN checking from where clause to inner join
- Create Indexes on your tables. This might be very tricky. Because if you create extra indexes it can effect your other insertion and updation transactions.

Creating indexes on your tables will really help you here.

CREATE NONCLUSTERED INDEX [INDEXNAME] ON [TABLENAME]
(Columns that are used in where conditions)
INCLUDE(columns that are used in select clause)
ON [PRIMARY]

Do it for each table and see the effect. Check your execution plan before and after indexing.

Sweetynewb 3-Feb-13 23:29pm

thanks for reply.
i need to use case in Sum function because there is one columns for 2 or 3 types( ex: timing column in table is used to store - moring, evening or full day.)

i did not understood your 3rd point- " Move IN checking from where clause to inner join"?

can you tell me how to apply indexing on my inner join tables?

Please help me to improve speed.

Irbaz Haider Hashmi 4-Feb-13 2:53am

CREATE NONCLUSTERED INDEX IX_doc_educator_master_01 on dbo.educator_master
(/*ADD COMMA SEPERATED COLS WHICH ARE IN WHERE OR CONDITION CLAUSE*/)
INCLUDE(/*ADD COMMA SEPERATED COLS WHICH ARE IN SELECT CLAUSE*/)
ON [PRIMARY]

THIS IS HOW TO CREATE INDEXES

Sweetynewb 4-Feb-13 2:58am

ok i will try to do this. Thank you.

Irbaz Haider Hashmi 4-Feb-13 2:54am

Remove this
EC.eid in (select LIDList FROM #2)
and add in inner join
inner join #2 t2 on t2.LIDList = EC.eid

Mike Meinz 1-Feb-13 9:00am

Any "key" specified in an inner join clause should have an index.

1 solution

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)

Rob Branaghan · Answer 1 · 2013-02-01T04:34:00

Dont use views. Views are slow....very slow....

You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.

With regards to your SQL I think one of the ways you could speed it up is placing your WHERE in the Inner Join.

SQL

CREATE Procedure getAdminReport   -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012                              
(                                                              
@eid varchar(500),                                      
@from_month int,                                                 
@to_month int,                                                        
@from_year int,                                                          
@to_year int,      
@desg int= null                                                            
)                                                              
as                                                                                                     
                                        
CREATE TABLE #2                                         
(                                         
LIDList Int                                         
)                                         
                                        
-- Optional index on the temp table                                         
CREATE INDEX idx1 ON #2 (LIDList)                                         
INSERT INTO #2                                         
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,                                         
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList                                         
FROM tblToolsStringParserCounter                                         
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +                                         
@eid + ',' , ID - 1, 1) = ','                                         
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0                                         
                                        
                                        
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,                      
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,                                                                  
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,                                                                     
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,                                                                  
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,          
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,                                                                         
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,                                                
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,          
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full                                              
from doc_business_data DB                                                                  
inner join educator_call_dtls EC on DB.call_id=EC.call_id                                                                  
inner join doctor_master DM on DM.doc_id=DB.doc_id                                                           
inner join educator_master ED on ED.eid=EC.eid  
-- ##########################################################################################
-- CHANGES BELOW
AND ED.designation <> 'BM' 
AND ED.designation <> 'SM' 
AND ED.designation <> 'SuperAdmin'                                                          
-- ##########################################################################################
inner join educator_state_master city on city.state_city_id=ED.city_id                      
inner join educator_state_master region on region.state_city_id=city.parent_id                      
inner join educator_state_master st on st.state_city_id = region.parent_id                                                                      
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and                                    
(((year(EC.call_date) > @from_year) OR

What that should do is narrow down the number of records needed in the join. If you can do that with more of the joins this will help a lot!

If you imagine you have 1,000,000 rows, but only 350,000 of them are the ones you are interested in, only joining on the 350,000 will be faster!

Good Luck!