Reduce the execution time of the query

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 2000 Forums

SQL Server Development (2000)

Reduce the execution time of the query

Author

Topic

sutr_kam
Starting Member

5 Posts

Posted - 2011-05-13 : 11:04:52

Hi all this is my executing query. But it taking very large time.
Can anyone changed this to optimized one this is very urgent
SELECT DISTINCT
case.column1, case.column2
FROM case
WHERE
case.mainid = 34 AND
case.Status = 'Open' And
case.caseID in
(select caseID from owner where ownerid in (1120,1121,1122)) AND
(SELECT count(*) from wtab w,ctab c where
detail like '%heman%'
and w.caseid = case.caseid
and w.ctabid = c.ctabid
and c.status = 1
and w.ceid=3129)>0
Order By case.openDate

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 11:20:54

You should look at the query plan to see what part of your query is taking up the time. But, one thing that stands out is that you are calculating a count just to check if it is greater than zero. That perhaps could be optimized as shown below:


SELECT DISTINCT 
case.column1, case.column2 
FROM case
WHERE 
case.mainid = 34 AND 
case.Status = 'Open' And 
case.caseID in
(select caseID from owner where ownerid in (1120,1121,1122)) AND 
EXISTS (SELECT *  from wtab w,ctab c where
(SELECT count(*) from wtab w,ctab c where 
detail like '%heman%'
and w.caseid = case.caseid 
and w.ctabid = c.ctabid 
and c.status = 1 
and w.ceid=3129) >0 
Order By case.openDate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 11:22:43

[code]SELECT DISTINCT
ca.column1,
ca.column2
FROM [case] ca
join [owner] o
on o.ownerid in (1120,1121,1122)
and o.caseID = ca.caseID

WHERE ca.mainid = 34
AND ca.[Status] = 'Open'
AND exists(SELECT * from wtab w,ctab c
where detail like '%heman%'
and w.caseid = ca.caseid
and w.ctabid = c.ctabid
and c.[status] = 1
and w.ceid=3129)

Order By ca.openDate
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 11:23:32

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 11:25:49

Oh darn!! Sorry about that webfred

But, your query also avoids the inner select and uses a join, which is better than leaving it as it is, which is what I did.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 11:27:45

you are allowed

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-13 : 11:31:19

What indexes exist on the tables in question?

--
Gail Shaw
SQL Server MVP

sutr_kam
Starting Member

5 Posts

Posted - 2011-05-13 : 13:26:32

quote:
Originally posted by GilaMonster

What indexes exist on the tables in question?

--
Gail Shaw
SQL Server MVP

Hi Gail,
I don't want to spam the threads.

those two queries are different. If you want you can check
after the line

SELECT count(*) from wtab w,ctab c
on two queries

Actually the thread which you locked is the main query.
I just changed that to query above i posted,
but later i checked and find that i have posted second query so
i posted a new thread

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-14 : 09:35:51

Could you post the executio plan?
Also , have you tries to update the statistics and then rerun?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-14 : 11:35:46

Indexes?

--
Gail Shaw
SQL Server MVP

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-05-15 : 13:33:25

Agreed , could you post indexes , ddl, and some sample data .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

sutr_kam
Starting Member

5 Posts

Posted - 2011-05-16 : 01:58:55

yes, indexes are there for sure, but those information not available to me

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-16 : 02:11:12

Did you try sunita's or my solution?

No, you're never too old to Yak'n'Roll if you're too young to die.

sutr_kam
Starting Member

5 Posts

Posted - 2011-05-16 : 02:38:41

thanks sunitabeck, webfred
those some what decreased the execution time..

(Infact I am trying to decrease it further)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-16 : 04:05:47

I'm sure there are indexes, but without seeing the definitions of those indexes it's impossible to advise you on modifying them if necessary. Please post the definitions of the indexes.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 07:15:15

quote:
Originally posted by sutr_kam

yes, indexes are there for sure, but those information not available to me

I know I am repeating what everyone else who responded to your post have been saying about the indexes, but without those, there isn't much anyone can do to help. If you don't have the proper access, you can always ask your DBA to help you get the indexes. There is a description of how to get the indexes in Brett's blog here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

A book I have been reading over the past few days is "Microsoft SQL Server 2008 Internals" by Kalen Delaney et.al. In the book, there is a chapter on Indexes, which starts out like this:

"Microsoft SQL Server doesn’t have a configuration option or a knob that allows you to make it run faster; there’s no magic bullet. However, indexes—when created and designed appropriately—are probably the closest thing to a magic bullet. The right index, created for the right query, can take query execution time from hours down to seconds."

So when people are requesting you to post the indexes, they are not kidding. That is THE one thing that will enable them to advise you on how to make the query run faster.

Priyap
Starting Member

1 Post

Posted - 2011-06-07 : 07:26:26

Hello Sir,
My query taking 20 minutes for execution,I wan to reduce it,Please,help me.

pkpkpk

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-06-07 : 08:34:35

Hello Sir. I want to win the Lottery. Please help me.

Different odds, same success rate.

C'mon. At least provide some sort of clue as to what your problem is.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-07 : 09:07:39

Absolutely LOVE<3 THIS
...

Only thing you should have done is include the original post

Like so:

quote:
Originally posted by AndrewMurphy

Hello Sir. I want to win the Lottery. Please help me.

Different odds, same success rate.

C'mon. At least provide some sort of clue as to what your problem is.

quote:
Originally posted by Priyap
Hello Sir,
My query taking 20 minutes for execution,I wan to reduce it,Please,help me.

pkpkpk

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources