replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankx
Can you be a bit more precise what you want to do? You want to change the
showplan to then force a different plan? You can try, but you should be
careful.
Best regards
Michael
<Preeti.s83@.gmail.com> wrote in message
news:1170427166.173456.152840@.j27g2000cwj.googlegr oups.com...
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankx
Hi
"Preeti.s83@.gmail.com" wrote:

> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =
A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF

> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.

> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John
sql

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxHi
"Preeti.s83@.gmail.com" wrote:

> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =
A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF

> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.

> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxCan you be a bit more precise what you want to do? You want to change the
showplan to then force a different plan? You can try, but you should be
careful.
Best regards
Michael
<Preeti.s83@.gmail.com> wrote in message
news:1170427166.173456.152840@.j27g2000cwj.googlegroups.com...
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...

for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...

so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...

thankx(Preeti.s83@.gmail.com) writes:

Quote:

Originally Posted by

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
>
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
>
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...

You don't say what the purpose would be to change the XML document. When
you talk about "join information get stored in xml" I get a bit nervous.
The XML document is just a representation of the query plan; it's not
a storage of its own.

That said, there is a point with retrieving a query plan and modify it
since you can use it in a plan guide, or with the query hint USE PLAN.
This is quite an advanced feature, and requires good understanding
of query plans to be successful. There is no risk that you will
cause incorrect results with a plan guide, the optimizer still
validates that the plan is correct, in which case it discards the
plan.

I have tried this sort of operation myself, and all I can recommend
is that you look at plans of the type you want to achieve and
play around. It will probably take some time, but you learn a lot
along the way. To get started, you can use query hints to force a
certain type of join, so you get to see different types of joins.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

replacing join operation in XML

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect...
for this we need to understand the way join information get stored in
xml ...and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankxHi
"Preeti.s83@.gmail.com" wrote:
> is it possible to replace join type ( for eg. nested loop with hash
> join and so on) in xml plan...
> we will fst take plan in xml format ( show xml plan ) and then we will
> replace join with other one .. and then execute this plane .. to see
> the effect...
You can use JOIN hints to force a specific type of join.
e.g.
USE ADVENTUREWORKS
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress A ON E.EmployeeID = A.EmployeeID
/* Should be merge join */
SELECT *
FROM HumanResources.Employee E
INNER HASH JOIN HumanResources.EmployeeAddress A ON E.EmployeeID =A.EmployeeID
/* Will be hash join */
SET SHOWPLAN_XML OFF
> for this we need to understand the way join information get stored in
> xml ...and then replace...
> for any extra info we can put garbage .. which will be filled by
> actual value while execution...
Showing the plan is not the same as storing it. You can not change the
output from the SHOWPLAN to effect the way a query is executed.
> so my question is : is it possible...( i think it is very much
> possible)
> and if yes then guide me... from where i can get these join
> format .. so that i can replace...
> or just running query on some dataset for both join type and thn
> comparing the way the get stored .. is sufficient to convert...
> thankx
>
Hopefully I have understood your question!
John

Repeating data on every page's header

My report has 9 subreports. When main report has 3 records then this report
goes into loop and run these 9 subreports 3 times. But I want the heading
in all the pages and should change the data as record changes.. This report
can be 30 - 40 pages long.
I am giving you an example on my report.
Suppose I want to show the detail of 3 staffs.
A, B and C
In heading, I want to show the name, age and gender of staff which will
change on next record.
In detail, In 1st subreport shows the brothers of the staff and 2nd
subreport shows the sisters of that staff etc.
I want to show the heading in all the page.
ThanksYou would have created a group in the table.. you have to check "Repeat
Group Header" checkbox.
Include Name,age, gender in Group Header in the table.
Edit Group -> General Tab -> Check Repeat Group Header.
hth
"Savita Gupta" <savita.r.gupta@.au.pwc.com> wrote in message
news:e6XRIZfoEHA.324@.TK2MSFTNGP11.phx.gbl...
> My report has 9 subreports. When main report has 3 records then this
> report
> goes into loop and run these 9 subreports 3 times. But I want the heading
> in all the pages and should change the data as record changes.. This
> report
> can be 30 - 40 pages long.
> I am giving you an example on my report.
> Suppose I want to show the detail of 3 staffs.
> A, B and C
> In heading, I want to show the name, age and gender of staff which will
> change on next record.
> In detail, In 1st subreport shows the brothers of the staff and 2nd
> subreport shows the sisters of that staff etc.
> I want to show the heading in all the page.
> Thanks
>
>|||Can anyone help me.
"Savita Gupta" <savita.r.gupta@.au.pwc.com> wrote in message
news:e6XRIZfoEHA.324@.TK2MSFTNGP11.phx.gbl...
> My report has 9 subreports. When main report has 3 records then this
report
> goes into loop and run these 9 subreports 3 times. But I want the heading
> in all the pages and should change the data as record changes.. This
report
> can be 30 - 40 pages long.
> I am giving you an example on my report.
> Suppose I want to show the detail of 3 staffs.
> A, B and C
> In heading, I want to show the name, age and gender of staff which will
> change on next record.
> In detail, In 1st subreport shows the brothers of the staff and 2nd
> subreport shows the sisters of that staff etc.
> I want to show the heading in all the page.
> Thanks
>
>

