table with more than 1.5 Lakh record simplet query taking too much time

Question

table with more than 1.5 Lakh record simplet query taking too much time

janki0430

SSC Veteran

Points: 267
More actions
October 30, 2013 at 7:19 am

#278648

AssembleTrn is table with about 170157 records
when i run this query it will take about more than 1.30 minute
plesae help me in getting recoeds in faster wat
select sum(Qty) as qty,at.POConfirmID from AssembleTrn at where at.Type =1 and at.status=1
group by POConfirmID
thank in advance
from
Janki Shah

Viewing 7 posts - 1 through 6 (of 6 total)

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

janki0430 (10/30/2013)
AssembleTrn is table with about 170157 records
when i run this query it will take about more than 1.30 minute
plesae help me in getting recoeds in faster wat
select sum(Qty) as qty,at.POConfirmID from AssembleTrn at where at.Type =1 and at.status=1
group by POConfirmID
thank in advance
from
Janki Shah

can you please provide some details like table structure...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 2

kapil_kk (10/30/2013)
janki0430 (10/30/2013)
AssembleTrn is table with about 170157 records
when i run this query it will take about more than 1.30 minute
plesae help me in getting recoeds in faster wat
select sum(Qty) as qty,at.POConfirmID from AssembleTrn at where at.Type =1 and at.status=1
group by POConfirmID
thank in advance
from
Janki Shah
can you please provide some details like table structure...

and any indexes that may be on the table. You can create a covering index for this query on AssembleTrn with columns Type, Status, POConfirmID and Qty

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You should look to the execution plan to understand what is happening in your query and how the optimizer is choosing to select your data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

janki0430 (10/30/2013)
AssembleTrn is table with about 170157 records
when i run this query it will take about more than 1.30 minute
plesae help me in getting recoeds in faster wat
select sum(Qty) as qty,at.POConfirmID from AssembleTrn at where at.Type =1 and at.status=1
group by POConfirmID
thank in advance
from
Janki Shah

Add an index that contains POConfirmID, Type, and Status in that order (for a first guess) with an INCLUDE for Qty.

Also, get into the habit of always using the 2 part naming convention on all table references.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Keith Tate (10/30/2013)
kapil_kk (10/30/2013)
janki0430 (10/30/2013)
AssembleTrn is table with about 170157 records
when i run this query it will take about more than 1.30 minute
plesae help me in getting recoeds in faster wat
select sum(Qty) as qty,at.POConfirmID from AssembleTrn at where at.Type =1 and at.status=1
group by POConfirmID
thank in advance
from
Janki Shah
can you please provide some details like table structure...
and any indexes that may be on the table. You can create a covering index for this query on AssembleTrn with columns Type, Status, POConfirmID and Qty

Be careful now. That would give you an index with a leading column that has very low cardinality. Inserts to the table will cause massive page/extent splits that could (likely will) actually lead to massive timeouts on the GUI side of the house.