Export - Excel Error

Hi All,
i am not able to export the report as excel. it showing the below
error:
i am using the MSRS2000. i have installed microsoft office excel 2003.
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\Documents and
Settings\vinod_banagani\Desktop\AppointmentLog.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible.
Excel attempted to recover your formulas and values, but some data may
have been lost or corrupted.
is RS2000 supports Excel 2000 only?
if anyone have faced this problem earlier, please tell me the solution.
Thanks in advance for your help...Thanks for your response Bruce.
i have upgraded SP2.
When I select the export to excel file format, I get the following in
the
excel file: "Data Regions within table/matrix cells are
ignored".
i am using tables and rectangles inside the table... and texboxes
inside the rectangle in my report. the report is showing perfectly but
when i select the export to excel file, it is throwing an error.
Is this a known bug in RS..
Any info will be appreciated.
Thanks,
Vinod
vinodsh_82@.hotmail.com wrote:
> Hi All,
>
> i am not able to export the report as excel. it showing the below
> error:
>
> i am using the MSRS2000. i have installed microsoft office excel 2003.
>
> Microsoft Office Excel File Repair Log
>
> Errors were detected in file 'C:\Documents and
> Settings\vinod_banagani\Desktop\AppointmentLog.xls'
> The following is a list of repairs:
>
> Damage to the file was so extensive that repairs were not possible.
> Excel attempted to recover your formulas and values, but some data may
> have been lost or corrupted.
>
> is RS2000 supports Excel 2000 only?
>
> if anyone have faced this problem earlier, please tell me the solution.
>
> Thanks in advance for your help...|||Well, at least you got a better error message. It sounds like whatever
combination of tables, rectangles, tables within tables etc causes a
problem.
I don't know if 2005 would work better or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<vinodsh_82@.hotmail.com> wrote in message
news:1147799266.536977.43720@.v46g2000cwv.googlegroups.com...
> Thanks for your response Bruce.
> i have upgraded SP2.
> When I select the export to excel file format, I get the following in
> the
> excel file: "Data Regions within table/matrix cells are
> ignored".
> i am using tables and rectangles inside the table... and texboxes
> inside the rectangle in my report. the report is showing perfectly but
> when i select the export to excel file, it is throwing an error.
> Is this a known bug in RS..
> Any info will be appreciated.
> Thanks,
> Vinod
>
> vinodsh_82@.hotmail.com wrote:
>> Hi All,
>>
>> i am not able to export the report as excel. it showing the below
>> error:
>>
>> i am using the MSRS2000. i have installed microsoft office excel 2003.
>>
>> Microsoft Office Excel File Repair Log
>>
>> Errors were detected in file 'C:\Documents and
>> Settings\vinod_banagani\Desktop\AppointmentLog.xls'
>> The following is a list of repairs:
>>
>> Damage to the file was so extensive that repairs were not possible.
>> Excel attempted to recover your formulas and values, but some data may
>> have been lost or corrupted.
>>
>> is RS2000 supports Excel 2000 only?
>>
>> if anyone have faced this problem earlier, please tell me the solution.
>>
>> Thanks in advance for your help...
>|||Hi Vinod. It sounds like you are using a sub-report. Sub-reports are not
exported in the Excel rendering extension in RS2000 or RS2005. MHTML and
PDF rendering extensions do support sub-reports.
-Tim
<vinodsh_82@.hotmail.com> wrote in message
news:1147799266.536977.43720@.v46g2000cwv.googlegroups.com...
> Thanks for your response Bruce.
> i have upgraded SP2.
> When I select the export to excel file format, I get the following in
> the
> excel file: "Data Regions within table/matrix cells are
> ignored".
> i am using tables and rectangles inside the table... and texboxes
> inside the rectangle in my report. the report is showing perfectly but
> when i select the export to excel file, it is throwing an error.
> Is this a known bug in RS..
> Any info will be appreciated.
> Thanks,
> Vinod
>
> vinodsh_82@.hotmail.com wrote:
>> Hi All,
>>
>> i am not able to export the report as excel. it showing the below
>> error:
>>
>> i am using the MSRS2000. i have installed microsoft office excel 2003.
>>
>> Microsoft Office Excel File Repair Log
>>
>> Errors were detected in file 'C:\Documents and
>> Settings\vinod_banagani\Desktop\AppointmentLog.xls'
>> The following is a list of repairs:
>>
>> Damage to the file was so extensive that repairs were not possible.
>> Excel attempted to recover your formulas and values, but some data may
>> have been lost or corrupted.
>>
>> is RS2000 supports Excel 2000 only?
>>
>> if anyone have faced this problem earlier, please tell me the solution.
>>
>> Thanks in advance for your help...
>

Export - Excel Error

Hi All,
i am not able to export the report as excel. it showing the below
error:
i am using the MSRS2000. i have installed microsoft office excel 2003.
Microsoft Office Excel File Repair Log
Errors were detected in file 'C:\Documents and
Settings\vinod_banagani\Desktop\AppointmentLog.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible.
Excel attempted to recover your formulas and values, but some data may
have been lost or corrupted.
is RS2000 supports Excel 2000 only?
if anyone have faced this problem earlier, please tell me the solution.
Thanks in advance for your help...
Regds,
vinodRS 2000 supports Excel 2003 out of the box. The inital release of RS did not
support Excel 2000 because it was using a format that 2003 knew but 2000 did
not. With SP1 this was fixed and both version of Excel work now. If you are
not on Sp1 or SP2 I recommend upgrading.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<vinodsh_82@.hotmail.com> wrote in message
news:1147792560.164426.63070@.g10g2000cwb.googlegroups.com...
> Hi All,
>
> i am not able to export the report as excel. it showing the below
> error:
>
> i am using the MSRS2000. i have installed microsoft office excel 2003.
>
> Microsoft Office Excel File Repair Log
>
> Errors were detected in file 'C:\Documents and
> Settings\vinod_banagani\Desktop\AppointmentLog.xls'
> The following is a list of repairs:
>
> Damage to the file was so extensive that repairs were not possible.
> Excel attempted to recover your formulas and values, but some data may
> have been lost or corrupted.
>
> is RS2000 supports Excel 2000 only?
>
> if anyone have faced this problem earlier, please tell me the solution.
>
> Thanks in advance for your help...
> Regds,
> vinod
>

explain XML as datasource in reporting services

Hi,

its new for me to use XML as datasource and webservices. Can you please explain below snippets

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

Read this article below

http://www.codeproject.com/cs/webservices/myservice.asp

|||

Hi,

The code you provide are queries for Datasets with XML Data Sources, a dataset includes a query, which is the command text that runs against a data source to retrieve a specific result set. The result set maps to the collection of fields in a dataset. You can also set filter values on the dataset to limit results returned from the data source. The following are possible XML Query elements:

XML data source

Required XML elementsOptional XML ElementsFor a Web service<Method Namespace="namespace" Name="MethodName" />-- or --<SoapAction>soap action</SoapAction><ElementPath> element path </ElementPath><Method Namespace="namespace" Name="MethodName" />-- or --<SoapAction>soap action</SoapAction>For an XML document<ElementPath> element path </ElementPath>For an embedded XML document<XmlData> inner XML </XmlData><ElementPath> element path </ElementPath>-- or --<ElementPath IgnoreNamespaces="true"> element path </ElementPath>

Besides, here are some materials for you.

Step-by-step instructions on how to retrieve XML data for a Reporting Services report using the XML data processing extension
http://msdn2.microsoft.com/en-us/library/ms345334.aspx

Defining Report Datasets for XML Data
http://msdn2.microsoft.com/en-us/library/ms159741.aspx

Thanks.

expects parameter......parameterized query

Hi all,

I am using the below parameterized query and get an error while executing it...can anyone please spot the error. Any help will be appreciated. I have gone cross-eyed now looking at it all day. The error I get it is

