performance - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

performance

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-06 : 19:16:07

I have veiw which has select query from a one table is used for report and had some calculations.
it is taking much time to retrieve the data which had nearly 150 million rows.
there is an index defined on already on table..

Can i create an index on a view does it helps.

Thanks for help in advance

Sachin.Nand

2937 Posts

Posted - 2011-11-07 : 02:42:55

You have to create Indexed Views.

Refer http://msdn.microsoft.com/en-us/library/ms191432.aspx for more information

PBUH

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-07 : 08:26:36

Thanks Sachin

Can you suggest based on the below view on which column an index needs to be created.

I have already create a index on the st_date



create view dbo.vw_student_prct as

select TOP(100) percent a.totalstudents,
b.totalscience students,
cast(cast((case when b.totalsciencesstudents is null then 0 else b.totalsciencestudents end)as decimal(8,2))
 / (case when a.totalstudents is null then 1 else a.totalstudents end )* 100 as decimal(8,2))  as scienceprnct,

--
--
--
b.st_date

from
(
select st_date,count(student_id) as totalstudents
from student as studnt1
where (ds_name like '%Doere%')
group by st_date) as a inner join
(
select st_date,count(student_id) as totalsciencestudents
from student as studnt
where (ss_name like '%physical%')
group by st_date) as b on a.student_date = b.student_date inner join
(
select st_date,count(student_id) as totalMathstudents
from student as studnt2
where (ss_name like '%math%')
group by st_date) as c on c.student_date = b.student_date inner join
(
select st_date,count(student_id) as totalenglishstudents
from student as studnt1
where (ts_name like '%eng%')
group by st_date) as d on b.student_date = d.student_date inner join

(
select st_date,count(student_id) as totalspecialstudystudents
from student as studnt1
where (ts_name like '%speci;%') OR (ts_name like '%speci;%') OR (ts_name like '%speci')  OR (ts_name like '%speciA%')
OR (ts_name like '%speciD%') OR (ts_name like '%speciTo%') OR (ts_name like '%speciK%')
group by st_date) as e on b.student_date = b.student_date inner join

(
select st_date,count(student_id) as totalkainistudents
from student as studnt3
where (Ks_name like '%kaini%') and (Lstatus ='Y'AND (kstatus ='Y')
OR (ss_name like '%speci;%')  and (lstatus = 'Y') AND (kstatus ='Y') 
OR (ks_name like '%seci')  OR (ts_name like '%VteciA%')
group by st_date) as f on b.student_date = f.student_date 
)
order by b.st_date

Lewie
Starting Member

42 Posts

Posted - 2011-11-07 : 08:36:49

I dont think the index will help you in this case. Indexs are not used when the 'Like' is used

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-07 : 08:36:51

Wow, I think optimisation of the view is the way to go here, not indexing.

Start by taking out the TOP (100) percent and Order By, since SQL won't honour them anyway.
Are the leading wildcards really necessary? They're preventing index seeks.

An index on just st_date isn't going to help. Maybe (ts_name, st_date, student_date) include (student_id) and (ss_name, st_date, student_date) include (student_id) and (ks_name, st_date, student_date) include (student_id) and (ds_name, st_date, student_date) include (student_id)
Those are all on the base table

The include of student_id won't be necessary if you can change count(student_id) to count(*), providing they meaning doesn't change. If student_id can't be null, then make that change.

Also, you've got at least one accidental cross join in there:
as e on b.student_date = b.student_date
That should probably be b.student_date = e.student_date

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-07 : 08:37:47

quote:
Originally posted by Lewie

I dont think the index will help you in this case. Indexs are not used when the 'Like' is used

It's not Like that prevents index seeks, it's Likes with leading wildcards. It'll still use the index, but for a scan, not a seek

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-07 : 09:30:13

Thanks for yur immediate response.
I will take out TOP (100) percent and Order By from view.

Wildcards is required for business requirement.
There is only on base table called student.

Do you want me to try out creating the index on the 'like columns' also.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-07 : 15:45:19

You missed possibly the most important thing Gail said

quote:

Also, you've got at least one accidental cross join in there:
as e on b.student_date = b.student_date
That should probably be b.student_date = e.student_date

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-07 : 17:20:44

Thanks Charlie.

I have upated it but still not able improve the performance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-07 : 17:36:53

As mentioned, if you need the preceding wildcard, then you are kinda stuck with tables scans. Sql might be smart enough to optimize some of these nearly identical queries, but you might try combining some of them together. For example something like:

