Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Reduce the execution time of the query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sutr_kam
Starting Member

India
5 Posts

Posted - 05/13/2011 :  11:04:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/13/2011 :  11:20:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 05/13/2011 :  11:22:43  Show Profile  Visit webfred's Homepage  Reply with Quote
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



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

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 05/13/2011 :  11:23:32  Show Profile  Visit webfred's Homepage  Reply with Quote



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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/13/2011 :  11:25:49  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 05/13/2011 11:27:10
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 05/13/2011 :  11:27:45  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/13/2011 :  11:31:19  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
What indexes exist on the tables in question?

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

sutr_kam
Starting Member

India
5 Posts

Posted - 05/13/2011 :  13:26:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 05/14/2011 :  09:35:51  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/14/2011 :  11:35:46  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Indexes?

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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 05/15/2011 :  13:33:25  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
5 Posts

Posted - 05/16/2011 :  01:58:55  Show Profile  Reply with Quote
yes, indexes are there for sure, but those information not available to me
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 05/16/2011 :  02:11:12  Show Profile  Visit webfred's Homepage  Reply with Quote
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

India
5 Posts

Posted - 05/16/2011 :  02:38:41  Show Profile  Reply with Quote
thanks sunitabeck, webfred
those some what decreased the execution time..

(Infact I am trying to decrease it further)
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 05/16/2011 :  04:05:47  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/16/2011 :  07:15:15  Show Profile  Reply with Quote
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

India
1 Posts

Posted - 06/07/2011 :  07:26:26  Show Profile  Reply with Quote
Hello Sir,
My query taking 20 minutes for execution,I wan to reduce it,Please,help me.

pkpkpk
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 06/07/2011 :  08:34:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/07/2011 :  09:07:39  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000