Parameterized Query '(@.Re_UK_Eligible nvarchar(4000),@.Re_Aus_Eligible nvarchar(33),@.R' expects parameter @.Re_JobType_Temp, which was not supplied.

 sqlStmt ="UPDATE Re_Users SET Re_UK_Eligible=@.Re_UK_Eligible,Re_Aus_Eligible=@.Re_Aus_Eligible,Re_Can_Eligible=@.Re_Can_Eligible,Re_USA_Eligible=@.Re_USA_Eligible,Re_Address1=@.Re_Address1,Re_Address2=@.Re_Address2,Re_Address3=@.Re_Address3,Re_City=@.Re_City,Re_Postcode=@.Re_Postcode,Re_Country=@.Re_Country,Re_Homephone=@.Re_Homephone,Re_Mobile=@.Re_Mobile,Re_JobType_Per=@.Re_JobType_Per,Re_JobType_Temp=@.Re_JobType_Temp,Re_JobType_Con=@.Re_JobType_Con,Re_Hours_Full=@.Re_Hours_Full,Re_Hours_Part=@.Re_Hours_Part,Re_Sector=@.Re_Sector,Re_StepTwoDone=1 WHERE Re_UserCount=" + Session["ReUserIdentity"]; cn =new SqlConnection(ConfigurationManager.ConnectionStrings["ReConnectionString"].ConnectionString); cmd =new SqlCommand(sqlStmt, cn); cmd.CommandType = CommandType.Text;//Insert UKif (chkUK.Checked ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_UK_Eligible", DBNull.Value)); }if ((chkUK.Checked ==true) && (UKRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@.Re_UK_Eligible", UKRadioButtonList.SelectedItem.Text)); }//Insert AUSif (chkAUS.Checked ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_Aus_Eligible", DBNull.Value)); }if ((chkAUS.Checked ==true) && (AUSRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@.Re_Aus_Eligible", AUSRadioButtonList.SelectedItem.Text)); }//Insert CANif ((chkCAN.Checked ==false)) { cmd.Parameters.Add(new SqlParameter("@.Re_Can_Eligible", DBNull.Value)); }if ((chkCAN.Checked ==true) && (CANRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@.Re_Can_Eligible", CANRadioButtonList.SelectedItem.Text)); }//Insert USAif (chkUSA.Checked ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_USA_Eligible", DBNull.Value)); }if ((chkUSA.Checked ==true) && (USARadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@.Re_USA_Eligible", USARadioButtonList.SelectedItem.Text)); }//Contact Details cmd.Parameters.Add(new SqlParameter("@.Re_Address1", Address1TextBox.Text));if (Address2TextBox.Text =="") { cmd.Parameters.Add(new SqlParameter("@.Re_Address2", DBNull.Value)); }else { cmd.Parameters.Add(new SqlParameter("@.Re_Address2", Address2TextBox.Text)); }if (Address3TextBox.Text =="") { cmd.Parameters.Add(new SqlParameter("@.Re_Address3", DBNull.Value)); }else { cmd.Parameters.Add(new SqlParameter("@.Re_Address3", Address3TextBox.Text)); } cmd.Parameters.Add(new SqlParameter("@.Re_City", CityTextBox.Text)); cmd.Parameters.Add(new SqlParameter("@.Re_Postcode", PostcodeTextBox.Text)); cmd.Parameters.Add(new SqlParameter("@.Re_Country", CountryDropDownList.SelectedItem.Text));if (HomeTelephoneTextBox.Text =="") { cmd.Parameters.Add(new SqlParameter("@.Re_Homephone", DBNull.Value)); }else { cmd.Parameters.Add(new SqlParameter("@.Re_Homephone", HomeTelephoneTextBox.Text)); }if (MobileTelephoneTextBox.Text =="") { cmd.Parameters.Add(new SqlParameter("@.Re_Mobile", DBNull.Value)); }else { cmd.Parameters.Add(new SqlParameter("@.Re_Mobile", MobileTelephoneTextBox.Text)); }//Job Preferencesfor (int i = 0; i < JobTypeCheckBoxList.Items.Count; i++) {if (JobTypeCheckBoxList.Items[i].Text =="Permanent" && JobTypeCheckBoxList.Items[i].Selected ==true) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Per", 1)); }else if (JobTypeCheckBoxList.Items[i].Text =="Permanent" && JobTypeCheckBoxList.Items[i].Selected ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Per", 0)); }if (JobTypeCheckBoxList.Items[i].Text =="Temporary" && JobTypeCheckBoxList.Items[i].Selected ==true) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Temp", 1)); }else if (JobTypeCheckBoxList.Items[i].Text =="Temporary" && JobTypeCheckBoxList.Items[i].Selected ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Temp", 0)); }if (JobTypeCheckBoxList.Items[i].Text =="Contract" && JobTypeCheckBoxList.Items[i].Selected ==true) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Con", 1)); }else if (JobTypeCheckBoxList.Items[i].Text =="Contract" && JobTypeCheckBoxList.Items[i].Selected ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_JobType_Con", 0)); } }//Hours and Sectorfor (int i = 0; i < HoursCheckBoxList.Items.Count; i++) {if (HoursCheckBoxList.Items[i].Text =="FullTime" && HoursCheckBoxList.Items[i].Selected ==true) { cmd.Parameters.Add(new SqlParameter("@.Re_Hours_Full", 1)); }else if (HoursCheckBoxList.Items[i].Text =="FullTime" && HoursCheckBoxList.Items[i].Selected ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_Hours_Full", 0)); }if (HoursCheckBoxList.Items[i].Text =="PartTime" && HoursCheckBoxList.Items[i].Selected ==true) { cmd.Parameters.Add(new SqlParameter("@.Re_Hours_Part", 1)); }else if (HoursCheckBoxList.Items[i].Text =="PartTime" && HoursCheckBoxList.Items[i].Selected ==false) { cmd.Parameters.Add(new SqlParameter("@.Re_Hours_Part", 0)); } } cmd.Parameters.Add(new SqlParameter("@.Re_Sector", SectorDropDownList.SelectedItem.Text)); cn.Open(); cmd.ExecuteNonQuery();

thanks,

vijay

I would add the parameters to the collection first and set the value in the IF loops. Sometimes there are other conditions that you might not have accounted for.

Example:

myCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int));if txtbox1.text ==""{myCommand.Parameters("@.userid").Value = 0}else{myCommand.Parameters("@.userid").Value = userid}

Also, I would do a response.write of JobTypeCheckBoxList.Items.Count to see what the count is.

|||

Gave my sore eyes some rest and changed the code as recommended and guess what it did do the trick!!!

thanks

vijay

Expanding Hierachy with multiple parents

I have a user assigned multiple roles and a role can be inherited from
multiple parents (see below). How do I answer such questions as "How
many roles does the user belongs to?"

I answered the above questions by using .NET but I think it can be
more efficient by using just SQL. I would appreciate if you can give
me an answer.

Thank you.

