Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C#
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
 
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 22:55pm
Edited 1-Feb-13 3:15am
CHill6067K
v3
Comments
RobBranaghan at 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/ at 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.
RobBranaghan at 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 Tolouee at 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 at 1-Feb-13 4:53am
   
but i need to use inner join to get data
Tejas Vaishnav at 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 at 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 at 1-Feb-13 7:38am
   
please use improve question and past your code in your question body
Irbaz Haider Hashmi at 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 at 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 at 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 at 4-Feb-13 2:58am
   
ok i will try to do this. Thank you.
Irbaz Haider Hashmi at 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 at 1-Feb-13 9:00am
   
Any "key" specified in an inner join clause should have an index.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
 
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!
  Permalink  
Comments
Sweetynewb at 4-Feb-13 0:37am
   
Thank you for suggestion. i am trying that. Did you saw my whole query?
RobBranaghan at 4-Feb-13 4:01am
   
There appears to be some missing... There is an OR, but nothing more.
Sweetynewb at 4-Feb-13 6:44am
   
hmm. because of limitation may be.. ok i am posting whole query below (Please look into it)-
 
CREATE Procedure getBFEAdminReport_MAIN_HQ -- '74',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 (year(EC.call_date)=@from_year AND month(EC.call_date) >=@from_mo
RobBranaghan at 5-Feb-13 4:27am
   
That last bit in the where clause is a little confusing, why aren't you just using Year(EC.call_date) >= @from_year AND MONTH(EC.call_date) >= @from_month?

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

  Print Answers RSS
0 Marcin Kozub 330
1 OriginalGriff 256
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 1 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100