What Does 'No Overload For Method 'Insert' Takes '1' Arguments' Mean?
Dec 10, 2007
code that caused this error: line AddInBookSqlDataSource2.Insert(item);protected void inbookButton_Click(object sender, EventArgs e){
try{
AddInBookSqlDataSource1.Insert();
}catch (Exception ex){
uploadSPoneLabel.Text = "Saved Failed: SP One" + ex.Message;
}foreach (ListItem item in authorsListBox5.Items){
try{
AddInBookSqlDataSource2.Insert(item);
saveStatusLabel.Text = "Save Successful: SP Two";
}catch (Exception ex1){saveStatusLabel.Text = "Save Failed: SP Two" + ex1.Message;
}
}
}
any help appreciated
Thanks in advance
View 1 Replies
ADVERTISEMENT
May 19, 2008
Dear All,
I have a problem while trying to update the content of my page to the database by the means of a stored procedure
string OcompConnection = ConfigurationManager.ConnectionStrings["GenUserCode"].ConnectionString;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(OcompConnection);
System.Data.SqlClient.SqlCommand ocmd = new System.Data.SqlClient.SqlCommand("Dealer_Insert", conn);ocmd.CommandType = CommandType.StoredProcedure;
ocmd.Parameters.Add(new SqlParameter("@UserCode"), SqlDbType.NVarChar);
ocmd.Parameters["@UserCode"] = TxtUserCode;
and also there is another error message saying that argument1: can not comvert from system.data.sqlclient.sqlparameter to string.
What am i Missing???
Eventually there is the try{open} and finally{close}
Many Thanks
View 3 Replies
View Related
May 31, 2008
I have problem with LINQ. I have created SQL Server 2005 database Database.mdf in App_Data folder with two tables - Pages and PagesGroups. Table Pages consist of fields PageID, AspxForm, DescriptionEN, DescriptionPL, PagesGroupID, NavNameEN, NavNamePL, PageActive, NavToolTipEN, NavToolTipPL and table PagesGroups consist of PagesGroupID, NavGroupNameEN, NavGroupNamePL, NavGroupToolTipEN, NavGroupToolTipPL, GroupDescriptionPL, GroupDescriptionEN, GroupActive. I added example rows. I created DataClasses.dbml, where Pages is child of PagesGroups, and DataClasses.designer.cs, which cause error "No overload for method 'DataContext' takes '0' arguments", in App_Code folder. I started writing LINQ commands in master page (DataClassesDataContext db = new DataClassesDataContext(); var pages = from p in db.Pages select new { Description = p.PagesGroup.GroupDescriptionPL };).
What should I write in DataClasses.designer.cs that errors does not occur? What is wrong in my DataClasses.designer.cs?
I wrote source of DataClasses.designer.cs, MasterPage.master and error message below.
App_Code/DataClasses.designer.cs:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;using System.Reflection;
[System.Data.Linq.Mapping.DatabaseAttribute(Name="Database")]
public partial class DataClassesDataContext : System.Data.Linq.DataContext
{
private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
public System.Data.Linq.Table<Page> Pages
{
get
{return this.GetTable<Page>();
}
}
public System.Data.Linq.Table<PagesGroup> PagesGroups
{
get
{return this.GetTable<PagesGroup>();
}
}
}
[Table(Name="dbo.Pages")]
public partial class Page : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
private int _PageID;
private string _AspxForm;
private string _DescriptionEN;
private string _DescriptionPL;
private int _PagesGroupID;
private string _NavNameEN;
private string _NavNamePL;
private bool _PageActive;
private string _NavToolTipEN;
private string _NavToolTipPL;
private EntityRef<PagesGroup> _PagesGroup;
public Page()
{
this._PagesGroup = default(EntityRef<PagesGroup>);
}
[Column(Storage="_PageID", AutoSync=AutoSync.Always, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int PageID
{
get
{
return this._PageID;
}
set
{
if ((this._PageID != value))
{
this.SendPropertyChanging();
this._PageID = value;
this.SendPropertyChanged("PageID");
}
}
}
[Column(Storage="_AspxForm", DbType="Char(10) NOT NULL", CanBeNull=false)]
public string AspxForm
{
get
{
return this._AspxForm;
}
set
{
if ((this._AspxForm != value))
{
this.SendPropertyChanging();
this._AspxForm = value;
this.SendPropertyChanged("AspxForm");
}
}
}
[Column(Storage="_DescriptionEN", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string DescriptionEN
{
get
{
return this._DescriptionEN;
}
set
{
if ((this._DescriptionEN != value))
{
this.SendPropertyChanging();
this._DescriptionEN = value;
this.SendPropertyChanged("DescriptionEN");
}
}
}
[Column(Storage="_DescriptionPL", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string DescriptionPL
{
get
{
return this._DescriptionPL;
}
set
{
if ((this._DescriptionPL != value))
{
this.SendPropertyChanging();
this._DescriptionPL = value;
this.SendPropertyChanged("DescriptionPL");
}
}
}
[Column(Storage="_PagesGroupID", DbType="Int NOT NULL")]
public int PagesGroupID
{
get
{
return this._PagesGroupID;
}
set
{
if ((this._PagesGroupID != value))
{
if (this._PagesGroup.HasLoadedOrAssignedValue)
{
throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
}
this.SendPropertyChanging();
this._PagesGroupID = value;
this.SendPropertyChanged("PagesGroupID");
}
}
}
[Column(Storage="_NavNameEN", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string NavNameEN
{
get
{
return this._NavNameEN;
}
set
{
if ((this._NavNameEN != value))
{
this.SendPropertyChanging();
this._NavNameEN = value;
this.SendPropertyChanged("NavNameEN");
}
}
}
[Column(Storage="_NavNamePL", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string NavNamePL
{
get
{
return this._NavNamePL;
}
set
{
if ((this._NavNamePL != value))
{
this.SendPropertyChanging();
this._NavNamePL = value;
this.SendPropertyChanged("NavNamePL");
}
}
}
[Column(Storage="_PageActive", DbType="Bit NOT NULL")]
public bool PageActive
{
get
{
return this._PageActive;
}
set
{
{
this.OnPageActiveChanging(value);
this.SendPropertyChanging();
this._PageActive = value;
this.SendPropertyChanged("PageActive");
}
}
}
private void OnPageActiveChanging(bool value)
{
throw new NotImplementedException();
}
[Column(Storage="_NavToolTipEN", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string NavToolTipEN
{
get
{
return this._NavToolTipEN;
}
set
{
if ((this._NavToolTipEN != value))
{
this.SendPropertyChanging();
this._NavToolTipEN = value;
this.SendPropertyChanged("NavToolTipEN");
}
}
}
[Column(Storage="_NavToolTipPL", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string NavToolTipPL
{
get
{
return this._NavToolTipPL;
}
set
{
if ((this._NavToolTipPL != value))
{
this.SendPropertyChanging();
this._NavToolTipPL = value;
this.SendPropertyChanged("NavToolTipPL");
}
}
}
[Association(Name="PagesGroup_Page", Storage="_PagesGroup", ThisKey="PagesGroupID", IsForeignKey=true)]
public PagesGroup PagesGroup
{
get
{
return this._PagesGroup.Entity;
}
set
{
PagesGroup previousValue = this._PagesGroup.Entity;
if (((previousValue != value)
|| (this._PagesGroup.HasLoadedOrAssignedValue == false)))
{
this.SendPropertyChanging();
if ((previousValue != null))
{
this._PagesGroup.Entity = null;
previousValue.Pages.Remove(this);
}
this._PagesGroup.Entity = value;
if ((value != null))
{
value.Pages.Add(this);
this._PagesGroupID = value.PagesGroupID;
}
else
{
this._PagesGroupID = default(int);
}
this.SendPropertyChanged("PagesGroup");
}
}
}
public event PropertyChangingEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void SendPropertyChanging()
{
if ((this.PropertyChanging != null))
{
this.PropertyChanging(this, emptyChangingEventArgs);
}
}
protected virtual void SendPropertyChanged(String propertyName)
{
if ((this.PropertyChanged != null))
{
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
}
[Table(Name="dbo.PagesGroups")]
public partial class PagesGroup : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
private int _PagesGroupID;
private string _NavGroupNameEN;
private string _NavGroupNamePL;
private string _NavGroupToolTipEN;
private string _NavGroupToolTipPL;
private string _GroupDescriptionEN;
private string _GroupDescriptionPL;
private bool _GroupActive;
private EntitySet<Page> _Pages;
public PagesGroup()
{
this._Pages = new EntitySet<Page>(new Action<Page>(this.attach_Pages), new Action<Page>(this.detach_Pages));
}
[Column(Storage="_PagesGroupID", AutoSync=AutoSync.Always, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int PagesGroupID
{
get
{
return this._PagesGroupID;
}
set
{
if ((this._PagesGroupID != value))
{
this.SendPropertyChanging();
this._PagesGroupID = value;
this.SendPropertyChanged("PagesGroupID");
}
}
}
[Column(Storage="_NavGroupNameEN", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string NavGroupNameEN
{
get
{
return this._NavGroupNameEN;
}
set
{
if ((this._NavGroupNameEN != value))
{
this.SendPropertyChanging();
this._NavGroupNameEN = value;
this.SendPropertyChanged("NavGroupNameEN");
}
}
}
[Column(Storage="_NavGroupNamePL", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string NavGroupNamePL
{
get
{
return this._NavGroupNamePL;
}
set
{
if ((this._NavGroupNamePL != value))
{
this.SendPropertyChanging();
this._NavGroupNamePL = value;
this.SendPropertyChanged("NavGroupNamePL");
}
}
}
[Column(Storage="_NavGroupToolTipEN", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string NavGroupToolTipEN
{
get
{
return this._NavGroupToolTipEN;
}
set
{
if ((this._NavGroupToolTipEN != value))
{
this.SendPropertyChanging();
this._NavGroupToolTipEN = value;
this.SendPropertyChanged("NavGroupToolTipEN");
}
}
}
[Column(Storage="_NavGroupToolTipPL", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string NavGroupToolTipPL
{
get
{
return this._NavGroupToolTipPL;
}
set
{
if ((this._NavGroupToolTipPL != value))
{
this.SendPropertyChanging();
this._NavGroupToolTipPL = value;
this.SendPropertyChanged("NavGroupToolTipPL");
}
}
}
[Column(Storage="_GroupDescriptionEN", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string GroupDescriptionEN
{
get
{
return this._GroupDescriptionEN;
}
set
{
if ((this._GroupDescriptionEN != value))
{
this.SendPropertyChanging();
this._GroupDescriptionEN = value;
this.SendPropertyChanged("GroupDescriptionEN");
}
}
}
[Column(Storage="_GroupDescriptionPL", DbType="NText", UpdateCheck=UpdateCheck.Never)]
public string GroupDescriptionPL
{
get
{
return this._GroupDescriptionPL;
}
set
{
if ((this._GroupDescriptionPL != value))
{
this.SendPropertyChanging();
this._GroupDescriptionPL = value;
this.SendPropertyChanged("GroupDescriptionPL");
}
}
}
[Column(Storage="_GroupActive", DbType="Bit NOT NULL")]
public bool GroupActive
{
get
{
return this._GroupActive;
}
set
{
if ((this._GroupActive != value))
{
this.SendPropertyChanging();
this._GroupActive = value;
this.SendPropertyChanged("GroupActive");
}
}
}
[Association(Name="PagesGroup_Page", Storage="_Pages", OtherKey="PagesGroupID")]
public EntitySet<Page> Pages
{
get
{
return this._Pages;
}
set
{
this._Pages.Assign(value);
}
}
public event PropertyChangingEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void SendPropertyChanging()
{
if ((this.PropertyChanging != null))
{
this.PropertyChanging(this, emptyChangingEventArgs);
}
}
protected virtual void SendPropertyChanged(String propertyName)
{
if ((this.PropertyChanged != null))
{
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
private void attach_Pages(Page entity)
{
this.SendPropertyChanging();
entity.PagesGroup = this;
}
private void detach_Pages(Page entity)
{
this.SendPropertyChanging();
entity.PagesGroup = null;
}
}
MasterPage.master:
...
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
...
public partial class MasterPage : System.Web.UI.MasterPage
{public void Page_Load(object sender, EventArgs e)
{
...
DataClassesDataContext db = new DataClassesDataContext();
...
if (Page.UICulture == "Polish")
{
...var pages = from p in db.Pages
where p.PagesGroup.GroupActive == true && p.PageActive == trueselect new { PagesGroupDescription = p.PagesGroup.GroupDescriptionPL };
}
else
{var pages = from p in db.Pages
where p.PagesGroup.GroupActive == true && p.PageActive == true
select new { PagesGroupDescription = p.PagesGroup.GroupDescriptionEN };
...
}
...
}
}
Error:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS1501: No overload for method 'DataContext' takes '0' argumentsc:UsersAdministratorDocumentsMy Web SitesKamil Szmit (szmitek)App_CodeDataClasses.designer.cs(25,22): error CS1501: No overload for method 'DataContext' takes '0' arguments
c:WindowsassemblyGAC_MSILSystem.Data.Linq3.5.0.0__b77a5c561934e089System.Data.Linq.dll: (Location of symbol related to previous error)
View 1 Replies
View Related
Oct 19, 2007
Hello, I am designing a small databse example.
The database only contains one table 'Employee'.
Only five fields: EmployeeID(primary key), firstName,lastName,phoneNumber,Salary.
Salary is an integer.
Now I drag some buttons to the form. One button is to find employee by ID, find employee by last name and find all employees inside a certain salary range.
My Table Adapter--Change the Update and Delete command to only need 1
parameter(primary key).
But I get three compiling errors.
Error1No overload for method 'FillByEmployeeID' takes '2' arguments
Error2No overload for method 'FillBylastName' takes '2' arguments
Error3No overload for method 'FillBySalary' takes '2' arguments
Here is my codes
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace HUIDB
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void employeeBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.employeeBindingSource.EndEdit();
this.employeeTableAdapter.Update(this.dataSet1.Employee);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'dataSet1.Employee' table. You can move, or remove it, as needed.
this.employeeTableAdapter.Fill(this.dataSet1.Employee);
}
private void ADD_Click(object sender, EventArgs e)
{
String EmployeeID = textBox1.Text;
String firstName = textBox2.Text;
String lastName = textBox3.Text;
String phoneNumber = textBox4.Text;
int Salary = Int32.Parse(textBox5.Text);
employeeTableAdapter.Fill(this.dataSet1.Employee);
}
private void DELETE_Click(object sender, EventArgs e)
{
String EmployeeID = textBox1.Text;
if (this.employeeTableAdapter.Delete(EmployeeID) == 0)
MessageBox.Show("Deletion failed");
else
this.employeeTableAdapter.Fill(this.dataSet1.Employee);
}
private void UPDATE_Click(object sender, EventArgs e)
{
String EmployeeID = textBox1.Text;
String firstName = textBox2.Text;
String lastName = textBox3.Text;
String phoneNumber = textBox4.Text;
int Salary = Int32.Parse(textBox5.Text);
if (this.employeeTableAdapter.Update(EmployeeID, firstName, lastName, phoneNumber,Salary, EmployeeID) == 0)
MessageBox.Show("Update failed");
else
this.employeeTableAdapter.Fill(this.dataSet1.Employee);
}
private void FIND_Click(object sender, EventArgs e)
{
String EmployeeID = textBox1.Text;
String lastName = textBox3.Text;
int Salary = Int32.Parse(textBox5.Text);
this.employeeTableAdapter.FillByEmployeeID(dataSet1.Employee, EmployeeID); // wrong here
this.employeeTableAdapter.FillBylastName(dataSet1.Employee, lastName); // wrong here
this.employeeTableAdapter.FillBySalary(dataSet1.Employee, Salary); // wrong here
}
private void REFRESH_Click(object sender, EventArgs e)
{
this.employeeTableAdapter.Fill(this.dataSet1.Employee);
}
}
}
Could you please give me some advice?
Thanks!
View 1 Replies
View Related
Feb 18, 2008
Hello, I want to get data from datatable as below. I am getting error:
BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments. I did not understand what is wrong. Because everything is same as msdn library.
my codebehind is:
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClientPartial Class Default2 Inherits System.Web.UI.Page
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=Database;Initial Catalog=otel;Integrated Security=True;Pooling=False"
End FunctionProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim connectionString As String = _
GetConnectionString()
' Create a SqlConnection to the database.Using connection As SqlConnection = New SqlConnection(connectionString)
' Create a SqlDataAdapter for the Suppliers table.Dim mailsAdapter As SqlDataAdapter = _ New SqlDataAdapter()
' A table mapping names the DataTable.mailsAdapter.TableMappings.Add("Table", "Pages")
connection.Open()Dim PagesCommand As SqlCommand = New SqlCommand( _"SELECT * FROM Pages", _
connection)
PagesCommand.CommandType = CommandType.Text
' Set the SqlDataAdapter's SelectCommand.
mailsAdapter.SelectCommand = PagesCommand
' Fill the DataSet.
Dim dataSet1 As Dataset = New Dataset("Pages") 'ERROR MESSAGE HERE...........................................mailsAdapter.Fill(dataSet1)
connection.Close()LblPageName.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(1))
TxtPageTitle.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(2))TxtPageSummary.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(3))
Rte1.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(4))TxtPageimgUrl.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(5))
End Using
End Sub
End Class
View 1 Replies
View Related
Mar 25, 2007
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose
Thanks Steve
View 5 Replies
View Related
May 14, 2008
Hi Teachies,
I am using SQL Server Standard Edition .
in one of my table , there i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please suggest me.
Thanks
Rajesh Varma
View 2 Replies
View Related
Jul 23, 2005
HelloI have these tables:CREATE TABLE [dbo].[COREAttribute] ([oid] [uniqueidentifier] NOT NULL ,[CLSID] [uniqueidentifier] NOT NULL) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON[PRIMARY]CREATE TABLE [dbo].[COREBstrAttribute] ([oid] [uniqueidentifier] NOT NULL ,[iid] [uniqueidentifier] NOT NULL ,[dispid] [int] NOT NULL ,[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]Now when I try this query, it's taking 8-10mins.Declare @t TABLE (oid uniqueidentifier primary key,[Description] nvarchar(1024) NULL,[Name] nvarchar(1024) NULL,[UID] nvarchar(1024) NULL)DECLARE @COREBSTRAttribute TABLE (oid uniqueidentifier, dispid intNULL, value nvarchar(1024) NULL)INSERT INTO @COREBSTRAttribute select oid, dispid, valueFROM dbo.COREBSTRAttributeWHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'INSERT @tSELECT distinctc0.oid,c1.Value,c2.Value,c3.ValueFROM(SELECT oid FROM dbo.COREAttributeWHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}','{1449DB2D-DB97-11D6-A551-00B0D021E10A}','{1449DB2F-DB97-11D6-A551-00B0D021E10A}','{1449DB31-DB97-11D6-A551-00B0D021E10A}','{1449DB33-DB97-11D6-A551-00B0D021E10A}','{1449DB35-DB97-11D6-A551-00B0D021E10A}','{1449DB37-DB97-11D6-A551-00B0D021E10A}','{1449DB39-DB97-11D6-A551-00B0D021E10A}','{1449DB3B-DB97-11D6-A551-00B0D021E10A}','{1449DB3D-DB97-11D6-A551-00B0D021E10A}','{1449DB3F-DB97-11D6-A551-00B0D021E10A}','{1449DB43-DB97-11D6-A551-00B0D021E10A}','{1449DB45-DB97-11D6-A551-00B0D021E10A}','{1449DB47-DB97-11D6-A551-00B0D021E10A}','{1449DB49-DB97-11D6-A551-00B0D021E10A}','{1449DB4B-DB97-11D6-A551-00B0D021E10A}','{1449DB4D-DB97-11D6-A551-00B0D021E10A}','{1449DB51-DB97-11D6-A551-00B0D021E10A}','{DAA598D9-E7B5-4155-ABB7-0C2C24466740}','{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}','{128F17D4-2014-480A-96C6-370599F32F67}','{9F3A64C9-28F3-440B-B694-3E341471ED8E}','{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}','{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}','{2BB836D3-2DC1-4899-9406-6A495ED395C3}','{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}','{E18E470B-B297-43D2-B9CD-71AF65654970}','{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}','{0E91AC62-7929-4B42-B771-7A6399A9E3B0}','{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}','{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}','{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}','{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}','{19082B90-EF02-45CC-B037-AFD0CF91D69E}','{6F76CEF7-EBC0-48C6-8B78-C5330324C019}','{18492042-B22A-4370-BFA3-D0481800BBC7}','{A71343AD-CC09-4033-A224-D2D8C300904A}','{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}','{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}','{A923226F-B920-4CFA-9B0D-F422D1C36902}','{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}','{C31DB61A-5221-42CF-9A73-FE76D5158647}')) AS c0LEFT JOIN @COREBSTRAttribute AS c1ON (c0.oid = c1.oid)AND c1.dispid = 28LEFT JOIN @COREBSTRAttribute AS c2ON (c0.oid = c2.oid)AND c2.dispid = 112LEFT JOIN @COREBSTRAttribute AS c3ON (c0.oid = c3.oid)AND c3.dispid = 192Any help is greatly appreciated.thanksSunit
View 4 Replies
View Related
Jul 20, 2005
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When thenew month starts I create a new table with the same fields and move the dataof previous month into that table and delete it from calls. So 'calls' holdsthe data of only the current month. for example at the start of november2003 I ran the queriesCreate Table Oct2003Calls {................................}/* Now insert data of october into new table */INSERT Oct2003CallsSELECT *FROM callsWHERE calldate < '11/1/03'/* Finaly delete october data from calls table */DELETE FROM callsWHERE calldate < '11/1/03'The problem is that while the insert query takes about 2 minutes to executethe delete queries takes over 10 minutes to affect the same no. of rows. Whyis that?This causes problems because user authentication stops when this query isrunning which means users cant connect to the internet.
View 4 Replies
View Related
Apr 7, 2008
Hi,
It seems inserting records takes a relatively long time. My guess is it needs time to allocate disk space for the extra space needed. Assuming this is true, are there any DB settings that allow auto space allocation in bigger chunk? I am looking for something like "DB growth factor" or " Table growth factor"
Thanks for any info.
View 6 Replies
View Related
May 14, 2008
Hi Teachies,
I am using SQL Server Standard Edition with good HardWare configuration.
In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please suggest me.
Thanks
Rajesh Varma
View 17 Replies
View Related
Apr 9, 2007
Could some body in microsoft database team explain this behavior? Problem is predominant when cardinality of a column is very high and a where clause is specified on that column. Both use the same index.
select a12.DATE_INVOICE_ID DATE_INVOICE_ID,
a11.CUSTOMER_ID CUSTOMER_ID,
sum(a11.EXTENDED_PRICE) WJXBFS1,
sum(a11.TOTAL_COST) WJXBFS2,
(sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3
from FACT_SALES_LINE a11
join RLTN_V_SL_INVOICE_YTD a12
on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID)
join LKP_V_SL_EXPENSE_CODE a13
on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID)
join LKP_V_SL_LAST_STATUS a14
on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID)
join LKP_V_SL_NEXT_STATUS a15
on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID)
join LKP_V_SL_ORDER_TYPE a16
on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID)
where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012)
and a12.DATE_INVOICE_ID = 106365
and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU')
and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600)
and a13.EXPENSE_CODE_SRCCD <> 'LC'
and a15.NEXT_STATUS_SRCCD = '999'
and a14.LAST_STATUS_SRCCD in ( '620','914') )
group by a12.DATE_INVOICE_ID,
a11.CUSTOMER_ID
OPTION ( FORCE ORDER )
PLAN without force order:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID 1 1 0 NULL NULL 1 NULL 1.138269 NULL NULL NULL 2.716851 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.138269 0 1.138269E-07 66 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END) [Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1023], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.17323 0.01126126 0.0001004628 41 2.71685 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID])) 1 6 5 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID]) NULL 1.17323 0 8.671883E-06 41 2.705488 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 7 6 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 2.074613 0 8.671883E-06 41 2.702026 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 8 7 Nested Loops Inner Join OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 2.074613 0 8.671883E-06 45 2.697204 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | |--Stream Aggregate(GROUP BY[a11].[FSL_SEQ_NO]) DEFINE[a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]))) 1 9 8 Stream Aggregate Aggregate GROUP BY[a11].[FSL_SEQ_NO]) [a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) 2.074613 0 0.1950575 49 2.692634 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED) 1 10 9 Nested Loops Inner Join OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED NULL 390113 0 1.630672 53 2.497577 [a11].[FSL_SEQ_NO], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 10 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 2.074613 0 9.189784E-06 53 0.0406176 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 2.198513 0 0.0001337915 57 0.03713583 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 14 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.00104035 66 0.02786905 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 15 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 0 32.00753
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 16 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003472585 NULL NULL PLAN_ROW 0 2.198513
| | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD) 1 17 10 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 188041.3 0.2616435 0.2070025 11 0.8262868 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 2.074613
| | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 31 8 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_SRCCD] 1 0.003125 0.0001581 14 0.004555401 .[ORDER_TYPE_SRCCD] NULL PLAN_ROW 0 2.074613
| |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 32 7 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.004812614 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 0 2.074613
|--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 33 6 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.003452996 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 2.074613
PLAN WITH FORCE ORDER:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID OPTION ( FORCE ORDER ) 1 1 0 NULL NULL 1 NULL 1.08425 NULL NULL NULL 9.249098 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.08425 0 1.08425E-07 66 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END) [Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1035], [Expr1036], [Expr1037], [Expr1038] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.103783 0.01126126 0.0001002863 41 9.249096 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Parallelism(Gather Streams) 1 6 5 Parallelism Gather Streams NULL NULL 1.103783 0 0.02850539 41 9.237735 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID])) 1 7 6 Merge Join Inner Join MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) NULL 1.103783 0.0004695 0.001520579 41 9.209229 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Sort(ORDER BY[a11].[ORDER_TYPE_ID] ASC)) 1 8 7 Sort Sort ORDER BY[a11].[ORDER_TYPE_ID] ASC) NULL 1.103783 0.002815315 2.507257E-05 45 0.09645625 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Bitmap(HASH[a11].[ORDER_TYPE_ID]), DEFINE[Bitmap1034])) 1 9 8 Bitmap Bitmap Create HASH[a11].[ORDER_TYPE_ID]) [Bitmap1034] 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[a11].[ORDER_TYPE_ID])) 1 10 9 Parallelism Repartition Streams PARTITION COLUMNS[a11].[ORDER_TYPE_ID]) NULL 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 11 10 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 1.103783 0 4.031072E-06 45 0.06510919 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 11 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 3.857486 0 3.344787E-05 49 0.06137029 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 32.00753 0 3.344787E-05 53 0.05315145 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH) 1 14 13 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH NULL 32.00753 0 3.344787E-05 57 0.03554453 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 16 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.0002600876 62 0.02708879 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 17 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.008185391 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 32.00753
| | | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 18 13 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.01755811 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 1 32.00753
| | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 19 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 1 32.00753
| |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 20 11 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003734868 NULL NULL PLAN_ROW 1 3.857486
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE)) 1 21 7 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE) NULL 20.5 0 0.0285224 11 9.11078 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID])) 1 22 21 Merge Join Inner Join MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]) NULL 20.5 0 0.001586888 11 9.082257 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC)) 1 23 22 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC) NULL 328 0 0.03033649 11 0.03529974 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
| |--Clustered Index Scan(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 24 23 Clustered Index Scan Clustered Index Scan OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_ID] 328 0.004606482 0.00013165 18 0.004738132 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 25 22 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 27 0 1.6875E-05 11 9.045368 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC)) 1 26 25 Parallelism Repartition Streams PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC) NULL 108 0 0.0291047 11 9.045351 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 27 26 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 108 0 0.634653 11 9.016247 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Index Scan(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD) 1 28 27 Index Scan Index Scan OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 5077116 6.985347 1.396246 11 8.381594 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
View 4 Replies
View Related
Aug 30, 2007
I have a row that is being used log track plays on our website.
Here's the table:
CREATE TABLE [dbo].[Music_BandTrackPlays](
[ListenDate] [datetime] NOT NULL DEFAULT (getdate()),
[TrackId] [int] NOT NULL,
[IPAddress] [varchar](20)
) ON [PRIMARY]
There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.
I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:
CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount]
ON [dbo].[Music_BandTrackPlays] AFTER INSERT
AS
UPDATE
Music_BandTracks
SET
Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount
FROM
(SELECT TrackId, COUNT(*) AS PlayCount
FROM inserted
GROUP BY TrackId) AS TP
WHERE
Music_BandTracks.TrackId = TP.TrackId
When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.
How exactly is the pseudo 'inserted' table formed?
For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.
I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?
Thanks!
View 6 Replies
View Related
Jun 13, 2007
I have spent the afternoon researching this, and to no avail so here's my question.
I have a user input form with upwards of 60 fields that are to be filled in by the user (intranet environment). I want to post those values as a new record in an SQL database. I have setup an SQLDataAdapter tied to a parameterized stored procedure on the SQL server. I can successfully post data from the controls on the form when I set the parameter type to Control based.
Here's the problem, though. I need to do some pre-processing on most of the values in the controls on the form. I have setup VB code (that executes when the user clicks the "Submit" button), that pulls in each control value, performs any necessary processing, and sets a variable to the value I actually want stored in the DB. How can I assign THAT value (from the variable) to the parameter for the stored procedure?
Thanks,
Dave
View 5 Replies
View Related
Feb 28, 2007
Hi,
i am using vs2005 and microsoft SQL server mobile edition (.NET Framework Data Provider for SQL Server CE) to develop an application for wm5.0 pocket pc in language C# and now, i'm facing a problem on how to insert a new data into .sdf file with TableAdapter?example, i have a combo box and a button (known as AddButton) on my form1.cs, when i click the button, all the data from combo box will automatic insert into a database. How to write the codes for this? i tried for a few methods to wrote it and it seems do not work.Does anybody know how to write it?Thank you.
View 3 Replies
View Related
Apr 6, 2006
hi,
i created a query to insert a row in DataSet in Visual Studio 2005. i gave the method name to the query i created. as i understood it returns '1' if successful or '0' if not.
is it possible to get the ID or the row instead?
View 7 Replies
View Related
Feb 18, 2008
hi, i am trying to insert my values into the database, however the code i have doesnt seem to work. i have looked at old posts, one suggested to take away my code behind code where the insert method has been written. i did try this but it does not seem to work, can some please help me sort out this problem and advice or examples of what i need to do will be very much appreciated, thank you. the following is the code i have
code behind code
'Save vlues into database.
If IsPostBack = False ThenDim test As SqlDataSource = New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
test.InsertCommand = "INSERT INTO [UserQuiz] ([QuizID], [DateTimeComplete], [CorrectAnswerCount], [UserName]) VALUES (@QuizID, @DateTimeComplete, @CorrectAnswerCount, @UserName)"test.InsertParameters.Add("QuizID", Session("QuizID").ToString())
test.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString())test.InsertParameters.Add("CorrectAnswerCount", "12")test.InsertParameters.Add("UserName", User.Identity.Name)
test.Insert()
End If
when i run the program i get this error
Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
Line 26: test.InsertParameters.Add("UserName", User.Identity.Name)
Line 27:
Line 28: test.Insert()
Line 29:
Line 30: End If
View 4 Replies
View Related
Mar 28, 2008
Hi there,
i have a problem with my smart device application that i develop.
To simplify:
1) C# smart device appliccation
2)a database file *sdf
3)a typed dataset using the wizard to do so
4)TableAdapter with connection string with no problems(cool)
5)use of Insert method that returns 1 ( also cool) [--> MyTableAdapter.Insert("blabla","blablabla"); <------]
6)no saved records in the DB file(baaaaaad)
Plz any help would be appreciated.
Dimoklis
Software Developer
View 9 Replies
View Related
Aug 1, 2006
Hi,
I want to write one function like that
dbo.function( number , 1 , 2 ) ,
but I would like to overload, and send char or number
dbo.function( number, 'abx' , ' xpto' ).
I would like to keep the same name, Can I do this? or Do I need to write to differents functions
thanks,
View 11 Replies
View Related
Jul 20, 2005
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
View 3 Replies
View Related
Feb 13, 2008
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
View 1 Replies
View Related
Oct 25, 2006
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
View 2 Replies
View Related
May 30, 2013
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies
View Related
Mar 23, 2006
Hi
When I try to print a very simple report with rs2005 via the print button on the http://servername/report/pages/... website, it says "printing" but nothing happend. When I look at the server (win2003 SP1 + latest hotfixes) the www and sql2005 (SP1 CTP installed) services eat up 100% cpu for ever.
I get no error messages neither in the rs log's nor in os application or system log. With profiler I found out, that there must be some loop. The following stored procedure exec DeletePersistedStreams @SessionID='24iumm55mp32l2ie2nezs0y0'is called about 650 times per second, which explain the load. The only way to stop this, is shut down and restart the www service.
This behaviour can be found with all reports.
The call, view and export of the same reports to pdf, xls, ... works without problems.
Any ideas?
Thanks for your help
Peter
View 6 Replies
View Related
Sep 21, 2007
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
View 8 Replies
View Related
Dec 15, 2006
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View 1 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we dont log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 Replies
View Related
Mar 3, 2006
Hi,I'm trying to store values from a session to sql server table. I created an sql stored procedure to pass the values into the database. For some reason when I'm running the website and trying to store value from session to database an error is being displayed with the following message "Procedure or function OrderFromCart has too many arguments specified". OrderFromCart is the name of the stored procedure. Any Ideas??ThanksInter FC
View 11 Replies
View Related
Apr 6, 2006
My stored procedure: ALTER PROCEDURE [dbo].[ShortUpdateSurvey]
@s_survey_id int,
@s_survey_num varchar(10),
@s_job_address varchar(30),
@s_job_city varchar(20),
@s_client_name varchar(50),
@s_date_ordered datetime,
@s_date_required datetime,
@s_amount money
AS
SET NOCOUNT OFF;
UPDATE [survey_master]
SET
[survey_num] = @s_survey_num,
[job_address] = @s_job_address,
[job_city] = @s_job_city,
[client_name] = @s_client_name,
[date_ordered] = @s_date_ordered,
[date_required] = @s_date_required,
[amount] = @s_amount
WHERE [survey_id] = @s_survey_id My ASP code:
<asp:BoundField DataField="survey_id" HeaderText="Survey ID" SortExpression="survey_id" />
<asp:BoundField DataField="survey_num" HeaderText="Survey Num." SortExpression="survey_num" />
<asp:BoundField DataField="job_address" HeaderText="Address" SortExpression="job_address" />
<asp:BoundField DataField="job_city" HeaderText="City" SortExpression="job_city" />
<asp:BoundField DataField="client_name" HeaderText="Client" SortExpression="client_name" ReadOnly="True" />
<asp:BoundField DataField="date_ordered" HeaderText="Ordered" SortExpression="date_ordered" DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="date_required" HeaderText="Needed" SortExpression="date_required" DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="amount" HeaderText="Amount" SortExpression="amount" />
...
SelectCommand="ShortAddressSearch"
SelectCommandType="StoredProcedure"
UpdateCommand="ShortUpdateSurvey"
UpdateCommandType="StoredProcedure">
...
<UpdateParameters>
<asp:Parameter Name="s_survey_id" Type="Int32" />
<asp:Parameter Name="s_survey_num" Type="String" />
<asp:Parameter Name="s_job_address" Type="String" />
<asp:Parameter Name="s_job_city" Type="String" />
<asp:Parameter Name="s_client_name" Type="String" />
<asp:Parameter Name="s_date_ordered" Type="DateTime" />
<asp:Parameter Name="s_date_required" Type="DateTime" />
<asp:Parameter Name="s_amount" Type="Decimal" />
...
and once again, the error message:
"Procedure or function ShortUpdateSurvey has too many arguments specified."
TIA
View 3 Replies
View Related
Sep 13, 2006
Can someone help me with this? For the life of me, I cannot figure out why I recieve this error: Procedure or function usp_ContactInfo_Update has too many arguments specified.Here is my code: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="usp_ContactInfo_Select" SelectCommandType="StoredProcedure" UpdateCommand="usp_ContactInfo_Update"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:Parameter Name="Division" Type="String" /><asp:Parameter Name="Address" Type="String" /><asp:Parameter Name="City" Type="String" /><asp:Parameter Name="State" Type="String" /><asp:Parameter Name="Zip" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="TollFree" Type="String" /><asp:Parameter Name="Fax" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Res" Type="Boolean" /><asp:Parameter Name="Res_Rec" Type="Boolean" /><asp:Parameter Name="Com" Type="Boolean" /><asp:Parameter Name="Com_Rec" Type="Boolean" /><asp:Parameter Name="Temp" Type="Boolean" /><asp:Parameter Name="Temp_Rec" Type="Boolean" /><asp:Parameter Name="Port" Type="Boolean" /><asp:Parameter Name="Land" Type="Boolean" /><asp:Parameter Name="Drop" Type="Boolean" /></UpdateParameters><SelectParameters><asp:ProfileParameter Name="DivID" PropertyName="Division" Type="String" /></SelectParameters></asp:SqlDataSource>Here is my Stored Proc:ALTER PROCEDURE dbo.usp_ContactInfo_Update @Division varchar(1),@Address varchar(50),@City varchar(50),@State varchar(2),@Zip varchar(10),@Phone varchar(20),@TollFree varchar(20),@Fax varchar(20),@Email varchar(50),@Res bit,@Res_Rec bit,@Com bit,@Com_Rec bit,@Temp bit,@Temp_Rec bit,@Port bit,@Land bit,@Drop bit/*(@parameter1 int = 5,@parameter2 datatype OUTPUT)*/AS/* SET NOCOUNT ON */ UPDATE tblDivisionSET Division = @Division, Address = @Address, City = @City, State = @State, Zip = @Zip, Phone = @Phone, TollFree = @TollFree, Fax = @Fax, Email = @Email,Residential = @Res, Residential_Recycling = @Res_Rec, Commercial = @Com, Commercial_Recycling = @Com_Rec, Temp = @Temp, Temp_Recycling = @Temp_Rec, Portable_Restrooms = @Port, Landscaping = @Land, Drop_Off_Center = @DropWHERE (Division = @Division) RETURNAny Help is greatly appreciated!
View 2 Replies
View Related