CREATE TABLE [dbo].[tb_User] (
[Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Name] nvarchar(99) NOT NULL UNIQUE,
[Password] nvarchar(99) NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_Role] (
[Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Name] nvarchar(99) NOT NULL UNIQUE,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_User_Role] (
[UserId] [int] NOT NULL ,
[RoleId] [int] NOT NULL,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADD
CONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED
(
[UserId],
[RoleId]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_Parent_Role] (
[RoleId] [int] NOT NULL ,
[ParentRoleId] [int] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADD
CONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED
(
[RoleId],
[ParentRoleId]
) ON [PRIMARY]
GO"John Smith" <hai_hoang@.hotmail.com> wrote in message
news:5661eadb.0311292155.4231580@.posting.google.co m...
> I have a user assigned multiple roles and a role can be inherited from
> multiple parents (see below). How do I answer such questions as "How
> many roles does the user belongs to?"
> I answered the above questions by using .NET but I think it can be
> more efficient by using just SQL. I would appreciate if you can give
> me an answer.
> Thank you.
>
> CREATE TABLE [dbo].[tb_User] (
> [Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
> [Name] nvarchar(99) NOT NULL UNIQUE,
> [Password] nvarchar(99) NOT NULL,
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tb_Role] (
> [Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
> [Name] nvarchar(99) NOT NULL UNIQUE,
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tb_User_Role] (
> [UserId] [int] NOT NULL ,
> [RoleId] [int] NOT NULL,
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADD
> CONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED
> (
> [UserId],
> [RoleId]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tb_Parent_Role] (
> [RoleId] [int] NOT NULL ,
> [ParentRoleId] [int] NOT NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADD
> CONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED
> (
> [RoleId],
> [ParentRoleId]
> ) ON [PRIMARY]
> GO

CREATE TABLE Users
(
user_name VARCHAR(35) NOT NULL PRIMARY KEY,
password VARCHAR(10) NOT NULL
)

CREATE TABLE Roles
(
role_name VARCHAR(20) NOT NULL PRIMARY KEY
)

CREATE TABLE ParentRoles
(
parent_role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name),
role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name),
CHECK (parent_role_name <> role_name),
PRIMARY KEY (role_name, parent_role_name)
)

CREATE TABLE UserRoles
(
user_name VARCHAR(35) NOT NULL REFERENCES Users (user_name),
role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name)
PRIMARY KEY (user_name, role_name)
)

-- UDF to return all roles for each user
-- A user can be directly assigned multiple roles and each role can have
-- multiple parents
CREATE FUNCTION AllUserRoles()
RETURNS @.roles TABLE
(user_name VARCHAR(35) NOT NULL,
role_name VARCHAR(20) NOT NULL,
distance INT NOT NULL CHECK (distance >= 0),
PRIMARY KEY (user_name, role_name))
AS
BEGIN
DECLARE @.distance INT, @.next_distance INT
SET @.distance = 0
SET @.next_distance = @.distance + 1
INSERT INTO @.roles (user_name, role_name, distance)
SELECT user_name, role_name, @.distance
FROM UserRoles
WHILE EXISTS (SELECT * FROM @.roles WHERE distance = @.distance)
BEGIN
INSERT INTO @.roles (user_name, role_name, distance)
SELECT DISTINCT R.user_name, P.parent_role_name, @.next_distance
FROM @.roles AS R
INNER JOIN
ParentRoles AS P
ON R.distance = @.distance AND
R.role_name = P.role_name AND
NOT EXISTS (SELECT *
FROM @.roles
WHERE user_name = R.user_name AND
role_name = P.parent_role_name)
SET @.distance = @.next_distance
SET @.next_distance = @.next_distance + 1
END
RETURN
END

-- Example

-- Users
INSERT INTO Users (user_name, password)
VALUES ('moe', 'forget')
INSERT INTO Users (user_name, password)
VALUES ('larry', 'ignore')

-- Roles
INSERT INTO Roles (role_name)
VALUES ('role1')
INSERT INTO Roles (role_name)
VALUES ('role2')
INSERT INTO Roles (role_name)
VALUES ('role3')
INSERT INTO Roles (role_name)
VALUES ('role4')
INSERT INTO Roles (role_name)
VALUES ('role5')
INSERT INTO Roles (role_name)
VALUES ('role6')

-- Parent roles
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role3', 'role4')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role3', 'role5')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role1', 'role3')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role2', 'role3')

-- User roles
INSERT INTO UserRoles (user_name, role_name)
VALUES ('moe', 'role4')
INSERT INTO UserRoles (user_name, role_name)
VALUES ('larry', 'role5')
INSERT INTO UserRoles (user_name, role_name)
VALUES ('larry', 'role6')

SELECT user_name, role_name
FROM AllUserRoles()
ORDER BY user_name, distance, role_name

user_name role_name
larry role5
larry role6
larry role3
larry role1
larry role2
moe role4
moe role3
moe role1
moe role2

Regards,
jag

Exists T-SQL

I would like to change the t-sql statement listed below to execute quicker.
Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
If a Pum value of 806478 is found within the last 60 minutes. The output
COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
Please help me complete this task.
Thank You,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
(Select count(Pum)
from Call_Movements
where DATEDIFF(mi, Started_Time, GETDATE()) <=60
AND left(cast(Pum as varchar(20)),6) = ('806478'))Try,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
declare @.d datetime
set @.d = convert(varchar(16), getdate(), 126) + ':00'
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
case when exists (
Select
*
from
dbo.Call_Movements
where
(Started_Time between dateadd(minutes, -60, @.d) and @.d)
AND cast(Pum as varchar(20)) like '806478%'
) 1 then 0 end
go
AMB
"Joe K." wrote:

> I would like to change the t-sql statement listed below to execute quicker
.
> Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
> If a Pum value of 806478 is found within the last 60 minutes. The output
> COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
> Please help me complete this task.
> Thank You,
>
> DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
> SET @.COUNT_CALL_MOVEMENTS_REC_0 =
> (Select count(Pum)
> from Call_Movements
> where DATEDIFF(mi, Started_Time, GETDATE()) <=60
> AND left(cast(Pum as varchar(20)),6) = ('806478'))

EXISTS -- NOT EXISTS QUERY

Good Morning
CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
QUERY BELOW WITH A SINGLE QUERY LIKE THIS
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOTEXISTS(select table2.fieldb from mytable where
table2.fieldb=mytable.A)
INSTEAD OF
SELECT mytable.A from mytable
where
exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
UNION
SELECT mytable.A from mytable
where
NOTEXISTS(select table2.fieldB from mytable where
table2.fieldB=mytable.A)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!On Thu, 12 Aug 2004 06:12:38 -0700, David Hills wrote:

>Good Morning
>CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
>QUERY BELOW WITH A SINGLE QUERY LIKE THIS
>SELECT mytable.A from mytable
>where
>exists(select table2.fielda from table2 where table2.fielda=mytable.A)
>OR
>NOTEXISTS(select table2.fieldb from mytable where
>table2.fieldb=mytable.A)
>
>INSTEAD OF
>SELECT mytable.A from mytable
>where
>exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
>UNION
>SELECT mytable.A from mytable
>where
>NOTEXISTS(select table2.fieldB from mytable where
>table2.fieldB=mytable.A)
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
Hi David,
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOT EXISTS(select table2.fieldb from table2 where
table2.fieldb=mytable.A)
(Note - I copied your query, inserted a space between NOT and EXISTS and
changed the table name in the second subquery from mytable to table2;
that's all that was needed. I'm surprised your original version with UNION
worked, as this one contains the same errors).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Does the following do what you want?
SELECT mytable.A
FROM mytable
WHERE EXISTS
(
SELECT *
FROM table2
WHERE table2.fielda=mytable.A
)
OR
NOT EXISTS
(
SELECT *
FROM table2
WHERE table2.fieldb=mytable.A
)
Hope this helps.
Dan Guzman
SQL Server MVP
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23S8fL4GgEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Good Morning
> CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
> QUERY BELOW WITH A SINGLE QUERY LIKE THIS
> SELECT mytable.A from mytable
> where
> exists(select table2.fielda from table2 where table2.fielda=mytable.A)
> OR
> NOTEXISTS(select table2.fieldb from mytable where
> table2.fieldb=mytable.A)
>
> INSTEAD OF
> SELECT mytable.A from mytable
> where
> exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
> UNION
> SELECT mytable.A from mytable
> where
> NOTEXISTS(select table2.fieldB from mytable where
> table2.fieldB=mytable.A)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||No I'm affraid it does not work.
I am running against oracle and the query stalls.
The union works and takes about 5 seconds
thanks
dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> No I'm affraid it does not work.
> I am running against oracle and the query stalls.
> The union works and takes about 5 seconds
Then perhaps you should upgrade to SQL Server :-)
Oracle questions are best asked in an Oracle forum. This one is specific to
Micrososft SQL Server. The only suggestion I can make is that you make sure
you have indexes on the columns references in your WHERE clauses.
Hope this helps.
Dan Guzman
SQL Server MVP

EXISTS -- NOT EXISTS QUERY

