Joins Position

Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
Lalit
Can you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit
|||This is SQL Statement that performed fine.
I interchanged second and third JOINs. Both are Linked
with the first one.
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Following SQL Statement is that one that had the problem
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Lalit
|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:

> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
sql

Joins Position

Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
LalitCan you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>

Join Performance

All
I have a quick question regarding the performance of a join.
I am performing a join between two tables and one table has the opid column
as VARCHAR(100) and the other table has the opid column as VARCHAR(8). Will
there be any performance gain if I change the opid column in both tables to
VARCHAR(8).
ThanksDavid
Well , it should not be, it is more question about indexes you defined on
those columns.
"David" <David@.discussions.microsoft.com> wrote in message
news:DA2B8E32-3BAB-43D6-AF9B-0677987F2349@.microsoft.com...
> All
> I have a quick question regarding the performance of a join.
> I am performing a join between two tables and one table has the opid
> column
> as VARCHAR(100) and the other table has the opid column as VARCHAR(8).
> Will
> there be any performance gain if I change the opid column in both tables
> to
> VARCHAR(8).
> Thanks|||no performance advantages to making the columns same length.

Join Performance

All
I have a quick question regarding the performance of a join.
I am performing a join between two tables and one table has the opid column
as VARCHAR(100) and the other table has the opid column as VARCHAR(8). Will
there be any performance gain if I change the opid column in both tables to
VARCHAR(8).
ThanksDavid
Well , it should not be, it is more question about indexes you defined on
those columns.
"David" <David@.discussions.microsoft.com> wrote in message
news:DA2B8E32-3BAB-43D6-AF9B-0677987F2349@.microsoft.com...
> All
> I have a quick question regarding the performance of a join.
> I am performing a join between two tables and one table has the opid
> column
> as VARCHAR(100) and the other table has the opid column as VARCHAR(8).
> Will
> there be any performance gain if I change the opid column in both tables
> to
> VARCHAR(8).
> Thanks|||no performance advantages to making the columns same length.

Join on subquery with distinct?

Can someone give me some reasonable explanation why these two queries are performing dramatically different:

First (slow):

select
tableA.column1,
tableA.column2
from
tableA join (select distinct col1,col2,col3 from myView) vw
on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

Second (fast):

create table #tempTable (col1 varchar(200),col2 int,col3 varchar(200))

insert into #tempTable
select distinct col1,col2,col3 from myView

select

tableA.column1,

tableA.column2

from

tableA join #tempTable vw

on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

SQL Server 2005 Express
Win2K3 Server

Regards,
Marko Simic

Marko:

I can't really mock this without knowing how the view is structured. It is not possible to mock a query plan that involves a view without knowing the components of the view. Can you give details of the makeup of the view?

Dave

|||

Can you post the plan for your queries, such as:

set showplan_text on
--set statistics profile on
go

--your query--

go
--set statistics profile off
set showplan_text off

The showplan one gives you estimated, the profile one gives you actual plan. That will be very helpful in this.

|||

When I executed queries (for generating estimated and actual plans), suddenly problematic one executed much faster.
Most probably, problem was about server's hardware performance, like insufficient RAM, at the moment of query execution.
My assumption is based on the fact that problematic query is using subquery, which by many views, is using more memory then query with temp table.

Anyway, I am sending you a link to XLS file with all statistics (of query with subquery) you asked, in case that something else could cause strange behavior and can be seen from this.

http://139.142.50.130/test/statistics.xls

Thank you for your efforts

|||

Without both to compare it is really impossible to say too confidently, but there are a couple of really large looking table scans (Clustered Index Scan = ordered table scan) followed by a hash match join. This kind of operation can be greatly affected by hardware performance/contention, as the hash match executes faster with more ram.

Comparing the plans might shed some light on it, but if it runs adequately now... :)

|||The optimiser will expand out queries like the first one. Just one of those annoying things about it. You could improve the speed by putting changing it to "select top (999999) distinct col1, col2, col3 from myView", because that will force the server to materialise the table first. Any number will do, so long as it's larger than the number of rows you expect to get out (otherwise you'll lose rows).

Which effectively is the same as populating a temporary table. :)

Rob|||

First of all, thank you all for your replies.
Rob, will you please tell me, what did you mean by "materialise table".
Did you mean that database would move view from memory to hard drive?
And, if that is true, would db engine make that table within tempdb space (as temp tables are) or in original database space?

|||When I say 'materialise', I just mean that the engine will create the table in memory and then join the other tables to it. It won't move it from memory to the hard drive - it will stay in RAM.

Rob|||

ok.Thanks. Then it works as I expected.

|||Ah, great. Glad to help. Can you mark it as an answer please? I know MS are keen for this to happen on all threads.|||With pleasure :) Done|||:) Cheers