@ninja's_RGR'us

ya...u was right.."world is full of surprises..."

like i again I got the surprise of 58 sec execution time:-D

and also seems result is not correct

u can see it...

@ninja's_RGR'us

Wht u suggest to do?

@frfernan

Here it is Execution Plan of Query using Primary Keys in group By and Order By Clause...nd now it is executed in 54 Sec

Hi Chris

could u plz tell me wht do u mean by

"Now, with the third query removed from your batch, how long does the batch take to run? I'd expect about...

@frfernan

Ordernumber is also unique key in both the tables,

and i have also try to join the qry on the basees of cono,ordersuf,orderno that are the primary keys,

but still it is...

Thx for ur advice, regarding force indexing :-),now i m not using it.

Now query is taking 58 sec to execute,find the attached paln and qry as well

@chris-2

Here it is New Execution Plan For Query with Index having Prodline Column at the Top,

Now this Query is taking 35 sec to exceute before it was executed...

This query is executed in 55 sec.

and at the same time i again execute the query with Ix_new that was taking 30 sec,(may be there was a n/w problem at...

Now Query along with index ,is executed in 56 sec.

Please find the actual exection paln and Index used is:

CREATE NONCLUSTERED INDEX [IX_New] ON [dbo].[orderlines]

(

[Cono] ASC,

[oetype] ASC,

[transtype]...

Order lines SubQuery is Executed in 40 sec without using Index,

and after using index it took 134 sec.

Here it is Execution Plan For Both.

* By mistake I have attached join...

Thx for reply chris

Now Query is Grouped By, 5 columns and it took 60 sec to execute complete join Query.

As u suggest i have created index with columns in where...

@chris-2 I did't get u on this

"to establish which if any can be dispensed with by using MAX() around the column in the output list, whilst retaining the...

@chris-2 After applying ur second solution,it seems query is not genrating the right results.

and also took the same time to execute and returning less numbers of rows.

Here it is Execution Plan after applying Chris 1st alternative.

Your query worked great when I tried with distinct.

But still its taking 83 Seconds to execute.

Do you have any other idea to make it more optimize.

Thanks in advance