Good Morning
CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
QUERY BELOW WITH A SINGLE QUERY LIKE THIS
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOTEXISTS(select table2.fieldb from mytable where
table2.fieldb=mytable.A)
INSTEAD OF
SELECT mytable.A from mytable
where
exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
UNION
SELECT mytable.A from mytable
where
NOTEXISTS(select table2.fieldB from mytable where
table2.fieldB=mytable.A)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!On Thu, 12 Aug 2004 06:12:38 -0700, David Hills wrote:
>Good Morning
>CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
>QUERY BELOW WITH A SINGLE QUERY LIKE THIS
>SELECT mytable.A from mytable
>where
>exists(select table2.fielda from table2 where table2.fielda=mytable.A)
>OR
>NOTEXISTS(select table2.fieldb from mytable where
>table2.fieldb=mytable.A)
>
>INSTEAD OF
>SELECT mytable.A from mytable
>where
>exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
>UNION
>SELECT mytable.A from mytable
>where
>NOTEXISTS(select table2.fieldB from mytable where
>table2.fieldB=mytable.A)
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi David,
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOT EXISTS(select table2.fieldb from table2 where
table2.fieldb=mytable.A)
(Note - I copied your query, inserted a space between NOT and EXISTS and
changed the table name in the second subquery from mytable to table2;
that's all that was needed. I'm surprised your original version with UNION
worked, as this one contains the same errors).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Does the following do what you want?
SELECT mytable.A
FROM mytable
WHERE EXISTS
(
SELECT *
FROM table2
WHERE table2.fielda=mytable.A
)
OR
NOT EXISTS
(
SELECT *
FROM table2
WHERE table2.fieldb=mytable.A
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23S8fL4GgEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Good Morning
> CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
> QUERY BELOW WITH A SINGLE QUERY LIKE THIS
> SELECT mytable.A from mytable
> where
> exists(select table2.fielda from table2 where table2.fielda=mytable.A)
> OR
> NOTEXISTS(select table2.fieldb from mytable where
> table2.fieldb=mytable.A)
>
> INSTEAD OF
> SELECT mytable.A from mytable
> where
> exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
> UNION
> SELECT mytable.A from mytable
> where
> NOTEXISTS(select table2.fieldB from mytable where
> table2.fieldB=mytable.A)
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

EXISTS -- NOT EXISTS QUERY

Good Morning
CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
QUERY BELOW WITH A SINGLE QUERY LIKE THIS
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOTEXISTS(select table2.fieldb from mytable where
table2.fieldb=mytable.A)
INSTEAD OF
SELECT mytable.A from mytable
where
exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
UNION
SELECT mytable.A from mytable
where
NOTEXISTS(select table2.fieldB from mytable where
table2.fieldB=mytable.A)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Thu, 12 Aug 2004 06:12:38 -0700, David Hills wrote:

>Good Morning
>CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
>QUERY BELOW WITH A SINGLE QUERY LIKE THIS
>SELECT mytable.A from mytable
>where
>exists(select table2.fielda from table2 where table2.fielda=mytable.A)
>OR
>NOTEXISTS(select table2.fieldb from mytable where
>table2.fieldb=mytable.A)
>
>INSTEAD OF
>SELECT mytable.A from mytable
>where
>exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
>UNION
>SELECT mytable.A from mytable
>where
>NOTEXISTS(select table2.fieldB from mytable where
>table2.fieldB=mytable.A)
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
Hi David,
SELECT mytable.A from mytable
where
exists(select table2.fielda from table2 where table2.fielda=mytable.A)
OR
NOT EXISTS(select table2.fieldb from table2 where
table2.fieldb=mytable.A)
(Note - I copied your query, inserted a space between NOT and EXISTS and
changed the table name in the second subquery from mytable to table2;
that's all that was needed. I'm surprised your original version with UNION
worked, as this one contains the same errors).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Does the following do what you want?
SELECT mytable.A
FROM mytable
WHERE EXISTS
(
SELECT *
FROM table2
WHERE table2.fielda=mytable.A
)
OR
NOT EXISTS
(
SELECT *
FROM table2
WHERE table2.fieldb=mytable.A
)
Hope this helps.
Dan Guzman
SQL Server MVP
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:%23S8fL4GgEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Good Morning
> CAN ANYONE HELP WITH THE CORRECT SYNTAX TO REPLACE THE UNION
> QUERY BELOW WITH A SINGLE QUERY LIKE THIS
> SELECT mytable.A from mytable
> where
> exists(select table2.fielda from table2 where table2.fielda=mytable.A)
> OR
> NOTEXISTS(select table2.fieldb from mytable where
> table2.fieldb=mytable.A)
>
> INSTEAD OF
> SELECT mytable.A from mytable
> where
> exists(select table2.fieldA from table2 where table2.fieldA=mytable.A)
> UNION
> SELECT mytable.A from mytable
> where
> NOTEXISTS(select table2.fieldB from mytable where
> table2.fieldB=mytable.A)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||No I'm affraid it does not work.
I am running against oracle and the query stalls.
The union works and takes about 5 seconds
thanks
dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> No I'm affraid it does not work.
> I am running against oracle and the query stalls.
> The union works and takes about 5 seconds
Then perhaps you should upgrade to SQL Server :-)
Oracle questions are best asked in an Oracle forum. This one is specific to
Micrososft SQL Server. The only suggestion I can make is that you make sure
you have indexes on the columns references in your WHERE clauses.
Hope this helps.
Dan Guzman
SQL Server MVP

Execution time of outer join versus inner

I've got two "heavy" queries here below, that I think should give the same
result.
I expected the first to be slower, and Query Analyzer agrees with me in the
estimated cost in the execution plans it generates for the two queries, but
in reality it's quite the opposite - now I'd like to know why ;)
What they do is join a large table onto itself, with a subquery on the same
table in the join conditions.
The table has 988,123 rows, LogIndex is primary key, CardNo is indexed.
The difference is in the method of the join: INNER versus LEFT OUTER.
Query 1:
- estimated by Query Analyzer: cost 12015, rows: 312617
- reality: finishes in 19 seconds, returns 3148 rows
SELECT {some fields}
FROM CardLog t1 LEFT OUTER JOIN CardLog t2
ON t1.CardNo = t2.CardNo
AND ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
FROM CardLog T3
WHERE t3.cardno = t1.cardno
AND t3.logindex > t1.logindex)
WHERE (t2.[Action] IN (2, 3, 4, 5))
ORDER BY t2.LogIndex
Query 2:
- estimated by Query Analyzer: cost 245, rows: 1325
- reality: doesn't finish in 17 minutes (that's the longest I let it run)
SELECT {some fields...}
FROM CardLog t1 INNER JOIN CardLog t2
ON t1.CardNo = t2.CardNo
AND ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
FROM CardLog T3
WHERE t3.cardno = t1.cardno
AND t3.logindex > t1.logindex)
WHERE (t2.[Action] IN (2, 3, 4, 5))
ORDER BY t2.LogIndexHi
Have you looked at the actual execution plans to see what is happening?
You may want to also make sure that the statistics are up-to-date.
You may also want to look at adding t2.logindex > t1.logindex to your join
criteria and seeing if that makes a difference and also possibly seeing if a
composite index helps. You may want to check that moving to the where clause
does not have any effect ABS(t1.Credit - t2.Credit) <> t2.Amount
John
"Lucvdv" <replace_name@.null.net> wrote in message
news:9c6rh1dgk3332ot7amhig4bromis937a4g@.
4ax.com...
> I've got two "heavy" queries here below, that I think should give the same
> result.
> I expected the first to be slower, and Query Analyzer agrees with me in
> the
> estimated cost in the execution plans it generates for the two queries,
> but
> in reality it's quite the opposite - now I'd like to know why ;)
> What they do is join a large table onto itself, with a subquery on the
> same
> table in the join conditions.
> The table has 988,123 rows, LogIndex is primary key, CardNo is indexed.
> The difference is in the method of the join: INNER versus LEFT OUTER.
> Query 1:
> - estimated by Query Analyzer: cost 12015, rows: 312617
> - reality: finishes in 19 seconds, returns 3148 rows
> SELECT {some fields}
> FROM CardLog t1 LEFT OUTER JOIN CardLog t2
> ON t1.CardNo = t2.CardNo
> AND ABS(t1.Credit - t2.Credit) <> t2.Amount
> AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
> FROM CardLog T3
> WHERE t3.cardno = t1.cardno
> AND t3.logindex > t1.logindex)
> WHERE (t2.[Action] IN (2, 3, 4, 5))
> ORDER BY t2.LogIndex
> Query 2:
> - estimated by Query Analyzer: cost 245, rows: 1325
> - reality: doesn't finish in 17 minutes (that's the longest I let it run)
> SELECT {some fields...}
> FROM CardLog t1 INNER JOIN CardLog t2
> ON t1.CardNo = t2.CardNo
> AND ABS(t1.Credit - t2.Credit) <> t2.Amount
> AND t2.LogIndex = (SELECT MIN(t3.LogIndex)
> FROM CardLog T3
> WHERE t3.cardno = t1.cardno
> AND t3.logindex > t1.logindex)
> WHERE (t2.[Action] IN (2, 3, 4, 5))
> ORDER BY t2.LogIndex|||Try to separate the subquery using a temporary table, like this:
CREATE TABLE #CardLogSequence (
LogIndex int PRIMARY KEY,
NextLogIndex int NOT NULL UNIQUE
)
INSERT INTO #CardLogSequence
SELECT LogIndex, NextLogIndex
FROM (
SELECT t1.LogIndex, (
SELECT MIN(t3.LogIndex)
FROM CardLog t3
WHERE t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
) as NextLogIndex
FROM CardLog t1
) X WHERE NextLogIndex IS NOT NULL
SELECT {some columns...}
FROM #CardLogSequence s
INNER JOIN CardLog t1 ON t1.LogIndex=s.LogIndex
INNER JOIN CardLog t2 ON t2.LogIndex=s.NextLogIndex
WHERE ABS(t1.Credit - t2.Credit) <> t2.Amount
AND t2.Action IN (2,3,4,5)
ORDER BY t2.LogIndex
DROP TABLE #CardLogSequence
(the above code is untested, because you didn't provide DDL as CREATE
TABLE statements)
What times are you getting with the above queries ?
Razvan|||On 7 Sep 2005 02:22:01 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> Try to separate the subquery using a temporary table, like this:
<snip>
> What times are you getting with the above queries ?
1 minute 45 seconds on the first run, 32 seconds if I restart it
immediately.
The LEFT version without a subquery still finishes in 17 seconds.
I had expected some difference when running the query a second time
(everything cached in RAM), but not that much.|||On Wed, 7 Sep 2005 10:10:00 +0100, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:

