Optimizing query execution...

We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...

SELECT DISTINCT a.auf_nr AS OrderNo,
e.ku_name AS Customer,
d.bestell_dat AS OrdDate,
d.liefer_dat AS DelvDate,
CAST(SUM(b.anz) AS FLOAT) Qty,
CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM,
CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv,
CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value

FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_LIP_STATUS b,
liorder..LIORDER.AUF_POS c,
liorder..LIORDER.AUF_KOPF d,
liorder..LIORDER.KUST_ADR e

WHERE a.auf_nr = b.auf_nr and
b.auf_nr = c.auf_nr and
c.auf_nr = d.auf_nr and
d.kunr = e.ku_nr and
a.auf_pos = b.auf_pos and
b.auf_pos = c.auf_pos and
b.lip_status = 7 and
c.ver_art !='V' and
a.history = 0 and
a.rg_stat != 2 and
e.ku_name IS not null and
e.ku_vk_ek = 0 and
d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'

GROUP BY a.auf_nr,
d.liefer_dat,
b.lip_status,
d.bestell_dat,
e.ku_name,
d.kopf_tour,
d.kopf_firma

HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0

..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?I'd use OPENQUERY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp).

-PatP

optimize the stored procedure

CREATE PROCEDURE emp_summary

@.emp_id int ,@.start_date datetime=0,@.end_date datetime=0

AS

SET NOCOUNT ON

IF @.start_date=0 AND @.end_date=0

BEGIN

SET @.end_date=getdate()

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date

END

ELSE

SELECT *
FROM emp WHERE emp_id_id=@.emp_id AND a.join_date>@.start_date AND a.joindate<=@.end_date+1
GO

This is the Stored procedure i wrote to get the emp summary with date range and with no date ranges.If i pass start_date and end_Date Sp executes 'else' part if dont pass the parameters it execultes 'IF' part.Can i optimize this SP further?I'd use:CREATE PROCEDURE
@.emp_id INT
, @.start_date DATETIME = NULL
, @.end_date DATETIME = NULL
AS

SELECT *
FROM emp
WHERE emp_id = @.emp_id
AND join_date BETWEEN Coalesce(@.start_date, join_date) AND Coalesce(@.end_date, join_date)

RETURN
GOIf you can't afford the table scan, I'd resort to using dynamic SQL, but I'd try this first.

-PatP

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Operand type clash error

Hello!

I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.

ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN

Help!

Thanks.

I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.

|||

You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).

|||

Thanks.

I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?

Thanks!

|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||

Here is it.

<asp:SqlDataSourceID="Update"runat="server"

SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">

<UpdateParameters>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>

</UpdateParameters>

</asp:SqlDataSource>

|||

Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?

Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.

|||

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

Any Ideas?

EndSub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Conversion from type 'DBNull' to type 'String' is not valid.

Here is the code for the updated parameters

ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

EndSub

|||

oops sorry for the last post...let me repost again.

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

Here's the code for updating parameters

Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Here is the code for the updated parameters

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

I got an error for the updated parameters code

Conversion from type 'DBNull' to type 'String' is not valid.

Any Ideas?

|||

Help?

It won't update the fields that currently have null values...

Help again? :)

|||Are you getting an error? Is it updating the fields that don't have null values?|||

Yes to both.

I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.

Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.

Thanks!!

|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.