No Overload For Method 'FillByEmployeeID' Takes '2
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 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);
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}
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"); } } }
... 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)
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.
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.
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.
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.
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
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
Hello everybody I created stand by server and do restore of database 'Alex' with standby every 15 min database 'Alex' on stand by server is set to read only I allow user to run queries using database 'Alex' on stand by server
I noticed in my job history foolwing
Job started job run ---------- ------- 10:00:00 00:01:00 10:30:00 00:19:23 --- at this time users did select on stand by server 11:00:00 00:02:00
So I am mising the job at 10:45:00 but I did not get any errors and then job goes back to every 15 min
what happen to job at 10:45:00 did it run at 11:00 ? job created from DTS and include a) back of transaction log from production database b) moving log stand by server c) restoring log "with stand by" on stand by server
What is best solution if I want used stand by server for reports ?
hi, this stored procedure below takes 25 mins to execute. is this normal? and is there a faster way to do this? its not like i have millions of records, i only have about 170.
Create Procedure sp_PayrollComplete
AS declare @weDate nvarchar(10)
Select @weDate= we FROM cbetts.tblArchive WHERE we = (select max(convert(datetime,we)) from cbetts.tblarchive)
Declare payroll_cursor CURSOR FOR SELECT distinct userid FROM cbetts.v_PayrollTotals where (we = @weDate) ORDER BY userid
OPEN payroll_cursor
-- Perform the first fetch. FETCH NEXT FROM payroll_cursor into @uid -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN
select @location=location from cbetts.v_payrolltotals where userid = @uid and we = @weDate select @name = name from cbetts.v_payrolltotals where userid = @uid and we = @weDate select @Direct = sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and employeetype='direct' and left(jobno,3) != '900' select @LBO = sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and employeetype='LBO' select @g1099 = sum(totals)from cbetts.v_payrolltotals where userid = @uid and we = @weDate and employeetype='1099' select @agency = sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and employeetype='Agency' select @nonbill= sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and left(jobno,3)='900' select @holiday = sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and (jobno='90010' or jobno='90011') select @vacation = sum(totals) from cbetts.v_payrolltotals where userid = @uid and we = @weDate and jobno='90020'
I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:
PROCEDURE NAME IS SPTOPTWENTYUSERS
SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE GROUP BY STRUSERNAME ORDER BY INTDOWNLOAD DESC
The code that runs it is as follows:
sSQLString = SPTOPTWENTYUSERS Using cnn As New SqlConnection(GetPath) Try Dim cmd As New SqlCommand(sSQLString, cnn) Dim dr As SqlDataReader
With cmd .CommandType = CommandType.StoredProcedure .CommandTimeout = 0 .Parameters.Add("@BEGINDATE", SqlDbType.DateTime) .Parameters.Add("@ENDDATE", SqlDbType.DateTime) .Parameters("@BEGINDATE").Value = dtpStartDate.Value .Parameters("@ENDDATE").Value = dtpEndDate.Value End With cnn.Open() dr = cmd.ExecuteReader
Any help on why this happens would be much appreciated.
Hi, Is there any way to audit or record in SQL Server 2000 what queries are the ones that consume more resources in the server so I can focus and improve them?
I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
We have a re-indexing all DBs schedule job in our SQL 2000 box,normally it took 7 hours to complete but all of the sudden now ittakes more than 20 hours.What do you think it cause this problem? We have no clue.
HiI a am executing a stored procedure from C code using SQLExecute . Ittakes some time to Execute the procedure. However when I see theprocessor usage during that time it shows 100% cpu usage.Can anyone tell me why SQLExecute uses 100% CPU when running thestored proceudre.Thanksvishal
An attempt to attach an auto-named database for file C:UsersCodeFreakDocumentsVisual Studio 2005WebSitesPersonalSiteApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I've tried allowing permissions I'VE EVEN TRIED ALLOWING EVERYONE! I've tried right clicking the datasource under the datasource explorer and clicking 'detatch.' It works Locally but fails when i pusblish with IIS. Whats with these SQL databases that just dont want to work with IIS 7 ?
Published with: IIS 7
I've tried removing the "user instance."
This is User roles and all that which uses SQL Server 2005.
So my question is, How do i publish a website with SQL Server 2005 database?
Please give it to me step by step as im still quite a newbie.
Dear all, I have MsAccess97 database. The size of this database is 450 MB, the same database i converted into SQL Compact Editon database. But the size reaches to 1.35 GB even after i did compact process. It reduces to 1.33 GB. I don't know why it increases from 450 MB to 1.3 GB? anybody knows the reason please reply then how do i reduce the size? waiting for the reply. Thanks Gops India gopalan@sofist.com
I could use a little help here. We have a stored procedure that runs on SQL2000 and for a large dataset only takes 1-2 minutes. On SQL2005 however, it takes around 25 minutes. Any advice or insight anyone could give would be great.
Here's the stored procedure:
CREATE PROCEDURE daa_upd_relationship_balance_hist AS begin tran insert fldarts..daa_relationship_bal_hist select <-- list snipped --> from daa_relationship_bal drb, daa_user_review dur where drb.acct_no = dur.acct_no and drb.control_2 = dur.control_2 and drb.nb_gl_cost_ctr = dur.nb_gl_cost_ctr and drb.nb_dda_sav_type = dur.nb_dda_sav_type and drb.acct_no+drb.control_2+drb.nb_gl_cost_ctr+drb.nb_dda_sav_type+convert(char(10),dur.activity_date, 101) not in (select acct_no+control_2+nb_gl_cost_ctr+nb_dda_sav_type+convert(char(10), activity_date, 101) from fldarts..daa_relationship_bal_hist) if @@error = 0 commit tran else begin rollback tran print '!!!Error (daa_relationship_bal_hist) : Relationship Balance History not updated' end return GO
So we have three tables. Here's a schema for each and the indexes on them. I've omitted columns from the tables that are not utilized in this query.
daa_relationship_bal:
CREATE TABLE [daa_relationship_bal] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (3) NOT NULL )
index:
idx_upd_balance_hist nonclustered located on PRIMARY acct_no, control_2, nb_gl_cost_ctr, nb_dda_sav_type
daa_user_review:
CREATE TABLE [daa_user_review] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (1) NOT NULL , [activity_date] [datetime] NULL )
index:
PK_daa_user_review_1__37 nonclustered, unique, primary key located on INDEXES control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type
daa_relationship_bal_hist:
CREATE TABLE [daa_relationship_bal_hist] ( [control_2] [char] (3) NOT NULL , [nb_gl_cost_ctr] [char] (7) NOT NULL , [acct_no] [char] (14) NOT NULL , [nb_dda_sav_type] [char] (3) NOT NULL , [activity_date] [datetime] NOT NULL )
index:
PK_daa_rel_bal_hist_1__37 nonclustered, unique, primary key located on PRIMARY control_2, nb_gl_cost_ctr, acct_no, nb_dda_sav_type, activity_date
Any help on this would be great. If more information is needed, please let me know.
I have a package which updates a table which contains a large amount of rows, when this package is runnning the process consumes 100% CPU and other services are crashing (SSAS, SPS, SSRS).
Is there any parameter which could be set to avoid SSIS to consume 100% of the CPU during the execution of this package ?
I've restored a database by the standard menu (the backup was a .bak file).
It worked really well, but now I can't use the database, but there is a "(Restoring...)" standing to the right of the database-name in the sql server management studio. I thought it should work like this and therefore was sitting and waiting, but hous later the status didn't change.
Hi, cube processing is taking more time in a new server while same cubes takes less time in another server. the cubes are processed through DTS package can anybody help finding out the possible reasons for this. Regards Naseem
We have a MS SQL Server 6.5 database table with 643,000 records. There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it takes 45 minutes to return. There is no indication of what the server is doing during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it? DBCC CheckTable finds no errors.
name rows reserved data index_size unused -------------------- ----------- ------------------ ------------------ ------------------ ------------------ WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY ======= CREATE VIEW HISTORY AS SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION FROM LOCAUDIT
LASTHISTORY ========== CREATE VIEW LASTHISTORY AS SELECT CODE, Max(UpdateDateTime) AS LastUpdated FROM HISTORY GROUP BY CODE
UPDATE RETAIL SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END) FROM RETAIL AS t4 LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE) LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime) LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
Recently my system encounter some problem when retrieving certain record from MSSQL. For an example i have a database which contains 1.5 million of members. so i have a perl scripts that will execute to query based on certain range.
the schedule like below: 1 script - 1-250k (Query finish less than 5 mins) <interval 5 mins> 1 script - 250k-500k (Query finish less than 5 mins) <interval 5 mins> 1 script - 500k-750k (Query finish less than 5 mins) <interval 5 mins> 1 script - 750k-1M (Query finish in 1++ hours) <interval 5 mins> 1 script - 1M-1.25M (Query finish in 1++ hours) <interval 5 mins> 1 script - 1.25M-1.50M (Query finish in 1++ hours) END
After the 4th query, the query seems to work very slow, and this problem only raise on windows 2003 with mssql 2005, current server that run smoothly is win2k with mssql2000.
anyone have any idea on this problem either cause by operating system and database or related to something else?
Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int PK_table1 on Year,Quarter,Hits
If i do a "Select Year From table1 group by Year", the executionplan looks like
select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01 |--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?
select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL |--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01 |--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11