select 
    st_date,
    count(CASE WHEN ds_name like '%Doere%' THEN student_id ELSE NULL END) as totalstudents,
    count(CASE WHEN ss_name like '%physical%' THEN student_id ELSE NULL END) as totalsciencestudents,
    count(CASE WHEN ss_name like '%math%' THEN student_id ELSE NULL END) as totalMathstudents,
    ...
from student as studnt1
group by st_date

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-08 : 04:28:43

quote:
Originally posted by sqlfresher2k7

I have upated it but still not able improve the performance.

Did you create the indexes I suggested?

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-08 : 12:11:46

I have created an index which has not shown any improvements.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-08 : 13:43:40

it's the LIKE's that are killing you

Do you really need them? It looks like you are specifying types of students by the class they are taking. I'm assuming then you could turn them into = checks How many different labels can their be?

If necessary you may want to consider updating the labels to all be the same. I suspect that this is a normalisation failure and that if you had a normalised db with a join to a class table (or a speciality table / whatever) and made all the different varieties of english eng / Eng / whatever all be 'English'

Then this problem would just go away and you could use index seeks on the speciality name

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-11-08 : 15:58:26

quote:

it is taking much time to retrieve the data which had nearly 150 million rows.

Your query returns 150 million rows? If so that's going to take some time no matter what you do.

Are you sure it is your view that is the issue? The reason I ask is it could be the way you are using the view in your select statement. I find Views are often misunderstood. When you create a query that involves views, SQL Server creates an execution plan based on the query as a whole (i.e. as if your View was substituted for SQL). So you need to look at your query as if it was one SQL statement. Looking at the view in isolation is still good but looking at the query as a whole is essential.

I am not disagreeing with the previous assessment regarding like '%...' - maybe you need to make extra columns like ss_name_math - bit.

Lewie
Starting Member

42 Posts

Posted - 2011-11-09 : 05:06:52

This may improve it by a factor of 6 or more but its a worth a shot

select st_date,SUm(case when ds_name like '%Doere%' then 1 else 0 end) as totalstudents,
SUM(case when ss_name like '%physical%' then 1 else 0 end) as students,
SUM(case when ss_name like '%math%' then 1 else 0 end) as totalMathstudents,
SUM(case when ts_name like '%eng%' then 1 else 0 end) AS totalenglishstudents,
sum(case when (ts_name like '%speci;%') OR (ts_name like '%speci;%') OR (ts_name like '%speci') OR (ts_name like '%speciA%')
OR (ts_name like '%speciD%') OR (ts_name like '%speciTo%') OR (ts_name like '%speciK%')
then 1 else 0 end) AS totalspecialstudystudents,
SUM(CASE WHEN (Ks_name like '%kaini%') and (Lstatus ='Y'AND (kstatus ='Y')
OR (ss_name like '%speci;%') and (lstatus = 'Y') AND (kstatus ='Y')
OR (ks_name like '%seci') OR (ts_name like '%VteciA%') THEN 1 ELSE 0 END) AS totalkainistudents
from Student
GROUP BY st_date

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-11 : 16:48:41

Thanks ..

indexes on view will not allow to create for the like operators since i am using on derived tables.

i have formula to get select query


cast(cast((case when b.totalsciencesstudents is null then 0 else b.totalsciencestudents end)as decimal(8,2))
 / (case when a.totalstudents is null then 1 else a.totalstudents end )* 100 as decimal(8,2))  as scienceprnct,

I am not sure how to rewrite this above query which lewie suggested..

Any ideas.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-14 : 21:53:54

thanks Lewie..I have modified the query it looks faster..however i have got an error divide by zero



select 
cast(cast((case when b.totalsciencesstudents is null then 0 else b.totalsciencestudents end)as decimal(8,2))
 / (case when a.totalstudents is null then 1 else a.totalstudents end )* 100 as decimal(8,2))  as scienceprnct,

How should i rewrite the above query to avoid the divide by zero error encountered.

Thanks for your help.

Lewie
Starting Member

42 Posts

Posted - 2011-11-15 : 04:43:02

select
cast(cast((case when b.totalsciencesstudents is null then 0 else b.totalsciencestudents end)as decimal(8,2))
/ (case when a.totalstudents is null or a.totalstudents = 0 then 1 else a.totalstudents end )* 100 as decimal(8,2)) as scienceprnct,

You need to check your results with the orginal query you had as the results may be different due to the fact that if some of your orginal SELECT's did not return any results for a day, that day would not be included the result set (inner join to other derived tables)

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-15 : 13:00:23

Thanks Lewie.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources