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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

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

--
Gail Shaw
SQL Server MVP
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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!!
Go to Top of Page
   

- Advertisement -