|
This is not a question!
I got caught by the parameter sniffing[^] issue the other day, it was a classic, I had built a proc that takes in 2 parameters, applies then against a bunch of tables and inserts the results into a table variable to return a summary of the current periods processing status.
It worked perfectly during development, ran like a dream (<10sec) during the first round of UAT. Then it spat the dummy and I was getting a timeout from the service, the procedure ran perfectly from SSMS but from the client it just refused to returned a result.
3 hours later having exhausted all probabilities the little bulb clicked, USE LOCAL VARIABLES. I was applying the parameters multiple times to different tables, by changing to a pair of local variables this fixed the problem, classic case of parameter sniffing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am having an issue with the selection list, which does not allow a selection of more than 1000 rows.
Like if I am trying to select more than 1000 rows, maybe the customer numbers, it does not allow more than 1000 rows. It comes back with this error message,
SQL Error: ORA-01795: maximum number of expressions in a list is 1000 .
I tried increasing the SQL array fetch size, and also increase the fetch size on the property window of SQL Developer but with no luck.
any help is much appreciate.
I need to update 4 million rows.
Thanks!!!
|
|
|
|
|
I don't think it is related to the row count.
Google the error message and you will find numerous descriptions and workarounds.
|
|
|
|
|
I bet you are building your query like this ...
select xx from myTable where ID in (val1,val2,val3)
The problem is that Oracle has a limit of 1000 values in their "in" clause. For example you can not have val1,val2,...val1001, Oracle will throw an error.
You need to restructure your "where" clause.
A possible solution would be to create a temp table and insert those selected values into the table, then do a join to the temp table.
Somthing just doesn't sound right if you are selecting 4 million rows, but you haven't give much information on the problem. Maybe more info will lead to a better solution.
Good luck. 
|
|
|
|
|
Not selecting but updating a value on 4 million rows.
Anyways, it is a limitation on the sql "in" clause select. You cannot have more than 1000 lines in a sql select.
I will store the data in temp table and join to it.
|
|
|
|
|
My bad for not seeing that you are updating, not selecting. Oops.
Good to know that you have a solution.
|
|
|
|
|
I have a single table called customers that stores all customers information. It has only one primary id field, which is identity. At times it happens duplicate records are inserted. But since the primary ID is the different, the records are actually different. There is no other field in the table that I can make unique.
What is the best way to introduce data integrity in the database so that duplicate records can not be inserted in the table. Note that table has more than 80 fields.
|
|
|
|
|
If you cannot make any other single column unique, then you need to make a combination of columns unique. For instance, you could choose to reject entries that have the same name that are at the same address. The problem with this approach is that you will have to decide how close the selection would be i.e would johnson PLC,e 123 Albert st be the same as Johnson Private Limited Company, 123 albert street. That is where your requirements and business process will need to come into play.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Well this would be interesting if I can do what you mention. I can use it for other purposes and will be helpful to me.
But in my situation, I can not make a combination of columns unique. Things are like
1. Customer are renewed so the data can be very similar to each other.
2. Some honest mistakes can occur where the same data is added twice. The user can figure it out but I want to stop it from the db end.
I am thinking I should change an existing field unique or add a new field which would be unique.
|
|
|
|
|
sharp_k wrote: But in my situation, I can not make a combination of columns unique.
The table represents a "customer". There are going to be columns in there, not all 80, which defines what a unique "customer" is.
You CANNOT procede until you determine which columns make it unique.
If there a few columns then you can add a uniqueness constraint.
But lets say you have a 'lot' of columns, like 50 columns, then you are probably out of luck for easy solutions because it is unlikely that you can add a uniqueness constraint for that many columns.
In that case you would need to wrap ALL access to table in a proc. The proc would verify, via a query, that no other record existed with those 50 columns before the insert. Views can often help with this.
You also need to consider exactly how those records get added. Because now the system is going to start producing errors where it didn't produce errors before.
Additionally if you have a 'lot' of columns which make it unique then for something called a "customer" I would think that there is a design problem.
|
|
|
|
|
Like others have said, you need to normalize your database.
When a customer is renewed you don't get a new customer and should therefore not get a new entry in the customer table, but rather a new entry in the subscription table or customer history table or whatever table makes most sense to your system.
As we don't know much about you database we can only give you generalized advice. Mine would be to read this[^] article.
It describes in an easy to understand way how to, and why, you normalize your database.
|
|
|
|
|
One way to stop duplicate records being inserted is to check and see if a record for a customer already exists before inserting the record.
For example, something along the lines of:
if (not exists(select top 1 1 from Customers where Field1 = @Param1 and Field2 = @Param2 ... and so on))
begin
insert into Customer (...)
values (...)
end
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
..should be wrapped in a transaction to prevent race-conditions, wouldn't it?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Indeed it could.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
sharp_k wrote: it happens duplicate records are inserted
How do you determine that?
To go along with what the others said, you can query the table for similar entries and present them to the user, e.g.:
You entered "John Smith at 123 Main St", is this the same customer as "John Smith at 123 North Main Street"?
|
|
|
|
|
Learn to normalize. You want one artificial key for quick access, that's the identity. Normalization gets you the combination of fields that make the record unique, which depends on the data being described.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I am getting the above error when I upload my website to host but it's working perfectly on my local PC although both are connected to the same database!
here is the error followed by the code:
Object reference not set to an instance of an object
[NullReferenceException: Object reference not set to an instance of an object.]
rmconline.salary_slip.btnGenerate_Click(Object sender, EventArgs e) in C:\Users\Jassim\documents\visual studio 2010\Projects\rmconline\rmconline\salary_slip.ascx.cs:70
DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +96
DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +540
DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
and here is my code:
protected void btnGenerate_Click(object sender, EventArgs e)
{
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_get_salary_slip", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
sql_command.Parameters.AddWithValue("param_is_exist", MySqlDbType.Bit).Direction = ParameterDirection.Output;
sql_reader = sql_command.ExecuteReader();
sql_reader.Read();
if (sql_reader.HasRows)
{
lblMonth.Text = String.Format("{0} {1:0000}", cboMonth.Text, txtYear.Value);
lblSalary.Text = "BD " + Convert.ToDouble(sql_reader["salary_slip_salary"]).ToString("#,###,##0.000");
panelNoSalarySlip.Visible = false;
panelSalarySlip.Visible = true;
sql_connection.Close();
populate_salary_slip_details();
}
else
{
panelSalarySlip.Visible = false;
panelNoSalarySlip.Visible = true;
}
}
private void populate_salary_slip_details()
{
data_table = new DataTable();
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_populate_salary_slip_details", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_employee_number", MySqlDbType.Int32).Value = Convert.ToInt32(Session["employee_number"]);
sql_command.Parameters.Add("param_salary_slip_month", MySqlDbType.Int32).Value = Convert.ToInt32(cboMonth.Value);
sql_command.Parameters.Add("param_salary_slip_year", MySqlDbType.Int32).Value = Convert.ToInt32(txtYear.Value);
sql_adapter = new MySqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataSalarySlip.DataSource = data_table;
dataSalarySlip.DataBind();
}
|
|
|
|
|
jrahma wrote: sql_adapter = new MySqlDataAdapter(sql_command);
the above you should change to include you connection object i.e.
sql_adapter = new MySqlDataAdapter(sql_command, sql_connection);
further reading MySQL DataAdapter[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
jrahma wrote: [NullReferenceException: Object reference not set to an instance of an object.]
rmconline.salary_slip.btnGenerate_Click(Object sender, EventArgs e) in C:\Users\Jassim\documents\visual studio 2010\Projects\rmconline\rmconline\salary_slip.ascx.cs:70
DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e) +96
DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String eventArgument) +540
Running a debug build rather than a release build would yield actual file name, class name AND line numbers for where the exception exactly gets thrown; from there it is a matter of seconds to pinpoint the problem.
|
|
|
|
|
Hi,
I have two MySQL servers.. one is sourceMySQL and targetMySQL.
I want to update all the tables but WITHOUT the data on the targetMySQL using sourceMySQL without having to do it one by one so it will only update the table structure...
Is there anyway to say:
UPDATE all table structure IN targetMySQL FROM
|
|
|
|
|
Your best bet would be to get a copy of MySql workbench MySQL Workbench Downloads[^], then under Server Administration -> Data Dump -> Advanced Export Options, you can select the No Data option, and this will give you a script which will contain all the Create Table statements for the selected database, without data. You can also select to include all stored procedures in the same script.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Hi,
I have two MySQL servers.. one is sourceMySQL and targetMySQL.
I want to update all the procedures on the targetMySQL using sourceMySQL without having to do it one by one.
Is there anyway to say:
UPDATE all procedures IN targetMySQL FROM sourceMySQL?
|
|
|
|
|
Suppose in my SQL2005 database accounting is used in LAN, their copy two files: data1.mdf and data1_log.LDF another folder and renamed data2_log.LDF data2.mdf and, then, di transfer these 2 files into the same directory that contains the original accounting database when attach data2.mdf file into SQL2005 error: Can not attach a database with the same name as an existing database there. While I have renamed then why not attach files? want to attach the data file do?
|
|
|
|
|
let me guess, you rename it from windows explorer, am i right?
it's okay, before you're going to press ok (proceed attach), at top of the form, there's "Attach As" column, change your database name below, good luck! 
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