repeat a statement in a loop

hello,
i need to repeat the update statement for all days in the actual month
example:if i run today (19.09.2005) the job - it must run 19 times for
the value of
GETDATE() to GetDate() -19. How can be done this in a loop.
In the update statement is GetDate() used in the where condition and it must
be replaced with the values
in the first loop with GetDate() -1
in the second loop with GetDate() - 2
...
in the last GetDate()-19
The update statement looks like :
UPDATE [table1]
SET [OrderDate]=(
SELECT DISTINCT Top 1 OrderCreationDate
FROM table2
WHERE (SalesDocNr = table1.OrderNr)
)
WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
FROM table1 INNER JOIN
table2 ON table1.OrderNr = table2.SalesDocNr
WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
CONVERT(VarChar(10), GETDATE() , 104))
)
thanks
XavierWhile can use a WHILE loop or a cursor to get this done as you want, a
better approach is to use a single UPDATE statement which can update all the
rows.
Based on the sample code you posted, it is hard to work out such a solution,
so please refer to www.aspfaq.com/5006 and post relevant information for
others to reproduce your problem.
Anith|||For a looped approach try this for your basic loop. @.date should be used in
place of GETDATE() in your update query. Replace the PRINT statement with
your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
ONLY. You may want a better method than what I did.
Mike
DECLARE @.day int
DECLARE @.date datetime
SET @.day = DAY(GETDATE())
SET @.date = GETDATE()
SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
WHILE @.day > 0
BEGIN
PRINT @.date
SET @.day = @.day - 1
SET @.date = DATEADD(d, -1, @.date)
END
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it
> must
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required
results.
I'm certain it's possible to do what you want in a single UPDATE statement
without a loop. However, the UPDATE you have posted may not give reliable
results because you've used TOP without ORDER BY. For that reason it's trick
y
to guess what you intended by it (even though it may not always work as you
wanted).
I expect the solution will look like
:
UPDATE Table1
SET orderdate =
(
..
)
WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)
AND insertdate <= CURRENT_TIMESTAMP ;
David Portas
SQL Server MVP
--
"Xavier" wrote:

> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it mu
st
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||it works,
thanks Mike
"Mike Jansen" wrote:

> For a looped approach try this for your basic loop. @.date should be used
in
> place of GETDATE() in your update query. Replace the PRINT statement with
> your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
> ONLY. You may want a better method than what I did.
> Mike
>
> DECLARE @.day int
> DECLARE @.date datetime
>
> SET @.day = DAY(GETDATE())
> SET @.date = GETDATE()
> SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
> WHILE @.day > 0
> BEGIN
> PRINT @.date
> SET @.day = @.day - 1
> SET @.date = DATEADD(d, -1, @.date)
> END
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
>
>|||> While can use a WHILE loop or a cursor to get this done as you want, a
> better approach is to use a single UPDATE statement which can update all
> the rows.
I don't think that's really possible without SQL Server 2005 (or perhaps a
_really_ messed up query in SQL 2000), in which case it would be something
like this as a base:
WITH DAYS(DayValue, Remaining) AS
(
SELECT
CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,
DAY(GETDATE()) - 1
UNION ALL
SELECT
DATEADD(d, -1, r.DayValue) AS DayValue,
r.Remaining - 1
FROM
DAYS r
WHERE
r.Remaining > 0
)
SELECT * FROM DAYS;
Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.
Mike