SQL QUERY TAKES more than 4 mnts....any help me plz..

Question

SQL QUERY TAKES more than 4 mnts....any help me plz..

Victory

SSC Rookie

Points: 39
More actions
March 9, 2010 at 7:03 am

#221408

Can Anyone help me little more..
I have execute one query as follows..
SELECT BookNo,BookAbbrDia,BookDescDia,RefModDescDC
FROM BookMaster
Where (RefModDescDC ='Page. Line') and
(BookNo IN(select distinct BookNo from slipmaster
where SLVID In (Select SLVID from Slipvocmaster
where (BinNo Between '0164' and '0164'))))
order by BookDescDia
No of records:
Bookmaster - 1467
Slipvocmaster - more then 1 lakh
slipmaster - more then 50 thousand
Now the problem is.This query takes more than 5 minutes to run on
SQL Server machine..
and
give Timeout exception for Remote machine..
But I give specific values like this..
SELECT BookNo,BookAbbrDia,BookDescDia,RefModDescDC
FROM BookMaster
Where (BookNo IN(select distinct BookNo from slipmaster
where SLVID between (Select Min(SLVID) from tmp_Slipvocmaster where BinNo =164)
and (Select Max(SLVID) from tmp_Slipvocmaster where BinNo =164)))
and (RefModDescDC ='Page. Line')
order by BookDescDia
It gives me result back in 45 seconds..
Whats wrong with above query..
Can u help me...
PLEASE ITS URGENT.....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

The query probably can run much faster, we can help you tune it if you provide the information we need.

We need the DDL scripts, with sample data in a ready consumable format, along with the execution plan of the poor performing query. Even 45 seconds is a long time for a query, depending on the amount of rows you actually have.

Please provide the desired information in a ready consumable format, and we'll see what we can do to help you. And by the way, you will end up having some testing to do, so it does not sound that much urgent... I don't see what's urgent...

Cheers,

J-F

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Please provide the DDL for the tables including indexes and attach the actual execution plans for both queries. You can save them as .sqlplan files, zip them and attach them to your post.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

After reformatting the code to make it more readable for me (I love indention and white space) I do see one difference between the two queries, but not sure if that is the problem. the first one has an order by clause and the second one doesn't. If the data volume is large enough, this could be (but may not be) part of the problem. we really need to see the actual execution plans for both of the queries.

vani_r14 SSCrazy Points: 2843 More actions · Answer 5

Hi

I recently had the same issue with query taking about 3 mins or so... I then decided to slice the query and put it into Temp Tables which made it faster. Now i get ther results in about a minute which is still not great but improved none the less.. May be try doing that.. Am no expert but this is what I did yesterday

Cheers

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Lynn Pettis (3/9/2010)
After reformatting the code to make it more readable for me (I love indention and white space) I do see one difference between the two queries, but not sure if that is the problem. the first one has an order by clause and the second one doesn't. If the data volume is large enough, this could be (but may not be) part of the problem. we really need to see the actual execution plans for both of the queries.

I agree with Lynn here. With the actual execution plans it will be much easier to determine what the sticking/ slow points of your query really is/are.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Even your 1 lakh table is comparatively small for a lot of people and should probably be nearly instantaneous. But, "It Depends" on things we cannot see from here. Take a look at the the second link in both Lynn's and my signature lines at the bottom of each of our posts for how to get some awesome help with your performance problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Post your table and index definition along with exec. plan

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Agree with above comments. One thing that jumps out immediately: using DISTINCT in an EXISTS or IN clause just slows it down.

Also - how many rows are in the temporary table (tmp_Slipvocmaster) as compared to the permanent one (Slipvocmaster )

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?