> Hi
> Have you looked at the actual execution plans to see what is happening?
Yes, and even there it looks like the LEFT version should be slower
(looking at what it's doing and how many times).

> You may want to check that moving to the where clause
> does not have any effect ABS(t1.Credit - t2.Credit) <> t2.Amount
I had put it there initially, but that was in Enterprise Manager, and it
moved it into the join criteria by itself when I started the query.
Now I checked it both ways in QA, and the execution plans it generates are
identical.|||Execute sp_updatestats to make sure the statistics are up-to-date.
Which of the two statements in my previous post is taking more time
(the insert or the final select) ?
Please post the execution plan, obtained using SET SHOWPLAN_TEXT. The
graphical execution plan produced by Query Analyzer would be easier to
understand (and contains more information, like the number of rows
affected by each step), but make sure it's a small file (preferably,
PNG) if you post it as an attachment.
Razvan|||On 7 Sep 2005 22:10:37 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> Execute sp_updatestats to make sure the statistics are up-to-date.
Done.

> Which of the two statements in my previous post is taking more time
> (the insert or the final select) ?
The insert takes nearly all. In a few runs:
Insert: 28-31 sec
Total: 35-37 sec
That's after changing to a fixed table instead of a temporary (see below,
at execution plan).

> Please post the execution plan, obtained using SET SHOWPLAN_TEXT. The
> graphical execution plan produced by Query Analyzer would be easier to
> understand (and contains more information, like the number of rows
> affected by each step), but make sure it's a small file (preferably,
> PNG) if you post it as an attachment.
Thanks for the help so far.
But don't waste too much time on it unless you're interested yourself, it's
not as if my job depends on it.
I'll mix some results from the graph into the text version.
The | at the left margin are just there to tell my newsreader not to wrap
those lines, if you have one where you can turn wrapping off it might be
better readable that way.
1) LEFT join:
| |--Parallelism(Gather Streams, ORDER BY:([t2].[LogIndex] ASC))
Estimated row count: 312,617
Estimated subtree cost: 12,041
| |--Sort(ORDER BY:([t2].[LogIndex] ASC))
| |--Filter(WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2)
)
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([t1].[Credit], [t1].[LogIndex],
[t1].[CardNo]))
Estimated row count: 988,123
Estimated row size: 110
Estimated CPU cost: 2.07
Estimated number of executes: 1
Estimated cost: 2.065804
Estimated subtree cost: 11989
| |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__C
ardLog__1A14E395] AS [t1]))`
Est row count: 988,123
Est row size: 53
Est I/O cost: 4.15
Est CPU cost: 0.453
Est # executes: 1.0
Est subtree cost: 4.69
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1
003]))
Est row count: 1
Est row size: 66
Est CPU cost: 0.00004
Est # executes: 988,123
Est cost: 5.30
Est subtree cost: 11982
| |--Stream Aggregate(DEFINE:([Expr1003]=MIN([T3].[LogIndex
])))
| | |--Top(1)
| | |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [T3]), SEEK:([T3].[CardNo]=[t1].[
CardNo] AND [T3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est row count: 1
Est row size: 33
Est # executes: 988,123
Est cost: 5789
| |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t2]), SEEK:([t2].[LogIn
dex]=[Expr1003]), WHERE:([t1].[CardNo]=[t2].[CardNo] AND abs([t1].[Credit]-[t2].[Credit])<>[t2].[Amount]) ORDERED
FOR
Est row count: 1
Est row size: 61
Est # executes: 988,123
Est cost: 6188
2) INNER join, my version:
| |--Parallelism(Gather Streams, ORDER BY:([t2].[LogIndex] ASC))
Estimated row count: 1325
Estimated subtree cost: 245
| |--Sort(ORDER BY:([t2].[LogIndex] ASC))
| |--Filter(WHERE:([Expr1003]=[t2].[LogIndex]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[LogIndex], [t1].[CardNo
]))
Est row count: 1,337,987
Est row size: 62
Est CPU cost: 2.80
Est # executes: 1
Est cost: 2.80
Est subtree cost: 244
| |--Parallelism(Repartition Streams, PARTITION COLUMNS:([t1].[LogIndex]
, [t1].[CardNo]))
Est row count: 1,337,987
Est row size: 58
Est CPU cost: 12.1
Est # executes: 1
Est cost: 12.0
Est subtree cost: 119
| | |--Hash Match(Inner Join, HASH:([t2].[CardNo])=([t1].[CardNo]), RESIDUAL:(abs([t1].[Cre
dit]-[t2].[Credit])<>[t2].[Amount]))
Est CPU cost: 90.6
Est cost: 90.6
Est subtree cost: 107
| | |--Parallelism(Repartition Streams, PARTITION COLU
MNS:([t2].[CardNo]))
| | | |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395]
AS [t2]), WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2))
Est row count: 312,617
Est row size: 61
Est CPU cost: 0.543
Est I/O cost: 4.15
Est cost: 4.69
| | |--Parallelism(Repartition Streams, PARTITION COLU
MNS:([t1].[CardNo]))
| | |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[dbo].[
CardLog].[PK__CardLog__1A14E395] AS [t1]))
Est row count: 988,123
Est row size: 53
Est I/O cost: 4.15
Est CPU cost: 0.543
Est cost: 4.69
| |--Hash Match(Cache, HASH:([t1].[LogIndex], [t1].[CardNo]), RESIDUAL:([t1].[LogIndex]=[t1].[LogIndex] AND
[t1].[CardNo]=[t1].[CardNo]))
Est row count: 1
Est row size: 11
Est CPU cost: 7.73
Est # executes: 1,339,789
Est cost: 13.8
Est subtree cost: 122
| |--Stream Aggregate(DEFINE:([Expr1003]=MIN([T3].[LogIndex
])))
| |--Top(1)
| |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [T3]), SEEK:([T3].[CardNo]=[t1].[
CardNo] AND [T3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est row count: 1
Est row size: 33
Est I/O cost: 0.00632
Est CPU cost: 0.000360
Est # executes: 18529
Est cost: 109
(3) Temporary table
I couldn't get it to work with a temporary table, it gave "Invalid object
name" when trying to get an execution plan.
I worked around that by creating it as a normal table, and TRUNCATEing it
before the SELECT INTO.
Creating (filling) the table: estimated cost 5873.
| |--Index Insert(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[UQ__CLSEQ__47FBA9D6]), SET:([LogIndex1009]=[CLSEQ].[LogIndex], [NextLog
Index1008]=[CLSEQ].[NextLogIndex], [IdxBmk1007]=[Bmk1006]))
Est Cost: 6.29, subtree: 5873
| |--Sort(ORDER BY:([CLSEQ].[NextLogIndex] ASC))
Est Cost: 66.15, subtree: 5867
| |--Clustered Index Insert(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[PK__CLSEQ__4707859D]), SET:([CLSEQ].[NextLogIndex]=[Expr1002]
, [CLSEQ].[LogIndex]=[t1].[LogIndex]))
Est cost: 1.00, subtree: 5801
| |--Top(ROWCOUNT est 0)
| |--Parallelism(Gather Streams, ORDER BY:([t1].[LogIndex] ASC))
Est cost: 3.58, subtree: 5800
| |--Filter(WHERE:([Expr1002]<>NULL))
Est cost: 0.23, subtree: 5796
| |--Compute Scalar(DEFINE:([Expr1002]=[Expr
1002]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([t1].[Log
Index], [t1].[CardNo]))
Est cost: 2.07, subtree: 5796
| |--Clustered Index Scan(OBJECT:([WM-Eu-Var].[d
bo].[CardLog].[PK__CardLog__1A14E395] AS [t1]), ORDERED FORWARD)
Est cost: 4.69
| |--Stream Aggregate(DEFINE:([Expr1002]=MIN
([t3].[LogIndex])))
Est cost: 0.0986, subtree: 5789
| |--Top(1)
| |--Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[IxCardLogCard] AS [t3]), SEEK:([t3].
[CardNo]=[t1].[CardNo] AND [t3].[LogIndex] > [t1].[LogIndex]) ORDERED FORWARD)
Est cost: 5789
Final select query: estimated cost 0.0634
| |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[LogIndex], [t2].[Amount], [t2].[Credit]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[NextLogIndex]))
| | |--Index Scan(OBJECT:([WM-Eu-Var].[dbo].[CLSEQ].[UQ__CLSEQ__47FBA9D6] AS [s])
, ORDERED FORWARD)
| | |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t2]), SEEK:([t2].[LogIndex]=[s].[NextLogIndex])
, WHERE:((([t2].[Action]=5 OR [t2].[Action]=4) OR [t2].[Action]=3) OR [t2].[Action]=2) ORDERED FORWARD)
| |--Clustered Index S(OBJECT:([WM-Eu-Var].[dbo].[CardLog].[PK__CardLog__1A14E395] AS [t1]), SEEK:([t1].[LogIndex]=[s].[LogInde
x]), WHERE:(abs([t1].[Credit]-[t2].[Credit])<>[t2].[Amount]) ORDERED FORWARD)|||1. Create a unique index on (CardNo, LogIndex), run sp_updatestats
again and re-try all three queries (I believe your IxCardLogCard index
on these columns, but it is not unique);
2. Also try this query:
INSERT INTO #CardLogSequence
SELECT t1.LogIndex, MIN(t3.LogIndex)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
GROUP BY t1.LogIndex
3. The actual execution plan produced by Query Analyzer is much more
useful than the estimated query plan (use Ctrl+K instead of Ctrl+L),
because when a query performs poorly it's probable that this is due to
wrong estimations of row counts (so we need the actual row counts)
4. Is is true that the condition "t2.Action IN (2,3,4,5)" greatly
restricts the number of rows (i.e. the number of rows that satisfy this
condition is much lower than the total number of rows) ? If so, you
should try moving this condition to the first query (i.e: add "AND
t3.Action IN (2,3,4,5)")
Razvan|||On 8 Sep 2005 22:28:45 -0700, "Razvan Socol" <rsocol@.gmail.com> wrote:

> 1. Create a unique index on (CardNo, LogIndex), run sp_updatestats
> again and re-try all three queries (I believe your IxCardLogCard index
> on these columns, but it is not unique);
That's correct, the index on CardNo can not be unique.
I created the unique index and updated the stats with "sample all rows".
Execution times remain the same (I aborted the INNER join after 6 minutes).
The original LEFT join actually went up to 19 seconds (from 17).
I also tried a (non-unique) index on CardNo/Action/Amount/Credit, it
doesn't change anything either.
That could be because all of the table's fields are in the query (at least
in the SELECT clause), scanning the table shouldn't be much harder than
scanning a 4-column index.

> 2. Also try this query:
> INSERT INTO #CardLogSequence
> SELECT t1.LogIndex, MIN(t3.LogIndex)
> FROM CardLog t1 INNER JOIN CardLog t3
> ON t3.CardNo = t1.CardNo
> AND t3.LogIndex > t1.LogIndex
> GROUP BY t1.LogIndex
It seems to go the same way as my own INNER join version.
I aborted it after 3 minutes and some seconds.
I think I know why it performs so badly though, see below.

> 3. The actual execution plan produced by Query Analyzer is much more
> useful than the estimated query plan (use Ctrl+K instead of Ctrl+L),
> because when a query performs poorly it's probable that this is due to
> wrong estimations of row counts (so we need the actual row counts)
I'm being called away into a meeting, I'll look into this later.

> 4. Is is true that the condition "t2.Action IN (2,3,4,5)" greatly
> restricts the number of rows (i.e. the number of rows that satisfy this
> condition is much lower than the total number of rows) ? If so, you
> should try moving this condition to the first query (i.e: add "AND
> t3.Action IN (2,3,4,5)")
No. It filters out less than half of the results.
Most of it is done by the ABS() condition.
Looking at the queries, this part of the join:
SELECT count(*)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
would return a *huge* number of rows (this query actually caused an
arithmetic overflow).
When I add the next line, it also seems to run forever:
SELECT count(*)
FROM CardLog t1 INNER JOIN CardLog t3
ON t3.CardNo = t1.CardNo
AND t3.LogIndex > t1.LogIndex
Could the problem be the high frequency of some CardNo values blowing up
intermediate results?
There are 1715 total, but some occur a lot more than others.
The top 5 occur more than 13,000 times each; the halfway point is 255
times, and about 1/3 occur less than 100 times.
BTW, just in case it wasn't clear from my first post: I _expected_ long
execution times, and 17 or 19 seconds is better than I hoped for.
I was only wondering why changing "LEFT" into "INNER" made such an enormous
difference.|||Hi
Why do you need to use ABS in
ABS(t1.Credit - t2.Credit) <> t2.Amount
Try t1.Credit <> t2.Amount + t2.Credit
and index Amount an Credit
John
"Lucvdv" <replace_name@.null.net> wrote in message
news:ud5uh1hm95lm2p3fso23jaqpm4o4q9l81p@.
4ax.com...
> On Wed, 7 Sep 2005 10:10:00 +0100, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>
> Yes, and even there it looks like the LEFT version should be slower
> (looking at what it's doing and how many times).
>
> I had put it there initially, but that was in Enterprise Manager, and it
> moved it into the join criteria by itself when I started the query.
> Now I checked it both ways in QA, and the execution plans it generates are
> identical.

Execution plan for update

Hi,
Please check two statements below, one is Update, and another is Select
statement based on that Update. Why they have different execution plan?
Problem is that Update statement first joins two titles tables instead to
first join titles and sales as Select does. It makes me problem on large
tables as Update statement never ends (while Select finishes in few
seconds).
I tried to use join hints but Update statement does not allow them.
I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
Thanks in advance
Nikola Milic
--UPDATE StmtText
UPDATE pubs.dbo.titles
SET royalty = STT.royalty -- meaningless, just for test
--SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--UPDATE ExecutionPlan Text
|--Clustered Index
Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
SET:([titles].[royalty]=[titles].[royalty]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([titles].[title_id]))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
| |--Clustered Index
S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
|--Index
S(OBJECT:([pubs].[dbo].[sales].[titleidind]),
SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
--SELECT StmtText
SELECT T.*
FROM pubs.dbo.titles T
JOIN(
SELECT S.title_id, S.qty, T.royalty
FROM(
SELECT DISTINCT title_id, qty
FROM pubs.dbo.sales
)S
JOIN(
SELECT DISTINCT title_id, royalty
FROM pubs.dbo.titles
)T
ON S.title_id = T.title_id
--OR S.qty = T.royalty -- meaningless, just for test
)STT
ON T.title_id = STT.title_id
--SELECT ExecutionPlan Text
|--Hash Match(Inner Join, HASH:([titles].[title_id])=([sales].[title_id]),
RESIDUAL:([sales].[title_id]=[titles].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
|--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC, [sales].[qty]
ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
|--Clustered Index
S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)Hi Nikola,
The first impression I have when I look at your SQL is that you are
overcomplicating things. Are these actual SQL statements you use or are
these just samples?
Your SELECT could be rewritten as follows with the same result ifI'm not
mistaken:
SELECT T.*
FROM pubs.dbo.titles T
JOIN pubs.dbo.sales STT
ON T.title_id = STT.title_id
HTH
Karl Gram
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
> Hi,
> Please check two statements below, one is Update, and another is Select
> statement based on that Update. Why they have different execution plan?
> Problem is that Update statement first joins two titles tables instead to
> first join titles and sales as Select does. It makes me problem on large
> tables as Update statement never ends (while Select finishes in few
> seconds).
> I tried to use join hints but Update statement does not allow them.
> I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4
> Thanks in advance
> Nikola Milic
>
>
> --UPDATE StmtText
> UPDATE pubs.dbo.titles
> SET royalty = STT.royalty -- meaningless, just for test
> --SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
> --UPDATE ExecutionPlan Text
> |--Clustered Index
> Update(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]),
> SET:([titles].[royalty]=[titles].[royalty]))
> |--Top(ROWCOUNT est 0)
> |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([titles].[title_id]))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))
> | |--Clustered Index
> S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[titles].[title_id]) ORDERED FORWARD)
> |--Index
> S(OBJECT:([pubs].[dbo].[sales].[titleidind]),
> SEEK:([sales].[title_id]=[titles].[title_id]) ORDERED FORWARD)
>
>
> --SELECT StmtText
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN(
> SELECT S.title_id, S.qty, T.royalty
> FROM(
> SELECT DISTINCT title_id, qty
> FROM pubs.dbo.sales
> )S
> JOIN(
> SELECT DISTINCT title_id, royalty
> FROM pubs.dbo.titles
> )T
> ON S.title_id = T.title_id
> --OR S.qty = T.royalty -- meaningless, just for test
> )STT
> ON T.title_id = STT.title_id
>
> --SELECT ExecutionPlan Text
> |--Hash Match(Inner Join,
> HASH:([titles].[title_id])=([sales].[title_id]),
> RESIDUAL:([sales].[title_id]=[titles].[title_id]))
> |--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([sales].[title_id]))
> |--Sort(DISTINCT ORDER BY:([sales].[title_id] ASC,
> [sales].[qty] ASC))
> | |--Clustered Index
> Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales]))
> |--Clustered Index
> S(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
> SEEK:([T].[title_id]=[sales].[title_id]) ORDERED FORWARD)
>|||UPDATE Titles
SET royalty
= (SELECT S.royalty
FROM Sales AS S
WHERE Titles.title_id = S.title_id);
Avoid the unpredictable proprietary syntax needless complexity.|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
My question is why Update statement makes different execution plan from
Select statement? Please note that I'm using here SQL Server syntax to make
Update statement.
Regards
Nikola
"Karl Gram" <karl@.gramonline.nl> wrote in message
news:%2337%23SL%23WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hi Nikola,
> The first impression I have when I look at your SQL is that you are
> overcomplicating things. Are these actual SQL statements you use or are
> these just samples?
> Your SELECT could be rewritten as follows with the same result ifI'm not
> mistaken:
> SELECT T.*
> FROM pubs.dbo.titles T
> JOIN pubs.dbo.sales STT
> ON T.title_id = STT.title_id
> --
> HTH
> Karl Gram
>
> "Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
> news:O$HwfO9WFHA.1468@.tk2msftngp13.phx.gbl...
>|||Many thanks for your reply,
I made those SQL statements from sample database pubs and they actually
work, you can test it in your SQL server. They are not overcomplicated as
they have real meaning in my real database. Idea is to make equal data from
two tables.
Also, please note that I'm using here SQL Server syntax to make Update
statement. My question is why Update statement makes different execution
plan from Select statement?
Your SQL does not work. Table S does not have royalty column.
Regards
Nikola
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116458847.966216.212830@.z14g2000cwz.googlegroups.com...
> UPDATE Titles
> SET royalty
> = (SELECT S.royalty
> FROM Sales AS S
> WHERE Titles.title_id = S.title_id);
> Avoid the unpredictable proprietary syntax needless complexity.
>

Execution Plan difft for 2 same queries..

HI,
Some confusing execution plan of query..
below are all indexes created on tmreqbatch table..
---
ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY BatchCode
NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
----
This is query and execution plan.
SELECT Corpprodid,reqbatstat
FROM disbursement.TMREQBATCH with (nolock)
WHERE Corpprodid = 5
AND Reqbatstat =12
|--Merge Join(Inner Join, MERGE:([KeyCo1])=([KeyCo1]), RESIDUAL:([KeyCo1]=[KeyCo1]))
|--Index Seek(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpProdId]), SEEK:([TMReqBatch].[CorpProdId]=Convert([@.1])) ORDERED FORWARD)
|--Index Seek(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[NIDX_TmRBatRst]), SEEK:([TMReqBatch].[ReqBatStat]=12) ORDERED FORWARD)
I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE JOIN..WHEREAS IM NOT USING ANY JOINS IN MY QUERY..
RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2 MILLION RECORDS...
I THINK it should use one index and bookmark loop......
==========================================
same query but using alias showing other execution plan...
--
SELECT TMREQBATCH.id, TMREQBATCH.batchcode
FROM disbursement.TMREQBATCH WITH (NOLOCK)
WHERE TMREQBATCH.Corpprodid = 5
AND TMREQBATCH.Reqbatstat =12
|--Clustered Index Scan(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[PK_TMReqBatch]), WHERE:([TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBatStat]=12))
=============== Pls help wot exactly is going on......
SAnjaySanjay
I've just tested it on [Order Details] table of the NorthWind database
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT OrderID,ProductID
FROM [order details]as t
WHERE OrderID=10348 AND ProductID=1
For me it produces the same query plan.
> FROM disbursement.TMREQBATCH
This way creating alias should throw the error 'Invalid object name
disbursement.TMREQBATCH'.
"Sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:CC28709B-6706-4C79-B20F-79339CED1805@.microsoft.com...
> HI,
> Some confusing execution plan of query..
> below are all indexes created on tmreqbatch table..
> ---
> ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
> ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
> IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY
BatchCode
> NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
> PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
> ----
> This is query and execution plan.
> SELECT Corpprodid,reqbatstat
> FROM disbursement.TMREQBATCH with (nolock)
> WHERE Corpprodid = 5
> AND Reqbatstat =12
> |--Merge Join(Inner Join, MERGE:([KeyCo1])=([KeyCo1]),
RESIDUAL:([KeyCo1]=[KeyCo1]))
> |--Index
Seek(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpPro
dId]), SEEK:([TMReqBatch].[CorpProdId]=Convert([@.1])) ORDERED FORWARD)
> |--Index
Seek(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[NIDX_TmRBatRst]),
SEEK:([TMReqBatch].[ReqBatStat]=12) ORDERED FORWARD)
> I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE JOIN..WHEREAS IM
NOT USING ANY JOINS IN MY QUERY..
> RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2 MILLION RECORDS...
> I THINK it should use one index and bookmark loop......
> ==========================================> same query but using alias showing other execution plan...
> --
> SELECT TMREQBATCH.id, TMREQBATCH.batchcode
> FROM disbursement.TMREQBATCH WITH (NOLOCK)
> WHERE TMREQBATCH.Corpprodid = 5
> AND TMREQBATCH.Reqbatstat =12
>
> |--Clustered Index
Scan(OBJECT:([ICICI_DISB].[disbursement].[TMReqBatch].[PK_TMReqBatch]),
WHERE:([TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBatStat]=12))
> ===============> Pls help wot exactly is going on......
> SAnjay|||hi,
but im still clueless when there are no joins in query..why execution plan is showing merge join...|||sql server is using an index intersection, you query is
equivalent to:
SELECT Corpprodid,reqbatstat
FROM TMREQBATCH a
INNER JOIN TMREQBATCH b ON b.ID = a.ID
WHERE a.Corpprodid = 5
AND b.Reqbatstat =12
so it using the index on each column of the SARG, then
does a merge join on the PK
your query would also run fast with a covered index (on
both columns of the SARG) if both columns are frequently
specified as SARGs
>--Original Message--
>HI,
> Some confusing execution plan of query..
>below are all indexes created on tmreqbatch table..
>---
>ID_TmReqBatch_CorpProdId nonclustered located on
PRIMARY CorpProdId
>ID_TmReqBatch_FundId nonclustered located on PRIMARY
FundId
>IX_TMReqBatch nonclustered, unique, unique key located
on PRIMARY BatchCode
>NIDX_TmRBatRst nonclustered located on PRIMARY
ReqBatStat
>PK_TMReqBatch clustered, unique, primary key located on
PRIMARY Id
>----
--
>This is query and execution plan.
>SELECT Corpprodid,reqbatstat
> FROM disbursement.TMREQBATCH with (nolock)
> WHERE Corpprodid = 5
> AND Reqbatstat =12
> |--Merge Join(Inner Join, MERGE:([KeyCo1])=([KeyCo1]),
RESIDUAL:([KeyCo1]=[KeyCo1]))
> |--Index Seek(OBJECT:([ICICI_DISB].[disbursement].
[TMReqBatch].[ID_TmReqBatch_CorpProdId]), SEEK:
([TMReqBatch].[CorpProdId]=Convert([@.1])) ORDERED FORWARD)
> |--Index Seek(OBJECT:([ICICI_DISB].[disbursement].
[TMReqBatch].[NIDX_TmRBatRst]), SEEK:([TMReqBatch].
[ReqBatStat]=12) ORDERED FORWARD)
>I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE
JOIN..WHEREAS IM NOT USING ANY JOINS IN MY QUERY..
>RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2
MILLION RECORDS...
>I THINK it should use one index and bookmark loop......
>==========================================>same query but using alias showing other execution plan...
>--
>SELECT TMREQBATCH.id, TMREQBATCH.batchcode
> FROM disbursement.TMREQBATCH WITH (NOLOCK)
> WHERE TMREQBATCH.Corpprodid = 5
> AND TMREQBATCH.Reqbatstat =12
>
> |--Clustered Index Scan(OBJECT:([ICICI_DISB].
[disbursement].[TMReqBatch].[PK_TMReqBatch]), WHERE:
([TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBatStat]
=12))
>===============>Pls help wot exactly is going on......
>SAnjay
>.
>|||Your 2 queries are NOT the same. The result sets include different columns;
therefore a comparison of the query plans is not meaningful. If you examine
the query plan closely for the 1st, you will note that it is using two
indexes. This is because your query selects rows based on only two columns,
and these same two columns are indexed separately. Therefore, the optimizer
concluded that it could join the two indexes to generate the appropriate
resultset.
The optimizer isn't perfect so perhaps it chose an inefficient plan.
Before you conclude that, you should make sure that your statistics are
current. In addition, you might want to re-examine your indexing design.
Perhaps you need another index on (Corpprodid,reqbatstat). You might also
want to consider adding one column to the index on the other column. Or
maybe a join hint. You might also want to consider clustering on something
other than (id); is this column the best choice for a clustered index? The
best answer is dependent on the data, how it is accessed, the frequency of
access, and the frequency of updates.
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:8E76A851-A121-411B-81B9-2275D8A47205@.microsoft.com...
> hi,
> but im still clueless when there are no joins in query..why execution plan
is showing merge join...
>|||Thanks Joe.
It solved the confusion

Execution Plan difft for 2 same queries..

HI,
Some confusing execution plan of query..
below are all indexes created on tmreqbatch table..
---
ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY BatchCode
NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
----
This is query and execution plan.
SELECT Corpprodid,reqbatstat
FROM disbursement.TMREQBATCH with (nolock)
WHERE Corpprodid = 5
AND Reqbatstat =12
|--Merge Join(Inner Join, MERGE[KeyCo1])=([KeyCo1]), RESIDUAL[KeyCo1]=[K
eyCo1]))
|--Index Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpProdId]), SEEK[TMReqBatch].[CorpProdId]=Co
nvert([@.1])) ORDERED FORWARD)
|--Index Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[NIDX_TmRBatRst]), SEEK[TMReqBatch].[ReqBatStat]=12) ORDERED
FORWARD)
I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE JOIN..WHEREAS IM NOT
USING ANY JOINS IN MY QUERY..
RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2 MILLION RECORDS...
I THINK it should use one index and bookmark loop......
========================================
==
same query but using alias showing other execution plan...
--
SELECT TMREQBATCH.id, TMREQBATCH.batchcode
FROM disbursement.TMREQBATCH WITH (NOLOCK)
WHERE TMREQBATCH.Corpprodid = 5
AND TMREQBATCH.Reqbatstat =12
|--Clustered Index Scan(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[PK_TMReqBatch]), WHERE[TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBat
Stat]=12))
===============
Pls help wot exactly is going on......
SAnjaySanjay
I've just tested it on [Order Details] table of the NorthWind database
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT OrderID,ProductID
FROM [order details]as t
WHERE OrderID=10348 AND ProductID=1
For me it produces the same query plan.

quote:

---

> FROM disbursement.TMREQBATCH

---

This way creating alias should throw the error 'Invalid object name
disbursement.TMREQBATCH'.
"Sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:CC28709B-6706-4C79-B20F-79339CED1805@.microsoft.com...

quote:

---

> HI,
> Some confusing execution plan of query..
> below are all indexes created on tmreqbatch table..
> ---
> ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
> ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
> IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY

---

BatchCode

quote:

---

> NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
> PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
> ----
> This is query and execution plan.
> SELECT Corpprodid,reqbatstat
> FROM disbursement.TMREQBATCH with (nolock)
> WHERE Corpprodid = 5
> AND Reqbatstat =12
> |--Merge Join(Inner Join, MERGE[KeyCo1])=([KeyCo1]),

---

RESIDUAL[KeyCo1]=[KeyCo1]))

quote:

---

[col
or=darkred]
> |--Index[/color]

---

Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpPro
dId]), SEEK[TMReqBatch].[CorpProdId]=Convert([@.1])) ORDERED FORWARD)

quote:

---

> |--Index

---

Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[NIDX_TmRBatRst]),
SEEK[TMReqBatch].[ReqBatStat]=12) ORDERED FORWARD)

quote:

---

> I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE JOIN..WHEREAS IM

---

NOT USING ANY JOINS IN MY QUERY..

quote:

---

> RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2 MILLION RECORDS...
> I THINK it should use one index and bookmark loop......
> ========================================
==
> same query but using alias showing other execution plan...
> --
> SELECT TMREQBATCH.id, TMREQBATCH.batchcode
> FROM disbursement.TMREQBATCH WITH (NOLOCK)
> WHERE TMREQBATCH.Corpprodid = 5
> AND TMREQBATCH.Reqbatstat =12
>
> |--Clustered Index

---

Scan(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[PK_TMReqBatch]),
WHERE[TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBatStat]=12))

quote:

---

> ===============
> Pls help wot exactly is going on......
> SAnjay

---

|||hi,
but im still clueless when there are no joins in query..why execution plan i
s showing merge join...|||Your 2 queries are NOT the same. The result sets include different columns;
therefore a comparison of the query plans is not meaningful. If you examine
the query plan closely for the 1st, you will note that it is using two
indexes. This is because your query selects rows based on only two columns,
and these same two columns are indexed separately. Therefore, the optimizer
concluded that it could join the two indexes to generate the appropriate
resultset.
The optimizer isn't perfect so perhaps it chose an inefficient plan.
Before you conclude that, you should make sure that your statistics are
current. In addition, you might want to re-examine your indexing design.
Perhaps you need another index on (Corpprodid,reqbatstat). You might also
want to consider adding one column to the index on the other column. Or
maybe a join hint. You might also want to consider clustering on something
other than (id); is this column the best choice for a clustered index? The
best answer is dependent on the data, how it is accessed, the frequency of
access, and the frequency of updates.
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:8E76A851-A121-411B-81B9-2275D8A47205@.microsoft.com...

quote:

---

> hi,
> but im still clueless when there are no joins in query..why execution plan

---

is showing merge join...

quote:

---

>

---